binlog 会记录表结构变更和表数据变更，有了 binlog 和 redolog，可以还原任意时刻的数据库状态。
binlog 二进制文件通常用来数据恢复、主从复制、审计。
本篇 Mysql 版本为 8.0。
归档日志（binlog）详解
binlog 的写入机制
-
事务执行过程中，先写到 binlog cache 中，日志提交时，再把 binlog cache 写到 binlog 文件中。
-
一个事务的 binlog 是一个整体，必须一次性写入，当超过了设置的 cache-size，就要暂存在磁盘中。
binlog 基础参数
-
log_bin binlog 的开关。
-
log_bin_basename log 文件位置。
-
log_bin_index binlog 索引文件。
-
sql_log_bin binlog 的开关。
mysql> show variables like "%log_bin%";
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/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.00 sec)
相关配置
-
binlog_cache_size 每个线程中，每个事务 binlog-cache 的最大容量，默认为 32M。
-
max_binlog_cache_size binlog-cache 的最大容量。
-
sync_binlog 持久化到磁盘的时机。write 指日志写入 cache；fsync 指日志持久化到磁盘中，默认为 1。
- 0，每次提交事务只 write 不 fsync。
- 1，每次提交事务都 fsync。
- N，每次提交事务都 write，累计 N 个事务后才 fsync。
-
binlog_group_commit_sync_delay 延迟多少微秒后才 fsync，默认为 0。
-
binlog_group_commit_sync_no_delay_count 累计多少个事务提交后才 fsync，默认为 0。
-
binlog_format binlog 的记录模式。默认为 MIXED。
-
ROW 清除记录每一行数据的修改细节，但是会产生大量日志。
-
STATEMENT 记录执行的 SQL 语句，日志量小，但有些情况会导致主从数据不一致。
-
MIXED 是前两种模式的混合，记录准确，文件大小适中。
-
-
max_binlog_size 每个 binlog 文件的大小，默认为 1G。
-
binlog_expire_logs_seconds binlog 过期时间，默认为 30 天。
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_expire_logs_seconds | 2592000 |
| binlog_format | MIXED |
| 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_row_metadata | MINIMAL |
| binlog_row_value_options | |
| 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 |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+--------------------------------------------+----------------------+
24 rows in set (0.00 sec)
SQL 命令查看 binlog
- binlog 文件列表。
show binary logs;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000083 | 8667374 |
| mysql-bin.000084 | 122684 |
| mysql-bin.000085 | 32099 |
| mysql-bin.000086 | 794208651 |
| mysql-bin.000087 | 39070717 |
+------------------+-----------+
5 rows in set (0.00 sec)
- 当前正在写入的 binlog 文件。
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000087 | 39120223 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-
查看 binlog 文件的内容。event 是 binlog 的最小单位。
show binlog events [in 'log_name'] [FROM pos] [limit [offset,] row_count]-
Log_name binlog 文件名
-
Pos 开始的位置
-
Event_type 事件类型
-
Server_id Mysql 服务 ID
-
End_log_pos 结束的位置
-
Info 事件内容
-
mysql> show binlog events in 'mysql-bin.000087' FROM 12542 limit 0,3 /G;
*************************** 1. row ***************************
Log_name: mysql-bin.000087
Pos: 12542
Event_type: Query
Server_id: 1
End_log_pos: 12666
Info: BEGIN
*************************** 2. row ***************************
Log_name: mysql-bin.000087
Pos: 12666
Event_type: Query
Server_id: 1
End_log_pos: 12988
Info: use `[数据库名]`; INSERT INTO xxxxxx (xxxxxxxxxxxxxxxx) VALUES (xxxxxxxxxxxxxx)
*************************** 3. row ***************************
Log_name: mysql-bin.000087
Pos: 12988
Event_type: Xid
Server_id: 1
End_log_pos: 13019
Info: COMMIT /* xid=541 */
3 rows in set (0.00 sec)
用 mysqlbinlog 查看二进制 binlog 文件
mysqlbinlog [binlog 文件名]
常用参数
-
–start-position 指定开始位置
-
–stop-position 指定结束位置
-
-start-datetime=”yyyy-MM-dd HH:mm:ss” 指定开始时间
-
–stop-datetime 指定结束时间
-
–database 指定数据库
文件内容
和上面 SQL 查得同样的 3 个 event，binlog 文件内容如下。
-
【# at 12666】一个 event 内容的开始，表示开始的位置
-
【#220714 14:11:35 server id 1 end_log_pos 12988 CRC32 0xbe5015be Query thread_id=65 exec_time=0 error_code=0】
-
记入时间，220714 14:11:35
-
Mysql 服务 ID，server id 1
-
结束位置，end_log_pos 12988
-
校验内容，CRC32 0xbe5015be
-
事件类型，Query
-
线程 ID，thread_id=65
-
执行时间，exec_time=0
-
错误编码，error_code=0
-
-
下面几行，到下一个【# at xxx】结束，是执行的 SQL 语句
# mysqlbinlog mysql-bin.000087 --start-position=12542 --stop-position=13019
.
.
.
# at 12542
#220714 14:11:35 server id 1 end_log_pos 12666 CRC32 0xca59c21a Query thread_id=65 exec_time=0 error_code=0
SET TIMESTAMP=1657775495/*!*/;
SET @@session.pseudo_thread_id=65/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113664/*!*/;
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/*!*/;
/*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 12666
#220714 14:11:35 server id 1 end_log_pos 12988 CRC32 0xbe5015be Query thread_id=65 exec_time=0 error_code=0
use `[数据库名]`/*!*/;
SET TIMESTAMP=1657775495/*!*/;
INSERT INTO xxxxxx (xxxxxxxxxxxx) VALUES (xxxxxxxxxxxx)
/*!*/;
# at 12988
#220714 14:11:35 server id 1 end_log_pos 13019 CRC32 0x81af69ed Xid = 541
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*/;
binlog 恢复数据
把查到的 binlog 的内容给到 mysql 去执行。（-v 表示执行 sql 语句）
# mysqlbinlog mysql-bin.000087 --databse=[database name] --start-position=12542 --stop-position=13019 | mysql -uroot -pxxxx -v [database name]
看懂 binlog 内容，可以更方便地还原数据等操作。
原创文章，作者：端木书台，如若转载，请注明出处：https://blog.ytso.com/275857.html