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

Security code: it is impossible to have inner peace without a considerable degree of loneliness.

1. Overview

Percona XtraBackup (abbreviated as PXB) is a backup tool developed by Percona for physical hot backup of MySQL database, which supports MySQl (Oracle), Percona Server and MariaDB, and is open source. The physical backup of RDS for MySQL in Taobao is based on XtraBackup.

Xtrabackup has two main tools: xtrabackup and innobackupex

(1) xtrabackup is a binary file compiled by Calgary +. Only InnoDB and XtraDB tables can be backed up, but MyISAM tables cannot be backed up.

(2) innobackupex encapsulates xtrabackup, which is a perl script encapsulation, so you can back up and process innodb and myisam at the same time, but you need to add a read lock when processing myisam.

1.1 backup principle

The entire backup process is shown in the following figure:

After innobackupex starts, it will first fork a process, start the xtrabackup process, and then wait for xtrabackup to back up the ibd data files.

When xtrabackup backs up InnoDB-related data, there are two threads, one is the redo copy thread, which is responsible for copying redo files, and the other is the ibd copy thread, which is responsible for copying ibd files. There is only one redo copy thread, which starts before the ibd copy thread and ends after the ibd thread ends. After the xtrabackup process starts execution, start the redo copy thread to copy the redo logs sequentially from the latest checkpoint point, and then start the ibd data copy thread, where the innobackupex process has been waiting (waiting for the file to be created) during the xtrabackup copy ibd process.

After the xtrabackup copy completes the idb, notify the innobackupex (by creating the file) while entering and waiting (the redo thread still continues to copy).

When innobackupex receives the xtrabackup notification, it executes FLUSH TABLES WITH READ LOCK (FTWRL), obtains the consistency point, and then starts backing up non-InnoDB files (including frm, MYD, MYI, CSV, opt, par, and so on). In the process of copying non-InnoDB files, because the database is global read-only, you should be especially careful if you back up the main database of the business. If there are more non-InnoDB tables (mainly MyISAM), the read-only time of the entire database will be longer, and this impact must be evaluated.

When innobackupex has copied all the non-InnoDB table files, notify xtrabackup (by deleting the file) and enter and wait (wait for another file to be created)

When xtrabackup receives a non-InnoDB notification from innobackupex backup, it stops the redo copy thread and then notifies innobackupex redo log that the copy is complete (by creating a file)

After receiving the notification of the completion of the redo backup, innobackupex begins to unlock and execute UNLOCK TABLES

Finally, the innobackupex and xtrabackup processes respectively complete the finishing work, such as releasing resources, writing backup metadata information, and so on. Innobackupex waits for the xtrabackup child process to finish and then exits.

1.2 Common parameters

-- defaults-file: indicates the configuration file of the server. This parameter must be the first parameter of innobackupex, otherwise an error is reported.

-- host: indicates the host to which the database is connected

-- user: indicates the user name that performs the database backup

-- password: indicates the password to perform the backup

-- backup: indicates backup. This parameter can be ignored.

-- apply-log: redo log

-- copy-back: perform data recovery

-- slave-info: back up the show slave status information of the slave library, which is only used when backing up the slave library.

-- no-lock: does not lock the table, only applies when the storage engine is innodb, and does not care about the use of backup location points

2. Install XtraBackup

2.1 Environmental preparation

Hostname IP system MySQL version MySQL-01192.168.10.20CentOS release 6.9 (Final) 5.6.36

The installation steps for MySQL are briefly described.

2.2 create backup user

Mysql > create user 'backup'@'localhost' identified by' 123456 query OK, 0 rows affected (0.04 sec) mysql > grant reload,lock tables,replication client,process on *. * to 'backup'@'localhost' identified by' 123456 question OK, 0 rows affected (0.30 sec) mysql > flush privileges;Query OK, 0 rows affected (0.04 sec)

2.3 create a backup directory

[root@MySQL-01 ~] # mkdir / backup

2.4 download xtraBackup

