使用Xtrabackup进行MySQL备份：
使用Xtrabackup进行MySQL备份：一、安装
1、简介
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
(1)备份过程快速、可靠；
(2)备份过程不会打断正在执行的事务；
(3)能够基于压缩等功能节约磁盘空间和流量；
(4)自动实现备份检验；
(5)还原速度快；
2、安装
其最新版的软件可从 http://www.percona.com/software/percona-xtrabackup/ 获得。本文基于CentOS 6.5 的系统，因此，直接下载相应版本的rpm包安装即可，这里不再演示其过程。
# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm
# yum installpercona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm
Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
二、备份的实现
1、完全备份
# innobackupex --user=DBUSER --password=DBUSERPASS/path/to/BACKUP-DIR/
1)、为备份操作创建MySQL账户：
MariaDB [(none)]> grant> Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2)、创建备份文件目录：
# mkdir -p /hotbackup
对数据库进行完整备份，如果在从库上进行备份，请添加--slave_info –safe-slave-backup参数来获取复制主库的binlog信息。默认备份会将当前库的binlog信息保存到xtrabackup_binlog_info文件中，而--slave_info参数将复制主库的binlog信息保存到xtrabackup_slave_info中，如果使用备份来添加复制，请注意选取正确的binlog信息。
3)、创建innobackupex的配置文件：
# vim /usr/local/etc/my.cnf
datadir = "/data/mysql/"
innodb_data_home_dir = "/data/mysql/"
innodb_data_home_path = "ibdata1:10M:autoextend"
innodb_log_group_home_dir = "/data/mysql/"
innodb_log_file_in_group = 2
innodb_log_file_size = 1G
4)、进行完整备份：
# innobackupex --user=backup --password=pancou/hotbackup/full
170427 17:00:51 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
170427 17:00:52version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'backup'(using password: YES).
170427 17:00:52version_check Connected to MySQL server
170427 17:00:52version_check Executing a version check against the server...
170427 17:00:52version_check Done.
170427 17:00:52 Connecting to MySQL server host: localhost, user: backup, password: set, port: not set, socket: not set
Using server version 10.1.22-MariaDB
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision> xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: open files limit requested 0, set to 60000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
......
.... .....
170427 17:00:57 Executing UNLOCK TABLES
170427 17:00:57 All tables unlocked
170427 17:00:57 Backup created in directory '/hotbackup/full/2017-04-27_17-00-51/'
MySQL binlog position: filename 'mysql-bin.000007', position '941', GTID of the last change '0-1-270'
170427 17:00:57 Writing backup-my.cnf
170427 17:00:57 ...done
170427 17:00:57 Writing xtrabackup_info
170427 17:00:57 ...done
xtrabackup: Transaction log of lsn (21843393) to (21843393) was copied.
170427 17:00:57 completed OK!
从信息中会发现备份被创建在'/hotbackup/full/2017-04-27_17-00-51/'
# innobackupex--defaults-file=/etc/my.cnf --user=backup --password=pancou --socket=/var/lib/mysql/mysql.sock/hotbackup/full
其他选项:
--no-timestamp，指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹。
--default-file，指定配置文件，用来配置innobackupex的选项。
# innobackupex--defaults-file=/etc/my.cnf --user=backup --password=pancou --socket=/var/lib/mysql/mysql.sock/hotbackup/full --no-timestamp
.....
170427 17:14:43 Executing UNLOCK TABLES
170427 17:14:43 All tables unlocked
170427 17:14:43 Backup created in directory '/hotbackup/full/'
MySQL binlog position: filename 'mysql-bin.000007', position '941', GTID of the last change '0-1-270'
170427 17:14:43 Writing backup-my.cnf
170427 17:14:43 ...done
170427 17:14:43 Writing xtrabackup_info
170427 17:14:43 ...done
xtrabackup: Transaction log of lsn (21843393) to (21843393) was copied.
170427 17:14:43 completed OK!
使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中。
在备份的同时，innobackupex还会在备份目录中创建如下文件：
(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；
(5)backup-my.cnf —— 备份命令用到的配置选项信息；
在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。
# ls /hotbackup/full/
backup-my.cnfib_logfile1performance_schematestdb xtrabackup_checkpoints
ibdata1 ibtmp1 sakila xtrabackup_binlog_info xtrabackup_info
ib_logfile0 mysql test xtrabackup_binlog_pos_innodbxtrabackup_logfile
MariaDB > drop table emp;
Query OK, 0 rows affected (0.10 sec)
2、恢复一个完全备份
1)、准备(prepare)一个完全备份
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
innobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：
# innobackupex --apply-log/path/to/BACKUP-DIR
如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
1204079:01:36InnoDB: Starting shutdown...
1204079:01:40InnoDB: Shutdown completed; log sequence number 92036620
120407 09:01:40innobackupex: completed OK!
在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。
# innobackupex --apply-log --use-memory=4G /hotbackup/full/
.... .......
InnoDB: 5.7.13 started; log sequence number 21843477
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 21843496
170427 17:23:19 completed OK!
2)、正式从一个完全备份中恢复数据
注意：恢复时要先关闭MySQL
innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。
# innobackupex --copy-back/path/to/BACKUP-DIR
如果执行正确，其输出信息的最后几行通常如下：
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2012-04-07_08-17-03'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Finished copying back files.
120407 09:36:10innobackupex: completed OK!
请确保如上信息的最行一行出现“innobackupex: completed OK!”。
当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：
# chown -Rmysql:mysql/mydata/data/
# service mysql stop
Shutting down MySQL...
# mv /data/mysql/ /opt/mysql_bak
# ls /data/
# mkdir /data/mysql
# rm -rf /data/mysql/
# ls /data/
# innobackupex --defaults-file=/usr/local/etc/my.cnf --copy-back /hotbackup/full/
# chown -R mysql:mysql /data/mysql/
# service mysql start
Starting MySQL.170427 17:35:13 mysqld_safe Logging to '/data/mysql/localhost.err'.
170427 17:35:13 mysqld_safe Starting mysqld daemon with databases from /data/mysql
MariaDB > show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| emp |
+------------------+
1 row in set (0.00 sec)
3、使用innobackupex进行增量备份和恢复
每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。
要实现第一次增量备份，可以使用下面的命令进行：
# innobackupex --incremental /backup --incremental-basedir=BASEDIR
其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向(上一次的增量备份)所在的目录。
需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
1)、执行增量备份
MariaDB > insert into emp(name) values('tom'),('jerry');
Query OK, 2 rows affected (0.03 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > select * from emp;
+------+-------+
|> +------+-------+
| 1 | z1 |
| 2 | z2 |
| NULL | tom |
| NULL | jerry |
+------+-------+
4 rows in set (0.00 sec)
# innobackupex--user=backup --password=pancou --incremental /hotbackup/increment-1 --incremental-basedir=/hotbackup/full/ --no-timestamp
MariaDB > create table test(id int(3),name char(6));
Query OK, 0 rows affected (0.22 sec)
MariaDB > insert into test(name) values('lucy'),('jerry');
Query OK, 2 rows affected (0.04 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > select * from test;
+------+-------+
|> +------+-------+
| NULL | lucy|
| NULL | jerry |
+------+-------+
2 rows in set (0.00 sec)
# innobackupex--user=backup --password=pancou --incremental /hotbackup/increment-2 --incremental-basedir=/hotbackup/increment-1 --no-timestamp
2)、恢复增量备份
增量备份的恢复大体需要三个步骤：
恢复基础备份(全备)
恢复增量备份到基础备份(开始恢复的增量备份需要加--redo-only参数，到最后一次增量备份去掉--redo-only参数)
对整体的基础备份进行恢复，回滚那些未提交的数据
“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：
(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。
(2)基于所有的备份将未提交的事务进行“回滚”。
两个参数：
--apply-log：表示只应用XtraBackup日志中已提交的事务日志，回滚未提交的事务数据。
--redo-only：表示只应用XtraBackup日志中已提交的事务日志，不回滚未提交的事务数据
于是，操作就变成了：
# innobackupex --apply-log --redo-only BASE-DIR
接着执行：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
而后是第二个增量:
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
而后是第三个增量(注意如果是最后一个增量备份时去掉--redo-only参数，回滚xtrabackup日志中那些还未提交的数据)：
# innobackupex --apply-logBASE-DIR --incremental-dir=INCREMENTAL-DIR-3
把所有合在一起的基础备份整体进行一次apply操作，回滚未提交的数据：
# innobackupex --apply-log --use-memory=4G BASE-DIR
其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指的是第一次增量备份的目录，INCREMENTAL-DIR-2指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；
一个示例：
全备：
# innobackupex--defaults-file=/etc/my.cnf --user=backup --password=pancou --socket=/var/lib/mysql/mysql.sock/hotbackup/full --no-timestamp
.... .....
170428 12:58:31 Executing UNLOCK TABLES
170428 12:58:31 All tables unlocked
170428 12:58:31 Backup created in directory '/hotbackup/full/'
MySQL binlog position: filename 'mysql-bin.000001', position '1818', GTID of the last change '0-1-9'
170428 12:58:31 Writing backup-my.cnf
170428 12:58:31 ...done
170428 12:58:31 Writing xtrabackup_info
170428 12:58:31 ...done
xtrabackup: Transaction log of lsn (21885782) to (21885782) was copied.
170428 12:58:31 completed OK!
第一次增量备份：
MariaDB > create table t3(id int(3),name char(8));
Query OK, 0 rows affected (0.21 sec)
MariaDB > insert into t3 values(1,'tom'),(2,'jerry');
Query OK, 2 rows affected (0.05 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > select * from t3;
+------+-------+
|> +------+-------+
| 1 | tom |
| 2 | jerry |
+------+-------+
2 rows in set (0.00 sec)
# innobackupex--defaults-file=/etc/my.cnf --user=backup --password=pancou --socket=/var/lib/mysql/mysql.sock--incremental-basedir=/hotbackup/full --incremental /hotbackup/increment-1 --no-timestamp
.... ....
170428 13:02:52 Executing UNLOCK TABLES
170428 13:02:52 All tables unlocked
170428 13:02:52 Backup created in directory '/hotbackup/increment-1/'
MySQL binlog position: filename 'mysql-bin.000001', position '2304', GTID of the last change '0-1-12'
170428 13:02:52 Writing backup-my.cnf
170428 13:02:52 ...done
170428 13:02:52 Writing xtrabackup_info
170428 13:02:52 ...done
xtrabackup: Transaction log of lsn (21894630) to (21894630) was copied.
170428 13:02:52 completed OK!
第二次增量备份：
MariaDB > insert into t3 values(3,'lucy'),(4,'linda');
Query OK, 2 rows affected (0.04 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > select * from t3;
+------+-------+
|> +------+-------+
| 1 | tom |
| 2 | jerry |
| 3 | lucy|
| 4 | linda |
+------+-------+
4 rows in set (0.00 sec)
# innobackupex--defaults-file=/etc/my.cnf --user=backup --password=pancou --socket=/var/lib/mysql/mysql.sock--incremental-basedir=/hotbackup/increment-1 --incremental /hotbackup/increment-2 --no-timestamp
..... ......
170428 13:04:52 Executing UNLOCK TABLES
170428 13:04:52 All tables unlocked
170428 13:04:52 Backup created in directory '/hotbackup/increment-2/'
MySQL binlog position: filename 'mysql-bin.000001', position '2477', GTID of the last change '0-1-13'
170428 13:04:52 Writing backup-my.cnf
170428 13:04:52 ...done
170428 13:04:52 Writing xtrabackup_info
170428 13:04:52 ...done
xtrabackup: Transaction log of lsn (21896433) to (21896433) was copied.
170428 13:04:52 completed OK!
第三次增量备份：
MariaDB > insert into t3 values(5,'xiaofeng'),(6,'linchong');
Query OK, 2 rows affected (0.05 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > select * from t3;
+------+----------+
|> +------+----------+
| 1 | tom |
| 2 | jerry |
| 3 | lucy |
| 4 | linda |
| 5 | xiaofeng |
| 6 | linchong |
+------+----------+
6 rows in set (0.00 sec)
# innobackupex--defaults-file=/etc/my.cnf --user=backup --password=pancou --socket=/var/lib/mysql/mysql.sock--incremental-basedir=/hotbackup/increment-2 --incremental /hotbackup/increment-3 --no-timestamp
.... ....
170428 13:09:24 Executing UNLOCK TABLES
170428 13:09:24 All tables unlocked
170428 13:09:24 Backup created in directory '/hotbackup/increment-3/'
MySQL binlog position: filename 'mysql-bin.000001', position '2657', GTID of the last change '0-1-14'
170428 13:09:24 Writing backup-my.cnf
170428 13:09:24 ...done
170428 13:09:24 Writing xtrabackup_info
170428 13:09:24 ...done
xtrabackup: Transaction log of lsn (21898226) to (21898226) was copied.
170428 13:09:24 completed OK!
# ls /hotbackup/
fullincrement-1increment-2increment-3
预备恢复：
# innobackupex --apply-log --redo-only --use-memory=4 /hotbackup/full/
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 21860430
InnoDB: Number of pools: 1
170428 12:38:26 completed OK!
# innobackupex --apply-log --redo-only --use-memory=4 /hotbackup/full/ --incremental-dir=/hotbackup/increment-1/
170428 13:11:12 Copying /hotbackup/increment-1/testdb/test.frm to ./testdb/test.frm
170428 13:11:12 ...done
170428 13:11:12 Copying /hotbackup/increment-1//xtrabackup_binlog_info to ./xtrabackup_binlog_info
170428 13:11:12 ...done
170428 13:11:12 Copying /hotbackup/increment-1//xtrabackup_info to ./xtrabackup_info
170428 13:11:12 ...done
170428 13:11:12 completed OK!
# innobackupex --apply-log --redo-only --use-memory=4 /hotbackup/full/ --incremental-dir=/hotbackup/increment-2/
170428 13:11:22 Copying /hotbackup/increment-2/testdb/db.opt to ./testdb/db.opt
170428 13:11:22 ...done
170428 13:11:22 Copying /hotbackup/increment-2/testdb/t1.frm to ./testdb/t1.frm
170428 13:11:22 ...done
170428 13:11:22 Copying /hotbackup/increment-2/testdb/test.frm to ./testdb/test.frm
170428 13:11:22 ...done
170428 13:11:22 Copying /hotbackup/increment-2//xtrabackup_binlog_info to ./xtrabackup_binlog_info
170428 13:11:22 ...done
170428 13:11:22 Copying /hotbackup/increment-2//xtrabackup_info to ./xtrabackup_info
170428 13:11:22 ...done
170428 13:11:22 completed OK!
# innobackupex --apply-log --use-memory=4 /hotbackup/full/ --incremental-dir=/hotbackup/increment-3/
.... ....
InnoDB: Setting file './ibtmp1'>
InnoDB: File './ibtmp1'> InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 21898773
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 21898885
170428 13:13:23 completed OK!
# innobackupex --apply-log--use-memory=4 /hotbackup/full/
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 21898904
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 21898923
170428 13:15:33 completed OK!
# service mysql stop
Shutting down MySQL..
# mv /data/mysql/ /tmp/data_bak
# innobackupex --defaults-file=/usr/local/etc/my.cnf --copy-back /hotbackup/full/
..... ......
170428 13:16:55 Copying ./testdb/test.ibd to /data/mysql/testdb/test.ibd
170428 13:16:55 ...done
170428 13:16:55 Copying ./testdb/t1.ibd to /data/mysql/testdb/t1.ibd
170428 13:16:55 ...done
170428 13:16:55 Copying ./testdb/t2.ibd to /data/mysql/testdb/t2.ibd
170428 13:16:55 ...done
170428 13:16:55 completed OK!
# chown -R mysql:mysql /data/mysql/
# service mysql start
Starting MySQL.170428 12:21:59 mysqld_safe Logging to '/data/mysql/localhost.err'.
170428 12:21:59 mysqld_safe Starting mysqld daemon with databases from /data/mysql
MariaDB > select * from t3;
+------+----------+
|> +------+----------+
| 1 | tom |
| 2 | jerry |
| 3 | lucy |
| 4 | linda |
| 5 | xiaofeng |
| 6 | linchong |
+------+----------+
6 rows in set (0.00 sec)
4、不完全恢复
找到记录备份结束时刻的binlog的位置文件xtrabackup_binlog_info,查看结束时刻binglogd 名称和位置：
# cat /hotbackup/increment-3/xtrabackup_binlog_info
mysql-bin.00000126570-1-14
查看当前数据库的binglog文件和位置：
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 313 |
+------------------+-----------+
1 row in set (0.00 sec)
从全备中恢复数据库，恢复全备，之后再从热备结束时刻binlog的位置开始，恢复到误操作时刻14：00之前的binlog:
mysqlbinlog --start-position='2657' --stop-datetime='2017-05-10 13:59:59' /data/mysql/mysql-bin.000001 | mysql -uroot -ppancou
跳过故障点的误操作时间点，应用之后的日志：
mysqlbinlog--stop-datetime='2016-05-10 14:01:00' /data/mysql/mysql-bin.000001 | mysql -uroot -ppancou
页:
[1]