Home >Database >Mysql Tutorial >MySQL备份和恢复（三）Xtrabackup实现热备：完全备份+增量备份

MySQL备份和恢复（三）Xtrabackup实现热备：完全备份+增量备份

WBOYOriginal: 2016-06-07 14:53:001022browse

Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits: Backups that complete

Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:

Backups that complete quickly and reliably
Uninterrupted transaction processing during backups
Savings on disk space and network bandwidth
Automatic backup verification
Higher uptime due to faster restore time

以上摘自官方文档对Xtrabackup的描述。

Xtrabackup是Percona公司的开源mysql热备软件，软件自身支持完全备份和增量备份，功能强大，使用简单，且备份结束会自动检查备份的可用性。对于InnoDB和XtraDB可以实现无阻塞的备份。

Xtrabackup会在备份目录下自动生成以当前日期和时间为名的目录，目录下包含当前备份所有数据文件和丰富的当前备份时的状态信息。

实例演示：

xtrabackup的安装，安装包可从官网下载http://www.percona.com/software/percona-xtrabackup，官方提供了二进制和源码包，根据自己需要下载

/* 下载官方的rpm包安装，xtrabackup要依赖perl-DBD-mysql包，不想手动解决依赖关系可以yum localinstall安装 */
[root@console ~]# yum localinstall --nogpgcheck percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm -y

/* xtrabackup安装后生成的文件不多 */
[root@console ~]# rpm -ql percona-xtrabackup
/usr/bin/innobackupex        /* 备份时用的命令,会根据mysql版本自动调用xtrabackup_{55,56} */
/usr/bin/innobackupex-1.5.1     
/usr/bin/xbcrypt        /* 提供备份过程加密支持 */
/usr/bin/xbstream        /* 支持流式备份 */
/usr/bin/xtrabackup
/usr/bin/xtrabackup_55    / * 这个和下面那个才是备份过程实际调用的备份程序 */
/usr/bin/xtrabackup_56
/usr/share/doc/percona-xtrabackup-2.1.4
/usr/share/doc/percona-xtrabackup-2.1.4/COPYING    /* 文档只有一个软件授权文件，没有man文档，不过--help给出的帮助信息也比较丰富 */

备份过程：

/* 在库db1中准备一张表tb1，做效果比较用 */
MariaDB [(none)]> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE db1
Database changed
MariaDB [db1]> CREATE TABLE `tb1` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `Name` varchar(30) NOT NULL,   `Gender` enum('F','M','O') NOT NULL,   PRIMARY KEY (`id`) );
Query OK, 0 rows affected (0.22 sec)

MariaDB [db1]> DESC tb1;
+--------+-------------------+------+-----+---------+----------------+
| Field  | Type              | Null | Key | Default | Extra          |
+--------+-------------------+------+-----+---------+----------------+
| id     | int(11)           | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(30)       | NO   |     | NULL    |                |
| Gender | enum('F','M','O') | NO   |     | NULL    |                |
+--------+-------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [db1]> INSERT INTO tb1 (Name,Gender) VALUES ('Tom','M'),('Jerry','F');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [db1]> SELECT * FROM tb1;
+----+-------+--------+
| id | Name  | Gender |
+----+-------+--------+
|  1 | Tom   | M      |
|  2 | Jerry | F      |
+----+-------+--------+
2 rows in set (0.00 sec)

/* 创建一个用于备份的最小权限的用户 */
MariaDB [(none)]> CREATE USER 'bakuser'@'localhost' IDENTIFIED BY 'backupass';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bakuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

现在先做一次完全备份：

/* 全备只需指定用于备份的用户名、密码和备份路径即可，最后出现innobackupex: completed OK! 则代表备份成功 */
[root@node1 ~]# innobackupex --user=bakuser --password=backupass /innobackup/
innobackupex: Backup created in directory '/innobackup/2014-07-31_09-27-36'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1270
140731 09:27:39  innobackupex: Connection to database server closed
140731 09:27:40  innobackupex: completed OK!

