mysql 数据还原恢复

1、停止对外服务，防止还原过程数据紊乱

     比如防火墙关掉3306端口，停止数据库本地访问数据库的服务

2、先备份原有数据（可选，数据恢复则必选）

mysqldump -uubackup -p2019@Iotcomm!@# -B --single-transaction  chengdu_sems_job |gzip >/program/backup/mysql_data/temp_chengdu_sems_job_202009031110.sql.gz

3、获取完全备份的执行sql(本例子中还原数据的sql都存储在 /recover/sql 目录下)

 zcat /program/backup/172.31.0.21-Q/chengdu_sems_job_03.sql.gz >   /program/recover/sql/chengdu_sems_job.sql

4.通过二进制日志获取执行sql

4.1 查看完全备份时二进制日志备份的最后位置（本例子为 /backup/mysql_data/test_db_27.sql.gz）

zcat /backup/mysql_data/test_db_27.sql.gz | grep MASTER_LOG_FILE

显示结果例子：-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=155;

4.1 查看完全备份时二进制日志备份的最后位置（本例子为 /backup/mysql_data/test_db_27.sql.gz）

zcat /backup/mysql_data/test_db_27.sql.gz | grep MASTER_LOG_FILE

显示结果例子：-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=155;

 

4.2 查看二进制日志文件（本例子二进制存储的路径为/var/lib/mysql）

ls -l /var/lib/mysql | grep "binlog.0"

 

4.3 本例子中还原数据的sql都存储在 /recover/sql 目录下

科普：mysqlbinlog的几个参数

参数1： --start-position=N

从二进制日志中第1个位置等于N参量时的事件开始读，注意导出的sql数据包含这一行；

不加这个参数不限制，则从头开始。

参数2： --database=db_name，-d

db_name只列出该数据库的条目(用于分库还原，如果整个数据库还原就不用加这个参数)

参数3：--result-file=name, -r

将输出指向给定的文件，追加的方式

 

4.3.1 创建或者清空存储增量sql的文件/recover/sql/recover_test.sql

echo "" > /recover/sql/recover_test_inc.sql

 

分支1：如果直接还原数据的，则直接还原第4步骤以后的sql

4.3.2：添加 MASTER_LOG_FILE 所指的二进制日志，开始位置为第4步中查询到的位置(本例为155,文件为/var/lib/mysql/binlog.000007)

mysqlbinlog --start-position=155 -d test_db /var/lib/mysql/binlog.000007 -r /recover/sql/recover_test_inc.sql

4.3.3：添加 MASTER_LOG_FILE 所指的二进制日志之后的二进制日志文件（即编码大于MASTER_LOG_FILE 所指的二进制日志）（本例子为/var/lib/mysql/binlog.000008），如果没有就跳过这一步

mysqlbinlog -d test_db /var/lib/mysql/binlog.000008 -r /recover/sql/recover_test_inc.sql

 

分支2：如果是因为误操作还原数据的，则要跳过误操作的数据步骤

执行同步骤分支1，但是要多一些跳过误操作数据的操作（即删除对应的日志数据行）

本文举个例子，跳过 delete from user where memo = 'for delete' 语句（误操作语句）

从recover_test_inc.sql 查找这条语句，如下

# at 2677

#200827 9:02:32 server id 1 end_log_pos 2755 CRC32 0x3e12b489 Query thread_id=70 exec_time=0 error_code=0

SET TIMESTAMP=1598490152/*!*/;

BEGIN

/*!*/;

# at 2755

#200827 9:02:32 server id 1 end_log_pos 2819 CRC32 0xab8d5c92 Table_map: `test_db`.`user` mapped to number 3846

# at 2819

#200827 9:02:32 server id 1 end_log_pos 2906 CRC32 0xd9f9988b Delete_rows: table id 3846 flags: STMT_END_F



BINLOG '

KAZHXxMBAAAAQAAAAAMLAAAAAAYPAAAAAAEAB3Rlc3RfZGIABHVzZXIAAwMPDwRQAHgABwEBAAID

/P8AklyNqw==

KAZHXyABAAAAVwAAAFoLAAAAAAYPAAAAAAEAAgAD/wACAAAACWxpbmd0ZXN0Mgpmb3IgZGVsZXRl

AAMAAAAJbGluZ3Rlc3QzCmZvciBkZWxldGWLmPnZ

'/*!*/;

# at 2906

#200827 9:02:32 server id 1 end_log_pos 2937 CRC32 0x4a447412 Xid = 41838

COMMIT/*!*/;

# at 2937



发现Delete_rows行

# at 2819

#200827 9:02:32 server id 1 end_log_pos 2906 CRC32 0xd9f9988b Delete_rows: table id 3846 flags: STMT_END_F



BINLOG '

KAZHXxMBAAAAQAAAAAMLAAAAAAYPAAAAAAEAB3Rlc3RfZGIABHVzZXIAAwMPDwRQAHgABwEBAAID

/P8AklyNqw==

KAZHXyABAAAAVwAAAFoLAAAAAAYPAAAAAAEAAgAD/wACAAAACWxpbmd0ZXN0Mgpmb3IgZGVsZXRl

AAMAAAAJbGluZ3Rlc3QzCmZvciBkZWxldGWLmPnZ

'/*!*/;

删除然后保存就可以了。

5.删除原有的数据库

mysql -ubackup -p2019@Iotcomm!@# -e "drop database if exists chengdu_sems_db"

6.执行还原的sql语句

mysql -ubackup -p2019@Iotcomm!@#  < /program/recover/sql/chengdu_sems_db.sql

7.检验数据是否成功还原

8.开始对外服务

比如防火墙放开3306端口，开始数据库本地访问数据库的服务