Mysql 系列 | binlog 详解

2022/7/22 2:22:47

本文主要是介绍Mysql 系列 | binlog 详解,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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 内容,可以更方便地还原数据等操作。



这篇关于Mysql 系列 | binlog 详解的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程