|
MySQL数据库增量恢复实践-单实例
1、具备什么条件才能恢复数据库?
a.全量备份(mysqldump)
b.全量备份以后的所有binlog增量日志。
2、准备条件
00点开始备份数据:
[iyunv@db02 backup]# mysql -e "select * from oldboy.test;"
+----+----------+
| id | name |
+----+----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
| 6 | bingbing |
| 7 | xiaoyu |
+----+----------+
[iyunv@db02 ~]# mkdir /data/backup -p
[iyunv@db02 ~]# date -s "20170317"
Fri Mar 17 00:00:00 CST 2017
[iyunv@db02 ~]# mysqldump -B --master-data=2 --single-transaction oldboy|gzip >/data/backup/oldboy_$(date +%F).sql.gz
00-10:00用户继续写数据:
[iyunv@db02 ~]# mysql -e "use oldboy;insert into test valuse(6,'bingbing');"
[iyunv@db02 ~]# mysql -e "use oldboy;insert into test valuse(7,'xiaoyu');"
[iyunv@db02 ~]# mysql -e "select * from oldboy.test;"
+----+----------+
| id | name |
+----+----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
| 6 | bingbing | <==模拟了两行
| 7 | xiaoyu | <==模拟了两行
+----+----------+
10:00点了,模拟老大删除了一个数据库。
[iyunv@db02 ~]# mysql -e "drop database oldboy;show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldgirl |
| performance_schema |
+--------------------+
10:10分发现了,运营人员发现问题、用户发现问题,先找开发----->找运维或DBA
问题发现,开始处理问题。
时间?为什么没了?
开始linux审计,查看binlog。
发现如下内容:
drop database oldboy
========================开始恢复前==============================
移走binlog:
[iyunv@db02 ~]# cp -a /application/mysql/data/oldboy-bin.* /data/backup/
确认是否有全备:
========================开始恢复===============================
因为删库:后面不会有写入了。
1、停库最佳(iptables屏蔽所有web写入)。
2、如果是update应停库(iptables屏蔽所有web写入)
3、处理全备
[iyunv@db02 backup]# gzip -cd oldboy_2017-03-17.sql.gz >oldboy.sql
4、解析binlog
[iyunv@db02 backup]# sed -n '22p' oldboy.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='oldboy-bin.000015', MASTER_LOG_POS=2190;
[iyunv@db02 backup]# mysqlbinlog -d oldboy oldboy-bin.000015 -r bin.sql
[iyunv@db02 backup]# mysqlbinlog -d oldboy oldboy-bin.000015 --start-position=2190 -r bin.sql
[iyunv@db02 backup]# grep -w drop bin.sql
drop database oldboy
[iyunv@db02 backup]# sed -i '/drop database oldboy/d' bin.sql
[iyunv@db02 backup]# grep -w drop bin.sql
准备彻底完成:开始正式恢复。
先恢复0点以前的全备
[iyunv@db02 backup]# mysql </data/backup/oldboy.sql
[iyunv@db02 backup]# mysql -e "select * from oldboy.test;"
+----+----------+
| id | name |
+----+----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
再恢复增量
[iyunv@db02 backup]# mysql oldboy</data/backup/bin.sql
[iyunv@db02 backup]# mysql -e "select * from oldboy.test;"
+----+----------+
| id | name |
+----+----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
| 6 | bingbing |
| 7 | xiaoyu |
+----+----------+
恢复完毕!
|
|