MySQL 查看binlog日志方法

最新推荐文章于 2024-07-19 10:37:54 发布

Tom Ma.

最新推荐文章于 2024-07-19 10:37:54 发布

阅读量1.8k

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/mshxuyi/article/details/94005010

版权

MySQL 专栏收录该内容

31 篇文章 2 订阅

订阅专栏

mysqlbinlog

mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000004

BEGIN
/*!*/;
# at 203
# at 235
#190630 10:40:51 server id 2  end_log_pos 235 CRC32 0x8ef8ae9c 	Intvar
SET INSERT_ID=2/*!*/;
#190630 10:40:51 server id 2  end_log_pos 363 CRC32 0x5deda9b3 	Query	thread_id=2	exec_time=0	error_code=0
use `school`/*!*/;
SET TIMESTAMP=1561862451/*!*/;
insert into student (name,age) values ('alice',22)
/*!*/;
# at 363
#190630 10:40:51 server id 2  end_log_pos 394 CRC32 0xfe84554c 	Xid = 37
COMMIT/*!*/;

日志转文本文件

 mysqlbinlog /var/lib/mysql/mysql-bin.000004 > /var/lib/mysql/4.txt  #日志转txt

恢复指定日志文件到指定数据库

mysqlbinlog /var/lib/mysql/mysql-bin.000004 | mysql -uroot -p123456 school

恢复指定日志中的开始位置 start-pos 至结束位置 stop-pos 的日志记录到指定数据库

1、查看位置

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 16624
#190805 23:43:56 server id 108  end_log_pos 16698 CRC32 0xd526b54c 	Query	thread_id=8	exec_time=0	error_code=0
SET TIMESTAMP=1565019836/*!*/;
BEGIN
/*!*/;
# at 16698
#190805 23:43:56 server id 108  end_log_pos 16754 CRC32 0x0912126d 	Table_map: `school`.`student` mapped to number 109
# at 16754
#190805 23:43:56 server id 108  end_log_pos 16804 CRC32 0x91fd6625 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `school`.`student`
### SET
###   @1=56
###   @2='tom56'
###   @3=56
# at 16804
#190805 23:43:56 server id 108  end_log_pos 16835 CRC32 0x64dafa52 	Xid = 219
COMMIT/*!*/;
# at 16835
#190805 23:43:56 server id 108  end_log_pos 16900 CRC32 0x2a0af842 	Anonymous_GTID	last_committed=61	sequence_number=62	rbr_only=yes


mysql>  show binlog events in 'mysql-bin.000003' from 16624 limit 5\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000003
        Pos: 16624
 Event_type: Query
  Server_id: 108
End_log_pos: 16698
       Info: BEGIN
*************************** 2. row ***************************
   Log_name: mysql-bin.000003
        Pos: 16698
 Event_type: Table_map
  Server_id: 108
End_log_pos: 16754
       Info: table_id: 109 (school.student)
*************************** 3. row ***************************
   Log_name: mysql-bin.000003
        Pos: 16754
 Event_type: Write_rows
  Server_id: 108
End_log_pos: 16804
       Info: table_id: 109 flags: STMT_END_F
*************************** 4. row ***************************
   Log_name: mysql-bin.000003
        Pos: 16804
 Event_type: Xid
  Server_id: 108
End_log_pos: 16835
       Info: COMMIT /* xid=219 */
*************************** 5. row ***************************
   Log_name: mysql-bin.000003
        Pos: 16835
 Event_type: Anonymous_Gtid
  Server_id: 108
End_log_pos: 16900
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

2、开始恢复

#根据position
mysqlbinlog /var/lib/mysql/mysql-bin.000006 --start-position=16624 --stop-position=16900 | mysql -uroot -p123456 school

#根据datetime
mysqlbinlog /var/lib/mysql/mysql-bin.000006 --start-datetime='2019-10-07 10:00:00' --stop-datetime='2019-10-07 10:30:00' | mysql -uroot -p123456 school

show binlog events

查询第一个(最早)的binlog日志

mysql> show binlog events\G;

查看 binlog 文件

 show binary logs;

指定查询 mysql-bin.000021这个文件

 mysql> show binlog events in 'mysql-bin.000021'\G;

指定查询 mysql-bin.000021 这个文件，从pos点8224开始查起：

mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

指定查询 mysql-bin.000021 这个文件，从pos点8224开始查起，查询10条

 mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

指定查询 mysql-bin.000021 这个文件，从pos点8224开始查起，偏移2行，查询10条

mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

显示的结果

*************************** 1. row ***************************
   Log_name: mysql-bin.000015    #查询的binlog日志文件名
        Pos: 4                   #pos起始点:
 Event_type: Query               #事件类型：Query
  Server_id: 6                   #标识是由哪台服务器执行的
End_log_pos: 123                 #pos结束点:123(即：下行的pos起始点)
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  #执行的sql语句
*************************** 2. row ***************************
   Log_name: mysql-bin.000015
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 6
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql-bin.000015
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 6
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'