mysql数据库误删库/表的恢复实战

这篇博文是对前期学习的一个综合总结。

一、企业场景全量和增量的频率是怎么做的？
1.中小公司，全量一般是每天一次，业务流量低谷执行全备，备份时会锁表。
2.大公司周备，每周六00点一次全量，其他时间通过binlog做增量。（优点：节省备份时间，减小备份压力。缺点：增量的binlog文件副本太多，还原会很麻烦。）
3.一主多从，使用一个从库专门做备份，必要时可以做延时同步。

二、mysql的mysqldump备份什么时候派上用场？
1.迁移或者升级数据库时。
2.增加从库时候。
3.人为的sql语句误操作。

三、本次模拟的就是人为的误操作后进行紧急恢复。

1）误删数据库

一、工作场景

（1）两台数据库，主从状态，binlog日志开启。（主从配置http://blog.csdn.net/sj349781478/article/details/77519698）

（2）MySQL数据库每晚12:00自动完全备份。

0 0 * * * root mysqldump -u root -p -S /data/mysql3308/mysql3308.sock --all-databases --single-transaction --flush-logs --master-data=2 --events| gzip > /opt/database_`date '+%m-%d-%Y'`.sql.gz

（3）某天早上上班，9点左右，接到反馈前端应用使用异常，程序报数据库不存在。

（4）进行紧急恢复！（利用备份的数据文件以及增量的binlog文件进行数据恢复.)

二、数据恢复思路

（1）利用全备的sql文件中记录的CHANGE MASTER语句，binlog文件及其位置点信息，找出binlog文件中增量的那部分。
（2）用mysqlbinlog命令将上述的binlog文件导出为sql文件，并剔除其中的drop语句。
（3）通过全备文件和增量binlog文件的导出sql文件，就可以恢复到完整的数据。

三、实例说明

场景：以全备的data数据库为例，data库中有cost表，全量备份时表中有三条记录，后续有插入的三条记录，某一时刻被误操作删除了data库。

1）发现库被删除：前端报故障过来说data库访问不了，或者监控发现data不存在；

2）向领导反馈或对外发布通知，数据库故障紧急维护；

3）刷新binlog，防止后续新增的在恢复过程中，会继续写入语句到该binlog，最终导致增量恢复数据部分变得比较混乱；

# mysqladmin -uroot -p -S /data/mysql3308/mysql3308.sock flush-logs

4）对当天凌晨备份的数据进行恢复（恢复时间与数据量大小成正比）；

# gunzip < /opt/database_09-03-2017.sql.gz | mysql -uroot -p -S /data/mysql3308/mysql3308.sock

5）查看全备之后新增的binlog文件点

# gunzip database_09-03-2017.sql.gz

# head -n 50 database_09-03-2017.sql | grep -i "CHANGE MASTER TO"

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=154;

6）拷贝备份点后续binlog文件，及上述flush-logs前的文件，导出为sql文件，剔除其中的drop语句

# cp mysql-bin.000008 mysql-bin.000009 /opt/

# cd /opt/

# mysqlbinlog --start-position=154 -vv --skip-gtids mysql-bin.000013>013bin.sql

# vi 008bin.sql 找到DROP的位置，然后再生成sql

# mysqlbinlog --start-position=154 --stop-position=984 -vv --skip-gtids mysql-bin.000008>008bin.sql

# mysql -uroot -p -S /data/mysql3308/mysql3308.sock data <008bin.sql

以上就是mysql数据库增量数据恢复的实例过程！

2）误删数据库的表

一、工作场景

（1）两台数据库，主从状态，binlog日志开启。（主从配置http://blog.csdn.net/sj349781478/article/details/77519698）

（2）MySQL数据库每晚12:00自动完全备份。

0 0 * * * root mysqldump -u root -p -S /data/mysql3308/mysql3308.sock --all-databases --single-transaction --flush-logs --master-data=2 --events| gzip > /opt/database_`date '+%m-%d-%Y'`.sql.gz

（3）某天早上上班，9点左右，接到反馈前端应用使用异常，程序报数据库表不存在。

（4）进行紧急恢复！（利用备份的数据文件以及增量的binlog文件进行数据恢复.)

二、数据恢复思路

三、实例说明

场景：以全备的data数据库为例，data库中有cost表，全量备份时表中有三条记录，后续有插入的三条记录，某一时刻被误操作删除了data库的cost表。

1）发现库中的表被删除：前端报故障过来说data库表访问不了，或者监控发现data库中的cost表不存在；

2）向领导反馈或对外发布通知，数据库故障紧急维护；

3）刷新binlog，防止后续新增的在恢复过程中，会继续写入语句到该binlog，最终导致增量恢复数据部分变得比较混乱；

# mysqladmin -uroot -p -S /data/mysql3308/mysql3308.sock flush-logs

4）断开主从，将对当天凌晨备份的数据在备库上进行恢复（恢复时间与数据量大小成正比）；

从库>stop slave; 或者slave stop; 看版本了

# gunzip < /opt/database_09-03-2017.sql.gz | mysql -uroot -p -S /data/mysql3309/mysql3309.sock

5）备库上导出数据表

mysqldump -uroot -p -S /data/mysql3309/mysql3309.sock -B data cost > /tmp/data_cost.sql

6）在主库上恢复备份的cost表

mysql -uroot -p -S /data/mysql3308/mysql3308.sock data < data_cost.sql

7）在备库上查看全备之后新增的binlog文件点

# gunzip database_09-03-2017.sql.gz

# head -n 50 database_09-03-2017.sql | grep -i "CHANGE MASTER TO"

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=154

8）拷贝备份点后续binlog文件，及上述flush-logs前的文件，导出为sql文件，剔除其中的drop语句

# cp mysql-bin.000013 /opt/

# cd /opt/

# mysqlbinlog --start-position=154 -vv --skip-gtids mysql-bin.000013>013bin.sql

# vi 013bin.sql 找到DROP的位置，然后再生成sql

# mysqlbinlog --start-position=154 --stop-position=984 -vv --skip-gtids mysql-bin.000013>013bin.sql

# mysql -uroot -p -S /data/mysql3308/mysql3308.sock data <013bin.sql

以上就是mysql数据库表的增量数据恢复的实例过程！

【完】

此文为头条号作者技术男诺言原创，特此申明