MySQL的二进制日志（binlog）使用分析（1）——binlog日志的格式及配置

Mysql的二进制日志（binlog）作用是用来记录对mysql数据库的更新，对数据库的查询select或show等不会被binlog日志记录。其主要用于数据库的主从复制以及增量恢复。可以使用mysqlbinlog命令查看二进制日志的内容。

一、MySQL binlog的三种格式

1、STATMENT

STATMENT格式是MySQL默认的二进制日志格式，基于SQL语句的复制（statement-based replication，SBR），把修改修改数据的每一条sql语句记录到binlog中。该日志模式只记录修改数据的sql语句，而不记录具体的数据变化。

2、ROW

基于行的复制（row-based replication，RBR)：记录哪条数据被修改，以及修改前和修改后的数据。而不记录具体执行的sql语句。ROW的格式是事务性或称为行的二进制日志，会将当前对数据的操作以事务的方式记录下来。

3、MIXED

混合模式复制（mixed-based replication，MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。MIXED是基于ROW的，也可能是STATEMENT格式，是一种混合格式，但以ROW格式为主。

说明：如果是replication环境，那么binlog_format建议设置为ROW格式，即以事务的格式来记录，并且replicaton环境要使用事务型引擎而不要使用非事务型引擎。

二、和binlog相关的配置

1、基本配置

[mysqld] ##打开binlog日志，同时指定日志的路径及基本文件名 log-bin=/var/lib/mysql/mysql-bin server-id=1 ## 为服务器指定一个编号

2、和binlog有关的其他设置

#设置日志格式，该选项可以设置为statement、row、mixed binlog_format = mixed #设置binlog清理时间 expire_logs_days = 7 #设置binlog每个日志文件的最大大小 max_binlog_size = 100m #设置binlog缓存大小 binlog_cache_size = 4m #最大binlog缓存大小 max_binlog_cache_size = 512m

重启MySQL使设置生效。

三、日志操作命令

1、查看日志文件信息

使用show binary logs或者show master logs可以查看日志文件的名称以及大小等信息。

mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 201 | | mysql-bin.000002 | 177 | | mysql-bin.000003 | 177 | | mysql-bin.000004 | 2206 | | mysql-bin.000005 | 718 | +------------------+-----------+ 5 rows in set (0.01 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 201 | | mysql-bin.000002 | 177 | | mysql-bin.000003 | 177 | | mysql-bin.000004 | 2206 | | mysql-bin.000005 | 718 | +------------------+-----------+ 5 rows in set (0.00 sec)

2、查看与日志相关的参数

mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------------+ 6 rows in set (0.01 sec) --说明： --log_bin：开启或关闭日志 --log_bin_basename：日志文件的位置和名称 --log_bin_index：日志文件的索引文件位置和名称 mysql> show variables like '%binlog%'; +--------------------------------------------+----------------------+ | Variable_name | Value | +--------------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log_statements_unsafe_for_binlog | ON | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sync_binlog | 1 | +--------------------------------------------+----------------------+ 22 rows in set (0.00 sec) --说明： --binlog_format：日志文件的格式，包括：statement，row，mixed三种。

3、查看master状态

利用show master status命令可以查看最后（最新）一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)的位置。

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 718 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

4、刷新log日志

使用flush logs命令，产生一个新编号的binlog日志文件。

mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 201 | | mysql-bin.000002 | 177 | | mysql-bin.000003 | 177 | | mysql-bin.000004 | 2206 | | mysql-bin.000005 | 718 | +------------------+-----------+ 5 rows in set (0.01 sec) mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 201 | | mysql-bin.000002 | 177 | | mysql-bin.000003 | 177 | | mysql-bin.000004 | 2206 | | mysql-bin.000005 | 765 | | mysql-bin.000006 | 154 | +------------------+-----------+ 6 rows in set (0.00 sec)

5、重置（清空）所有binlog日志

mysql> reset master; Query OK, 0 rows affected (0.05 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 154 | +------------------+-----------+ 1 row in set (0.00 sec)

6、查看二进制日志的信息

二进制日志文件中是二进制的数据，因此不能直接使用文本命令或工具查看，需要使用专门的工具。mysql自带的mysqlbinlog就是一个用于查看二进制日志文件的工具，使用方式如下：

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200704 9:56:25 server id 2 end_log_pos 123 CRC32 0x20e2ab6b Start: binlog v 4, server v 5.7.27-log created 200704 9:56:25 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' yeH/Xg8CAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADJ4f9eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AWur4iA= '/*!*/; # at 123 #200704 9:56:25 server id 2 end_log_pos 154 CRC32 0x00ab9ec4 Previous-GTIDs # [empty] # at 154 #200704 10:11:38 server id 2 end_log_pos 219 CRC32 0xfa0d8cf1 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #200704 10:11:38 server id 2 end_log_pos 298 CRC32 0x3d6a80da Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1593828698/*!*/; SET @@session.pseudo_thread_id=2/*!*/; 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 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/*!*/; BEGIN /*!*/; # at 298 #200704 10:11:38 server id 2 end_log_pos 397 CRC32 0xa994d353 Query thread_id=2 exec_time=0 error_code=0 use `hist`/*!*/; SET TIMESTAMP=1593828698/*!*/; update t2 set id=id+10000 /*!*/; # at 397 #200704 10:11:38 server id 2 end_log_pos 428 CRC32 0x1976aede Xid = 11 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*/;

二进制日志中的每条记录是从一个【at 数字】开始到一个【at 数字】结束，之间是一条二进制日志记录。每条记录的end_log_pos的值与记录结束的at后的值是一致的。