MySQL二进制日志Binlog
一、MySQL Binlog
文件记录了mysql数据库所有的dml，ddl语句事件，不包括select，也可以记录SQL语句，也可以记录行的变化，还可以记录这些操作时间。
比如一条语句 update t1 set id=3 where id between 1 and 6; 加入他改了三行数据；
binlog记录的内容有可能是：
1）可能是这条update语句；
2）可能是修改的这三行数据；
3）肯呢个两个都记录；
MySQL中三种日志的区别：
1、general log ：记录数据库里边所有SQL操作记录。
2、redo log ： 知识记录innodb存储引擎级的修改日志。
3、binlog：只记录数据库server层面内部的修改情况。select和show不记录
1.1 binlog的作用
1) MySQL主从复制；
2）数据恢复，如果宕机恢复，异常操作恢复；
缺点： 大概损失1%的性能，占用空间。
1.2 binlog的三种工作模式
1）row level 日志记录每一行的修改情况。优点：可以更方便查看每一条数据修改情况，确定数据量比较大；
使用场景：数据安全复制强一致；
2） statement level 记录每一条修改的SQL； 数据量量小；
使用场景：使用mysql的功能较少，不适用存储过程，触发器较少
3）混合模式 结合row 和statement
二 、 MySQL binlog 二进制格式参数
binlog_format=row (row,statement,mixed)
binlog_row_image=full (full,minimal,noblob)
binlog_row_query_log_events=on (on,off)
2.1 binlog_format=statement
先将binlog的格式设置为statement
mysql> set global binlog_format=statement; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.binlog_format; +------------------------+ | @@global.binlog_format | +------------------------+ | STATEMENT | +------------------------+ 1 row in set (0.00 sec) mysql> mysql> set session binlog_format=statement; Query OK, 0 rows affected (0.00 sec) mysql> select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | STATEMENT | +-----------------+ 1 row in set (0.00 sec)
刷新日志，查看日志状态
mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | BIN_LOG.000035 | 154 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
创建一个表，并插入数据：
mysql> create table yddl(id int,timepoint datetime not null default current_timestamp); Query OK, 0 rows affected (0.06 sec) mysql> insert into yddl(id) values (1); Query OK, 1 row affected (0.07 sec) mysql> select * from yddl; +------+---------------------+ | id | timepoint | +------+---------------------+ | 1 | 2022-10-13 13:40:02 | +------+---------------------+ 1 row in set (0.01 sec)
查看日志的状态：
mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | BIN_LOG.000035 | 656 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
日志position从154变为了656，
进入日志目录，查看日志信息：
# cd /mysql/log/binlog/
[root@localhost binlog]# mysqlbinlog --start-position=154 --stop-position=656 BIN_LOG.000035
日志都是以SQL语句形式记录DDL，DML操作
create table yddl(id int,timepoint datetime not null default current_timestamp) /*!*/; # at 368 #221013 13:40:02 server id 6 end_log_pos 433 CRC32 0x1398a8a9 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 433 #221013 13:40:02 server id 6 end_log_pos 516 CRC32 0x2298d420 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1665639602/*!*/; SET @@session.time_zone='SYSTEM'/*!*/; BEGIN /*!*/; # at 516 #221013 13:40:02 server id 6 end_log_pos 625 CRC32 0xf1c535c7 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1665639602/*!*/; insert into yddl(id) values (1)
对主从复制的影响：
1）对有些参数，在不同服务器执行，结果不一样，会导致主从不一致，特别是一些系统函数，uuid（），user（），now（）。
2）性能问题；
3）数据问题。
2.2 binlog_format=row 和 binlog_rows_query_log_events=off
mysql> set binlog_format=row; Query OK, 0 rows affected (0.00 sec) mysql> set global binlog_format=row; Query OK, 0 rows affected (0.00 sec) mysql> select @@session.binlog_format; +-------------------------+ | @@session.binlog_format | +-------------------------+ | ROW | +-------------------------+ 1 row in set (0.00 sec) mysql> select @@global.binlog_format; +------------------------+ | @@global.binlog_format | +------------------------+ | ROW | +------------------------+ 1 row in set (0.00 sec)
修改表，观察日志的变化
mysql> create table yddl2(id int,timepoint datetime not null default current_timestamp); Query OK, 0 rows affected (0.04 sec) mysql> insert into yddl2(id) values (1); Query OK, 1 row affected (0.01 sec)
执行一条update语句
mysql> update yddl2 set id=2 where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
查看日志中的记录
#221013 14:19:03 server id 6 end_log_pos 1141 CRC32 0x9ecde135 Update_rows: table id 226 flags: STMT_END_F BINLOG ' 161HYxMGAAAAMAAAAD0EAAAAAOIAAAAAAAEAAmpsAAV5ZGRsMgACAxIBAAEmnEkf 161HYx8GAAAAOAAAAHUEAAAAAOIAAAAAAAEAAgAC///8AQAAAJmuGuPs/AIAAACZrhrj7DXhzZ4=
ddl的记录是明文的，DML的记录是加密的（只记录行的操作，不记录SQL语句），针对加密的反解析，加参数-v
加参数：
[root@localhost binlog]# mysqlbinlog --base64-output=decode-rows -v --start-position=154 --stop-position=1172 BIN_LOG.000036
观察日志的变化
# at 1355 #221013 14:36:50 server id 6 end_log_pos 1411 CRC32 0x83d5f8df Update_rows: table id 226 flags: STMT_END_F ### UPDATE `jl`.`yddl2` ### WHERE ### @1=2 ### @2='2022-10-13 14:15:44' ### SET ### @1=5 ### @2='2022-10-13 14:15:44' # at 1411 #221013 14:36:50 server id 6 end_log_pos 1442 CRC32 0xd3884a48 Xid = 57 COMMIT/*!*/; # at 1442
binlog_format=row对主从的影响：
1）同步最安全；
2）不管是更新还是删除，或者批量操作，都是按照行进行，依次处理所有的记录，二不是整条SQL；
2.3 binlog_format=row 状态和 binlog_row_query_log_events=on 这样不仅可以记录行的变化，而且可以记录SQL语句
观察日志变化，日志更加详细。
## UPDATE `jl`.`yddl2` ### WHERE ### @1=4 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2022-10-13 15:01:36' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### SET ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2022-10-13 15:01:36' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ # at 1115 #221013 15:02:12 server id 6 end_log_pos 1146 CRC32 0x143854f8 Xid = 74 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ;
mysql> show binlog events in 'BIN_LOG.000037'; +----------------+------+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+------+----------------+-----------+-------------+---------------------------------------+ | BIN_LOG.000037 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | BIN_LOG.000037 | 123 | Previous_gtids | 6 | 154 | | | BIN_LOG.000037 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | BIN_LOG.000037 | 219 | Query | 6 | 289 | BEGIN | | BIN_LOG.000037 | 289 | Rows_query | 6 | 344 | # update yddl set id=4 where id=1 | | BIN_LOG.000037 | 344 | Table_map | 6 | 391 | table_id: 225 (jl.yddl) | | BIN_LOG.000037 | 391 | Update_rows | 6 | 467 | table_id: 225 flags: STMT_END_F | | BIN_LOG.000037 | 467 | Xid | 6 | 498 | COMMIT /* xid=70 */ | | BIN_LOG.000037 | 498 | Anonymous_Gtid | 6 | 563 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | BIN_LOG.000037 | 563 | Query | 6 | 641 | BEGIN | | BIN_LOG.000037 | 641 | Rows_query | 6 | 696 | # insert into yddl2(id) values(4) | | BIN_LOG.000037 | 696 | Table_map | 6 | 744 | table_id: 226 (jl.yddl2) | | BIN_LOG.000037 | 744 | Write_rows | 6 | 789 | table_id: 226 flags: STMT_END_F | | BIN_LOG.000037 | 789 | Xid | 6 | 820 | COMMIT /* xid=72 */ | | BIN_LOG.000037 | 820 | Anonymous_Gtid | 6 | 885 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | BIN_LOG.000037 | 885 | Query | 6 | 955 | BEGIN | | BIN_LOG.000037 | 955 | Rows_query | 6 | 1011 | # update yddl2 set id=6 where id=4 | | BIN_LOG.000037 | 1011 | Table_map | 6 | 1059 | table_id: 226 (jl.yddl2) | | BIN_LOG.000037 | 1059 | Update_rows | 6 | 1115 | table_id: 226 flags: STMT_END_F | | BIN_LOG.000037 | 1115 | Xid | 6 | 1146 | COMMIT /* xid=74 */ | +----------------+------+----------------+-----------+-------------+---------------------------------------+ 20 rows in set (0.01 sec)
2.4 binlog_row_image=full,minimal(默认),noblob
binlog_row_image=full, insert/update语句的set部分是新记录，delete/update的where部分是全部的旧记录；
binlog_row_image=minial, update语句只有修改的列内容，delete及update的where部分都没有（如果主键或唯一索引会有）
binlog_row_image=noblob , 不记录
2.5 binlog_format=mixed
大多数以statement记录binlog，当隔离级别为rc的时候，则为row的方式记录。
2.6 binlog过期清理天数
mysql> show variables like '%expire_log%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 99 | +------------------+-------+ 1 row in set (0.00 sec)
手动清除日志，
mysql> purge binary logs to 'BIN_LOG.000004'; -----------清楚4之前的日志 Query OK, 0 rows affected (0.06 sec)
查看：
mysql> show binary logs; +----------------+-----------+ | Log_name | File_size | +----------------+-----------+ | BIN_LOG.000004 | 1317 | | BIN_LOG.000005 | 177 |
全部清除日志
mysql> reset master; Query OK, 0 rows affected (0.00 sec) mysql> show binary logs; +----------------+-----------+ | Log_name | File_size | +----------------+-----------+ | BIN_LOG.000001 | 154 | +----------------+-----------+ 1 row in set (0.00 sec)
2.7 show binlog events 查看日志
mysql> show binlog events in 'BIN_LOG.000002';
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| BIN_LOG.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| BIN_LOG.000002 | 123 | Previous_gtids | 6 | 154 | |
| BIN_LOG.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000002 | 219 | Query | 6 | 289 | BEGIN |
| BIN_LOG.000002 | 289 | Table_map | 6 | 336 | table_id: 109 (jl.yddl) |
| BIN_LOG.000002 | 336 | Delete_rows | 6 | 381 | table_id: 109 flags: STMT_END_F |
| BIN_LOG.000002 | 381 | Xid | 6 | 412 | COMMIT /* xid=24 */ |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
show binlog events参数：
1）in ‘日志文件名’ 不指定默认查看第一个日志文件
2）from pos 指定其实日志的起始pos位置
mysql> show binlog events in 'BIN_LOG.000002' from 219; +----------------+-----+-------------+-----------+-------------+---------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+-----+-------------+-----------+-------------+---------------------------------+ | BIN_LOG.000002 | 219 | Query | 6 | 289 | BEGIN | | BIN_LOG.000002 | 289 | Table_map | 6 | 336 | table_id: 109 (jl.yddl) | | BIN_LOG.000002 | 336 | Delete_rows | 6 | 381 | table_id: 109 flags: STMT_END_F | | BIN_LOG.000002 | 381 | Xid | 6 | 412 | COMMIT /* xid=24 */ | +----------------+-----+-------------+-----------+-------------+---------------------------------+ 4 rows in set (0.00 sec)
3） limit 步长，查多少条记录
mysql> show binlog events in 'BIN_LOG.000002' from 219 limit 2; +----------------+-----+------------+-----------+-------------+-------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+-----+------------+-----------+-------------+-------------------------+ | BIN_LOG.000002 | 219 | Query | 6 | 289 | BEGIN | | BIN_LOG.000002 | 289 | Table_map | 6 | 336 | table_id: 109 (jl.yddl) | +----------------+-----+------------+-----------+-------------+-------------------------+ 2 rows in set (0.00 sec)
2.8 mysqlbinlog查看二进制日志
1）默认查看日志
[root@localhost binlog]# mysqlbinlog BIN_LOG.000004
2）指定数据库
[root@localhost binlog]# mysqlbinlog -d jl BIN_LOG.000004>1.txt
[root@localhost binlog]# mysqlbinlog --database jl BIN_LOG.000005>3.txt
3)禁止恢复过程中产生日志，查看该日志的时候，不会产生新的日志，不会给该日志写如新的日志
[root@localhost binlog]# mysqlbinlog --disable-log-bin BIN.000005 [root@localhost binlog]# mysqlbinlog -D BIN.000005
4）最后一个日志文件
-t, --to-last-log Requires -R. Will not stop at the end of the requested binlog but rather continue printing until the end of the last binlog of the MySQL server. If you send the output to the same MySQL server, that may lead to an endless loop.
5)控制输出的编码格式
--base64-output=name Determine when the output statements should be base64-encoded BINLOG statements: 'never' disables it and works only for binlogs without row-based events; 'decode-rows' decodes row events into commented pseudo-SQL statements if the --verbose option is also given; 'auto' prints base64 only when necessary (i.e., for row-based events and format description events). If no --base64-output[=name] option is given at all, the default is 'auto'.
[root@localhost binlog]# mysqlbinlog --base64-output=decode-rows -v BIN_LOG.000006
6）跳过行
[root@localhost binlog]# mysqlbinlog -o 10 BIN_LOG.000006
7）起始位置
[root@localhost binlog]# mysqlbinlog --start-position=123 --stop-position=289 BIN_LOG.000004
8）起始时间
[root@localhost binlog]# mysqlbinlog --start-datetime='2022-10-14 22:16:52' BIN_LOG.000004
