MySQL中的binlog简单查看

DRS数据迁移

DRS做数据迁移有两个阶段，初始化阶段，和增量阶段
初始化就是把表中的存量数据从源端迁移到目标端去，增量阶段就是从源端实时拉取binlog并解析成SQL，并在目标端执行，以达到实时同步的目的

binlog参数配置

1、binlog开关
MySQL中可以配置增量日志binlog，来保存实例上的数据变更，可能通过参数sql_log_bin来控制，其中on表示开启

mysql> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+

2、binlog格式
binlog记录格式有两种，statement/row，其中statment记录SQL格式，row格式记录每行的变更内容，控制格式的参数有三种，statement/mix/row，其中mix表示两者的混合

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

3、row格式的binlog的镜像有两种，full和minimal，其中full情况下，即使某一列未发生变化也会把变化前变化后的值内容记录下来，而minimal则不会

mysql> show variables like '%binlog_row_image%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+

4、对于从主库复制过来的binlog是否记入本地binlog中，可以通过参数log_slave_updates来控制

mysql> show variables like '%log_slave%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | ON    |
+-------------------+-------+

通过MySQL连接查看binlog

1、可以通过show master status来查看当前写入的binlog的位点，其中
file表示下一个binlog的bytes内容即将写入的文件名
position表示下一个binlog的bytes内容写入到file中的位点的起点(byte字节数)
Binlog_Do_DB表示需要记录binlog的库名列表，也就是白名单
Binlog_Ignore_DB表示需要忽略的binlog的库名列表，也就是黑名单
Executed_Gtid_Set表示本库中已经执行完的全局事务的id集合

mysql> show master status;
+---------------+-----------+--------------+------------------+-------------------------------------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
+---------------+-----------+--------------+------------------+-------------------------------------------------+
| binlog.000049 | 392867987 |              |                  | dac56362-4d8e-11ea-ad85-6805caaf2dbd:1-18828368 |
+---------------+-----------+--------------+------------------+-------------------------------------------------+

2、binlog列表，表示本地保存的binlog及其大小(byte字节数)

mysql> show binary logs;
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| binlog.000048 |     107816 |
| binlog.000049 |  392966547 |
+---------------+------------+

3、预览binlog内容，命令内容中
in后面为binlog的文件名
from为byte数偏移量
limit为需要读取的binlog的event数量
event内容中
pos为本event的byte在binlog文件中的偏移量
end_log_pos为下一个event的偏移量
event_type为event类型
server_id为MySQL的id
info为binlog内容

mysql> show binlog events in 'binlog.000048' from 1226 limit 4;
+---------------+------+------------+-----------+-------------+-----------------------------------------------------------------------------------+
| Log_name      | Pos  | Event_type | Server_id | End_log_pos | Info                                                                              |
+---------------+------+------------+-----------+-------------+-----------------------------------------------------------------------------------+
| binlog.000048 | 1226 | Query      | 123453310 |        1294 | BEGIN                                                                             |
| binlog.000048 | 1294 | Table_map  | 123453310 |        1421 | table_id: 109 (drs_global_83cb2e89_0ca8_45d3_bb1d_81a07429fac1.trep_commit_seqno) |
| binlog.000048 | 1421 | Write_rows | 123453310 |        1483 | table_id: 109 flags: STMT_END_F                                                   |
| binlog.000048 | 1483 | Xid        | 123453310 |        1514 | COMMIT /* xid=38 */                                                               |
+---------------+------+------------+-----------+-------------+-----------------------------------------------------------------------------------+

通过文件来查看binlog

1、MySQL连接查看到的binlog内容比较简略，要想查看具体内容，可以通过mysqlbinlog命令来查看，–start-position为读取起点偏移量，–stop-position为读取截止点的偏移量，-vv为解析出来内容，后面跟需要查看的binlog文件名

> mysqlbinlog --start-position=1226 --stop-position=1514 -vv binlog.000048
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210816  9:37:19 server id 123453310  end_log_pos 123 CRC32 0x7430aae7  Start: binlog v 4, server v 5.7.22-log created 210816  9:37:19 at startup
ROLLBACK/*!*/;
BINLOG '
T8EZYQ9+v1sHdwAAAHsAAAAAAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABPwRlhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeeqMHQ=
'/*!*/;
# at 1226
#210819 18:05:50 server id 123453310  end_log_pos 1294 CRC32 0xdf37b623         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1629367550/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1294
#210819 18:05:50 server id 123453310  end_log_pos 1421 CRC32 0x768dcfe8         Table_map: `drs_global_83cb2e89_0ca8_45d3_bb1d_81a07429fac1`.`trep_commit_seqno` mapped to number 109
# at 1421
#210819 18:05:50 server id 123453310  end_log_pos 1483 CRC32 0x9eccabe0         Write_rows: table id 109 flags: STMT_END_F

BINLOG '
/iweYRN+v1sHfwAAAI0FAAAAAG0AAAAAAAEAL2Ryc19nbG9iYWxfODNjYjJlODlfMGNhOF80NWQz
X2JiMWRfODFhMDc0MjlmYWMxABF0cmVwX2NvbW1pdF9zZXFubwANAwgC/g8I/AMSDxIS/Av+A4AB
BACAAQAABP4f6M+Ndg==
/iweYR5+v1sHPgAAAMsFAAAAAG0AAAAAAAEAAgAN///49gAAAAD/////////////mapmoXKZqmah
cuCrzJ4=
'/*!*/;
### INSERT INTO `drs_global_83cb2e89_0ca8_45d3_bb1d_81a07429fac1`.`trep_commit_seqno`
### SET
###   @1=0 /* INT meta=0 nullable=0 is_null=0 */
###   @2=-1 (18446744073709551615) /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=-1 (65535) /* SHORTINT meta=0 nullable=1 is_null=0 */
###   @4=NULL /* STRING(3) meta=65027 nullable=1 is_null=1 */
###   @5=NULL /* VARSTRING(384) meta=384 nullable=1 is_null=1 */
###   @6=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @7=NULL /* LONGBLOB/LONGTEXT meta=4 nullable=1 is_null=1 */
###   @8=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @9='2021-08-19 10:05:50' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @10=NULL /* VARSTRING(384) meta=384 nullable=1 is_null=1 */
###   @11=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @12='2021-08-19 10:05:50' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @13=NULL /* LONGBLOB/LONGTEXT meta=4 nullable=1 is_null=1 */
# at 1483
#210819 18:05:50 server id 123453310  end_log_pos 1514 CRC32 0x0544c8e9         Xid = 38
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2、拉取远程binlog下载
其中read-from-remote-server表示要从远程拉取binlog
raw表示不解析内容
to-last-log表示拉取直到最后一个binlog文件

> mysqlbinlog -h192.168.12.126 -uroot -P3306 -phuawei --raw --read-from-remote-server binlog.000048 --to-last-log
> ls
binlog.000048   binlog.000049   binlog.000050