MySQL之XtraBackup实现完全备份、增量备份、数据还原

gddge11

XtraBackup：是一个开源的免费的备份工具，支持热备份，对Innodb和MyISam存储引擎都支持备份，Innodb为热备、MyISam为温备份、且支持将备份结果进行压缩存放、支持部分备份（如只备份某个库或者某个库中的某个表）、支持即时点数据还原、支持完全备份、支持增量备份、支持并行备份、流式备份、并行备份压缩、支持将单个表数据从一个数据库中导出然后导入另一个数据库中、支持将表恢复到一个不同的数据库server上

mylvmbackup:是一款可以实现自动化对LVM逻辑卷上的数据实现几乎热备（LVM+快照 因为要施加锁，而在上锁的时间中不能进行写入，所以为几乎热备份，而不是热备份；且LVM+快照无法进行自动化备份）

ibbackup：性能还不如XtraBackup且每台server授权为5000$

XtraBackup下载链接： https://www.percona.com/downloads/XtraBackup/LATEST/

[iyunv@node2 tool]# rpm -ivh percona-xtrabackup-24-2.4.1-1.el6.x86_64.rpm
warning: percona-xtrabackup-24-2.4.1-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.1-1.el6.x86_64
perl(DBD::mysql) is needed by percona-xtrabackup-24-2.4.1-1.el6.x86_64
有依赖，直接用官方的yum源安装，按照下面的连接建立一个yum源和gpg_check源
http://www.cnblogs.com/zejin2008/p/4649327.html

[iyunv@node2 tool]# yum install percona-xtrabackup
[iyunv@node2 tool]# rpm -ql percona-xtrabackup
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-2.3.6
/usr/share/doc/percona-xtrabackup-2.3.6/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

例1：
下面将演示 一次完全备份+完全备份后数据改变，用innobackupex+二进制日志 进行数据还原

先进行一次完全备份
[iyunv@node2 backup]# innobackupex   --user=root --password=123  /backup/
..............

170216 11:13:17 Executing UNLOCK TABLES
170216 11:13:17 All tables unlocked
170216 11:13:17 Backup created in directory '/backup//2017-02-16_11-12-46'
MySQL binlog position: filename 'master-bin.000016', position '120'
170216 11:13:17 [00] Writing backup-my.cnf
170216 11:13:17 [00]        ...done
170216 11:13:17 [00] Writing xtrabackup_info
170216 11:13:17 [00]        ...done
xtrabackup: Transaction log of lsn (1739022) to (1739022) was copied.
170216 11:13:17 completed OK!
[iyunv@node2 backup]# cd /backup/2017-02-16_11-12-46/
[iyunv@node2 2017-02-16_11-12-46]# ll
total 12436
drwx------. 2 root root     4096 Feb 16 11:13 api
drwx------. 2 root root     4096 Feb 16 11:12 archiver
自动备份的配置文件和备份命令用到的配置选项信息
-rw-r-----. 1 root root     387 Feb 16 11:13 backup-my.cnf
drwx------. 2 root root     4096 Feb 16 11:12 config
drwx------. 2 root root     4096 Feb 16 11:12 data
drwx------. 2 root root     4096 Feb 16 11:12 hello
ibdata1为备份的表空间文件
-rw-r-----. 1 root root 12582912 Feb 16 11:12 ibdata1
drwx------. 2 root root     4096 Feb 16 11:12 install
drwx------. 2 root root     4096 Feb 16 11:12 my
drwx------. 2 root root     4096 Feb 16 11:12 mydb
drwx------. 2 root root     4096 Feb 16 11:12 mysql
drwx------. 2 root root     4096 Feb 16 11:12 performance_schema
drwx------. 2 root root     4096 Feb 16 11:12 source
drwx------. 2 root root     4096 Feb 16 11:13 static
drwx------. 2 root root     4096 Feb 16 11:13 template
drwx------. 2 root root     4096 Feb 16 11:12 test
drwx------. 2 root root     4096 Feb 16 11:12 testdb
drwx------. 2 root root     4096 Feb 16 11:13 uc_client
drwx------. 2 root root     4096 Feb 16 11:12 uc_server
drwx------. 2 root root     61440 Feb 16 11:13 ultrax
备份这一刻你的二进制文件和position
-rw-r-----. 1 root root     22 Feb 16 11:13 xtrabackup_binlog_info
此文件描述了是什么备份（完全|增量）及备份从哪里开始哪里结束
-rw-r-----. 1 root root     113 Feb 16 11:13 xtrabackup_checkpoints
-rw-r-----. 1 root root     65 Feb 16 11:13 xtrabackup_info
-rw-r-----. 1 root root     2560 Feb 16 11:13 xtrabackup_logfile

