小型网站MYSQL问题二：Percona Xtrabackup实现数据库备份和恢复

故事背景：前面大胆的选择了innodb，接下来就要考虑数据库的备份和恢复。俗话说“常在河边走，哪能不湿鞋“，自从接触数据这块，使我一直对它保持敬畏之心。在这里使用了percona公司的xtrabckup工具来实现数据库备份和恢复。操作步骤记录如下：

1、安装软件仓库（不要问我为什么不用源码安装，好吧，其实我懒。）

wget https://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
rpm -ivh percona-release-0.1-3.noarch.rpm
rpm -ql percona-release
    /etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
    /etc/yum.repos.d/percona-release.repo
    /usr/share/doc/percona-release-0.1
    /usr/share/doc/percona-release-0.1/RPM-GPG-KEY-Percona
yum -y install percona-xtrabackup

2、备份策略及准备测试数据

采用先全备份加增量备份的方案。在利用xtrabackup对innodb表做备份工作时，建议mysql启用“innodb_file_per_table=1”变量且同时启用innodb_file_per_table = 1 innodb_expand_import = 1

这样使每表都有一个自己的表空间，不然很难进行单表备份和还原。还有二进制日志文件就不要与数据文件放在同一个目录了，你不想当数据丢失时，二进制日志也一同丢了。

测试数据：

mysql> SELECT VERSION(); 
    +------------+ 
    | VERSION()  | 
    +------------+ 
    | 5.5.36-log | 
    +------------+ 
    1 row in set (0.00 sec) 
mysql> SHOW DATABASES; #创建了一个mydb1数据库 
    +--------------------+ 
    | Database          | 
    +--------------------+ 
    | information_schema | 
    | mydb1              | 
    | mysql              | 
    | performance_schema | 
    | test              | 
    +--------------------+ 
mysql> SELECT * FROM mydb1.tb1; #表中只有一条数据 
    +----+------+------+ 
    | id | name | age  | 
    +----+------+------+ 
    |  1 | tom  |  10 | 
    +----+------+------+

创建备份数据存放目录：

mkdir -pv /backup/{fullbackup,incremental} 
    #fullbackup  存放全备份数据 
    #incremental 存放增量备份数据

创建复制用户：

mysql> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost' IDENTIFIED BY '123456'; 
mysql> FLUSH PRIVILEGES;

3、全备份数据

innobackupex --user=bkuser --password=123456 /backup/fullbackup/ 
    #最后出现“150415 16:30:23  innobackupex: completed OK!”这样的信息表示备份完成 
ls /backup/fullbackup/2015-04-15_16-30-19/ 
    backup-my.cnf  mysql              xtrabackup_binary      xtrabackup_logfile 
    ibdata1        performance_schema  xtrabackup_binlog_info 
    mydb1          test                xtrabackup_checkpoints 
cat /backup/fullbackup/2015-04-15_16-30-19/xtrabackup_checkpoints 
    backup_type = full-backuped 
    from_lsn = 0 
    to_lsn = 1644877 
    last_lsn = 1644877 
    compact = 0

4、增量备份数据

先做一些数据修改：

mysql> INSERT INTO mydb1.tb1 (name,age) VALUES ('jack',20); 
mysql> SELECT * FROM tb1;  #增加一条数据 
    +----+------+------+ 
    | id | name | age  | 
    +----+------+------+ 
    |  1 | tom  |  10 | 
    |  2 | jack |  20 | 
    +----+------+------+

做第一次增量备份：

innobackupex --user=bkuser --password=123456 --incremental /backup/incremental/ --incremental-basedir=/backup/fullbackup/2015-04-15_16-30-19/ 
ls /backup/incremental/2015-04-15_16-42-00/ 
    backup-my.cnf  mydb1              test                    xtrabackup_checkpoints 
    ibdata1.delta  mysql              xtrabackup_binary      xtrabackup_logfile 
    ibdata1.meta  performance_schema  xtrabackup_binlog_info 
cat /backup/incremental/2015-04-15_16-42-00/xtrabackup_checkpoints 
    backup_type = incremental 
    from_lsn = 1644877  #这是全备时的"to_lsn"值 
    to_lsn = 1645178 
    last_lsn = 1645178 
    compact = 0

再做数据修改：

