步骤2:恢复删除的数据
恢复
[root@centos7-mysql-1 mysql]# /usr/bin/mysqlbinlog --start-position=1818 --stop-position=2049 --database=atguigudb33 /var/lib/mysql/binlog.000010 | /usr/bin/mysql -uroot -p123456 -v atguigudb33
结果
mysql> select * from student; +----+---------+--------+ | id | name | class | +----+---------+--------+ | 1 | 张三3 | 一班 | | 3 | 李四1 | 一班 | | 6 | jerry | 一班 | | 8 | 王五 | 二班 | | 11 | Tim | 一班 | | 15 | 赵六 | 二班 | | 17 | Tom1 | 三班 | | 18 | Jerry | 四班 | | 20 | 钱七 | 三班 | | 22 | aaa | No.1 | | 23 | aaa | No.1 | +----+---------+--------+ 11 rows in set (0.00 sec) mysql>
步骤3:恢复更新的数据
恢复
[root@centos7-mysql-1 mysql]# /usr/bin/mysqlbinlog --start-position=2128 --stop-position=2383 --database=atguigudb33 /var/lib/mysql/binlog.000010 | /usr/bin/mysql -uroot -p123456 -v atguigudb33
结果
mysql> select * from student; +----+---------+--------+ | id | name | class | +----+---------+--------+ | 1 | 张三3 | 一班 | | 3 | 李四1 | 一班 | | 6 | jerry | 一班 | | 8 | 王五 | 二班 | | 11 | Tim | 一班 | | 15 | 赵六 | 二班 | | 17 | Tom1 | 三班 | | 18 | Jerry | 四班 | | 20 | 钱七 | 三班 | | 22 | bbb | No.1 | | 23 | aaa | No.1 | +----+---------+--------+ 11 rows in set (0.00 sec) mysql>
可以看到最终结果和删除数据之前的结果一样，利用binlog实现了数据恢复。
当然也可以使用日期恢复，命令格式如下:
/usr/bin/mysqlbinlog --start-datetime="2022-01-05 15:39:22” --stop-datetime="2022-01-05 15:40:19 " --database=atguigu14 /var/lib/mysql/binlog/atguigu-bin.000005 | /usr/bin/mysql -uroot -pabc123 -v atguigu14
日期可以根据binlog日志详情查看，如下所示。可能出现一个事务执行时间过短，那么就是同样的时间，几秒内执行完成，此时我们找到下一个事务的开始时间即可，多计算一些时间就可以了。本次事务的开始时间是22010515:39:22，结束时间设置为220105 15:40:19
# at 2462 #220813 17:23:30 server id 1 end_log_pos 2544 CRC32 0x7f2a50e7 Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1660382610/*!*/; BEGIN /*!*/; # at 2544 #220813 17:23:30 server id 1 end_log_pos 2613 CRC32 0xe57d4c09 Table_map: `atguigudb33`.`student` mapped to number 105 # at 2613 #220813 17:23:30 server id 1 end_log_pos 2676 CRC32 0x45a08932 Delete_rows: table id 105 flags: STMT_END_F BINLOG ' km33YhMBAAAARQAAADUKAAAAAGkAAAAAAAEAC2F0Z3VpZ3VkYjMzAAdzdHVkZW50AAMDDw8EPAAe AAYBAQACASEJTH3l km33YiABAAAAPwAAAHQKAAAAAGkAAAAAAAEAAgAD/wAWAAAAA2JiYgROby4xABcAAAADYWFhBE5v LjEyiaBF '/*!*/; ### DELETE FROM `atguigudb33`.`student` ### WHERE ### @1=22 ### @2='bbb' ### @3='No.1' ### DELETE FROM `atguigudb33`.`student` ### WHERE ### @1=23 ### @2='aaa' ### @3='No.1' # at 2676 #220813 17:23:30 server id 1 end_log_pos 2707 CRC32 0x122b6021 Xid = 57 COMMIT/*!*/;
mysqlbinlog命令对于意外操作非常有效，比如因操作不当误删了数据表。
5.5 删除二进制日志
MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。PURGE MASTER LOGS只删除指定部分的二进制日志文件， RESET MASTER删除所有的二进制日志文件。具体如下：
1. PURGE MASTER LOGS：删除指定日志文件
PURGE MASTER LOGS语法如下：
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名' PURGE {MASTER | BINARY} LOGS BEFORE '指定日期'
举例:使用PURGE MASTER LOGS语句删除创建时间比binlog.000005早的所有日志
(1）多次重新启动MySQL服务，便于生成多个日志文件。然后用SHOW语句显示二进制日志文件列表
SHOW BINARY LOGS;
(2）执行PURGE MASTER LOGS语句删除创建时间比binlog.oo0005早的所有日志
PURGE MASTER LoGS TO "binlog.000005";
(3）显示二进制日志文件列表
SHOW BINARY LOGS ;
比binlog.000005早的所有日志文件都已经被删除了。
举例:使用PURGE MASTER LOGS语句删除2020年10月25号前创建的所有日志文件。具体步骤如下:
(1）显示二进制日志文件列表
SHOW BINARY LOGS;
(2）执行mysqlbinlog命令查看二进制日志文件binlog.000005的内容
mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.000005"
结果可以看出20220105为日志创建的时间，即2022年1月05日。
(3）使用PURGE MASTER LOGs语句删除2022年1月05日前创建的所有日志文件
PURGE MASTER LoGS before "20220105";
(4）显示二进制日志文件列表
SHOW BINARY LOGS ;
2022年01月05号之前的二进制日志文件都已经被删除，最后一个没有删除，是因为当前在用，还未记录最后的时间，所以未被删除。
2.RESET MASTER:删除所有二进制日志文件
使用RESET MASTER语句，清空所有的binlog日志。MySQL会重新创建二进制文件，新的日志文件扩展名将重新从o00001开始编号。慎用!
举例:使用RESET MASTER语句删除所有日志文件。
(1）重启MySQL服务若干次，执行SHOW语句显示二进制日志文件列表。
SHOW BINARY LOGS;
(2)执行RESET MASTER语句，删除所有日志文件
RESET MASTER;
执行完该语句后，原来的所有二进制日志已经全部被删除。
5.6 其它场景
二进制日志可以通过数据库的全量备份和二进制日志中保存的增量信息 ，完成数据库的无损失恢复 。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。
在这种情况下，一个有效的解决办法是配置主从数据库服务器 ，甚至是一主多从 的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。
6. 再谈二进制日志(binlog)
6.1 写入机制
binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
我们可以通过binlog_cache_size参数控制单个线程binlog cache大小，如果存储内容超过了这个参数，就要暂存到磁盘(Swap)。binlog日志刷盘流程如下:
上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快
上图的 fsync，才是将数据持久化到磁盘的操作
write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的 binglog 会丢失。如下图：
为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。
最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。
在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。