[iyunv@node2 2017-02-16_11-12-46]# cat xtrabackup_binlog_info
master-bin.000016        120  使用的是master-bin.000016日志 position为120
[iyunv@node2 2017-02-16_11-12-46]# cat xtrabackup_checkpoints
backup_type = full-backuped 为一个完全备份
from_lsn = 0   从第0号开始备份
to_lsn = 1739022 到1739022号备份结束
last_lsn = 1739022 最后的备份点，下次进行增量备份则从1739022开始备份
compact = 0
recover_binlog_info = 0

做还原前的准备操作

--apply-log 这个参数会将为提交的事务进行回滚，将已近提交的事务从事务日志同步到数据文件；防止备份时有的事务还没有完成，再次启动mysql时进行长时间的数据修复

[iyunv@node2 2017-02-16_11-12-46]# innobackupex --apply-log /backup/2017-02-16_11-12-46/
.......
InnoDB: Shutdown completed; log sequence number 1739286
170216 11:32:39 completed OK!

做完全备份后数据又发生了改变
[iyunv@node2 2017-02-16_11-12-46]# mysql -uroot -p
Enter password:
mysql> use mydb
mysql> insert into test1 (cid,name,sid) values (6,'tom','F');
Query OK, 1 row affected (0.08 sec)

mysql> insert into test1 (cid,name,sid) values (7,'jerry','F');
Query OK, 1 row affected (0.01 sec)
mysql> flush logs; 记得滚动日志
Query OK, 0 rows affected (0.10 sec)

mysql> \q

