使用XtraBackup 备份MySQL数据库

本次测试使用XtraBackup备份MySQL数据库

版本：XtraBackup2.4.5+MySQL5.7.16

下载地址:https://www.percona.com/downloads/XtraBackup/

1、安装XtraBackup

本次为了方便，使用解压版本进行安装，直接解压就可以用了。

使用过程中可能会遇到缺少perl依赖包的问题，我的方法是直接操作把镜像包里的perl都安装了，yum install -y perl*

2、简介

XtraBackup主要包括两个备份工具xtrabackup和innobackupex。其中innobackupex对xtrabackup进行了封装。本次只介绍innobackupex备份MySQL

3、全量备的操作方法

1)创建备份

#创建存储备份数据的目录[root@rhel7 ~]# mkdir /mysqlbackup#开始备份[root@rhel7 ~]# innobackupex --user=root --password=123456 /mysqlbackup161213 12:27:13 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".161213 12:27:13  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1314.161213 12:27:13 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: (null)Using server version 5.7.16innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)xtrabackup: uses posix_fadvise().xtrabackup: cd to /usr/local/mysql/dataxtrabackup: open files limit requested 0, set to 1024xtrabackup: 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: 1161213 12:27:13 >> log scanned up to (2671643)xtrabackup: Generating a list of tablespacesInnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0161213 12:27:13 [01] Copying ./ibdata1 to /mysqlbackup/2016-12-13_12-27-13/ibdata1161213 12:27:14 >> log scanned up to (2671643)161213 12:27:15 [01]        ...done161213 12:27:15 >> log scanned up to (2671643)161213 12:27:15 [01] Copying ./mysql/plugin.ibd to /mysqlbackup/2016-12-13_12-27-13/mysql/plugin.ibd161213 12:27:15 [01]        ...done......161213 12:27:16 [01] Copying ./wl/zx.ibd to /mysqlbackup/2016-12-13_12-27-13/wl/zx.ibd161213 12:27:16 [01]        ...done161213 12:27:16 >> log scanned up to (2671643)161213 12:27:16 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...161213 12:27:16 Executing FLUSH TABLES WITH READ LOCK...161213 12:27:16 Starting to backup non-InnoDB tables and files161213 12:27:16 [01] Copying ./mysql/db.opt to /mysqlbackup/2016-12-13_12-27-13/mysql/db.opt161213 12:27:16 [01]        ...done......161213 12:27:18 [01] Copying ./wl/wl.frm to /mysqlbackup/2016-12-13_12-27-13/wl/wl.frm161213 12:27:18 [01]        ...done161213 12:27:18 [01] Copying ./wl/zx.frm to /mysqlbackup/2016-12-13_12-27-13/wl/zx.frm161213 12:27:18 [01]        ...done161213 12:27:18 Finished backing up non-InnoDB tables and files161213 12:27:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...xtrabackup: The latest check point (for incremental): '2671634'xtrabackup: Stopping log copying thread..161213 12:27:18 >> log scanned up to (2671643)161213 12:27:18 Executing UNLOCK TABLES161213 12:27:18 All tables unlocked161213 12:27:18 [00] Copying ib_buffer_pool to /mysqlbackup/2016-12-13_12-27-13/ib_buffer_pool161213 12:27:18 [00]        ...done161213 12:27:18 Backup created in directory '/mysqlbackup/2016-12-13_12-27-13'161213 12:27:18 [00] Writing backup-my.cnf161213 12:27:18 [00]        ...done161213 12:27:18 [00] Writing xtrabackup_info161213 12:27:18 [00]        ...donextrabackup: Transaction log of lsn (2671634) to (2671643) was copied.161213 12:27:19 completed OK!#在指定的目录下生成了一个时间目录[root@rhel7 ~]# ls -l /mysqlbackup/total 4drwxr-x---. 7 root root 4096 Dec 13 12:27 2016-12-13_12-27-13[root@rhel7 ~]# du -sm /mysqlbackup/*90      /mysqlbackup/2016-12-13_12-27-13

