mysql数据恢复或数据找回方法

一时失言乱红尘 2022-05-27 04:18 293阅读 0赞

**注意：在恢复全备数据之前必须刷新该库****binlog****，否则恢复过程中，可能会继续写入语句到****binlog****，最终导致增量恢复数据部分变得比较混乱**

1、关闭应用，取出全量备份和binlog（腾讯cdb全备的position无法自行定位，需要提工单腾讯技术支持）

2、flush logs ; 刷新binlog

3、解析binlog，根据时间或其他方法（如：全备时使用\--master-data或\--dump-slave参数）确定全量备份时的position；

4、收集binlog中从全量备份点到最后的sql（删除误操作相关部分）；

5、恢复全量备份；（可考虑新建一个库，待数据恢复后确认无误，再恢复到生产库，做好备份！）

6、执行从binlog中获取的sql，恢复增量数据；

参考命令：mysql -uroot -p test\_db <002bin.sql

7、flush logs ; 再次刷新binlog

8、恢复应用。（如果有从库，需要考虑从库同步问题）

**如果使用的是腾讯云数据库，目前其cdb的全量备份未记录备份时刻的position，需要提交工单给腾讯技术后台确认......蛋疼。为掌握主动，于是采取了如下处理方式，避免耽误时间。**

**1、为腾讯云数据库搭建一个从库，不是直接在其cdb上买一个从库，是自己在本地或云服务器上搭建一个slave；（如果是在本地搭建slave，需要cdb开放公网访问，存在安全隐患，建议在云主机上搭建）**

**2、在从库定时执行全备作业，在备份文件内记录备份时刻主库的binlog文件及position；**

**3、利用定时作业备份生成的全备恢复基准数据；**

**4、利用云数据库上下载的binlog，结合第二点记录的position，增量恢复业务数据。**

**5、如果是数据找回，在binlog里面找到误操作或需要找回的sql，第四点时执行从position到误删除之前的sql，找回数据。**

**脚本参考：**

在云主机上为腾讯云数据库搭建从库参考：

http://bbs.qcloud.com/forum.php?mod=viewthread&tid=4108&highlight=cdb

解析binlog

mysqlbinlog --no-defaults  \--skip-gtids=true  mysql-bin.000002 -r 002bin.sql

或

mysqlbinlog --no-defaults  \--skip-gtids=true  mysql-bin.000002 > 002bin.sql

\#\#实际应用时，解析后的binlog文件中每个事物开始前，都执行了SET @@SESSION.GTID\_NEXT=操作来执行下一个要执行的GTID。但是这些GTID都已经存在数据库的Executed\_Gtid\_Set中(因为这些GTID都之前已经在实例上执行过)，所以在执行解析后的binlog文件时，所有的事物都被忽略(已经存在于Executed\_Gtid\_Set集合中的GTID会跳过)。

所以，在使用GTID时，如果我们想通过解析binlog来恢复数据的话，在使用mysqlbinlog解析binlog日志时需要指定--skip-gtids=true，这样的话解析出来的文件中就不会包含SET @@SESSION.GTID\_NEXT=

全量备份参考

\[root@vm ~\]\#  **mysqldump -u root -p -B -F -R -x --master-data=2**  test\_db **|gzip**  >/opt/backup/ test\_db \_$(date +%F).sql.gz

Enter password:

\[root@vm ~\]\# ls /opt/backup/

test\_db \_2018-01-17.sql.gz

\-----------------

参数说明：

\-B ：指定数据库

\-F ：刷新日志

\-R ：备份存储过程等

\-x ：锁表

\--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；

\--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；

\--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；

\--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；

注意:在从库上执行备份时，即--dump-slave=2，这时整个dump过程都是stop io\_thread的状态

mysqldump导出数据时，当这个参数（ master-data ）的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1

当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

\--single-transaction： 从5.1.13开始mysqldump指定--single-transaction备份时使用START TRANSACTION /\*!40100 WITH CONSISTENT SNAPSHOT \*/ 来代替begin 开启一个事物，这样就能在备份的时候产生一个一致的快照

在主库上导出，记录主库binlog及position

**mysqldump -uroot -p -B XX**\_db  **--master-data=2** \--single-transaction\--quick\--skip-add-drop-table\--force**>/home/slavedbbak/XX**\_db**\_$(date +%F).sql**

在从库上导出，记录主库binlog及position

mysqldump -uroot -p123456 \-B xx\_db oo\_db \--dump-slave=2 \--single-transaction \--quick\--set-gtid-purged=OFF\--skip-add-drop-table \--force>/home/slavedbbak/all\_$(date +%F).sql

最后，总结几点：

1）本案例适用于人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复

2）恢复条件为mysql要开启binlog日志功能，并且要全备和增量的所有数据

3）恢复时建议对外停止更新，即禁止更新数据库

4）先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除（也可通过时间和位置点），再恢复到数据库。