MySQL-innobackupex备份工具

• 安装percona软件包
• innobackupex完整备份、增量备份操作。
• 恢复数据

步骤一：安装XtraBackup软件包

1）了解软件包描述信息

[root@zhangyx ~]# rpm -qpi percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm  //这里版本写自己的版本warning: percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEYName        : percona-xtrabackup-24Version     : 2.4.7Release     : 1.el7Architecture: x86_64Install Date: (not installed)Group       : Applications/DatabasesSize        : 32462102License     : GPLv2Signature   : DSA/SHA1, Mon 17 Apr 2017 10:19:23 PM CST, Key ID 1c4cbdcdcd2efd2aSource RPM  : percona-xtrabackup-24-2.4.7-1.el7.src.rpmBuild Date  : Mon 17 Apr 2017 10:18:13 PM CSTBuild Host  : v-centos-7-x64-01.ci.percona.comRelocations : (not relocatable)URL         : http://www.percona.com/software/percona-xtrabackupSummary     : XtraBackup online backup for MySQL / InnoDBDescription :Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines

2）安装依赖包perl-DBD-MySQL perl-Digest-MD5 libev
使用RHEL 7自带的即可，yum方式安装：

[root@zhangyx ~]# yum -y install perl-DBD-MySQL perl-Digest-MD5[root@zhangyx ~]#rpm –ivh libev-4.15-1.el6.rf.x86_64.rpm

如果未安装这些依赖包，则直接安装percona-xtrabackup时会报错：
解决办法：

[root@zhangyx ~]# yum -y install rsync perl l perl-Digest-MD5

3）安装percona-xtrabackup

[root@zhangyx ~]# rpm -ivh percona-xtrabackup-*.rpmwarning: percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEYPreparing...                          ################################# [100%]Updating / installing...1:percona-xtrabackup-24-2.4.7-1.el7################################# [100%]

4）确认安装的主要程序/脚本

[root@zhangyx ~]# rpm -ql percona-xtrabackup-24-2.4.7-1.el7.x86_64/usr/bin/innobackupex/usr/bin/xbcloud/usr/bin/xbcloud_osenv/usr/bin/xbcrypt/usr/bin/xbstream/usr/bin/xtrabackup/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

步骤二：innobackupex完整备份、增量备份操作

–host 主机名
–port 3306
–user 用户名
–password 密码
–databases=“库名”
–databases=“库1 库2”
–databases=“库.表”
–no-timestamp 不用日期命名备份文件存储的子目录，使用备份的数据库名做备份目录名
–no-timestmap 不使用日期命名备份目录名
1）做一个完整备份
默认情况下，备份文件存储的子目录会用日期命名，
innobackupex作为客户端工具，以mysql协议连入mysqld，将数据备份到/backup文件夹：

[root@zhangyx ~]# innobackupex --user root --password 1234567 /backup/mysql --no-timestamp200725 16:41:22 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.At the end of a successful backup run innobackupexprints \"completed OK!\".200725 16:41:22 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not setUsing server version 5.7.17innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799)xtrabackup: uses posix_fadvise().xtrabackup: cd to /var/lib/mysqlxtrabackup: open files limit requested 0, set to 65535xtrabackup: using the following InnoDB configuration:xtrabackup:   innodb_data_home_dir = .xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 2xtrabackup:   innodb_log_file_size = 50331648InnoDB: Number of pools: 1200725 16:41:22 >> log scanned up to (2782387)xtrabackup: Generating a list of tablespacesInnoDB: Allocated tablespace ID 13 for mysql/innodb_table_stats, old maximum was 0200725 16:41:24 [01]        ...done200725 16:41:24 Finished backing up non-InnoDB tables and files200725 16:41:24 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...xtrabackup: The latest check point (for incremental): \'2782378\'xtrabackup: Stopping log copying thread..200725 16:41:24 >> log scanned up to (2782387)200725 16:41:24 Executing UNLOCK TABLES200725 16:41:24 All tables unlocked200725 16:41:24 [00] Copying ib_buffer_pool to /backup/mysql/ib_buffer_pool200725 16:41:24 [00]        ...done200725 16:41:24 Backup created in directory \'/backup/mysql/\'200725 16:41:24 [00] Writing backup-my.cnf200725 16:41:24 [00]        ...done200725 16:41:24 [00] Writing xtrabackup_info200725 16:41:24 [00]        ...donextrabackup: Transaction log of lsn (2782378) to (2782387) was copied.200725 16:41:24 completed OK!

