使用xtrabackup进行MySQL数据库备份
前面介绍mysqldump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用mysqldump备份就不太适合了。而使用lvm快照功能对数据库进行备份，可以实现几乎热备的功能，但备份过程较为复杂，不过现在倒是有个工具mylvmbackup可以实现自动化备份。
前面我们也说道，使用物理备份时最快的，那有没有办法实现物理热备呢？
目前主流的有两个工具可以实现热备：ibbackup和xtrabackup；ibbackup是商业软件，没服务器授权为5000美元，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我们这里就来介绍xtrabackup的使用。
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
- (1)备份过程快速、可靠；
- (2)备份过程不会打断正在执行的事务；
- (3)能够基于压缩等功能节约磁盘空间和流量；
- (4)自动实现备份检验；
- (5)还原速度快；
Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构,xtrabackup命令只备份数据文件,并不备份数据表结构(.frm)，所以使用xtrabackup恢复的时候必须有对应表结构文件(.frm)。用innobackupex命令，此命令相当于冷备份，复制数据目录的索引，数据，结构文件，但会有短暂的锁表（时间依赖于MyISAM大小）。；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中
二、使用xtrabackup实现对MySQL的备份：
2.1 完全备份：
开始使用xtarbackup进行备份：参数按照要备份的数据库信息填写.--defaults-file=/etc/my.cnf参数必须要放在第一个参数，不然会报错，host参数和user参数要根据MySQL数据库中的user表信息配置不然会连接不上MySQL服务器。 查看要备份的数据库的信息： [root@localhost104 opt]# ps -ef | grep mysqlroot 12567 1 0 Jul27 ? 00:00:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/usr/local/mysql/data/ --pid-file=/usr/local/mysql/mysql.pid
mysql 12854 12567 0 Jul27 ? 00:01:58 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/mysql_safe.log --pid-file=/usr/local/mysql/mysql.pid --socket=/usr/local/mysql/mysql.sock --port=3306
创建全备份的目录并给相应的用户权限和属组 [root@localhost104 opt]# mkdir /opt/xtrabackup_full
[root@localhost104 opt]# chmod 700 /opt/xtrabackup_full
#全备份 [root@localhost 2017-07-29_14-05-49]# which xtrabackup
/usr/bin/xtrabackup
[root@localhost104 opt]# innobackupex --defaults-file=/etc/my.cnf --user='root' --password='123456' --host='localhost' --port=3306 --socket='/usr/local/mysql/mysql.sock' --parallel=3 /opt/xtrabackup_full ........ 170729 12:54:48 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '20125155'
xtrabackup: Stopping log copying thread.
.170729 12:54:48 >> log scanned up to (20125164)
170729 12:54:49 Executing UNLOCK TABLES
170729 12:54:49 All tables unlocked
170729 12:54:49 [00] Copying ib_buffer_pool to /opt/xtrabackup/2017-07-29_12-54-44/ib_buffer_pool
170729 12:54:49 [00] ...done
170729 12:54:49 Backup created in directory '/opt/xtrabackup/2017-07-29_12-54-44/'
MySQL binlog position: filename 'mysql_bin.000414', position '154'
170729 12:54:49 [00] Writing /opt/xtrabackup/2017-07-29_12-54-44/backup-my.cnf
170729 12:54:49 [00] ...done
170729 12:54:49 [00] Writing /opt/xtrabackup/2017-07-29_12-54-44/xtrabackup_info
170729 12:54:49 [00] ...done
xtrabackup: Transaction log of lsn (20125155) to (20125164) was copied.
170729 12:54:49 completed OK!
备份后的文件：
在备份的同时，备份数据会在备份目录下创建一个以当前日期时间为名字的目录存放备份文件：
[root@localhost 2017-07-29_12-34-51]# pwd/opt/xtrabackup/2017-07-29_12-34-51
[root@localhost 2017-07-29_12-34-51]# ll
total 77876
-rw-r----- 1 root root 425 Jul 29 12:34 backup-my.cnf
-rw-r----- 1 root root 546 Jul 29 12:34 ib_buffer_pool
-rw-r----- 1 root root 79691776 Jul 29 12:34 ibdata1
drwxr-x--- 2 root root 4096 Jul 29 12:34 mysql
drwxr-x--- 2 root root 8192 Jul 29 12:34 performance_schema
drwxr-x--- 2 root root 8192 Jul 29 12:34 sys
drwxr-x--- 2 root root 146 Jul 29 12:34 testdb
-rw-r----- 1 root root 21 Jul 29 12:34 xtrabackup_binlog_info
-rw-r----- 1 root root 115 Jul 29 12:34 xtrabackup_checkpoints
-rw-r----- 1 root root 566 Jul 29 12:34 xtrabackup_info
-rw-r----- 1 root root 2560 Jul 29 12:34 xtrabackup_logfile
各文件说明：
[root@localhost 2017-07-29_12-34-51]# cat xtrabackup_binlog_infomysql_bin.000414 154
[root@localhost 2017-07-29_12-34-51]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 20125155
last_lsn = 20125164
compact = 0
recover_binlog_info = 0
[root@localhost 2017-07-29_12-34-51]# cat xtrabackup_info
uuid = 45d5fa84-7417-11e7-bdad-000c297442b7
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=root --password=... --host=localhost --port=3306 --socket=/usr/local/mysql/mysql.sock --parallel=3 /opt/xtrabackup
tool_version = 2.4.8
ibbackup_version = 2.4.8
server_version = 5.7.18-log
start_time = 2017-07-29 12:34:52
end_time = 2017-07-29 12:34:56
lock_time = 0
binlog_pos = filename 'mysql_bin.000414', position '154'
innodb_from_lsn = 0
innodb_to_lsn = 20125155
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
(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目录来存储备份数据。
另外还需注意：备份数据库的用户需要具有相应权限，如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：grant us
grant user 'bkpuser'@'localhost' identified by '123';
grant reload,lock tables,replication client on *.* to 'bkpuser'@'localhost';
flush privileges;
(三)增量备份 创造第一个增量数据表xtrabackup_increment_1_t mysql> create table testdb.xtrabackup_increment_1_t(id int,name varchar(20));
Query OK, 0 rows affected (0.32 sec)
mysql> show tables;
+--------------------------+
| Tables_in_testdb |
+--------------------------+
| add_data1 |
| add_data2 |
| student_t |
| xtrabackup_increment_1_t |
+--------------------------+
4 rows in set (0.00 sec)
mysql> insert into xtrabackup_increment_1_t values(1,'anzhen'),(2,'anzhu');
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from testdb.xtrabackup_increment_1_t;
+------+--------+
| id | name |
+------+--------+
| 1 | anzhen |
| 2 | anzhu |
+------+--------+
2 rows in set (0.03 sec)
创建增量备份目录 [root@localhost opt]# mkdir -p /opt/xtrabackup_increment
[root@localhost opt]# chmod 700 /opt/xtrabackup_increment
增量备份命令1： 注意：第一次执行增备的时候--incremental-basedir这个参数是填写最近一次全备的目录做为增备的基础目录 [root@localhost opt]# innobackupex --defaults-file=/etc/my.cnf --user='root' --password='123456' --socket='/usr/local/mysql/mysql.sock' --port=3306 --incremental --incremental-basedir='/opt/xtrabackup_full/2017-07-29_12-34-51/' --parallel=3 /opt/xtrabackup_increment
如果有completed OK!说明增量备份成功！ xtrabackup: Transaction log of lsn (20132701) to (20132710) was copied. 170729 14:05:57 completed OK!
查看相关全备份信息： [root@localhost104 2017-07-29_12-54-44]# cat xtrabackup_binlog_info
mysql_bin.000414 154
[root@localhost104 2017-07-29_12-54-44]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 20125155
last_lsn = 20125164
compact = 0
recover_binlog_info = 0
[root@localhost104 2017-07-29_12-54-44]# pwd
/opt/xtrabackup_full/2017-07-29_12-54-44
说明全备份的最后一个日志文件是mysql_bin.000414，位置是154，from_lsn 来自的日志号为0，to_lsn = 20125155。下个增量备份要用到这个to_lsn = 20125155。才可以全备和曾备连接起来。 查看相关增量备份信息：
[root@localhost 2017-07-29_14-05-49]# cat xtrabackup_binlog_info
mysql_bin.000414 667
[root@localhost 2017-07-29_14-05-49]# cat xtrabackup_binlog_info
mysql_bin.000414 667
[root@localhost 2017-07-29_14-05-49]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 20125155
to_lsn = 20132701
last_lsn = 20132710
compact = 0
recover_binlog_info = 0
[root@localhost 2017-07-29_14-05-49]# pwd
/opt/xtrabackup_increment/2017-07-29_14-05-49
说明增备份的最后一个日志文件是mysql_bin.000414，位置是667，from_lsn 来自的日志号为20125155，也就是最近一次全部或者增备的to_lsn号。这次增备为to_lsn = 20132701。
创造第二个增量数据表xtrabackup_increment_2_t
mysql> create table testdb.xtrabackup_increment_2_t(id int,name varchar(20));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into xtrabackup_increment_2_t values(113,'ansfzhen'),(1232,'ansfzhu');
Query OK, 2 rows affected (0.21 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+--------------------------+
| Tables_in_testdb |
+--------------------------+
| add_data1 |
| add_data2 |
| student_t |
| xtrabackup_increment_1_t |
| xtrabackup_increment_2_t |
+--------------------------+
5 rows in set (0.00 sec)
mysql> select * from testdb.xtrabackup_increment_2_t;
+------+----------+
| id | name |
+------+----------+
| 113 | ansfzhen |
| 1232 | ansfzhu |
+------+----------+
2 rows in set (0.04 sec)
创建第2次增备： 注意：第二次或者以后执行增备的时候--incremental-basedir这个参数是填写最近的上一次增备备的目录做为这次增备的基础目录 [root@localhost 2017-07-29_14-05-49]# innobackupex --defaults-file=/etc/my.cnf --user='root' --password='123456' --socket='/usr/local/mysql/mysql.sock' --port=3306 --incremental --incremental-basedir='/opt/xtrabackup_increment/2017-07-29_14-05-49/' --parallel=3 /opt/xtrabackup_increment
信息如下： 170729 14:24:04 [00] Writing /opt/xtrabackup_increment/2017-07-29_14-23-44/xtrabackup_info
170729 14:24:04 [00] ...done
xtrabackup: Transaction log of lsn (20139866) to (20139875) was copied.
170729 14:24:05 completed OK!
记录了lsn号的备份信息。OK就成功！
创建第3次增备数据：
mysql> create table testdb.xtrabackup_increment_3_t(id int,name varchar(20));
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> insert into xtrabackup_increment_3_t values(3252,'ansfssdffzhen'),(5332,'awthu');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
[root@localhost xtrabackup_increment]# innobackupex --defaults-file=/etc/my.cnf --user='root' --password='123456' --socket='/usr/local/mysql/mysql.sock' --port=3306 --incremental --incremental-basedir='/opt/xtrabackup_increment/2017-07-29_14-23-44/' --parallel=3 /opt/xtrabackup_increment
（四）误操作 mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.16 sec)
mysql> drop database testdb;
Query OK, 6 rows affected (0.82 sec)
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)
mysql> create database test_new;
Query OK, 1 row affected (0.00 sec)
mysql> create table test_new.xtrabackup_increment_4_t(id int,name varchar(20));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into test_new.xtrabackup_increment_4_t values(32,'3zhen'),(242,'asfwthu');
Query OK, 2 rows affected (0.09 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_new.xtrabackup_increment_4_t;
+------+---------+
| id | name |
+------+---------+
| 32 | 3zhen |
| 242 | asfwthu |
+------+---------+
2 rows in set (0.00 sec)
（四）全备恢复和增备恢复。现在要恢复四个xtrabackup_increment_4_t 数据表的数据和误操作的数据 4.1 停掉MySQL服务 [root@localhost104 mysql]# ps -ef | grep mysql
root 12567 1 0 Jul27 ? 00:00:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/usr/local/mysql/data/ --pid-file=/usr/local/mysql/mysql.pid
mysql 12854 12567 0 Jul27 ? 00:02:08 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/mysql_safe.log --pid-file=/usr/local/mysql/mysql.pid --socket=/usr/local/mysql/mysql.sock --port=3306
root 48441 48401 0 13:53 pts/3 00:00:00 mysql -uroot -p
root 49567 45091 0 15:45 pts/1 00:00:00 grep --color=auto mysql
[root@localhost104 mysql]#
[root@localhost104 mysql]#
[root@localhost104 mysql]# cd /usr/local/mysql/
[root@localhost104 support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@localhost104 support-files]# ./mysql.server stop
Shutting down MySQL...... SUCCESS!
[root@localhost104 support-files]# ps -ef | grep mysql
root 48441 48401 0 13:53 pts/3 00:00:00 mysql -uroot -p
root 49614 45091 0 15:46 pts/1 00:00:00 grep --color=auto mysql
#把原来的MySQL数据目录进行重命名 [root@localhost104 mysql]#cd /usr/local/mysql
[root@localhost104 mysql]#mv /usr/local/mysql/data /usr/local/mysql/data_bak
[root@localhost104 mysql]#mkdir /usr/local/mysql/data
[root@localhost104 mysql]#chown mysql:mysql /usr/local/mysql/data
建完备份之后数据被没有马上可以被还原，需要回滚未提交事务，前滚提交事务，让数据库文件保持一致性。
innobackupex使用—apply-log来做预备备份。
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
innobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：
[root@localhost 2017-07-29_12-34-51]# pwd
/opt/xtrabackup/2017-07-29_12-34-51
[root@localhost 2017-07-29_12-34-51]# innobackupex --apply-log /opt/xtrabackup_full/2017-07-29_12-34-51
如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
xtrabackup: This target seems to be not prepared yet.
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 20125736
170729 13:30:29 completed OK!
成功后，备份可以被用来还原数据库了。
看到redo log已经出现。xtrabackup_开头的几个文件记录了一些日志偏移量的信息和日志名和时间等信息。在xtrabackup_checkpoints中记录了备份的模式：
backup_type = full-backuped。
xtrabackup_binlog_info：记录当前最新的LOG Position
xtrabackup_binlog_pos_innodb:innodb log postion
xtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化
xtrabackup_info:备份的一些具体信息日志4.3恢复全备份，/opt/xtrabackup_full/2017-07-29_12-34-51/为全备份的目录[root@localhost104 xtrabackup_full]# innobackupex --defaults-file=/etc/my.cnf --user='root' --password='123456' --host='localhost' --port=3306 --socket='/usr/local/mysql/mysql.sock' --copy-back --rsync /opt/xtrabackup_full/2017-07-29_12-34-51/检查全备份的文件是否已经恢复[root@localhost104 xtrabackup_full]#cd /usr/local/mysql/data[root@localhost104 data]# ll
total 188456
-rw-r----- 1 root root 546 Jul 29 15:58 ib_buffer_pool
-rw-r----- 1 root root 79691776 Jul 29 15:58 ibdata1
-rw-r----- 1 root root 50331648 Jul 29 15:58 ib_logfile0
-rw-r----- 1 root root 50331648 Jul 29 15:58 ib_logfile1
-rw-r----- 1 root root 12582912 Jul 29 15:58 ibtmp1
drwxr-x--- 2 root root 4096 Jul 29 15:58 mysql
drwxr-x--- 2 root root 8192 Jul 29 15:58 performance_schema
drwxr-x--- 2 root root 8192 Jul 29 15:58 sys
drwxr-x--- 2 root root 146 Jul 29 15:58 testdb
-rw-r----- 1 root root 25 Jul 29 15:58 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 566 Jul 29 15:58 xtrabackup_info