书接上回:
相关参数:
binlog_format=row
binlog_row_image=minimal
binlog_rows_query_log_events=on
相关参数的修改和binlog日志文件的重新生成
-- 查看当前的binlog日志文件和pos点
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| 21_mysql_bin.000002 | 1718 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 查看binlog_rows_query_log_events参数在全局和当前会话下的值
mysql> select @@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events;
+---------------------------------------+--------------------------------+
| @@global.binlog_rows_query_log_events | @@binlog_rows_query_log_events |
+---------------------------------------+--------------------------------+
| 0 | 0 |
+---------------------------------------+--------------------------------+
1 row in set (0.00 sec)
mysql> select @@global.binlog_row_image,@@binlog_row_image;
+---------------------------+--------------------+
| @@global.binlog_row_image | @@binlog_row_image |
+---------------------------+--------------------+
| FULL | FULL |
+---------------------------+--------------------+
1 row in set (0.00 sec)
-- 修改binlog_rows_query_log_events和binlog_row_image参数在全局和当前会话下的值
mysql> set global binlog_rows_query_log_events=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set session binlog_rows_query_log_events=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global binlog_row_image=minimal;
Query OK, 0 rows affected (0.00 sec)
mysql> set session binlog_row_image=minimal;
Query OK, 0 rows affected (0.00 sec)
-- 查看binlog_rows_query_log_events和binlog_row_image参数是否修改成功
mysql> select @@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events,@@global.binlog_row_image,@@binlog_row_image;
+---------------------------------------+--------------------------------+---------------------------+--------------------+
| @@global.binlog_rows_query_log_events | @@binlog_rows_query_log_events | @@global.binlog_row_image | @@binlog_row_image |
+---------------------------------------+--------------------------------+---------------------------+--------------------+
| 1 | 1 | MINIMAL | MINIMAL |
+---------------------------------------+--------------------------------+---------------------------+--------------------+
1 row in set (0.00 sec)
-- 查看binlog的模式
mysql> select @@global.binlog_format,@@binlog_format;
+------------------------+-----------------+
| @@global.binlog_format | @@binlog_format |
+------------------------+-----------------+
| ROW | ROW |
+------------------------+-----------------+
1 row in set (0.00 sec)
-- 生成新的binlog日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| 21_mysql_bin.000003 | 154 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
测试数据
-- 进入chenliang库
use chenliang;
-- 查看是否成功进入到chenliang库下面
select database();
-- 创建test3表
create table if not exists test3(
id int(10) unsigned not null auto_increment primary key comment"序列号",
name varchar(20) not null comment"姓名",
age int(3) unsigned not null comment"年龄",
jobdate date not null comment"参加工作的时间"
)engine=innodb character set utf8 collate utf8_general_ci comment"测试表3";
-- 插入两行数据
insert into test3(name,age,jobdate) values
("chenliang01",25,curdate()),
("chenliang02",26,curdate());
-- 更改age字段的内容为26,条件是name等于chenliang01,id等于1
update test3 set age=26 where id=1 and name="chenliang01";
-- 删除id等于2,name内容等于chenliang02的记录
delete from test3 where id=2 and name="chenliang02";
-- truncate表
truncate test3;
-- binlog日志文件和pos点
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| 21_mysql_bin.000003 | 2129 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
用mysqlbinlog命令来查看二进制日志文件(一)
[root@master binlog]# mysqlbinlog 21_mysql_bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190913 6:40:48 server id 21 end_log_pos 123 CRC32 0x138c0bba Start: binlog v 4, server v 5.7.22-log created 190913 6:40:48
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
MHJ7XQ8VAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AboLjBM=
'/*!*/;
# at 123
#190913 6:40:48 server id 21 end_log_pos 154 CRC32 0xbaca8d68 Previous-GTIDs
# [empty]
# at 154
-- 这是创建test2表的记录(对于DDL语句不解密也是可以看到的)
#190913 6:41:19 server id 21 end_log_pos 219 CRC32 0xfa188ec7 Anonymous_GTID last_committed=0 sequence_number=1rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#190913 6:41:19 server id 21 end_log_pos 658 CRC32 0x247796f7 Query thread_id=12 exec_time=0 error_code=0
use `chenliang`/*!*/;
SET TIMESTAMP=1568371279/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
-- 创建test3表
create table if not exists test3(
id int(10) unsigned not null auto_increment primary key comment"序列号",
name varchar(20) not null comment"姓名",
age int(3) unsigned not null comment"年龄",
jobdate date not null comment"参加工作的时间"
)engine=innodb character set utf8 collate utf8_general_ci comment"测试表3"
/*!*/;
# at 658
-- 这是insert语句的记录(没有解密,是看不到具体执行的sql语句,插入的什么数据也是看不到的)
#190913 6:41:20 server id 21 end_log_pos 723 CRC32 0xdc41a042 Anonymous_GTID last_committed=1 sequence_number=2rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 723
#190913 6:41:20 server id 21 end_log_pos 808 CRC32 0x805ed203 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1568371280/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 808
# at 955
#190913 6:41:20 server id 21 end_log_pos 1013 CRC32 0x06929859 Table_map: `chenliang`.`test3` mapped to number 233
# at 1013
#190913 6:41:20 server id 21 end_log_pos 1096 CRC32 0x43a6e636 Write_rows: table id 233 flags: STMT_END_F
BINLOG '
UHJ7XRMVAAAAOgAAAPUDAAAAAOkAAAAAAAEACWNoZW5saWFuZwAFdGVzdDMABAMPAwoCPAAAWZiS
Bg==
UHJ7XR4VAAAAUwAAAEgEAAAAAOkAAAAAAAEAAgAED/ABAAAAC2NoZW5saWFuZzAxGQAAAC3HD/AC
AAAAC2NoZW5saWFuZzAyGgAAAC3HDzbmpkM=
'/*!*/;
# at 1096
#190913 6:41:20 server id 21 end_log_pos 1127 CRC32 0xbf764cfc Xid = 1370
COMMIT/*!*/;
# at 1127
-- 这是update语句的记录(没有解密,是看不到具体执行的sql语句,更新的什么数据也是看不到的)
#190913 6:41:20 server id 21 end_log_pos 1192 CRC32 0x3fef3c7b Anonymous_GTID last_committed=2 sequence_number=3rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1192
#190913 6:41:20 server id 21 end_log_pos 1269 CRC32 0xfb2432f1 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1568371280/*!*/;
BEGIN
/*!*/;
# at 1269
# at 1426
#190913 6:41:20 server id 21 end_log_pos 1484 CRC32 0x7997bdcc Table_map: `chenliang`.`test3` mapped to number 233
# at 1484
#190913 6:41:20 server id 21 end_log_pos 1530 CRC32 0x5b43cd75 Update_rows: table id 233 flags: STMT_END_F
BINLOG '
UHJ7XRMVAAAAOgAAAMwFAAAAAOkAAAAAAAEACWNoZW5saWFuZwAFdGVzdDMABAMPAwoCPAAAzL2X
eQ==
UHJ7XR8VAAAALgAAAPoFAAAAAOkAAAAAAAEAAgAEAQT+AQAAAP4aAAAAdc1DWw==
'/*!*/;
# at 1530
#190913 6:41:20 server id 21 end_log_pos 1561 CRC32 0x38b116f5 Xid = 1372
COMMIT/*!*/;
# at 1561
-- 这是delete语句的记录(没有解密,是看不到具体执行的sql语句,删除了什么数据也是看不到的)
#190913 6:41:20 server id 21 end_log_pos 1626 CRC32 0x31502d9c Anonymous_GTID last_committed=3 sequence_number=4rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1626
#190913 6:41:20 server id 21 end_log_pos 1703 CRC32 0x10c6b29b Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1568371280/*!*/;
BEGIN
/*!*/;
# at 1703
# at 1834
#190913 6:41:20 server id 21 end_log_pos 1892 CRC32 0xdeefb93c Table_map: `chenliang`.`test3` mapped to number 233
# at 1892
#190913 6:41:20 server id 21 end_log_pos 1932 CRC32 0xff3a8f58 Delete_rows: table id 233 flags: STMT_END_F
BINLOG '
UHJ7XRMVAAAAOgAAAGQHAAAAAOkAAAAAAAEACWNoZW5saWFuZwAFdGVzdDMABAMPAwoCPAAAPLnv
3g==
UHJ7XSAVAAAAKAAAAIwHAAAAAOkAAAAAAAEAAgAEAf4CAAAAWI86/w==
'/*!*/;
# at 1932
#190913 6:41:20 server id 21 end_log_pos 1963 CRC32 0xe046a015 Xid = 1373
COMMIT/*!*/;
# at 1963
-- 这是truncate语句的记录(对于DDL语句是明文的)
#190913 6:41:20 server id 21 end_log_pos 2028 CRC32 0x2ae67560 Anonymous_GTID last_committed=4 sequence_number=5rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2028
#190913 6:41:20 server id 21 end_log_pos 2129 CRC32 0xcfcf12ee Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1568371280/*!*/;
-- truncate表
truncate test3
/*!*/;
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*/;
用mysqlbinlog命令来查看二进制日志文件(二)
[root@master binlog]# mysqlbinlog --base64-output=decode-row -vv 21_mysql_bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190913 6:40:48 server id 21 end_log_pos 123 CRC32 0x138c0bba Start: binlog v 4, server v 5.7.22-log created 190913 6:40:48
# Warning: this binlog is either in use or was not closed properly.
# at 123
#190913 6:40:48 server id 21 end_log_pos 154 CRC32 0xbaca8d68 Previous-GTIDs
# [empty]
# at 154
-- 这是创建test3表的sql语句(对于DDL语句是明文的)
#190913 6:41:19 server id 21 end_log_pos 219 CRC32 0xfa188ec7 Anonymous_GTID last_committed=0 sequence_number=1rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#190913 6:41:19 server id 21 end_log_pos 658 CRC32 0x247796f7 Query thread_id=12 exec_time=0 error_code=0
use `chenliang`/*!*/;
SET TIMESTAMP=1568371279/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
-- 创建test3表
create table if not exists test3(
id int(10) unsigned not null auto_increment primary key comment"序列号",
name varchar(20) not null comment"姓名",
age int(3) unsigned not null comment"年龄",
jobdate date not null comment"参加工作的时间"
)engine=innodb character set utf8 collate utf8_general_ci comment"测试表3"
/*!*/;
# at 658
-- 这是insert语句的记录(解密后,可以看到具体执行的sql语句,和具体插入的数据)
#190913 6:41:20 server id 21 end_log_pos 723 CRC32 0xdc41a042 Anonymous_GTID last_committed=1 sequence_number=2rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 723
#190913 6:41:20 server id 21 end_log_pos 808 CRC32 0x805ed203 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1568371280/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 808
#190913 6:41:20 server id 21 end_log_pos 955 CRC32 0x0f345824 Rows_query
# -- 插入两行数据
# insert into test3(name,age,jobdate) values
# ("chenliang01",25,curdate()),
# ("chenliang02",26,curdate())
# at 955
#190913 6:41:20 server id 21 end_log_pos 1013 CRC32 0x06929859 Table_map: `chenliang`.`test3` mapped to number 233
# at 1013
#190913 6:41:20 server id 21 end_log_pos 1096 CRC32 0x43a6e636 Write_rows: table id 233 flags: STMT_END_F
### INSERT INTO `chenliang`.`test3`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='chenliang01' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @3=25 /* INT meta=0 nullable=0 is_null=0 */
### @4='2019:09:13' /* DATE meta=0 nullable=0 is_null=0 */
### INSERT INTO `chenliang`.`test3`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='chenliang02' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @3=26 /* INT meta=0 nullable=0 is_null=0 */
### @4='2019:09:13' /* DATE meta=0 nullable=0 is_null=0 */
# at 1096
#190913 6:41:20 server id 21 end_log_pos 1127 CRC32 0xbf764cfc Xid = 1370
COMMIT/*!*/;
# at 1127
-- 这是update语句(解密后,可以看到具体执行的sql语句,和更新前的数据)
#190913 6:41:20 server id 21 end_log_pos 1192 CRC32 0x3fef3c7b Anonymous_GTID last_committed=2 sequence_number=3rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1192
#190913 6:41:20 server id 21 end_log_pos 1269 CRC32 0xfb2432f1 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1568371280/*!*/;
BEGIN
/*!*/;
# at 1269
#190913 6:41:20 server id 21 end_log_pos 1426 CRC32 0x58596904 Rows_query
# -- 更改age字段的内容为26,条件是name等于chenliang01,id等于1
# update test3 set age=26 where id=1 and name="chenliang01"
# at 1426
#190913 6:41:20 server id 21 end_log_pos 1484 CRC32 0x7997bdcc Table_map: `chenliang`.`test3` mapped to number 233
# at 1484
#190913 6:41:20 server id 21 end_log_pos 1530 CRC32 0x5b43cd75 Update_rows: table id 233 flags: STMT_END_F
### UPDATE `chenliang`.`test3`
### WHERE -- 更新前的条件,且条件还不全
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### SET -- 更新后某个字段的值
### @3=26 /* INT meta=0 nullable=0 is_null=0 */
# at 1530
#190913 6:41:20 server id 21 end_log_pos 1561 CRC32 0x38b116f5 Xid = 1372
COMMIT/*!*/;
# at 1561
-- 这是delete语句的记录(解密后,可以看到具体执行的sql语句,但看不到具体删除了哪些数据,条件也不全)
#190913 6:41:20 server id 21 end_log_pos 1626 CRC32 0x31502d9c Anonymous_GTID last_committed=3 sequence_number=4rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1626
#190913 6:41:20 server id 21 end_log_pos 1703 CRC32 0x10c6b29b Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1568371280/*!*/;
BEGIN
/*!*/;
# at 1703
#190913 6:41:20 server id 21 end_log_pos 1834 CRC32 0xccb0fb8b Rows_query
# -- 删除id等于2,name内容等于chenliang02的记录
# delete from test3 where id=2 and name="chenliang02"
# at 1834
#190913 6:41:20 server id 21 end_log_pos 1892 CRC32 0xdeefb93c Table_map: `chenliang`.`test3` mapped to number 233
# at 1892
#190913 6:41:20 server id 21 end_log_pos 1932 CRC32 0xff3a8f58 Delete_rows: table id 233 flags: STMT_END_F
### DELETE FROM `chenliang`.`test3`
### WHERE #删除的条件,条件还不全
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
# at 1932
#190913 6:41:20 server id 21 end_log_pos 1963 CRC32 0xe046a015 Xid = 1373
COMMIT/*!*/;
# at 1963
-- 这是truncate语句的记录(对于DDL语句是明文的)
#190913 6:41:20 server id 21 end_log_pos 2028 CRC32 0x2ae67560 Anonymous_GTID last_committed=4 sequence_number=5rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2028
#190913 6:41:20 server id 21 end_log_pos 2129 CRC32 0xcfcf12ee Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1568371280/*!*/;
-- truncate表
truncate test3
/*!*/;
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*/;
用show binlog events in "binlogfile"来进行查看
总结
-- 验证相关参数
binlog_format=row
binlog_row_image=minimal
binlog_rows_query_log_events=on
-- 结果总结
binlog_format=row
01:直接用"mysqlbinlog PATH/二进制日志文件"来查看二进制日志文件。
02:binlog日志文件中对于DML数据操纵语言是加密的(需要解密才能看到)
03:binlog日志文件中对于DDL数据定义语言是明文的(也就是可以直接看到)
binlog_row_image=minimal
01:用"mysqlbinlog --base64-output=decode-row -vv PATH/二进制日志文件"来查看二进制日志文件;
02:binlog日志文件中对于DML数据操作语言insert;只记录在哪些字段上插入了数据;
03:binlog日志文件中对于DML数据操作语言update;不会记录更新前的数据,只会记录更新了的字段的值;
04:binlog日志文件中对于DML数据操作语言delete;不会记录删除前的数据;
binlog_rows_query_log_events=on
01:用"mysqlbinlog --base64-output=decode-row -vv PATH/二进制日志文件"来查看二进制日志文件。
02:binlog日志文件中对于DML数据操纵语言,会显示具体执行的SQL语句。
03:我们在mysql中用show binlog events in "二进制日志文件";命令可以看到DDL语句,也可以看到DML语句。