Project Practice 6-mysql enterprise-level log management, backup and recovery practices, mysql enterprise-level

Project Practice 6-mysql enterprise-level log management, backup and recovery practices, mysql enterprise-level

 

Mysql implements enterprise-level log management, backup, and recovery

　　Environment background:With the development of business, the company's business and scale continue to expand, the website has accumulated a large amount of user information and data, for an Internet company, users and business data is the foundation. Once the company's data goes wrong or is lost, it is a disaster tolerance for Internet companies. To prevent data loss caused by system errors or system faults, the company needs to enhance the reliability of user data, it is required that data-level backup be fully strengthened and be restored immediately when a fault occurs.

Overall Architecture, See http://www.cnblogs.com/along21/p/8000812.html

Mysql backup solution:

①Mysqldump + binlog:

Complete backup first, and then Incremental Backup through backup of binary logs

② Xtrabackup:

For InnoDB: Hot Standby, full backup and Incremental Backup are supported

For MyISAM: Warm Standby, only full backup is supported

③ Lvm snapshot + binlog:

Almost hot backup, physical backup

Practice 1: mysqldump + binlog backup and recovery 1. Prepare the Backup Directory and enable binary log

Mkdir/backup

Chown-R Mysql. mysql/Backup/change the group of the backup Directory to mysql

Vim/etc/my. cnf

Log-bin =/var/lib/mysql/bin-log enable binary logs and specify the path

2. Prepare the data and tables to be backed up.

Simulate routine database operations

MariaDB [(none)]> create database along; create an along table