模拟数据损坏，删除/mydata/data/*,再次之前一定要停止mysql
[iyunv@node2 2017-02-16_11-12-46]# cd /mydata/data/
[iyunv@node2 data]# cp master-bin.000016 /root
[iyunv@node2 data]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[iyunv@node2 data]# rm -rf /mydata/data/*
[iyunv@node2 data]# cd /usr/local/mysql/
进行mysql初始化
[iyunv@node2 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
[iyunv@node2 mysql]# mysql -uroot -p
Enter password: 此时没有密码，直接回车
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database        |
+--------------------
| information_schema |
| mysql         |
| performance_schema |
| test          |
+--------------------+
mysql> OK MySQL可以正常使用，但没有数据。

[iyunv@node2 mysql]# service mysqld stop 停止MySQL，在进行数据恢复是不需要启动MySQL？

进行数据恢复
[iyunv@node2 data]# innobackupex --copy-back /backup/2017-02-16_11-12-46/
Original data directory /mydata/data is not empty!
提示原始数据保存目录非空，进行删除操作
[iyunv@node2 data]# rm -rf ./*

[iyunv@node2 data]# innobackupex --copy-back /backup/2017-02-16_11-12-46/
170216 11:55:42 completed OK!

完全还原操作完成
[iyunv@node2 data]# ll
total 110728
drwx------. 2 root root     4096 Feb 16 11:55 api
drwx------. 2 root root     4096 Feb 16 11:55 archiver
drwx------. 2 root root     4096 Feb 16 11:55 config
drwx------. 2 root root     4096 Feb 16 11:55 data
drwx------. 2 root root     4096 Feb 16 11:55 hello
-rw-r-----. 1 root root 12582912 Feb 16 11:55 ibdata1
-rw-r-----. 1 root root 50331648 Feb 16 11:55 ib_logfile0
-rw-r-----. 1 root root 50331648 Feb 16 11:55 ib_logfile1
drwx------. 2 root root     4096 Feb 16 11:55 install
drwx------. 2 root root     4096 Feb 16 11:55 my
drwx------. 2 root root     4096 Feb 16 11:55 mydb
drwx------. 2 root root     4096 Feb 16 11:55 mysql
drwx------. 2 root root     4096 Feb 16 11:55 performance_schema
drwx------. 2 root root     4096 Feb 16 11:55 source
drwx------. 2 root root     4096 Feb 16 11:55 static
drwx------. 2 root root     4096 Feb 16 11:55 template
drwx------. 2 root root     4096 Feb 16 11:55 test
drwx------. 2 root root     4096 Feb 16 11:55 testdb
drwx------. 2 root root     4096 Feb 16 11:55 uc_client
drwx------. 2 root root     4096 Feb 16 11:55 uc_server
drwx------. 2 root root    61440 Feb 16 11:55 ultrax
-rw-r-----. 1 root root     25 Feb 16 11:55 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root     465 Feb 16 11:55 xtrabackup_info

还原回来的数据用户和属组改变为了root,改为mysql用户
[iyunv@node2 data]# chown -R mysql.mysql /mydata/data/
[iyunv@node2 data]# service mysqld start
Starting MySQL..... SUCCESS!
[iyunv@node2 data]# mysql -uroot -p
Enter password: 进行数据还原后，密码也会被还原，现在密码为123（原始密码）

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
mysql> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test1;
+-----+-------+-----+
| cid | name  | sid |
+-----+-------+-----+
|  1 | zxl  | A   |
|  3 | fade | C   |
|  4 | faded | D   |
|  5 | my   | E   |
+-----+-------+-----+
4 rows in set (0.10 sec)

可以看到数据恢复了但却没有cid=6,和cid=7的数据，这就要靠二进制日志进行即时点还原

进行即时点还原
[iyunv@node2 data]# mysqlbinlog /root/master-bin.000016 > /tmp/abc.sql
[iyunv@node2 data]# mysql -uroot -p
Enter password:
mysql> set sql_log_bin=0; 进行二进制日志即时点还原之前先关闭二进制日志记录，避免产生大量I/O
Query OK, 0 rows affected (0.06 sec)

mysql> source /tmp/abc.sql; 读取保存的二进制日志，进行即时点还原
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)
mysql> use mydb;
Database changed
mysql> select * from test1;
+-----+-------+-----+
| cid | name  | sid |
+-----+-------+-----+
|   1 | zxl   | A   |
|   3 | fade  | C   |
|   4 | faded | D   |
|   5 | my   | E   |
|   6 | tom  | F   |
|   7 | jerry | F   |
+-----+-------+-----+
6 rows in set (0.00 sec)

可以看到数据全部已近还原


例2：
下面将演示 一次完全备份+两次增量备份 ，再用完全和增量备份进行数据还原
注:xtrabackup对Innodb支持完全和增量备份，对MyISam只支持完全备份

先让数据改变一次
mysql> insert into test1 (cid,name,sid) values (8,'hehe','H');
Query OK, 1 row affected (0.06 sec)

mysql> insert into test1 (cid,name,sid) values (9,'ao','I');
Query OK, 1 row affected (0.07 sec)
mysql> \q

做完全备份
[iyunv@node2 data]# innobackupex --user=root --password=123 /backup/
170216 12:19:09 Backup created in directory '/backup//2017-02-16_12-18-37'
MySQL binlog position: filename 'master-bin.000001', position '594'
170216 12:19:09 [00]        ...done
xtrabackup: Transaction log of lsn (1748576) to (1748576) was copied.
170216 12:19:09 completed OK!

让数据再次改变
mysql> insert into test1 (cid,name,sid) values (10,'en','G');
Query OK, 1 row affected (0.11 sec)

mysql> insert into test1 (cid,name,sid) values (11,'he','K');
Query OK, 1 row affected (0.10 sec)

做第一次增量备份
[iyunv@node2 data]# innobackupex --user=root --password=123 --incremental /backup/ --incremental-basedir=/backup/2017-02-16_12-18-37/

170216 12:26:25 All tables unlocked
170216 12:26:25 Backup created in directory '/backup//2017-02-16_12-26-08'
MySQL binlog position: filename 'master-bin.000001', position '1068'
170216 12:26:25 [00] Writing backup-my.cnf
170216 12:26:25 [00]        ...done
170216 12:26:25 [00] Writing xtrabackup_info
170216 12:26:25 [00]        ...done
xtrabackup: Transaction log of lsn (1749940) to (1749940) was copied.
170216 12:26:25 completed OK!

再次让数据改变
mysql> insert into test1 (cid,name,sid) values (12,'haha','L');
Query OK, 1 row affected (0.09 sec)
mysql> insert into test1 (cid,name,sid) values (13,'aaa','M');
Query OK, 1 row affected (0.01 sec)

做第二次增量备份
[iyunv@node2 2017-02-16_12-26-08]# innobackupex --user=root --password=123  --incremental  /backup/ --incremental-basedir=/backup/2017-02-16_12-26-08/

170216 12:33:29 Backup created in directory '/backup//2017-02-16_12-33-16'
MySQL binlog position: filename 'master-bin.000001', position '1545'
170216 12:33:29 completed OK!

下面是一次完全备份和两次增量备份的起始点和结束点，可以看出备份是连续的
[iyunv@node2 2017-02-16_12-18-37]# cd /backup/2017-02-16_12-18-37/
[iyunv@node2 2017-02-16_12-18-37]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1748576
last_lsn = 1748576
compact = 0
recover_binlog_info = 0
[iyunv@node2 2017-02-16_12-18-37]# cd /backup/2017-02-16_12-26-08/
[iyunv@node2 2017-02-16_12-26-08]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1748576
to_lsn = 1749940
last_lsn = 1749940
compact = 0
recover_binlog_info = 0

[iyunv@node2 2017-02-16_12-26-08]# cd /backup/2017-02-16_12-33-16/
[iyunv@node2 2017-02-16_12-33-16]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1749940
to_lsn = 1751359
last_lsn = 1751359
compact = 0
recover_binlog_info = 0


进行数据还原前的准备操作

先将完全备份数据和两次增量备份数据合并为一个完全备份
[iyunv@node2 backup]# innobackupex --apply-log --redo-only /backup/2017-02-16_12-18-37/
170216 12:37:09 completed OK!
注：有增量备份时，要指定只执行redo操作，而不是undo操作
/backup/2017-02-16_12-18-37/ 为完全备份目录
[iyunv@node2 backup]# innobackupex --apply-log --redo-only /backup/2017-02-16_12-18-37/  --incremental-dir=/backup/2017-02-16_12-26-08/
170216 12:39:06 completed OK!
/backup/2017-02-16_12-18-37/ 为完全备份目录
incremental-dir=/backup/2017-02-16_12-26-08/ 为第一次增量备份目录

[iyunv@node2 backup]# innobackupex --apply-log --redo-only /backup/2017-02-16_12-18-37/  --incremental-dir=/backup/2017-02-16_12-33-16/
170216 12:40:28 completed OK!
/backup/2017-02-16_12-18-37/ 为完全备份目录
/backup/2017-02-16_12-33-16/ 为第二次增量备份目录

[iyunv@node2 backup]# cd /backup2017-02-16_12-18-37/
[iyunv@node2 2017-02-16_12-18-37]# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1751359
last_lsn = 1751359
compact = 0
recover_binlog_info = 0
从0到1751359可以看出两个增量已经和完全备份合并为一个完全备份

注：如果此时还有数据发生改变，又没有做增量，则要备份二进制日志，用二进制日志做即时点还原，就和例1一样，用 完全+增量+二进制日志 做数据还原，简单

模拟数据损坏，进行数据还原
[iyunv@node2 2017-02-16_12-18-37]# cd ..
[iyunv@node2 backup]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[iyunv@node2 backup]# cd /mydata/data/
[iyunv@node2 data]# rm -rf ./*
数据还原只需--copy-back 完全备份目录便可
[iyunv@node2 data]# innobackupex --copy-back /backup/2017-02-16_12-18-37/
170216 12:47:17 completed OK!

[iyunv@node2 data]# chown -R mysql.mysql /mydata/data/
[iyunv@node2 data]# ll
total 12428
drwx------. 2 mysql mysql     4096 Feb 16 12:47 api
drwx------. 2 mysql mysql     4096 Feb 16 12:47 archiver
drwx------. 2 mysql mysql     4096 Feb 16 12:47 config
drwx------. 2 mysql mysql     4096 Feb 16 12:47 data
drwx------. 2 mysql mysql     4096 Feb 16 12:47 hello
-rw-r-----. 1 mysql mysql 12582912 Feb 16 12:47 ibdata1
drwx------. 2 mysql mysql     4096 Feb 16 12:47 install
drwx------. 2 mysql mysql     4096 Feb 16 12:47 my
drwx------. 2 mysql mysql     4096 Feb 16 12:47 mydb
drwx------. 2 mysql mysql     4096 Feb 16 12:47 mysql
drwx------. 2 mysql mysql     4096 Feb 16 12:47 performance_schema
drwx------. 2 mysql mysql     4096 Feb 16 12:47 source
drwx------. 2 mysql mysql     4096 Feb 16 12:47 static
drwx------. 2 mysql mysql     4096 Feb 16 12:47 template
drwx------. 2 mysql mysql     4096 Feb 16 12:47 test
drwx------. 2 mysql mysql     4096 Feb 16 12:47 testdb
drwx------. 2 mysql mysql     4096 Feb 16 12:47 uc_client
drwx------. 2 mysql mysql     4096 Feb 16 12:47 uc_server
drwx------. 2 mysql mysql    61440 Feb 16 12:47 ultrax
-rw-r-----. 1 mysql mysql     23 Feb 16 12:47 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql     537 Feb 16 12:47 xtrabackup_info


[iyunv@node2 data]# service mysqld start
Starting MySQL............. SUCCESS!
注：模拟数据损坏，删除/mydata/data/* 后不用进行mysql初始化也可以在还原后启动mysql

[iyunv@node2 data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mydb;
Database changed

mysql> select * from test1;
+-----+-------+-----+
| cid | name  | sid |
+-----+-------+-----+
|   1 | zxl  | A   |
|   3 | fade  | C  |
|   4 | faded | D   |
|   5 | my   | E   |
|   6 | tom  | F   |
|   7 | jerry | F   |
|   8 | hehe | H   |
|   9 | ao   | I   |
|  10 | en   | G   |
|  11 | he   | K   |
|  12 | haha | L   |
|  13 | aaa  | M   |
+-----+-------+-----+
12 rows in set (0.01 sec)

可以看到所有数据都已经完全恢复


用XtraBackup导入或导出单张表

默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项。而使用Xtrabackup工具可以实现此种功能，不过，此时需要“导出”表的mysql服务器必须启用了innodb_file_per_table选项（严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项），并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。

(1)“导出”表
导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：
# innobackupex --apply-log --export /path/to/backup

此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器，即你可以将某个表的.exp文件和.ibd（表空间）这两个文件复制出来，就可以导入其他数据库在了

(2)“导入”表
要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表且存储引擎必须为Innodb，而后才能实现将表导入：
mysql> CREATE TABLE mytable (...)  ENGINE=InnoDB;

然后将此表的表空间删除：DISCARD TABLESPACE 丢弃表空间
mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;

接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：
mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;