mysql> INSERT INTO mydb1.tb1 (name,age) VALUES ('jason',30); 
mysql> SELECT * FROM tb1; 
    +----+-------+------+ 
    | id | name  | age  | 
    +----+-------+------+ 
    |  1 | tom  |  10 | 
    |  2 | jack  |  20 | 
    |  3 | jason |  30 | 
    +----+-------+------+

做第二次增量备份：

innobackupex --user=bkuser --password=123456 --incremental /backup/incremental/ --incremental-basedir=/backup/incremental/2015-04-15_16-42-00/ 
    #这里的"--incremental-basedir"是指向第一次增量备份的目录 
ls /backup/incremental/2015-04-15_16-49-07/ 
    backup-my.cnf  mydb1              test                    xtrabackup_checkpoints 
    ibdata1.delta  mysql              xtrabackup_binary      xtrabackup_logfile 
    ibdata1.meta  performance_schema  xtrabackup_binlog_info 
cat /backup/incremental/2015-04-15_16-49-07/xtrabackup_checkpoints 
    backup_type = incremental 
    from_lsn = 1645178 #这是第一次增量的"to_lsn"值 
    to_lsn = 1645463 
    last_lsn = 1645463 
    compact = 0

再来做一次数据修改，以便演示根据二进制日志做时间点的恢复：

mysql> INSERT INTO mydb1.tb1 (name,age) VALUES ('lucky',40); 
mysql> SELECT * FROM tb1; 
    +----+-------+------+ 
    | id | name  | age  | 
    +----+-------+------+ 
    |  1 | tom  |  10 | 
    |  2 | jack  |  20 | 
    |  3 | jason |  30 | 
    |  4 | lucky |  40 | 
    +----+-------+------+

5、恢复

5.1、数据破坏

