Mysql Innobackupex-based backup & Recovery

Backup, any system is a priority for any database. For MySQL, I chose Percona xtrabackup software. I prefer a physical layer of hot backup. Rather than the logical level of backup (mysqldump), of course, in many cases, you should also do mysqldump backups on a regular basis. Add a secure backup option.

For information on how to download and install Percona Xtrabackup, please refer to:

http://blog.51cto.com/hsbxxl/2107388

First look at Innobackupex common parameters

--compact         Create a backup that does not contain a second index (other than the primary key)--decompress          before extracting all backup files with the. qp format backed up by the –compress parameter, the--parallel parameter allows multiple files to be unlocked or unzipped at the same time. Need to install qpress software. --defaults-file=[my. The path to the cnf]  configuration file--incremental-basedir  more than one full or incremental backup path, as the basis for an incremental backup. Specifying this parameter should also specify the--incremental parameter--incremental  create an incremental backup, when specifying this parameter, The--INCREMENTAL-LSN or--incremental-basedir parameter should be specified, otherwise it will be backed up to the--incremental-basedir path--apply-log   in the backup directory, Prepare the backup by applying the transaction log file with the name Xtrabackup_logfile. Also, create a new transaction log. --redo-only  This parameter needs to be specified when preparing a full or consolidated incremental backup of the database. This parameter actually executes the xtrabackup --apply-log-only, which causes the xtrabackup to skip the rollback node and only do the "redo" step. This parameter needs to be specified when the database needs to apply an incremental backup. --incremental-dir=directory  Specifies the directory for an incremental backup, which needs to be paired with the--incremental parameter. --no-timestamp  This parameter allows Xtrabackup to not create subfolders with a time format when backing up. When this parameter is specified, the backup is created directly under the specified backup directory--stream=streamname  the specified stream backup format. The backup will be output to stdout in the specified format. The currently supported formats are tar  and  xbstream. If this parameter is specified, then the Tmpdir directory needs to be appended as an intermediate directory for the processing stream. --slave-info     when you back up a server that is a replication environment, this parameter automaticallyThe Change master statement is written to the backup, and the Change master statement does not have to be executed after the backup is resumed. --tables-file=file         This parameter accepts a string that specifies a file that contains the name of the table to be backed up. Format as database.table, one line at a. --use-memory=#         This parameter is used when preparing a backup, Xtrabackup executes crash  The amount of memory used by the recovery. This parameter only takes effect when paired with--apply-log.

For more Innobackupex parameter explanation, please refer to the official website

Https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/innobackupex_option_reference.html

The entire test process, such as

Start my backup & recovery test below

1. Take a look at my/etc/my.cnf settings first. I chose Binlog as the row mode, which is the "line" mode, not the SQL mode. Binlog and datafile are stored separately to facilitate subsequent database management.

Binlog_format = Rowexpire_logs_days = 7max_binlog_size = 100mbinlog_cache_size = 4mmax_binlog_cache_size = 512mcharacter _set_server = utf8lower_case_table_names=1datadir=/data/mysqllog_bin=/data/mysql-binlog/mysql-binserver_id=101

Create a table before backing up

Test1--innodb engine test2--innodb engine Testmyisam--myisam engine

2. Database full backup (Innobackupex parameters please refer to)

Create a backup directory

Mkdir/backup

Full Library Backup

Innobackupex--defaults-file=/etc/my.cnf--user=root--password= ' abcd234! '/backup/20180424/

3. Operation after full backup

A. Create a table for the MyISAM storage engine TESTMYISAM2

Mysql> Show tables;+----------------+| Tables_in_test |+----------------+| Test1 | | Test2 | | Testmyisam | | Testmyisam2 |+----------------+4 rows in Set (0.00 sec)

B. Deleting a table test2

mysql> drop table test2; Query OK, 0 rows affected (0.03 sec) mysql> show tables;+----------------+| Tables_in_test |+----------------+| Test1 | | Testmyisam | | Testmyisam2 |+----------------+3 rows in Set (0.00 sec)

4. Innobackupex backup MySQL process, is a copy of the file, in the process of copying data, the data will change, so the backup of the data file is inconsistent. In order to ensure the consistency of data, it is necessary to apply the binlog generated during this time to achieve the consistency of backup data files. Subsequent replies will be valid.

