书接上回:
相关参数
binlog_format=row
binlog_row_image=full
binlog_rows_query_log_events=off
相关参数的修改和binlog日志文件的重新生成
-- 查看当前的binlog日志文件和pos点
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| 21_mysql_bin.000001 | 2230 | | | |
+---------------------+----------+--------------+------------------+-------------------+
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 |
+---------------------------------------+--------------------------------+
| 1 | 1 |
+---------------------------------------+--------------------------------+
1 row in set (0.00 sec)
-- 设置binlog_rows_query_log_events参数在全局下等于off
mysql> set global binlog_rows_query_log_events=off;
Query OK, 0 rows affected (0.00 sec)
-- 设置binlog_rows_query_log_events参数在当前会话下等于off
mysql> set session binlog_rows_query_log_events=off;
Query OK, 0 rows affected (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)
-- 查看binlog_format参数在全局和当前会话下的值
mysql> select @@global.binlog_format,@@binlog_format;
+------------------------+-----------------+
| @@global.binlog_format | @@binlog_format |
+------------------------+-----------------+
| ROW | ROW |
+------------------------+-----------------+
1 row in set (0.00 sec)
-- 查看binlog_row_image参数在全局和当前会话下的值
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日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
-- 查看新生成的binlog日志文件和Pos点
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| 21_mysql_bin.000002 | 154 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
测试数据
-- 进入chenliang库
use chenliang;
-- 查看是否成功进入到chenliang库下面
select database();
-- 创建test2表
create table if not exists test2(
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"测试表2";
-- 插入两行数据
insert into test2(name,age,jobdate) values
("chenliang01",25,curdate()),
("chenliang02",26,curdate());
-- 更改age字段的内容为26,条件是name等于chenliang01,id等于1
update test2 set age=26 where id=1 and name="chenliang01";
-- 删除id等于2,name内容等于chenliang02的记录
delete from test2 where id=2 and name="chenliang02";
-- truncate表
truncate test2;
-- 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)
用mysqlbinlog命令来查看二进制日志文件(一)
[root@master binlog]# mysqlbinlog 21_mysql_bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190913 5:59:30 server id 21 end_log_pos 123 CRC32 0x785bf8e1 Start: binlog v 4, server v 5.7.22-log created 190913 5:59:30
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
gmh7XQ8VAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeH4W3g=
'/*!*/;
# at 123
#190913 5:59:30 server id 21 end_log_pos 154 CRC32 0xf1482bd5 Previous-GTIDs
# [empty]
# at 154
-- 这是创建test2表的数据(对于DDL语句,不解密也是可以看到的)
#190913 6:00:44 server id 21 end_log_pos 219 CRC32 0x4142bdb8 Anonymous_GTID last_committed=0 sequence_number=1rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#190913 6:00:44 server id 21 end_log_pos 640 CRC32 0xf77797b4 Query thread_id=5 exec_time=0 error_code=0
use `chenliang`/*!*/;
SET TIMESTAMP=1568368844/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
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/*!*/;
create table if not exists test2(
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"测试表2"
/*!*/;
# at 640
-- 这是insert语句的记录(没解密,是看不到具体执行的sql语句和具体插入的数据)
#190913 6:00:53 server id 21 end_log_pos 705 CRC32 0xa65a2304 Anonymous_GTID last_committed=1 sequence_number=2rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 705
#190913 6:00:53 server id 21 end_log_pos 790 CRC32 0x4a9ce882 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1568368853/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 790
#190913 6:00:53 server id 21 end_log_pos 848 CRC32 0x5330e110 Table_map: `chenliang`.`test2` mapped to number 228
# at 848
#190913 6:00:53 server id 21 end_log_pos 931 CRC32 0x0bcd39d1 Write_rows: table id 228 flags: STMT_END_F
BINLOG '
1Wh7XRMVAAAAOgAAAFADAAAAAOQAAAAAAAEACWNoZW5saWFuZwAFdGVzdDIABAMPAwoCPAAAEOEw
Uw==
1Wh7XR4VAAAAUwAAAKMDAAAAAOQAAAAAAAEAAgAE//ABAAAAC2NoZW5saWFuZzAxGQAAAC3HD/AC
AAAAC2NoZW5saWFuZzAyGgAAAC3HD9E5zQs=
'/*!*/;
# at 931
#190913 6:00:53 server id 21 end_log_pos 962 CRC32 0xdc019b93 Xid = 859
COMMIT/*!*/;
# at 962
-- 这是update语句的记录(没解密,是看不到具体执行的sql语句和具体更新的数据)
#190913 6:00:57 server id 21 end_log_pos 1027 CRC32 0x557621d3 Anonymous_GTID last_committed=2 sequence_number=3rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1027
#190913 6:00:57 server id 21 end_log_pos 1104 CRC32 0x2b947318 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1568368857/*!*/;
BEGIN
/*!*/;
# at 1104
#190913 6:00:57 server id 21 end_log_pos 1162 CRC32 0xd4ce561a Table_map: `chenliang`.`test2` mapped to number 228
# at 1162
#190913 6:00:57 server id 21 end_log_pos 1246 CRC32 0x8abf9ce3 Update_rows: table id 228 flags: STMT_END_F
BINLOG '
2Wh7XRMVAAAAOgAAAIoEAAAAAOQAAAAAAAEACWNoZW5saWFuZwAFdGVzdDIABAMPAwoCPAAAGlbO
1A==
2Wh7XR8VAAAAVAAAAN4EAAAAAOQAAAAAAAEAAgAE///wAQAAAAtjaGVubGlhbmcwMRkAAAAtxw/w
AQAAAAtjaGVubGlhbmcwMRoAAAAtxw/jnL+K
'/*!*/;
# at 1246
#190913 6:00:57 server id 21 end_log_pos 1277 CRC32 0xa31e9197 Xid = 868
COMMIT/*!*/;
# at 1277
-- 这是delete语句的记录(没解密,是看不到具体执行的sql语句和具体删除的数据)
#190913 6:01:00 server id 21 end_log_pos 1342 CRC32 0x82a6b177 Anonymous_GTID last_committed=3 sequence_number=4rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1342
#190913 6:01:00 server id 21 end_log_pos 1419 CRC32 0x1659b9bf Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1568368860/*!*/;
BEGIN
/*!*/;
# at 1419
#190913 6:01:00 server id 21 end_log_pos 1477 CRC32 0x148148ba Table_map: `chenliang`.`test2` mapped to number 228
# at 1477
#190913 6:01:00 server id 21 end_log_pos 1536 CRC32 0x7f1b93eb Delete_rows: table id 228 flags: STMT_END_F
BINLOG '
3Gh7XRMVAAAAOgAAAMUFAAAAAOQAAAAAAAEACWNoZW5saWFuZwAFdGVzdDIABAMPAwoCPAAAukiB
FA==
3Gh7XSAVAAAAOwAAAAAGAAAAAOQAAAAAAAEAAgAE//ACAAAAC2NoZW5saWFuZzAyGgAAAC3HD+uT
G38=
'/*!*/;
# at 1536
#190913 6:01:00 server id 21 end_log_pos 1567 CRC32 0x3d00eb95 Xid = 877
COMMIT/*!*/;
# at 1567
-- 这是truncate语句的记录(对于DDL语句是明文的)
#190913 6:01:03 server id 21 end_log_pos 1632 CRC32 0x9534cffe Anonymous_GTID last_committed=4 sequence_number=5rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1632
#190913 6:01:03 server id 21 end_log_pos 1718 CRC32 0x866a454f Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1568368863/*!*/;
truncate test2
/*!*/;
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.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190913 5:59:30 server id 21 end_log_pos 123 CRC32 0x785bf8e1 Start: binlog v 4, server v 5.7.22-log created 190913 5:59:30
# Warning: this binlog is either in use or was not closed properly.
# at 123
#190913 5:59:30 server id 21 end_log_pos 154 CRC32 0xf1482bd5 Previous-GTIDs
# [empty]
# at 154
-- 这是创建test2表的sql记录(对于DDL语句不解密,sql语句也是明文的)
#190913 6:00:44 server id 21 end_log_pos 219 CRC32 0x4142bdb8 Anonymous_GTID last_committed=0 sequence_number=1rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#190913 6:00:44 server id 21 end_log_pos 640 CRC32 0xf77797b4 Query thread_id=5 exec_time=0 error_code=0
use `chenliang`/*!*/;
SET TIMESTAMP=1568368844/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
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/*!*/;
create table if not exists test2(
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"测试表2"
/*!*/;
# at 640
-- 这是insert语句(解密后,无法看到执行的完整sql语句,但是可以看到插入的值)
#190913 6:00:53 server id 21 end_log_pos 705 CRC32 0xa65a2304 Anonymous_GTID last_committed=1 sequence_number=2rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 705
#190913 6:00:53 server id 21 end_log_pos 790 CRC32 0x4a9ce882 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1568368853/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 790
#190913 6:00:53 server id 21 end_log_pos 848 CRC32 0x5330e110 Table_map: `chenliang`.`test2` mapped to number 228
# at 848
#190913 6:00:53 server id 21 end_log_pos 931 CRC32 0x0bcd39d1 Write_rows: table id 228 flags: STMT_END_F
### INSERT INTO `chenliang`.`test2`
### 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`.`test2`
### 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 931
#190913 6:00:53 server id 21 end_log_pos 962 CRC32 0xdc019b93 Xid = 859
COMMIT/*!*/;
# at 962
-- 这是update语句(解密后,无法看到执行的完整sql语句,但可以看到更新前和更新后的数据)
#190913 6:00:57 server id 21 end_log_pos 1027 CRC32 0x557621d3 Anonymous_GTID last_committed=2 sequence_number=3rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1027
#190913 6:00:57 server id 21 end_log_pos 1104 CRC32 0x2b947318 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1568368857/*!*/;
BEGIN
/*!*/;
# at 1104
#190913 6:00:57 server id 21 end_log_pos 1162 CRC32 0xd4ce561a Table_map: `chenliang`.`test2` mapped to number 228
# at 1162
#190913 6:00:57 server id 21 end_log_pos 1246 CRC32 0x8abf9ce3 Update_rows: table id 228 flags: STMT_END_F
### UPDATE `chenliang`.`test2`
### WHERE -- 更新前的数据
### @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 */
### SET -- 更新后的数据
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='chenliang01' /* 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 1246
#190913 6:00:57 server id 21 end_log_pos 1277 CRC32 0xa31e9197 Xid = 868
COMMIT/*!*/;
# at 1277
-- 这是delete语句(解密后,无法看到执行的完整sql语句,但可以看到具体删除了哪些数据)
#190913 6:01:00 server id 21 end_log_pos 1342 CRC32 0x82a6b177 Anonymous_GTID last_committed=3 sequence_number=4rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1342
#190913 6:01:00 server id 21 end_log_pos 1419 CRC32 0x1659b9bf Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1568368860/*!*/;
BEGIN
/*!*/;
# at 1419
#190913 6:01:00 server id 21 end_log_pos 1477 CRC32 0x148148ba Table_map: `chenliang`.`test2` mapped to number 228
# at 1477
#190913 6:01:00 server id 21 end_log_pos 1536 CRC32 0x7f1b93eb Delete_rows: table id 228 flags: STMT_END_F
### DELETE FROM `chenliang`.`test2`
### WHERE
### @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 1536
#190913 6:01:00 server id 21 end_log_pos 1567 CRC32 0x3d00eb95 Xid = 877
COMMIT/*!*/;
# at 1567
-- 这是truncate的sql记录(对于DDL语句不解密)
#190913 6:01:03 server id 21 end_log_pos 1632 CRC32 0x9534cffe Anonymous_GTID last_committed=4 sequence_number=5rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1632
#190913 6:01:03 server id 21 end_log_pos 1718 CRC32 0x866a454f Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1568368863/*!*/;
truncate test2
/*!*/;
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=full
binlog_rows_query_log_events=off
-- 结果总结
binlog_format=row
01:直接用"mysqlbinlog PATH/二进制日志文件"来查看二进制日志文件。
02:binlog日志文件中对于DML数据操纵语言的语句是加密的(需要解密才能看到)
03:binlog日志文件中对于DDL数据定义语言的语句是明文的(也就是可以直接看到)
binlog_row_image=full
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=off
01:用"mysqlbinlog --base64-output=decode-row -vv PATH/二进制日志文件"来查看二进制日志文件;
02:binlog日志文件中对于DML数据操纵语言语句,不会显示具体执行的SQL语句;
03:我们在mysql中用show binlog events in "二进制日志文件";命令可以看到DDL语句,但是看不到DML语句