Shulou(Shulou.com)06/01 Report--

This article mainly introduces the innobackupex backup and recovery test brief analysis, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the next topic content.

Take a look at the common parameters of innobackupex first.

-- compact creates a backup that does not contain the second index (except the primary key)-- decompress unzips all backup files with .qp format backed up with the-compress parameter,-- the parallel parameter allows multiple files to be unlocked or unzipped at the same time. Qpress software needs to be installed. -- the path to the defaults-file= [MY.CNF] configuration file-- the path to the last full or incremental backup of incremental-basedir, which serves as the basis for the incremental backup. When you specify this parameter, you should also specify-- incremental parameter-- incremental to create an incremental backup. When you specify this parameter, you should specify the-- incremental-lsn or-- incremental-basedir parameter, otherwise it will be backed up to-- incremental-basedir path-- apply-log in the backup directory, and prepare the backup by applying a transaction log file named xtrabackup_logfile. At the same time, create a new transaction log. -- redo-only specifies this parameter when preparing a full or merged incremental backup of the database. This parameter actually executes xtrabackup-- apply-log-only, which tells xtrabackup to skip the rollback node and only do the "redo" step. This parameter needs to be specified when the database needs to apply incremental backups. -- incremental-dir=DIRECTORY specifies the directory for incremental backups, which needs to be matched with the-- incremental parameter. The parameter no-timestamp prevents xtrabackup from creating time-formatted subfolders when backing up. When this parameter is specified, the backup is created directly under the specified backup directory-stream=STREAMNAME specifies the format of the stream backup. The backup will be output to STDOUT in the specified format. Currently supported formats are tar and xbstream. If you specify this parameter, you need to follow the tmpdir directory as an intermediate directory for processing the stream. -- slave-info when backing up a server as a replication environment, this parameter automatically writes the CHANGE MASTER statement to the backup, and there is no need to execute the CHANGE MASTER statement after the backup is restored. The parameter tables-file=FILE accepts a string that specifies a file that contains the name of the table to be backed up in a format such as database.table, one by one. -- use-memory=# this parameter is used for the amount of memory that xtrabackup uses to perform crash recovery when preparing for backup. This parameter takes effect only when paired with-- apply-log.

For more explanation of innobackupex parameters, please refer to the official website.

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

The whole test process is as follows

Let's start my backup & restore test.

1. Take a look at my / etc/my.cnf settings first. I chose binlog to be in row mode, that is, "line" mode, rather than 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

two。 Full backup of database (please refer to innobackupex parameters)

Create a backup directory

Mkdir / backup

Full database backup

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- passwordkeeper Abcd234'/ backup/20180424/

3. Operation after full backup

a. Create a table testmyisam2 for the myisam storage engine

Mysql > show tables;+-+ | Tables_in_test | +-+ | test1 | | test2 | | testmyisam | | testmyisam2 | +-+ 4 rows in set (0.00 sec)

b. Delete 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. The process of innobackupex backing up mysql is the copy of files. In the process of copying data, the data will change, so the backed up data files are inconsistent. In order to ensure the consistency of data, it is necessary to apply the binlog generated during this period of time in order to achieve the consistency of backup data files. Only a follow-up reply can be effective.

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

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

Innobackupex-- defaults-file=/etc/my.cnf-- apply-log-- user=root-- passwordkeeper Abcd1234 / backup/20180424/2018-04-22-21-22-30 /

5. Restore the data file to the mysql path, which depends on the datadir=/data/mysql parameter in my.cnf.

You need to clear the datadir=/data/mysql path before you can restore it, otherwise an error will be reported. By default,-- copy-back does not over write files that already exist, and can be forcibly overwritten with the parameter-- force-non-empty-directories

Stop mysql

Service mysqld stop

Clear the mysql path (be careful, it is recommended to back up mv, 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 only a full backup of restore and is not restored to the current based on the point in time.

After the full back recovery is complete, query the database and you can see that the test2 table has not been deleted and the testmyisam2 has not appeared.

Mysql > show tables;+-+ | Tables_in_test | +-+ | test1 | | test2 | | testmyisam | +-+

7. First of all, by backing up the file, find the binglog position location of the last backup completed, and record it in the xtrabackup_binlog_info under the backup file path. This position is very important and is the key to later recovery.

# cd / backup/20180424/2018-04-22nd 21-22-30max # cat xtrabackup_binlog_info mysql-bin.000002 29388004

8. Recover via mysqlbinglog

Mysqlbinlog-- start-position=29388004 / data/mysql-binlog/mysql-bin.000002 | mysql- uroot-paired Abcd234'

9. After the archive is restored, query again that the test2 table has been deleted and the 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. (here are the steps for full restore to test again later, not in the previous step. )

Find position information

# mysqlbinlog-- start-position=29388004 / data/mysql-binlog/mysql-bin.000002 | grep "^ # at" # at at 2938800 "at 2938806" at 2938830 "at 2938836" at 29388487

11. Specified range recovery

Mysqlbinlog-- start-position=29388004-- stop-position=29388304 / data/mysql-binlog/mysql-bin.000002 | mysql- uroot-paired Abcd1234' After restoring this binlog, you can see that mysql > show tables;+-+ has appeared in testmyisam2 | Tables_in_test | +-+ | test1 | | test2 | | testmyisam | | testmyisam2 | +-+ 4 rows in set (0.00 sec)

twelve。 Segmented recover helps to retrieve human error data

Mysqlbinlog-- start-position=29388304-- stop-position=29388487 / data/mysql-binlog/mysql-bin.000002 | mysql- uroot-paired Abcd1234' When you restore this binlog, you can see that test2 has been deleted mysql > show tables;+-+ | Tables_in_test | +-+ | test1 | | testmyisam | | testmyisam2 | +-+

Summary

For full recovery, we need to use the combination of backup and binlog.

B. During the recovery period, first use apply-log with read-only to prepare full backup (read-only is not required when there are incremental backups to be merged, and if there is no incremental backup)

C. Next, use apply-log with read-only to add prepare. Only the last addition can be done without 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 the Innobakcupex backup information

F. Use mysqlbinlog to append the log to the latest time

After reading the above brief analysis of innobackupex backup and recovery testing, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.