确认备份好的文件数据：

[root@zhangyx ~]#ls /backup/backup-my.cnf  ib_buffer_pool  mysql      sys        xtrabackup_infodb1  ibdata1      performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

2）做一个增量备份（基于前一步的完整备份）
随意做一些新增或更改库表的操作，比如在db1库中新建一个mytb的表：

1.	mysql> create database db;Query OK, 1 row affected (0.00 sec)2.	mysql> USE db;Database changedmysql> CREATE TABLE mytb(id int(4), name varchar(24));Query OK, 0 rows affected (0.03 sec)mysql>  INSERT INTO mytb VALUES-> (1,\'bon\'),-> (2,\'bo\');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT * FROM db;ERROR 1146 (42S02): Table \'db.db\' doesn\'t existmysql> SELECT * FROM mytb;+------+------+| id   | name |+------+------+|    1 | bon  ||    2 | bo   |+------+------+2 rows in set (0.00 sec)

以前一次保存到/backup的完整备份为基础，做一个增量备份，保存到/incr01/，指定增量备份参照的基本目录（完整备份目录）需要用到选项–incremental-basedir。相关操作如下：

1.	[root@dbsvr1 ~]# innobackupex  --user=root --password=12345678 --incremental /incr01 --incremental-basedir=/backup/ --no-timestamp2.	170425 11:30:14 innobackupex: Starting the backup operation3.4.	IMPORTANT: Please check that the backup run completes successfully.5.	           At the end of a successful backup run innobackupex6.	           prints \"completed OK!\".7.8.	Unrecognized character \\x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.9.	170425 11:30:14 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set10.	Using server version 5.7.1711.	innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)12.	incremental backup from 2543884 is enabled.13.	xtrabackup: uses posix_fadvise().14.	xtrabackup: cd to /var/lib/mysql15.	xtrabackup: open files limit requested 0, set to 102416.	xtrabackup: using the following InnoDB configuration:17.	xtrabackup:   innodb_data_home_dir = .18.	xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend19.	xtrabackup:   innodb_log_group_home_dir = ./20.	xtrabackup:   innodb_log_files_in_group = 221.	xtrabackup:   innodb_log_file_size = 5033164822.	InnoDB: Number of pools: 123.	170425 11:30:14 >> log scanned up to (2549933)24.	xtrabackup: Generating a list of tablespaces25.	InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 026.	xtrabackup: using the full scan for incremental backup27.	170425 11:30:15 [01] Copying ./ibdata1 to /incr01/ibdata1.delta28.	170425 11:30:15 [01]        ...done29.	170425 11:30:15 >> log scanned up to (2549933)30.	170425 11:30:15 [01] Copying ./mysql/plugin.ibd to /incr01/mysql/plugin.ibd.delta31.	170425 11:30:15 [01]        ...done32.	... ...33.	170425 11:30:35 Executing UNLOCK TABLES34.	170425 11:30:35 All tables unlocked35.	170425 11:30:35 [00] Copying ib_buffer_pool to /incr01/ib_buffer_pool36.	170425 11:30:35 [00]        ...done37.	170425 11:30:35 Backup created in directory \'/incr01/\'38.	170425 11:30:35 [00] Writing backup-my.cnf39.	170425 11:30:35 [00]        ...done40.	170425 11:30:35 [00] Writing xtrabackup_info41.	170425 11:30:35 [00]        ...done42.	xtrabackup: Transaction log of lsn (2549924) to (2549933) was copied.43.	170425 11:30:35 completed OK!

确认备份好的文件数据：

[root@zhangyx ~]# ls /incr01/backup-my.cnf  ib_buffer_pool  ibdata1.meta  performance_schema  xtrabackup_checkpoints  xtrabackup_logfiledb1            ibdata1.delta   mysql         sys

步骤三：恢复数据

通过XtraBackup工具备份的数据库目录，若要恢复到另一个MySQL服务器，需要先做一个“–apply-log –redo-only ”的准备操作。

1）准备恢复“完整备份”

完成准备以后，最终/backup可用来重建MySQL服务器。这种情况下，需要先做一个“–apply-log –redo-only ”的准备操作，以确保数据一致性：

[root@zhangyx ~]#innobackupex  --user=root --password=12345678 --apply-log  --redo-only /backup/170425 11:42:19 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully.At the end of a successful apply-log run innobackupexprints \"completed OK!\".innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)xtrabackup: cd to /backup/xtrabackup: This target seems to be already prepared.InnoDB: Number of pools: 1xtrabackup: notice: xtrabackup_logfile was already used to \'--prepare\'.xtrabackup: using the following InnoDB configuration for recovery:xtrabackup:   innodb_data_home_dir = .xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextendxtrabackup:   innodb_log_group_home_dir = .xtrabackup:   innodb_log_files_in_group = 2xtrabackup:   innodb_log_file_size = 50331648xtrabackup: using the following InnoDB configuration for recovery:xtrabackup:   innodb_data_home_dir = .xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextendxtrabackup:   innodb_log_group_home_dir = .xtrabackup:   innodb_log_files_in_group = 2xtrabackup:   innodb_log_file_size = 50331648xtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)InnoDB: PUNCH HOLE support availableInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Uses event mutexesInnoDB: GCC builtin __atomic_thread_fence() is used for memory barrierInnoDB: Compressed tables use zlib 1.2.7InnoDB: Number of pools: 1InnoDB: Not using CPU crc32 instructionsInnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100MInnoDB: Completed initialization of buffer poolInnoDB: page_cleaner coordinator priority: -20InnoDB: Highest supported file format is Barracuda.xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 2544177InnoDB: Number of pools: 1170425 11:42:20 completed OK!

准备恢复“增量备份”：

[root@zhangyx ~]#innobackupex  --user=root --password=12345678 --apply-log  --redo-only /backup/  --incremental-dir=/incr01170425 11:42:55 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully.At the end of a successful apply-log run innobackupexprints \"completed OK!\".innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)incremental backup from 2543884 is enabled.xtrabackup: cd to /backup/xtrabackup: This target seems to be already prepared with --apply-log-only.InnoDB: Number of pools: 1xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2549924)xtrabackup: using the following InnoDB configuration for recovery:xtrabackup:   innodb_data_home_dir = .xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextendxtrabackup:   innodb_log_group_home_dir = /incr01/xtrabackup:   innodb_log_files_in_group = 1xtrabackup:   innodb_log_file_size = 8388608xtrabackup: Generating a list of tablespacesInnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0xtrabackup: page size for /incr01//ibdata1.delta is 16384 bytesApplying /incr01//ibdata1.delta to ./ibdata1...... ...170425 11:43:09 [01] Copying /incr01/performance_schema/global_status.frm to ./performance_schema/global_status.frm170425 11:43:09 [01]        ...done170425 11:43:09 [01] Copying /incr01/performance_schema/session_status.frm to ./performance_schema/session_status.frm170425 11:43:09 [01]        ...done170425 11:43:09 [00] Copying /incr01//xtrabackup_info to ./xtrabackup_info170425 11:43:09 [00]        ...done170425 11:43:10 completed OK!

2）关闭mysql服务，并将/var/lib/mysql/下的文件删除，假设数据被删除。

[root@zhangyx ~]#systemctl  stop  mysqld[root@zhangyx ~]#rm -rf /var/lib/mysql

3）恢复“完整备份+增量备份”

完成准备以后，最终仍然是/backup用来重建MySQL服务器，但这种情况下需提前合并相关增量备份的数据

[root@zhangyx ~]# innobackupex --user=root --password=12345678 --copy-back  /backup/... ...170425 11:51:39 [01] Copying ./performance_schema/global_status.frm to /var/lib/mysql/performance_schema/glo.frm170425 11:51:39 [01]        ...done170425 11:51:39 [01] Copying ./performance_schema/session_status.frm to /var/lib/mysql/performance_schema/seus.frm170425 11:51:39 [01]        ...done170425 11:51:39 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool170425 11:51:39 [01]        ...done170425 11:51:39 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1170425 11:51:39 [01]        ...done170425 11:51:39 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info170425 11:51:39 [01]        ...done170425 11:51:39 completed OK!

4）修改/var/lib/mysql/下文件属主与属组,查看数据：
恢复后，/var/lib/mysql下文件属组与属主皆为root，需要更改为mysql

[root@zhangyx ~]#chown -R mysql:mysql /var/lib/mysql[root@zhangyx ~]#systemctl  start mysqld.service[root@zhangyx ~]#mysql -uroot -p12345678 -e \"select * from db1.tb1\"mysql: [Warning] Using a password on the command line interface can be insecure.+------+------+| id   | name |+------+------+|    1 | bob  ||    2 | bo   |+------+------+

innobackupex选项：