MySQL binlog

1、查看binlog的位置

  1. mysql> show variables like '%datadir%';

image.png

2、查看binlog文件的列表

  1. mysql> show binary logs;
  2. +---------------+-----------+-----------+
  3. | Log_name      | File_size | Encrypted |
  4. +---------------+-----------+-----------+
  5. | binlog.000016 |      1950 | No        |
  6. | binlog.000017 |       178 | No        |
  7. | binlog.000018 |       178 | No        |
  8. | binlog.000019 |   2182043 | No        |
  9. | binlog.000020 |       178 | No        |
  10. | binlog.000021 |    711145 | No        |
  11. +---------------+-----------+-----------+
  12. 6 rows in set (0.02 sec)
  14. mysql>

image.png

3、查看当前正在写入的binlog文件

  1. mysql> show master status\G
  2. *************************** 1. row ***************************
  3.              File: binlog.000021
  4.          Position: 711145
  5.      Binlog_Do_DB:
  6.  Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.00 sec)

image.png

4、查看binlog的文件内容

A.默认查看第一个binlog的文件内容

  1. mysql> show binlog events;

image.png

B.查看指定文件的binlog的文件内容

  1. mysql> show binlog events in 'binlog.000021';

5、使用mysqlbinlog工具查看binlog

该工具是MySQL提供的，在安装目录下可找到
image.png
Window上切换到mysqlbinlog程序目录下执行，指定的binlog文件需要指定，并不会自动搜索
Linux上可以配置mysqlbinlog可执行权限，或者添加软链接至用户的bin目录（原则上配置在环境变量PATH中的都可以），然后就可以随处执行了。

A.查看指定时间段的binlog日志

  1. fcsca@FCANT D:\LinkSpace\Download\DevelopPackage\MySQL\mysql-8.0.16-winx64\mysql-8.0.16-winx64\bin    [22:29]
  2. ❯ mysqlbinlog --no-defaults --database=test --start-datetime="2020-08-08 22:00:09" --stop-datetime="2020-08-08 22:28:50" ..\data\binlog.000021 | more
  3. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  4. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  5. DELIMITER /*!*/;
  6. # at 4
  7. #200723 21:59:06 server id 1  end_log_pos 124 CRC32 0x81b597fe  Start: binlog v 4, server v 8.0.16 created 200723 21:59:06 at startup
  8. # Warning: this binlog is either in use or was not closed properly.
  9. ROLLBACK/*!*/;
  10. BINLOG '
  11. qpcZXw8BAAAAeAAAAHwAAAABAAQAOC4wLjE2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  12. AAAAAAAAAAAAAAAAAACqlxlfEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
  13. CgH+l7WB
  14. '/*!*/;
  15. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  16. DELIMITER ;
  17. # End of log file
  18. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  19. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

image.png

B.查看指定时间的binlog将其重定向至文件（Window和Linux下皆可），文件内容会被清空，将本次的结果写入

  1. ❯ mysqlbinlog --no-defaults --database=test --start-datetime="2020-08-08 22:00:09" --stop-datetime="2020-08-08 22:28:50" ..\data\binlog.000021 > see.log

image.png
image.pngimage.png

C.查看指定时间的binlog将其追加至文件（Window和Linux下皆可），文件内容不会被清空，将本次的结果追加写入

  1. ❯ mysqlbinlog --no-defaults --database=test --start-datetime="2020-08-08 22:00:09" --stop-datetime="2020-08-08 22:28:50" ..\data\binlog.000021 >> see.log

image.png

D.查询时添加过滤条件（grep只适用于Linux）

  1. ❯ mysqlbinlog --no-defaults --database=test  binlogs.000078 | grep update | more