[root@MySQL-01 ~] # cd / opt# download may be slow. [root@MySQL-01 opt] # wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm

2.4 install XtraBackup

# an error occurred during installation because the dependency package was not installed. [root@MySQL-01 opt] # rpm-ivh percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpmwarning: percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature Key ID cd2efd2a: NOKEYerror: Failed dependencies: libev.so.4 () (64bit) is needed by percona-xtrabackup-24-2.4.7-2.el6.x86_64 perl (DBD::mysql) is needed by percona-xtrabackup-24-2.4.7-2.el6.x86_64# installation dependent package [root@MySQL-01 opt] # yum-y install libev-devel perl-DBD-MySQL [root@MySQL-01 opt] # rpm-ivh percona -xtrabackup-24-2.4.7-2.el6.x86_64.rpmwarning: percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature Key ID cd2efd2a: NOKEYPreparing... # 1:percona-xtrabackup-24 # [root@MySQL-01 opt] # rpm-ql percona-xtrabackup- 24-2.4.7-2.el6.x86_64/usr/bin/innobackupex # innobackupex scripting tool / usr/bin/xbcloud/usr/bin/xbcloud_osenv/usr/bin/xbcrypt/usr/bin/xbstream/usr/bin/xtrabackup # main backup tool / usr/share/doc/percona-xtrabackup-24-2.4.7/usr/share/doc/percona-xtrabackup-24-2.4 .7 / COPYING/usr/share/man/man1/innobackupex.1.gz/usr/share/man/man1/xbcrypt.1.gz/usr/share/man/man1/xbstream.1.gz/usr/share/man/man1/xtrabackup.1.gz

3. Backup and recovery testing

3.1 full backup

# defaults-file:my.cnf configuration path [root@MySQL-01 opt] # innobackupex-- defaults-file=/app/mysql/my.cnf-- user=backup-- password=123456 / backup# check that there are backup files in the backup directory [root@MySQL-01 opt] # ll / backup/total 4drwxr Murashi-5 root root 4096 Jul 14 17:25 2017-07-14 October 17-25-24 [root@MySQL-01 opt] # ls / backup/2017-07-14 cycles 17-25 -24/backup-my.cnf performance_schema xtrabackup_checkpointsibdata1 rep xtrabackup_infomysql xtrabackup_binlog_info xtrabackup_ log [root @ MySQL-01 opt] # cat / backup/2017-07-14017-25-24/xtrabackup_checkpointsbackup_type = full-backuped # backup method is full backup from_lsn = 0 # because it is complete In fact, the LSN number is 0to_lsn = 13835894 # until the LSN number last_lsn = 13835894compact = 0recover_binlog_info = 0

3.2 simulated recovery

Now that the full backup is complete, let's test it.

# stop the database [root@MySQL-01] # service mysqld stopShutting down MySQL. Success # move the data file to the backup directory [root@MySQL-01 ~] # mv / app/mysql/data/ backup/data_bak# failed to start mysqld [root@MySQL-01 ~] # service mysqld startStarting MySQL.Logging to'/ app/mysql/data/MySQL-01.err'.. ERROR! The server quit without updating PID file (/ app/mysql/data/MySQL-01.pid). # apply-log parameter: restore the database redo log state before actually restoring the database data. [root@MySQL-01] # innobackupex-- defaults-file=/app/mysql/my.cnf-- user=backup-- password=123456-- apply-log / backup/2017-07-14 "17-25-2" perform data recovery [root@MySQL-01] # innobackupex-- defaults-file=/app/mysql/my.cnf-- user=backup-- password=123456-- copy-back / backup/2017-07-14 "17-25-24 [root@MySQL-01] # chown-R mysql. Mysql/ app/mysql/data# starts the database again Display successful [root@MySQL-01 ~] # service mysqld startStarting MySQL.Logging to'/ app/mysql/data/MySQL-01.err'.. SUCCESS!

4. Reference

Http://mysql.taobao.org/monthly/2016/03/07/

Http://www.cnblogs.com/olinux/p/5207887.html

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.