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.