mysql增量物理增量_mysql增量恢复

老男孩教育(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.0000112399

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

检查:

[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.