/* 看一下备份都生成了哪些文件,除了数据库文件外还多了一些文件，注意由于我用root登陆系统，备份后的文件属主属组都是root，恢复后要改为运行mysqld进程的用户，不然mysql起不来 */
[root@node1 ~]# cd /innobackup/2014-07-31_09-27-36/
[root@node1 2014-07-31_09-27-36]# ll
total 18476
-rw-r--r--. 1 root root      260 Jul 31 09:27 backup-my.cnf /* 备份命令用到的配置选项信息 */
drwx------. 2 root root     4096 Jul 31 09:27 db1    
drwx------. 2 root root     4096 Jul 31 09:27 hellodb
-rw-r-----. 1 root root 18874368 Jul 31 09:27 ibdata1
drwx------. 2 root root     4096 Jul 31 09:27 mydb
drwxr-xr-x. 2 root root     4096 Jul 31 09:27 mysql
drwxr-xr-x. 2 root root     4096 Jul 31 09:27 performance_schema
drwxr-xr-x. 2 root root     4096 Jul 31 09:27 test
-rw-r--r--. 1 root root       13 Jul 31 09:27 xtrabackup_binary    /* 记录备份过程实际用的备份程序 */
-rw-r--r--. 1 root root       24 Jul 31 09:27 xtrabackup_binlog_info    /* 记录备份时的二进制日志文件和当前的位置 */
-rw-r-----. 1 root root       89 Jul 31 09:27 xtrabackup_checkpoints    /* 记录备份类型、
状态（是否prepared)、是否压缩、备份的LSN(Log Sequence Number日志序列号）范围等信息 */
-rw-r-----. 1 root root     2560 Jul 31 09:27 xtrabackup_logfile    /* xtrabackup自己的日志文件，新版本中不直接可见 */

[root@node1 2014-07-31_09-27-36]# cat backup-my.cnf 
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5242880
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
[root@node1 2014-07-31_09-27-36]# cat xtrabackup_binary 
xtrabackup_55[root@node1 2014-07-31_09-27-36]# 
[root@node1 2014-07-31_09-27-36]# cat xtrabackup_binlog_info 
mysql-bin.000001	1270		
[root@node1 2014-07-31_09-27-36]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1660869
last_lsn = 1660869
compact = 0
[root@node1 2014-07-31_09-27-36]# file xtrabackup_logfile 
xtrabackup_logfile: data

全备完成后，对数据库做一些修改后再做一次增备：

/* 第一次增量备份,--incremental 备份类型为增量， --incremental-basedir 指定这次增备是相对哪一个备份做的增量,这里是相对全备  */
[root@node1 innobackup]# innobackupex --user=bakuser --password=backupass --incremental /innobackup/ --incremental-basedir=/innobackup/2014-07-31_09-27-36/
innobackupex: Backup created in directory '/innobackup/2014-07-31_11-51-19'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1525
140731 11:51:48  innobackupex: Connection to database server closed
140731 11:51:48  innobackupex: completed OK!

/* 做些修改，做第二次增备 */
MariaDB [db1]> DELETE FROM tb1 WHERE Name='Tom';
Query OK, 1 row affected (0.09 sec)

MariaDB [db1]> UPDATE tb1 SET Gender='F' WHERE Name='Dongfang Bubai';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [db1]> SELECT * FROM tb1;
+----+----------------+--------+
| id | Name           | Gender |
+----+----------------+--------+
|  2 | Jerry          | F      |
|  3 | Bob            | M      |
|  4 | Dongfang Bubai | F      |
+----+----------------+--------+
3 rows in set (0.00 sec)

/* incremental-basedir 相对第一次增量的增量 */
[root@node1 innobackup]# innobackupex --user=bakuser --password=backupass --incremental /innobackup/ --incremental-basedir=/innobackup/2014-07-31_11-51-19/
innobackupex: Backup created in directory '/innobackup/2014-07-31_11-59-29'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1922
140731 11:59:33  innobackupex: Connection to database server closed
140731 11:59:33  innobackupex: completed OK!

/* 第二次增备完成后，再做一些数据修改操作，以模拟实际生产环境数据库突然崩溃场景 */
MariaDB [db1]> INSERT INTO tb1 (Name,Gender) VALUES ('Kangshifu Guamian','O');
Query OK, 1 row affected (0.04 sec)

/* 将二进制日志备份出来一份，生产环境中二进制日志切记不要和数据文件放在一起，
最好不要在同一分区甚至同一物理磁盘, 以免一旦玉石俱焚，悔之晚矣 */
[root@node1 innobackup]# mysql -e 'SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     2168 |              |                  |
+------------------+----------+--------------+------------------+
[root@node1 innobackup]# cp /mydata/data/
aria_log.00000001   hellodb/            ib_logfile1         mysql-bin.000001    node1.bob.org.pid
aria_log_control    ibdata1             mydb/               mysql-bin.index     performance_schema/
db1/                ib_logfile0         mysql/              node1.bob.org.err   test/
[root@node1 innobackup]# cp /mydata/data/mysql-bin.000001 /innobackup/

/* 人工让数据库彻底崩盘 */
[root@node1 innobackup]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
[root@node1 innobackup]# rm -rf /mydata/data/*

恢复：

xtrabackup的恢复比较特殊，恢复前要先执行prepare过程，prepare主要完成把已提交的事务数据同步到数据文件，未完成的事务则回滚，这是对于只有一个完全备份来说。当还需要结合其他的增备时，则在些过程只需要将已提交的事务同步，未提交的事务则不用回滚，因为在本次备份时未提交的事务可能会在下一备份中已经提交，以避免这种无谓的劳动。

