MySQL 8.0.11 binlog文件查看

运行环境：MySQL8.0.11+centos7.4
1.查看binlog文件的列表：
方法1：查看binlog的日志文件：
# cat node1_bin.index 
/home/mysql/node1_bin.000001
方法2：通过MySQL的命令查看：
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| node1_bin.000001 |      1406 |
+------------------+-----------+
1 row in set (0.00 sec)

2.查看正在使用的binlog：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| node1_bin.000001 |     1406 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.查看binlog文件中的内容：
mysql> help SHOW BINLOG EVENTS
语法格式为：
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos]  [LIMIT [offset,] row_count]

3.1查看binlog中的全部内容：
mysql> show binlog events in 'node1_bin.000001';
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| node1_bin.000001 |    4 | Format_desc    |       255 |         124 | Server ver: 8.0.11, Binlog ver: 4                                                                                                     |
| node1_bin.000001 |  124 | Previous_gtids |       255 |         155 |                                                                                                                                       |
| node1_bin.000001 |  155 | Anonymous_Gtid |       255 |         228 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                  |
| node1_bin.000001 |  228 | Query          |       255 |         337 | create database wuhan /* xid=255 */                                                                                                   |
| node1_bin.000001 |  337 | Anonymous_Gtid |       255 |         412 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                  |
| node1_bin.000001 |  412 | Query          |       255 |         614 | create table wuhan.city(cityid int not null auto_increment primary key,cityname varchar(20),citylevel tinyint not null) /* xid=256 */ |
| node1_bin.000001 |  614 | Anonymous_Gtid |       255 |         689 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                  |
| node1_bin.000001 |  689 | Query          |       255 |         760 | BEGIN                                                                                                                                 |
| node1_bin.000001 |  760 | Table_map      |       255 |         818 | table_id: 251 (wuhan.city)                                                                                                            |
| node1_bin.000001 |  818 | Write_rows     |       255 |         867 | table_id: 251 flags: STMT_END_F                                                                                                       |
| node1_bin.000001 |  867 | Xid            |       255 |         898 | COMMIT /* xid=257 */                                                                                                                  |
| node1_bin.000001 |  898 | Anonymous_Gtid |       255 |         973 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                  |
| node1_bin.000001 |  973 | Query          |       255 |        1044 | BEGIN                                                                                                                                 |
| node1_bin.000001 | 1044 | Table_map      |       255 |        1102 | table_id: 251 (wuhan.city)                                                                                                            |
| node1_bin.000001 | 1102 | Write_rows     |       255 |        1149 | table_id: 251 flags: STMT_END_F                                                                                                       |
| node1_bin.000001 | 1149 | Xid            |       255 |        1180 | COMMIT /* xid=258 */                                                                                                                  |
| node1_bin.000001 | 1180 | Anonymous_Gtid |       255 |        1253 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                  |
| node1_bin.000001 | 1253 | Query          |       255 |        1406 | alter table wuhan.city add column citystate tinyint not null default 0 /* xid=260 */                                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.00 sec)
3.2 binlog文件内容过滤查询：
mysql> show binlog events in 'node1_bin.000001' from 1253 limit 1;
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                                                                                 |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------------------+
| node1_bin.000001 | 1253 | Query      |       255 |        1406 | alter table wuhan.city add column citystate tinyint not null default 0 /* xid=260 */ |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.通过mysqlbinlog命令查看binlog中的内容：
4.1 查看binlog输入保存到文件：
#mysqlbinlog node1_bin.000001  > binlog.sql
或者
#mysqlbinlog node1_bin.000001  -r binlog.sql

4.2 根据binlog的位置信息(position)提取binlog的内容:
#mysqlbinlog node1_bin.000001 --start-position=200  --stop-position=300
4.3 根据位置信息提取binlog内容并压缩：
#mysqlbinlog node1_bin.000001 --start-position=200  --stop-position=300 |gzip > binlog.sql.gz
4.4 可以一次读取多个binlog文件：
#mysqlbinlog node1_bin.000001 node1_bin.000002 node1_bin.000003

4.5可以根据binlog文件中的起始时间提取binlog中的内容：
# mysqlbinlog node1_bin.000001 --start-datetime='2018-07-23 14:00:00' --stop-datetime='2018-07-23 16:10:00'
注释根据时间和position查询binlog中的内容可以只选择开始或者结束或者起始。

4.6可以根据某个指定的数据库来过滤binlog中的内容：
#mysqlbinlog node1_bin.000001 --start-datetime='2018-07-23 14:00:00' --stop-datetime='2018-07-23 16:10:00' --database=wuhan 
4.7 可以转换binlog中的字符集为utf8 或者utfmb4：
#mysqlbinlog node1_bin.000001 --start-datetime='2018-07-23 14:00:00' --stop-datetime='2018-07-23 16:10:00' --database=wuhan  --set-charset=utf8
4.8 可以读取远程主机的binlog：
#mysqlbinlog  --read-from-remote-server --host=172.16.1.79 --user=repl --password=repl --port=3311 node1_bin.000001 | more
注释：此用户只需最少的权限REPLICATION SLAVE即可。
4.9 查看只需要基本的binlog信息而不需要binlog的附加信息：
# mysqlbinlog --short-form node1_bin.000001 
WARNING: --short-form is deprecated and will be removed in a future version
4.10 跳过前N条binlog信息：
# mysqlbinlog --offset=10 node1_bin.000001 
# mysqlbinlog -o 10 node1_bin.000001 
4.11 将binlog中的内容还原到数据库中：
# mysqlbinlog node1_bin.000001 |mysql -uroot -p 
4.12 查看binlog文件中的SQL内容：
#mysqlbinlog  --base64-output=decode-rows -v  /home/mysql/node1_bin.000001  > test.sql