灾备演练

场景一 数据库被黑，数据全部丢失

  1. 创建新的数据库

#[脚本自动化安装](https://www.cnblogs.com/kcxg/p/10929968.html) chmod +x auto_install_mysql.sh ./auto_install_mysql.sh

  1. 恢复最近一次全量备份

1. 解压备份 unzip gunzip alldb-20190524-010001.sql.gz
2.sql文件还原/usr/local/mysql/bin/mysql  -uusername   -pPASSWORD  -f  <  alldb-20190524-010001.sql  （-f 参数表示在导出过程中忽略出现的SQL错误）

  1. 通过binlog恢复全量备份之后的数据

3.1 mysqlbinlog1). binlog拷贝到恢复机上2). 使用mysqlbinlog 生成sql脚本mysqlbinlog --database=[db_name] --start-datetime='2019-05-23 18:19:12' --stop-datetime='2019-05-25 21:53:56'  > 1.sql3).binlog生成的sql脚本导入mysql -uuser -ppassword < 1.sql4).验证数据正确性
3.1 binlog2sql(https://www.cnblogs.com/kcxg/p/10644231.html)）#根据备份文件名称确定开始还原时间以及binlog文件，解析得到sql语句。python binlog2sql/binlog2sql.py  -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2019-05-27 12:34:00'

场景二 误删整张表数据，需要紧急回滚

1.登录mysql，查看目前的binlog文件mysql> show master status;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000051 |       967 || mysql-bin.000052 |       965 |+------------------+-----------+
2.最新的binlog文件是mysql-bin.000052，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'输出：INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 514 end 701 time 2016-12-13 20:27:07DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
3.我们得到了误操作sql的准确位置在728-938之间，再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确(注：真实环境下，此步经常会进一步筛选出需要的sql。结合grep、编辑器等)shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-position=3346 --stop-position=3556 -B > rollback.sql | cat输出：INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05
4.确认回滚sql正确，执行回滚语句。登录mysql确认，数据回滚成功。shell> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < rollback.sqlmysql> select * from tbl;+----+--------+---------------------+| id | name   | addtime             |+----+--------+---------------------+|  1 | 小赵   | 2016-12-10 00:04:33 ||  2 | 小钱   | 2016-12-10 00:04:48 ||  3 | 小孙   | 2016-12-13 20:25:00 ||  4 | 小李   | 2016-12-12 00:00:00 |+----+--------+---------------------+
赞 (0)

相关推荐