service mysqld stop 
rm -rf /mnt/mydata/data/*  #把数据目录数据全部删除

对于xtrabackup做数据恢复的理解：

xtrabackup做数据恢复与mysqldump这样的逻辑备份工作的恢复有很大的区别，xtrabackup进行数据恢复时需要把各个增量的数据备份与全备份的数据进行合并，

对每次增量备份的合并只能将已提交的事务进行重放(redo)，对合备份的数据恢复也只能做redo操作，把各个增量都合并完成后再把没有提交的事务进行回滚(undo)操作，

合并完增量备份后，全备份的“xtrabackup_checkpoints”文件中的“last_lsn”应该是最后一次增量备份时的值，这些合并做redo的过程就是恢复数据前的准备工作（prepare）。

而真正在做数据恢复，建议先把全备和增量备份的文件都copy一份为副本，避免操作失误导致备份文件的损坏。

5.2、数据恢复的准备(prepare)工作

#准备全备份的数据 
innobackupex --apply-log --redo-only /backup/fullbackup/2015-04-15_16-30-19/ 
#准备第一次增量数据 
innobackupex --apply-log --redo-only /backup/fullbackup/2015-04-15_16-30-19/ --incremental-dir=/backup/incremental/2015-04-15_16-42-00/ 
#准备第二次增量数据 
innobackupex --apply-log --redo-only /backup/fullbackup/2015-04-15_16-30-19/ --incremental-dir=/backup/incremental/2015-04-15_16-49-07/

cat /backup/fullbackup/2015-04-15_16-30-19/xtrabackup_checkpoints 
    backup_type = full-prepared 
    from_lsn = 0 
    to_lsn = 1645463 #这是最后一次增量备份的位置，到此数据已全部合并 
    last_lsn = 1645463 
    compact = 0

5.3、真正数据恢复

innobackupex --copy-back /backup/fullbackup/2015-04-15_16-30-19/  #仅一个“--copy-back”选项 
ls /mnt/mydata/data/ 
    ibdata1  mydb1  mysql  performance_schema  test
chown -R mysql.mysql /mnt/mydata/data/  #修改恢复数据的属主与属组 
service mysqld start 
    Starting MySQL.. SUCCESS! 
mysql> SELECT * FROM mydb1.tb1;  #检查数据 
    +----+-------+------+ 
    | id | name  | age  | 
    +----+-------+------+ 
    |  1 | tom  |  10 | 
    |  2 | jack  |  20 | 
    |  3 | jason |  30 | 
    +----+-------+------+ 
#lucky信息还没有恢复

5.4、利用二进制日志文件基于时间点恢复

查看最后一次增量备份时的二进制日志文件及position信息：
cat /backup/incremental/2015-04-15_16-49-07/xtrabackup_binlog_info 
    mysql-bin.000031    1665  
mysqlbinlog --start-position=1665 /var/log/mysql_log/mysql-bin.000031 > /tmp/position.sql 
    #用mysqlbinlog工具导出最后一次增量备份后的sql操作

导入数据：
mysql> source /tmp/position.sql 
mysql> SELECT * FROM mydb1.tb1; 
    +----+-------+------+ 
    | id | name  | age  | 
    +----+-------+------+ 
    |  1 | tom  |  10 | 
    |  2 | jack  |  20 | 
    |  3 | jason |  30 | 
    |  4 | lucky |  40 | 
    +----+-------+------+ 
#数据全部恢复

6、总结

利用innobackupex工具可对在有混合存储引擎的场景下对数据库进行全备份，当是备份innodb表时innobackupex脚本内部会自动调用xtrabackup工具来进行备份操作，

在数据还原时应停止mysql实例，清空数据目录，然后先做prepare准备工作，第二步才是真正的数据还原（–copy-back），数据还原到数据目录后不要忘记修改数据目录的权限。

当然innobackupex工具也能实现对单库单表的导出、导出，如果是对Innodb表，那建议直接用xtrabackup工具，这样更直观的使用各个选项进行备份。

============================华丽分隔线（下面是重点）================================================

背景：在到第5.3步的时候，数据库启动失败。why、what、fuck。说好的一条龙服务？

痛定思痛，上日志。

151216 11:16:20
mysqld_safe Starting mysqld daemon with databases from /data/mysql
151216 11:16:20 [Note] /usr/libexec/mysqld (mysqld 5.5.47-log) starting as process 2931 ...
151216 11:16:20 [Note] Plugin 'FEDERATED' is disabled.
151216 11:16:20 InnoDB: The InnoDB memory heap is disabled
151216 11:16:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151216 11:16:20 InnoDB: Compressed tables use zlib 1.2.3
151216 11:16:20 InnoDB: Using Linux native AIO
151216 11:16:20 InnoDB: Initializing buffer pool, size = 128.0M
151216 11:16:20 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes        #这里是48M
InnoDB: than specified in the .cnf file 0 5242880 bytes!           #这里是5M
151216 11:16:20 [ERROR] Plugin 'InnoDB' init function returned error.
151216 11:16:20 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.   #注册引擎失败，打击好大
151216 11:16:20 [ERROR] Unknown/unsupported storage engine: innodb     #什么，又不支持innodb
151216 11:16:20 [ERROR] Aborting
 
151216 11:16:20 [Note] /usr/libexec/mysqld: Shutdown complete
 
151216 11:16:20 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

首先我删除了恢复到/data/mysql目录下面的ibdata1和ib_logfile0、ib_logfile1，此时可以启动数据库，然而提示表不存在。

向各位老师请教后，修改innodb_log_file_size=48M并写入/etc/my.cnf，启动正常。

总结：innodb_log_file_size参数属于mysql系统参数，当配置不当时，mysql会启动失败。使用xtrabackup备份时默认值是5M，但是当迁移到新的环境后，初始化为48M，导致不匹配。后续应该将此参数写入/etc/my.cnf防止迁移带来的参数不匹配问题。

Command-Line Format	`--innodb_log_file_size=#`
System Variable	Name	`innodb_log_file_size`
	Variable Scope	Global
	Dynamic Variable	No
Permitted Values	Type	`integer`
	Default	`5242880`
	Min Value	`1048576`
	Max Value	`4GB / innodb_log_files_in_group`

The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 4GB. A pair of 2047 MB log files, for example, would allow you to approach the range limit but not exceed it. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration. For general I/O tuning advice,

具体参考：http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_file_size

原创文章，作者：陈近南，如若转载，请注明出处：http://www.178linux.com/10139

小型网站MYSQL问题二：Percona Xtrabackup实现数据库备份和恢复

相关推荐

推荐-zabbix原理篇（1）

PHP安全模式详解(PHP5.4安全模式将消失)

Ansible playbook

Linus Torvalds 语录 Top 10

TCP连接的状态详解以及故障排查

Net25 第二周作业

评论列表（3条）