注意：innobackupex会去读取my.cnf文件中的[mysqld]和[xtrabackup]部分，也可以使用--defaults-file指定参数文件。

2)恢复全备的数据

备份的数据不能直接使用，需要先做prepare操作(可以理解为应用日志的过程，commit的事务提交，未commit的事务回滚)

#使用--apply-log应用日志，[root@rhel7 ~]# innobackupex --apply-log /mysqlbackup/2016-12-13_12-27-13/161213 12:36:24 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully.           At the end of a successful apply-log run innobackupex           prints "completed OK!".innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)xtrabackup: cd to /mysqlbackup/2016-12-13_12-27-13/xtrabackup: This target seems to be not prepared yet.InnoDB: Number of pools: 1xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2671634)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 = 1xtrabackup:   innodb_log_file_size = 8388608xtrabackup: 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 = 1xtrabackup:   innodb_log_file_size = 8388608xtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)InnoDB: PUNCH HOLE support not availableInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Uses event mutexesInnoDB: GCC builtin __sync_synchronize() is used for memory barrierInnoDB: Compressed tables use zlib 1.2.3InnoDB: 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.InnoDB: Log scan progressed past the checkpoint lsn 2671634InnoDB: Doing recovery: scanned up to log sequence number 2671643 (0%)InnoDB: Doing recovery: scanned up to log sequence number 2671643 (0%)InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: Creating shared tablespace for temporary tablesInnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...InnoDB: File './ibtmp1' size is now 12 MB.InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.InnoDB: 32 non-redo rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.7.13 started; log sequence number 2671643xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 2671671InnoDB: Number of pools: 1xtrabackup: 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 = 50331648InnoDB: PUNCH HOLE support not availableInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Uses event mutexesInnoDB: GCC builtin __sync_synchronize() is used for memory barrierInnoDB: Compressed tables use zlib 1.2.3InnoDB: 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: Setting log file ./ib_logfile101 size to 48 MBInnoDB: Setting log file ./ib_logfile1 size to 48 MBInnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0InnoDB: New log files created, LSN=2671671InnoDB: Highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 2672140InnoDB: Doing recovery: scanned up to log sequence number 2672149 (0%)InnoDB: Doing recovery: scanned up to log sequence number 2672149 (0%)InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: Removed temporary tablespace data file: "ibtmp1"InnoDB: Creating shared tablespace for temporary tablesInnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...InnoDB: File './ibtmp1' size is now 12 MB.InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.InnoDB: 32 non-redo rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.7.13 started; log sequence number 2672149xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 2672168161213 12:36:30 completed OK!#查看prepare后数据目录的大小[root@rhel7 ~]# ls -l /mysqlbackup/total 4drwxr-x---. 7 root root 4096 Dec 13 12:36 2016-12-13_12-27-13[root@rhel7 ~]# du -sm /mysqlbackup/*206     /mysqlbackup/2016-12-13_12-27-13

prepare完成后恢复数据到原MySQL的数据目录

注意：MySQL的数据目录必须为空，MySQL服务也必须停止，否则恢复会报错(除非恢复部分备份)

