老男孩教育(www.oldboyedu.com) xtrabackup
中小企业MySQL Xtrabackup物理增量恢复案例实战
条件:
1.具备全量备份（xtrabckup备份的全备）。
2.具备全量之后的所有增量备份（xtrabckup备份的增量）。
3.具备最后一次增量备份以后的所有MySQL的Binlog增量日志。
1、模拟数据
use oldboy
delete from test;
insert into test values(1,'full01');
insert into test values(2,'full02');
insert into test values(3,'full03');
insert into test values(4,'full04');
insert into test values(5,'full05');
检查:
select * from test;
2、2017/06/26(周一) 0点全量备份
date -s "2017/06/26"
innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp /server/backup/new_base_full
数据库继续更新:
mysql -e "use oldboy;insert into test values(6,'new_inc_one_1');"
mysql -e "use oldboy;insert into test values(7,'new_inc_one_2');"
mysql -e "select * from oldboy.test;"
3、2017/06/27(周二) 0点增量备份
第一次增量备份
date -s "2017/06/27"
innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --incremental-basedir=/server/backup/new_base_full --incremental /server/backup/new_one_inc
数据库继续更新:
mysql -e "use oldboy;insert into test values(8,'binlog_data_1');"
mysql -e "use oldboy;insert into test values(9,'binlog_data_2');"
mysql -e "select * from oldboy.test;"
4、2018/06/28(周三) 0点增量备份
第二次增量备份
date -s "2017/06/28"
innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --incremental-basedir=/server/backup/new_one_inc --incremental /server/backup/new_two_inc
数据库继续更新:
mysql -e "use oldboy;insert into test values(10,'realbinlog_data_3');"
mysql -e "use oldboy;insert into test values(11,'realbinlog_data_4');"
mysql -e "select * from oldboy.test;"
5、2018/06/28(周上)-10点出故障了
mysql -e "use oldboy;update test set name='oldboy';"
mysql -e "select * from oldboy.test;"
6、开始恢复
a.建议停库：
最好用iptables
iptables -I INPUT -p tcp --dport 3306 -j DROP
/etc/init.d/mysqld stop
b.恢复数据库：合并数据文件
innobackupex --apply-log --use-memory=32M --redo-only /server/backup/new_base_full/
innobackupex --apply-log --use-memory=32M --redo-only --incremental-dir=/server/backup/new_one_inc /server/backup/new_base_full/
innobackupex --apply-log --use-memory=32M --incremental-dir=/server/backup/new_two_inc /server/backup/new_base_full/
数据文件准备完毕.
7、开始正式恢复
cd /application/mysql
mv data data.ori
cp -a /server/backup/new_base_full data
chown -R mysql.mysql data
================偷偷的看一下======================
[root@db02 mysql]# /etc/init.d/mysqld start
Starting MySQL.... SUCCESS!
[root@db02 mysql]# mysql -e "select * from oldboy.test;"
+----+---------------+
| id | name |
+----+---------------+
| 1 | full01 |
| 2 | full02 |
| 3 | full03 |
| 4 | full04 |
| 5 | full05 |
| 6 | new_inc_one_1 |
| 7 | new_inc_one_2 |
| 8 | binlog_data_1 |
| 9 | binlog_data_2 |
+----+---------------+
[root@db02 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
==================================================
8、处理binlog(00-10点数据)
a.查看binlog位置点
[root@db02 mysql]# cat /server/backup/new_two_inc/xtrabackup_binlog_info
oldboy-bin.000011 2399
b.解析binlog
cd /application/mysql/logs/
mysqlbinlog -d oldboy --start-position=2399 oldboy-bin.000011 -r bin1.sql
mysqlbinlog -d oldboy oldboy-bin.000006 >>bin1.sql
(根据你的情况调整)
本次不用操作,但工作中可能有会有很多个binlog文件.
#mysqlbinlog -d oldboy oldboy-bin.000012 oldboy-bin.000013 oldboy-bin.000014 >>bin2.sql
c.删除错误的update一行。update test set name='oldboy'
grep update bin1.sql
sed -i "/update test set name='oldboy'/d" bin1.sql
grep update bin1.sql
d.开始恢复
iptables -I INPUT -p tcp --dport 3306 -j DROP
/etc/init.d/mysqld start
e.导入binlog
mysql oldboy <bin1.sql
检查:
[root@db02 logs]# mysql -e "select * from oldboy.test;"
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | full01 |
| 2 | full02 |
| 3 | full03 |
| 4 | full04 |
| 5 | full05 |
| 6 | new_inc_one_1 |
| 7 | new_inc_one_2 |
| 8 | binlog_data_1 |
| 9 | binlog_data_2 |
| 10 | realbinlog_data_3 |
| 11 | realbinlog_data_4 |
+----+-------------------+
9.调整防火墙规则
iptables -D INPUT -p tcp --dport 3306 -j DROP
10.和开发/运营交流,检查数据恢复情况.
专业故障恢复报告发邮件当面分享.
over.