文章目录

• • 1. binlog文件内容查看及数据恢复
  • • 1.1 binlog事件查看
    • 1.2 binlog内容查看
    • 1.2 binlog日志截取恢复

1. binlog文件内容查看及数据恢复

1.1 binlog事件查看

# (1) 查看最新mysql正在使用最新的日志文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      885 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+# (2) 查看最新日志内容
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                    |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.30-log, Binlog ver: 4   |
| mysql-bin.000001 | 123 | Previous_gtids |         6 |         154 |                                         |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000001 | 219 | Query          |         6 |         338 | create database dongkun charset utf8mb4 |
| mysql-bin.000001 | 338 | Anonymous_Gtid |         6 |         403 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000001 | 403 | Query          |         6 |         476 | BEGIN                                   |
| mysql-bin.000001 | 476 | Table_map      |         6 |         534 | table_id: 108 (world.city)              |
| mysql-bin.000001 | 534 | Delete_rows    |         6 |         854 | table_id: 108 flags: STMT_END_F         |
| mysql-bin.000001 | 854 | Xid            |         6 |         885 | COMMIT /* xid=40 */                     |
+------------------+-----+----------------+-----------+-------------+----------------------------------------(3) 查看DDL语句，用的频率比较高
[root@db01 ~]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000001'" | grep DROP

1.2 binlog内容查看

# (1) binlog日志不能使用vim进行查看，mysqlbinlog专门用来查看binlog日志文件内容，重定向普通文件后才可以查看
[root@db01 binlog]# mysqlbinlog /data/binlog/mysql-bin.000001 > /tmp/a.sql# (2) 查看重定向后的binlog DDL日志内容，DDL直接可以看到sql语句  
## at是每个事件的开头位置。下一个at的开始，就是上一个at的结束点。at下面的set命令不用关注，重点看的是sql语句
[root@db01 binlog]# vim /tmp/a.sql       
# at 219
#221205 23:11:45 server id 6  end_log_pos 338 
create database dongkun charset utf8mb4
/*!*/;
# at 338# (3) 查看重定向后的binlog DML日志内容，begin开始的一个事务
[root@db01 binlog]# vim /tmp/a.sql     
# at 403
#221205 23:19:58 server id 6  end_log_pos 476 
BEGIN
# at 476
#221205 23:19:58 server id 6  end_log_pos 534 
# at 534
#221205 23:19:58 server id 6  end_log_pos 854 CRC32 0xff40056c  
BINLOG '
HgyOYxMGAAAAOgAAABYCAAAAAGwAAAAAAAEABXdvcmxkAARjaXR5AAUD/v7+Awb+I/4D/hQAw3TN
lA==
HgyOYyAGAAAAQAEAAFYDAAAAAGwAAAAAAAEAAgAF/+ABAAAABUthYnVsA0FGRwVLYWJvbCApGwDg
AgAAAAhRYW5kYWhhcgNBRkcIUWFuZGFoYXK8nwMA4AMAAAAFSGVyYXQDQUZHBUhlcmF0sNkCAOAE
AAAADk1hemFyLWUtU2hhcmlmA0FGRwVCYWxraDjzAQDgBQAAAAlBbXN0ZXJkYW0DTkxEDU5vb3Jk
LUhvbGxhbmRAKAsA4AYAAAAJUm90dGVyZGFtA05MRAxadWlkLUhvbGxhbmSpDQkA4AcAAAAESGFh
ZwNOTEQMWnVpZC1Ib2xsYW5kRLoGAOAIAAAAB1V0cmVjaHQDTkxEB1V0cmVjaHRTkwMA4AkAAAAJ
RWluZGhvdmVuA05MRA1Ob29yZC1CcmFiYW50cxQDAGwFQP8=
'/*!*/;
# at 854
#221205 23:20:15 server id 6  end_log_pos 885 
COMMIT/*!*/;# (4) 因为binlog使用的是row模式，DML人类不可读，可以使用--base64-output=decode-rows -vvv可以看个大致内容
## mysqlbinlog --base64-output=decode-rows -vvv 这条命令仅用于查看，如果要截取日志的话不能加这个--base64-output=decode-rows -vvv参数
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001 > /tmp/b.sql
[root@db01 binlog]# vim /tmp/b.sql
# at 534
#221205 23:19:58 server id 6  end_log_pos 854 CRC32 0xff40056c  Delete_rows: table id 108 flags: STMT_END_F
### DELETE FROM `world`.`city`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Kabul' /* STRING(35) meta=65059 nullable=0 is_null=0 */
###   @3='AFG' /* STRING(3) meta=65027 nullable=0 is_null=0 */
###   @4='Kabol' /* STRING(20) meta=65044 nullable=0 is_null=0 */
###   @5=1780000 /* INT meta=0 nullable=0 is_null=0 */
### DELETE FROM `world`.`city`# (5) 可以看mysqlbinlog命令的使用帮助，命令参数记不住可以使用--help来查询
[root@db01 binlog]# mysqlbinlog --help--base64-output=name Determine when the output statements should bebase64-encoded BINLOG statements: 'never' disables it andworks only for binlogs without row-based events;'decode-rows' decodes row events into commentedpseudo-SQL statements if the --verbose option is alsogiven; 'auto' prints base64 only when necessary (i.e.,for row-based events and format description events).  Ifno --base64-output[=name] option is given at all, thedefault is 'auto'.

1.2 binlog日志截取恢复