Use the--apply-log parameter and specify a backup subdirectory with time for backup consistency merging. To speed up the process of apply-log, it is recommended to use the--use-memory parameter.

This operation can be performed on any machine to prepare the backup action, not limited to the backup server.

Innobackupex--defaults-file=/etc/my.cnf--apply-log--user=root--password= ' abcd1234! '/backup/20180424/2018-04-22 _21-22-30/

5. Restore the data file to the MySQL path, the recovery path depends on the Datadir=/data/mysql parameter in the MY.CNF.

You need to clear the Datadir=/data/mysql path before you can recover, or you will get an error. By default,--copy-back is not a file that already exists over write, and can be overridden by parameter--force-non-empty-directories

Stop MySQL

Service Mysqld Stop

Empty the MySQL path ( be cautious, it is recommended to be a MV backup, not RM)

rm-rf/data/mysql/*

Full library Recovery

Innobackupex--defaults-file=/etc/my.cnf--copy-back/backup/20180424/2018-04-22_21-22-30/

Modify Path Permissions

Chown Mysql:mysql-r/data/mysql/

Start MySQL

Service mysqld Start

6. The current database is just a full backup of restore, and there is no point-in-time recovery to the current

After the recovery is complete, query the database, you can see the Test2 table has not been deleted, Testmyisam2 did not appear

Mysql> Show tables;+----------------+| Tables_in_test |+----------------+| Test1 | | Test2 | | Testmyisam |+----------------+

7. First, through the backup file to find the last backup completed, the Binglog position location, in the backup file path Xtrabackup_binlog_info recorded, this position is important, is the key to restore back.

# cd/backup/20180424/2018-04-22_21-22-30/# Cat Xtrabackup_binlog_info mysql-bin.000002 29388004

8. Recover through the Mysqlbinglog

Mysqlbinlog--start-position=29388004/data/mysql-binlog/mysql-bin.000002 | Mysql-uroot-p ' abcd234! '

9. Once the archive is restored, the Test2 table has been deleted and Testmyisam2 has appeared

Mysql> Show tables;+----------------+| Tables_in_test |+----------------+| Test1 | | Testmyisam | | Testmyisam2 |+----------------+3 rows in Set (0.00 sec)

10. You can also restore to a specified point-in-time range based on position (the following is the step of full restore testing from the back, not the next step.) ）

Find position Information

# Mysqlbinlog--start-position=29388004/data/mysql-binlog/mysql-bin.000002|grep "^# at" @ 4# at 29388004# at 29388069# At 29388304# at 29388369# at 29388487

11. Specify Scope Recovery

Mysqlbinlog--start-position=29388004--stop-position=29388304/data/mysql-binlog/mysql-bin.000002 | Mysql-uroot-p ' abcd1234! ' After recovering this section of the Binlog, you can see that Testmyisam2 has appeared mysql> show tables;+----------------+| Tables_in_test |+----------------+| Test1 | | Test2 | | Testmyisam | | Testmyisam2 |+----------------+4 rows in Set (0.00 sec)

12. Segmented recover to help recover data in human error

Mysqlbinlog--start-position=29388304--stop-position=29388487/data/mysql-binlog/mysql-bin.000002 | Mysql-uroot-p ' abcd1234! ' To restore this section of Binlog, you can see that test2 has been removed mysql> show tables;+----------------+| Tables_in_test |+----------------+| Test1 | | Testmyisam | | Testmyisam2 |+----------------+

Summarize

A, for full recovery, we need to use a combination of backup plus binlog to achieve

b, during recovery, the first use of the Apply-log mode with read-only to prepare full (in the case of incremental backup needs to be merged, there is no increment, there is no need for read-only)

C, the next use with read-only Apply-log way to prepare, only the last one can not be added read-only

D, stop the original instance, and start the restored instance after Copy-back

E. Get the last Binlog log and location information from Innobakcupex backup information

F. Append the log to the latest time using the Mysqlbinlog method

Reference links

http://blog.itpub.net/26506993/viewspace-2088737/

72954519

Mysql Innobackupex-based backup & Recovery