MySql ocp认证之备份与恢复(四)

一、使用二进制文件进行数据恢复
常用的方式有两种：
1.使用时间点恢复：通常在换会员物理备份或mysqldump备份之后，再用此方法还原完整备份至当前时间以来的数据更改，以确保数据完整性。
2.使用时间SID恢复:在某些特殊情况下也用于还原误操作。
注意：要从二进制日志还原数据，必须知道当前二进制文件的文件名和当前事件的位置。
a.查询当前有效的所有二进制日志文件名：

show binary log;
b.显示当前二进制日志文件名以及当前事件的位置：
show master status;
注意：如果还原操作需要使用多个二进制日志文件，安全的方式是使用mysqlbinlog命令。因为每个文件执行完后会删除临时表，下一个文件执行会报错。
不安全的示例：

mysqlbinlog binlog.000001 |mysql -uroot -p123456
mysqlbinlog binlog.000002 |mysql -uroot -p123456
1
2

正确的方法：

mysqlbinlog binlog.000001 binlog.000002 |mysql -uroot -p123456
或者
mysqlbinlog binlog.00000{1,2,3,4} |mysql -uroot -p123456
1
2
3

或者将二进制文件先重定向为sql语句，然后执行：

mysqlbinlog binlog.000001 >~/temp/statement.sql
mysqlbinlog binlog.000002 >~/temp/statement.sql
mysql -uroot -p123456 <~/temp/statement.sql
1
2
3

如果用了gtid的二进制日志转储的时候需要使用–skip-gtids选项跳过gtid(gtid主要用于主从同步),如下：

mysqlbinlog --skip-gtids binlog.000001 >~/temp/statement.sql
mysqlbinlog --skip-gtids binlog.000002 >~/temp/statement.sql
mysql -uroot -p123456 <~/temp/statement.sql
1
2
3

三、使用时间点恢复

全部时间的sql：

mysqlbinlog --skip-gtids --base64-output=decode-rows -vv /usr/local/mysql/mysql-bin/mysql-bin.000004 >~/temp/statement.sql
1

导出指定时间点到当前时间的sql：

mysqlbinlog --start-datetime="2022-09-03 00:00:00" binlog.000001 >~/temp/statement-20220903000000.sql
1

导出指定时间段到当前时间的sql：

mysqlbinlog --start-datetime="2022-09-03 00:00:00" --stop-datetime="2022-09-04 00:00:00" binlog.000001 >~/temp/statement-20220903000000.sql
1

注意：此时显示的sql语句是转码后的，不方便阅读。
–base64-output=decode-rows：显示具体的sql
-v,-vv,-vvv:显示详细信息。

四、使用事件位置恢复
使用前必须确定时间的SID，SID值是binlog文件中at后的数字。
查看sid：

mysqlbinlog --skip-gtids --base64-output=decode-rows -vv /usr/local/mysql/mysql-bin/mysql-bin.000004 |grep -i at
1

注意：SID的编号在每个二进制文件中是重复只用的。
show binlog events 可以查看binary log中的事件，能够查询到每个event的Pos和End_log_pos如下

show binlog events in ‘mysql-bin.000004’;

例一：导出指定SID的sql语句

mysqlbinlog --skip-gtids --base64-output=decode-rows -vv --start-position=4 --stop-position=125 /usr/local/mysql/mysql-bin/mysql-bin.00000{1,2,3,4} >~/temp/statement-1_4-4_125.sql
1

注意：上面语句表示导出mysql-bin.000001文件sid为4到mysql-bin.000004文件sid为125的所有sql。–start-position=表示第一个文件的开始，不是所有文件的开始，–stop-position=125也一样。