#创建一个测试库zx[root@rhel7 ~]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.16 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               || wl                 |+--------------------+6 rows in set (0.10 sec)mysql> create database zx;Query OK, 1 row affected (0.00 sec)mysql> exitBye#停止MySQL数据库并创建新的data目录[root@rhel7 ~]# service mysqld stopShutting down MySQL.. SUCCESS! [root@rhel7 ~]# cd $MYSQL_HOME[root@rhel7 mysql]# lsbin  COPYING  data  docs  include  lib  man  mysql-test  README  share  support-files[root@rhel7 mysql]# mv data data_bak[root@rhel7 mysql]# mkdir data#恢复数据[root@rhel7 mysql]# innobackupex --copy-back /mysqlbackup/2016-12-13_12-27-13/161213 13:03:50 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully.           At the end of a successful copy-back run innobackupex           prints "completed OK!".innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)161213 13:03:50 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0161213 13:03:50 [01]        ...done161213 13:03:51 [01] Copying ib_logfile1 to /usr/local/mysql/data/ib_logfile1161213 13:03:51 [01]        ...done161213 13:03:52 [01] Copying ibdata1 to /usr/local/mysql/data/ibdata1161213 13:03:54 [01]        ...done161213 13:03:55 [01] Copying ./mysql/plugin.ibd to /usr/local/mysql/data/mysql/plugin.ibd161213 13:03:55 [01]        ...done161213 13:03:55 [01] Copying ./mysql/servers.ibd to /usr/local/mysql/data/mysql/servers.ibd161213 13:03:55 [01]        ...done161213 13:03:55 [01] Copying ./mysql/help_topic.ibd to /usr/local/mysql/data/mysql/help_topic.ibd161213 13:03:55 [01]        ...done161213 13:03:55 [01] Copying ./mysql/help_category.ibd to /usr/local/mysql/data/mysql/help_category.ibd161213 13:03:55 [01]        ...done.......161213 13:03:57 [01] Copying ./ibtmp1 to /usr/local/mysql/data/ibtmp1161213 13:03:57 [01]        ...done161213 13:03:57 completed OK!#修改data目录下的权限[root@rhel7 mysql]# chown mysql:mysql -R data#启动MySQL数据库查看恢复是否成功[root@rhel7 mysql]# service mysqld startStarting MySQL. SUCCESS! [root@rhel7 mysql]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.16 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               || wl                 |+--------------------+6 rows in set (0.00 sec)#数据恢复成功，测试数据库zx不见了。

4.增量备份的操作步骤

1)创建备份，增量备份需要有一个全量备份做基础，所以先做一个全量备份再做增量备份

#创建全备[root@rhel7 mysql]# innobackupex --user=root --password=123456  /mysqlbackup/[root@rhel7 mysql]# ls -l /mysqlbackup/total 4drwxr-x---. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48#删除一个测试库mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               || wl                 || zx                 |+--------------------+7 rows in set (0.00 sec)mysql> drop database zx;Query OK, 0 rows affected (0.07 sec)#创建增量备份(基于全备)[root@rhel7 mysql]# innobackupex --user=root --password=123456 --incremental /mysqlbackup/ --incremental-basedir=/mysqlbackup/2016-12-13_13-10-48/......[root@rhel7 mysql]# ls -l /mysqlbackup/total 8drwxr-x---. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48drwxr-x---. 7 root root 4096 Dec 13 13:16 2016-12-13_13-16-17[root@rhel7 mysql]# du -sm /mysqlbackup/*90      /mysqlbackup/2016-12-13_13-10-483       /mysqlbackup/2016-12-13_13-16-17#删除一个测试库mysql> drop database wl;Query OK, 0 rows affected (0.07 sec)#创建增量备份(基于上次增量备份)[root@rhel7 mysql]# innobackupex --user=root --password=123456 --incremental /mysqlbackup/ --incremental-basedir=/mysqlbackup/2016-12-13_13-16-17/......[root@rhel7 mysql]# ls -l /mysqlbackup/total 12drwxr-x---. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48drwxr-x---. 7 root root 4096 Dec 13 13:16 2016-12-13_13-16-17drwxr-x---. 6 root root 4096 Dec 13 13:18 2016-12-13_13-18-30[root@rhel7 mysql]# du -sm /mysqlbackup/*90      /mysqlbackup/2016-12-13_13-10-483       /mysqlbackup/2016-12-13_13-16-174       /mysqlbackup/2016-12-13_13-18-30

2)恢复增量备份

同全量备份一样恢复里也需要prepare