/* 注意 backup_type 和 to_lsn的变化 */
[root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1660869
last_lsn = 1660869
compact = 0 

/* Prepare全备,--apply-log 指定要prepare, --redo-only 只处理已提交事务，未提交的事务则不回滚
 backup_type由full-backuped --> full-prepared */
[root@node1 innobackup]# innobackupex --apply-log --redo-only /innobackup/2014-07-31_09-27-36/
...
140731 15:05:53  innobackupex: completed OK!
[root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1660869
last_lsn = 1660869
compact = 0

/* prepare 增备1， --incremental-dir 增备的路径 */
[root@node1 innobackup]# innobackupex --apply-log --redo-only /innobackup/2014-07-31_09-27-36/ --incremental-dir /innobackup/2014-07-31_11-51-19/

/* 再来看full backup 的 to_lsn 已经 --> 增备1 的 to_lsn了，此时增量1也就退出舞台了 */
[root@node1 innobackup]# cat 2014-07-31_11-51-19/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1660869
to_lsn = 1661993
last_lsn = 1661993
compact = 0
[root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1661993
last_lsn = 1661993
compact = 0

/* prepare 增备2，增量1已经prepare上去了，所以基准还是全备，--incremental-dir 指向增量2 */
[root@node1 innobackup]# innobackupex --apply-log /innobackup/2014-07-31_09-27-36/ --incremental-dir=/innobackup/2014-07-31_11-59-29/

/* 完事后再瞅一眼，全备的 to_lsn 已经 --> 增量2 的了 */
[root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1664255
last_lsn = 1664255
compact = 0
[root@node1 innobackup]# cat 2014-07-31_11-59-29/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1661993
to_lsn = 1664255
last_lsn = 1664255
compact = 0

至此，恢复的预准备工作已完成，两个增备也完成了历史使命，恢复只要用全备的一个就可以了：

/* 恢复过程灰常简单，恢复过程也不需要启动mysql, 但是备份过程是一定要启动mysql的，因为要以备份用户身份连到Mysql上的嘛 */
[root@node1 innobackup]# innobackupex --copy-back /innobackup/2014-07-31_09-27-36/
/* 现在还不能启动mysql, 对了，属主属组还没改 */
[root@node1 innobackup]# ll /mydata/data/
total 18456
drwxr-xr-x. 2 root root     4096 Jul 31 15:54 db1
drwxr-xr-x. 2 root root     4096 Jul 31 15:54 hellodb
-rw-r--r--. 1 root root 18874368 Jul 31 15:54 ibdata1
drwxr-xr-x. 2 root root     4096 Jul 31 15:54 mydb
drwxr-xr-x. 2 root root     4096 Jul 31 15:54 mysql
drwxr-xr-x. 2 root root     4096 Jul 31 15:54 performance_schema
drwxr-xr-x. 2 root root     4096 Jul 31 15:54 test
[root@node1 innobackup]# chown -R mysql.mysql /mydata/data/*

/* 现在就可以启动了 。。 */
[root@node1 innobackup]# service mysqld start
Starting MySQL.....                                        [  OK  ]

/* 正常启动，数据都在 */
MariaDB [db1]> SELECT * FROM tb1;
+----+----------------+--------+
| id | Name           | Gender |
+----+----------------+--------+
|  2 | Jerry          | F      |
|  3 | Bob            | M      |
|  4 | Dongfang Bubai | F      |
+----+----------------+--------+
3 rows in set (0.00 sec)

再加上最后的二进制日志恢复，整个恢复过程就全搞颠了

[root@node1 innobackup]# cat /innobackup/2014-07-31_11-59-29/xtrabackup_binlog_info 
mysql-bin.000001	1922		
[root@node1 innobackup]# mysqlbinlog --start-position=1922 mysql-bin.000001 > 11.sql
[root@node1 innobackup]# mysql < 11.sql 

MariaDB [(none)]> SELECT * FROM db1.tb1;
+----+-------------------+--------+
| id | Name              | Gender |
+----+-------------------+--------+
|  2 | Jerry             | F      |
|  3 | Bob               | M      |
|  4 | Dongfang Bubai    | F      |
|  5 | Kangshifu Guamian | O      |
+----+-------------------+--------+
4 rows in set (0.01 sec)
/* 二进制日志恢复，kangshifu guamian finnaly O 出锅了 */

To be continued ...

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：oracle11gr2rac中的4种IP解说Next article：6.SQLServer数据库监控-如何告警

See more

MySQL备份和恢复（三）Xtrabackup实现热备：完全备份+增量备份

Related articles