MariaDB [along]> create table home (id int not null, name char (20); create a home table

MariaDB [along]> show master status; query the binary file, where the number is the text

3. Complete backup

All databases can be backed up.

Mysqldump-- All-databases-- Flush-log>/backup/mysql-All-Backup-'Date + % F-% t'. SQL

You can also back up a specified database.

Mysqldump-- Database along-- Flush-log>/backup/mysql-along-backup-'date + % F-% t'. SQL

Command analysis:

Mysqldump[OPTIONS]-- Databases[OPTIONS] DB1 [DB2 DB3. ..]: Back up one or more databases

OPTIONS:

-- Lock-all-tables:LockAll Tables

-- Lock-tables: lock the backup table

-- Single-transaction: Start a large single transaction to implement backup

-- Compress: Compressed Transmission

-- Events: Back up the event scheduler of the specified database

-- Routines: Backup stored procedures and storage functions

-- Triggers: Backup trigger

-- Master-data= {0 | 1 | 2}

0: no record

1: records the change master to statement. This statement is not commented out.

2: record as a comment statement

-- Flush-logs:After the table is lockedRun the flush logs command to generate a new binary log.

4. insert data into the table

Simulate routine operations

MariaDB [along]> insert into home values (1, 'mayun ');

MariaDB [along]> insert into home values (2, 'mahuateng ');

5. Perform Incremental backup to back up binary logs

Principle: To perform Incremental backup, you must first knowStartAnd then back up the data.

(1) query start

① Start with a database command query,Recommendation

MariaDB [along]> show master status; query the currently used binary log

MariaDB [along]> show binlog events in 'bin-log.htm 14'; query binary logs with numbers in them

② You can also query through the command line

Cd/var/lib/mysql/

Bin-log.000014 for mysqlbinlog

(2) ProceedIncremental Backup

Cd/var/lib/mysql/

Mysqlbinlog -- start-position = 314 -- stop-position = 637 bin-log.000014>/backup/mysql-along-backup-add-'date + % F-% t'. SQL

Note:The starting number must be pushed forward and backward.For example, 412 is used to execute the INSERT command. It must be backed up from the previous code 314.

6. Continue to insert data, delete the database without backup, and simulate misoperations.

① Continue daily operations

MariaDB [along]> insert into home values (3, 'hangzhou ');

② The along database is deleted by mistake, and the backup has not been completed in the last day.

MariaDB [along]>DropDatabase along;

Hold on at this time, don't panic, and start recovery below

7. Data Recovery

① Because the database is deleted without a backup, we need to first protect the last binary log and view the position number before the deletion operation.

MariaDB [(none)]> show binlog events in 'bin-log.htm 14 ';

② Backup operations that have not been completed

Cd/var/lib/mysql/

Mysqlbinlog -- start-position =706-- Stop-position =837Bin-log.000014>/backup/mysql-along-backup-add-'date + % F-% t'. SQL

8. All backups before import

① View our backup directory

② Import all backups in order

Full backup ---> Incremental Backup

Mysql-uroot-p<Mysql-along-backup-2017-11-16-16 \: 45 \: 22. SQL

Mysql-uroot-p<Mysql-along-backup-add-2017-11-16-17 \: 15 \: 25. SQL

Mysql-uroot-p<Mysql-along-backup-add-2017-11-16-17 \: 27 \: 50. SQL

9. Check the database and data and restore the database.

Practice 2: xtrabackup implements backup and cache 1. Install xtrabackup

Yum-y install xtrabackup

For permission management, you can also create a minimum permission backup user. For experiment convenience, I do not have

2. Full backup

① Full backup

Innobackupex -- user =Root/backup/Full backup: A directory is generated after the backup.All the database data is included.

If the user with the permission is set: innobackupex -- user = bakupuser -- password = bakuppass/backup/

Note: grant the mysql permission to the backup directory recursively.

ChownMysql. mysql/Backup/2017-11-16_17-57-57/-R add permissions to the generated directory

 

② The Directory generated based on the full backup can also restore Data

Datadir =/Backup/2017-11-16_17-57-57 direct the directory to the backup directory

Systemctl restart mariadb restart the service

③ Check the data, no changes, consistent data

3. Incremental Backup

① Add data and perform routine operations

MariaDB [along]>InsertHome values (4, 'dinglei'), (5, 'liyanhong ');

② Incremental Backup

Innobackupex -- user = root -- incremental/backup/-- incremental-basedir =/backup/2017-11-16_17-57-57 incremental backup Based on/backup/2017-11-16_17-57-57

Directory generated for Incremental Backup

4. Data Recovery preparation: "replay" and "rollback"

Principle: Generally, after the backup is complete,The data cannot be used for restoration.Because the backup data may containUncommitted transactionsOrSubmitted but not synchronized to dataFileTransactions

① On each backup (including full and Incremental Backup,Replay committed transactions". After "replay", all the backup data will be merged to the full backup.

②Roll back uncommitted transactions based on all backups".

Innobackupex -- apply-log-- Redo-only/Backup/2017-11-16_17-57-57/full backup data recovery preparation

Innobackupex -- apply-log-- Redo-only/Backup/2017-11-16_20-14-05/-- incremental-dir =/backup/2017-11-16_20-37-40/prepare for incremental backup Data Recovery

5. misoperation to restore Data

Mv/var/lib/mysql. bak simulates accidental deletion of database storage files

Mkdir/var/lib/mysql

Innobackupex -- copy-back/Backup/2017-11-16_20-14-05/Data Recovery

Chown mysql. mysql var/lib/mysql/-R

Cp-/Var/lib/mysql. bak/Mysql. sock/Var/lib/mysql put the socket file cp over

Systemctl start mariadb restart the service

6. Check the database and data. The data is completely consistent.

Experiment 3: lvm snapshot + binlog for data backup and recovery

Principle:LVM snapshots simply save the metadata of all files in the snapshot source partition at a time point. If the source file does not change, accessing the corresponding file of the snapshot volume directly refers to the source file in the source partition, if the source file changes, the corresponding files in the snapshot volume will not change. Snapshot volumes are used to backup files.

1. Preparations

Because the database directory of the database in our experiment environment is not on lvm, first we need to build the lvm environment and then migrate the database to lvm. In the experiment:

(1) Add a hard disk and divide the disk type into lvm

Echo '---'>/sys/class/scsi_host/host2/scan Virtual Machine to add Disks without restarting synchronization of New Disks

Fdisk/dev/sdg

(2) partx-a/dev/sdb or partprobe enable the kernel to identify the new disk

Partprobe: Can be used normally in centos5 and 7; centos6 has bugs

Partx-a/dev/sdb: Can be used normally

(3) create a logical volume

①Pvcreate/Dev/sdg1 add physical volume

②VgcreateAdd a volume group to myvg/dev/sdg1

③Lvcreate-N mydata-L 50G myvg add logical volume

④Mkfs. ext4/Dev/mapper/myvg-mydata format logical volume, file system format

2. mount a logical volume

① Mkdir/lvm_data create a directory mounted to lvm

② Mount/dev/mapper/myvg-mydata/lvm_data mounting

We 'd better write it/Etc/fstab, Such:

Vim/etc/fstab

/Dev/mapper/myvg-mydata /Lvm_data/ Ext4Defaults 0 0

③ Change the owner and group of the mounted directory to mysql.

Chown-R mysql. mysql/lvm_data

3. Modify Mysql Configuration

(1) modify the Mysql configuration so that the data file datadir =/lvm_data on the logical volume

Vim/etc/my. cnf

[Mysqld]

Datadir =/Lvm_data

Socket =/var/lib/mysql. sock

(2) copy the database file to the/lvm_data directory:

Cp-a/var/lib/mysql/*/lvm_data

(3) service mysqld restart Mysql service

4. Create a snapshot

(1) Before we create a snapshot, we needLock tableTo lock the tables in the database so that the external data cannot be read:

MariaDB [(none)]>Flush tables with read lock;

(2) create a snapshot: Logical volume "mydata-snap" created

Lvcreate-L 1G-n mydata-snap-PR-s/dev/mapper/myvg-mydata

(3) unlock the table:

MariaDB [(none)]>UnlockTables;

5. Package data and back up data

(1) package data:

Tar czvf/tmp/mysqlback.tar.gz/lvm_data

(2) After using snapshots, We Need To Unmount and delete them. The command is as follows:

Umount/lvm_snap/

Lvremove myvg/dev/myvg/mydata-snap

6. Simulate database deletion and data recovery

(1) we delete all the data under/lvm_data to simulate the loss of all our databases.

Rm-rf/lvm_data /*

(2) Data Recovery

Mv/tmp/mysqlback.tar.gz/lvm_data: compress the package cp.

Tar xvf/tmp/mysqlback.tar.gz./decompress the data and restore the data.

Note: After the data is copied to the database file directory, you must check whether the File Permission is owned by the mysql user. If not, you need to manually change the permission.