#全量备份的prepare --redo-only表示只提交commit的事务[root@rhel7 mysql]# innobackupex --apply-log --redo-only /mysqlbackup/2016-12-13_13-10-48/#第一次增量备份prepare[root@rhel7 mysql]# innobackupex --apply-log --redo-only /mysqlbackup/2016-12-13_13-10-48/ --incremental-dir=/mysqlbackup/2016-12-13_13-16-17/ #最后一次增量备份prepare 不再需要redo-only参数[root@rhel7 mysql]# innobackupex --apply-log /mysqlbackup/2016-12-13_13-10-48/ --incremental-dir=/mysqlbackup/2016-12-13_13-18-30/#停止MySQL服务创建新的data目录[root@rhel7 mysql]# service mysqld stopShutting down MySQL.. SUCCESS! [root@rhel7 mysql]# rm -rf data[root@rhel7 mysql]# mkdir data#恢复数据[root@rhel7 mysql]# innobackupex --copy-back /mysqlbackup/2016-12-13_13-10-48/#修改data目录权限[root@rhel7 mysql]# chown mysql:mysql -R data#启动MySQL服务并验证[root@rhel7 mysql]# service mysqld startStarting MySQL... SUCCESS! [root@rhel7 mysql]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.16 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               || wl                 |+--------------------+6 rows in set (0.00 sec)mysql> show tables in wl;Empty set (0.00 sec)#测试库zx没有了，wl库还在，但是库里的表已经没有了。

前边只是提供了简单的恢复方法，如果数据库备份之后运行了一段时间，数据库宕机了，如果只恢复备份的数据那就会丢失一部分数据。那这部分数据要怎么恢复呢。答案就是应用binlog。

在启用binlog的数据库中，备份完成后日志中会输出关于binlog的信息，生成的备份目录中也会有一个xtrabackup_binlog_info文件保存备份时的binlog位置。

161214 10:24:34 Executing UNLOCK TABLES161214 10:24:34 All tables unlocked161214 10:24:34 [00] Copying ib_buffer_pool to /mysqlbackup//2016-12-14_10-24-27/ib_buffer_pool161214 10:24:34 [00]        ...done161214 10:24:34 Backup created in directory '/mysqlbackup//2016-12-14_10-24-27'#binlog位置MySQL binlog position: filename 'mysql-bin.000001', position '154'161214 10:24:34 [00] Writing backup-my.cnf161214 10:24:34 [00]        ...done161214 10:24:34 [00] Writing xtrabackup_info161214 10:24:34 [00]        ...donextrabackup: Transaction log of lsn (2677865) to (2677874) was copied.161214 10:24:34 completed OK![root@rhel7 2016-12-14_10-24-27]# ls -ltotal 77876-rw-r-----. 1 root root      425 Dec 14 10:24 backup-my.cnf-rw-r-----. 1 root root      307 Dec 14 10:24 ib_buffer_pool-rw-r-----. 1 root root 79691776 Dec 14 10:24 ibdata1drwxr-x---. 2 root root     4096 Dec 14 10:24 mysqldrwxr-x---. 2 root root     8192 Dec 14 10:24 performance_schemadrwxr-x---. 2 root root     8192 Dec 14 10:24 sysdrwxr-x---. 2 root root       87 Dec 14 10:24 test-rw-r-----. 1 root root       21 Dec 14 10:24 xtrabackup_binlog_info-rw-r-----. 1 root root      113 Dec 14 10:24 xtrabackup_checkpoints-rw-r-----. 1 root root      469 Dec 14 10:24 xtrabackup_info-rw-r-----. 1 root root     2560 Dec 14 10:24 xtrabackup_logfile#记录binlog位置的文件[root@rhel7 2016-12-14_10-24-27]# cat xtrabackup_binlog_infomysql-bin.000001        154

有了这个binlog日志位置就可以应用binlog恢复备份到宕机时间的数据

mysqlbinlog --start-position=154 mysql-bin.000001 | mysql -uroot -p123456