MySQL二进制日志

二进制日志（归档日志，还原数据库使用）

记录导致数据改变或潜在导致数据改变的SQL语句

记录已提交的日志

不依赖于存储引擎类型

功能：通过重放日志文件中的事件来生成数据副本

注意：建议二进制日志与数据文件分开存放

三种日志格式

基于‘语句’记录：statement，记录语句，日志量较少。（MariaDB 10.2.3版以下默认）
基于‘行’记录：row，记录数据，日志量较大，更加安全，建议使用的格式。（MySQL 8.0默认格式）
混合模式：mixed，让系统自行判定该基于哪种方式进行。（MariaDB 10.2.4及以上默认）

查看数据库使用的格式：

mysql:

07:40:41(root@localhost) [(none)] show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mariadb:

MariaDB [(none)]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)

日志文件构成

日志文件：mysql-bin.000001,mysql-bin.000002, 二进制格式

索引文件：mysql-bin.index 文本格式，记录已有的二进制日志文件列表

[root@Rocky8-11 ~]# ll /var/lib/mysql/binlog*
-rw-r----- 1 mysql mysql   180 May 10 20:43 /var/lib/mysql/binlog.000001
-rw-r----- 1 mysql mysql   180 May 10 20:52 /var/lib/mysql/binlog.000002
-rw-r----- 1 mysql mysql   180 May 10 20:52 /var/lib/mysql/binlog.000003
-rw-r----- 1 mysql mysql 11866 May 10 21:37 /var/lib/mysql/binlog.000004
-rw-r----- 1 mysql mysql   157 May 11 17:03 /var/lib/mysql/binlog.000005
-rw-r----- 1 mysql mysql    80 May 11 17:03 /var/lib/mysql/binlog.index

查看二进制日志文件列表及大小

04:53:48(root@localhost) [(none)] show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       180 | No        |
| binlog.000002 |       180 | No        |
| binlog.000003 |       180 | No        |
| binlog.000004 |     11866 | No        |
| binlog.000005 |       201 | No        |
| binlog.000006 |       180 | No        |
| binlog.000007 |       687 | No        |
+---------------+-----------+-----------+
7 rows in set (0.00 sec)

查看当前正在使用中的二进制日志文件

04:52:09(root@localhost) [(none)] show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000007 |      687 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

切换二进制日志文件

1 2	`02:51:29(root@localhost) [(none)] flush logs; Query OK, 0 rows affected (0.03 sec)`

1 2	`[root@Rocky8-11 ~]# mysqladmin flush-binary-log [root@Rocky8-11 ~]# mysqladmin flush-logs`

二进制日志相关变量

sql_log_bin=ON|OFF：是否记录二进制日志，默认on,支持动态修改，系统变量，支持set命令修改（在恢复数据库时临时关闭）

log_bin=/PATH/BIN_LOG_FILE：指定文件位置，默认off,表示不启用二进制日志功能

上述两项都开启才可以启用二进制日志

08:03:21(root@localhost) [(none)] show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |          
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.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)

08:02:02(root@localhost) [(none)] show variables like '%binlog%';
+------------------------------------------------+----------------------+
| Variable_name                                  | Value                |
+------------------------------------------------+----------------------+
| binlog_cache_size                              | 32768                |        ## 每次事务中保存日志记录的缓存大小
| binlog_checksum                                | CRC32                |
| binlog_direct_non_transactional_updates        | OFF                  |
| binlog_encryption                              | OFF                  |
| binlog_error_action                            | ABORT_SERVER         |
| binlog_expire_logs_auto_purge                  | ON                   |
| binlog_expire_logs_seconds                     | 2592000              |
| 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_rotate_encryption_master_key_at_startup | OFF                  |
| binlog_row_event_max_size                      | 8192                 |
| 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_compression                 | OFF                  |
| binlog_transaction_compression_level_zstd      | 3                    |
| 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                    |        ## 设定是否启动即时同步磁盘功能
+------------------------------------------------+----------------------+
30 rows in set (0.02 sec)

二进制日志自动删除的天数，默认0，不自动删除

08:11:16(root@localhost) [(none)] show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |       
+------------------+-------+
1 row in set (0.01 sec)

修改二进制日志路径

[root@Rocky8-11 ~]# mkdir -p /data/binlog/
[root@Rocky8-11 ~]# chown mysql. /data/binlog/
[root@Rocky8-11 ~]# ll /data
total 0
drwxr-xr-x 2 mysql mysql 6 May 17 15:25 binlog

[root@Rocky8-11 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/data/binlog/mysql-bin
## mysql-bin 为二进制日志的文件名头

[root@Rocky8-11 ~]# systemctl restart mysqld
[root@Rocky8-11 ~]# ll /data/binlog
total 8
-rw-r----- 1 mysql mysql 157 May 17 16:12 mysql-bin.000001
-rw-r----- 1 mysql mysql  30 May 17 16:12 mysql-bin.index

查看二进制日志内容

04:16:45(root@localhost) [(none)] show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.30, Binlog ver: 4 |
| mysql-bin.000001 | 126 | Previous_gtids |         1 |         157 |                                   |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)

[root@Rocky8-11 ~]# mysqlbinlog  /data/binlog/mysql-bin.000001 
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230517 16:12:50 server id 1  end_log_pos 126 CRC32 0x6ac5be4e 	Start: binlog v 4, server v 8.0.30 created 230517 16:12:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
goxkZA8BAAAAegAAAH4AAAABAAQAOC4wLjMwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACCjGRkEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAU6+xWo=
'/*!*/;
# at 126
#230517 16:12:50 server id 1  end_log_pos 157 CRC32 0x3a5441b0 	Previous-GTIDs
# [empty]
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*/;

加-v 选项，可查看到行语句

1	`[root@Rocky8-11 ~]# mysqlbinlog /data/binlog/mysql-bin.000001 -v`

部分日志导出，可用于恢复部分数据：

[root@Rocky8-11 ~]# mysqlbinlog  /data/binlog/mysql-bin.000001  --start-position=999  --stop-position=1171  -v
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 157
#230517 16:12:50 server id 1  end_log_pos 126 CRC32 0x6ac5be4e 	Start: binlog v 4, server v 8.0.30 created 230517 16:12:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
goxkZA8BAAAAegAAAH4AAAABAAQAOC4wLjMwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACCjGRkEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAU6+xWo=
'/*!*/;
# at 999
#230517 16:41:31 server id 1  end_log_pos 1066 CRC32 0x157cc5fd 	Table_map: `hellodb`.`teachers` mapped to number 96
# at 1066
#230517 16:41:31 server id 1  end_log_pos 1140 CRC32 0x57369f8a 	Update_rows: table id 96 flags: STMT_END_F

BINLOG '
O5NkZBMBAAAAQwAAACoEAAAAAGAAAAAAAAEAB2hlbGxvZGIACHRlYWNoZXJzAAQCDwH+BCwB9wEI
AQHAAgEh/cV8FQ==
O5NkZB8BAAAASgAAAHQEAAAAAGAAAAAAAAEAAgAE//8ABAAMAExpbiBDaGFveWluZyoBAAQADABM
aW4gQ2hhb3lpbmcoAYqfNlc=
'/*!*/;
### UPDATE `hellodb`.`teachers`
### WHERE
###   @1=4
###   @2='Lin Chaoying'
###   @3=42
###   @4=1
### SET
###   @1=4
###   @2='Lin Chaoying'
###   @3=40
###   @4=1
# at 1140
#230517 16:41:31 server id 1  end_log_pos 1171 CRC32 0x60c0cb3b 	Xid = 33
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*/;


[root@Rocky8-11 ~]# mysqlbinlog  /data/binlog/mysql-bin.000001  --start-position=999  --stop-position=1171  > binlog.log 
[root@Rocky8-11 ~]# mysql < binlog.log

持续性同步备份二进制日志到另一台服务器

1、建立同步用户、授权

05:03:10(root@localhost) [(none)] create user backbinlog@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)

05:04:06(root@localhost) [(none)] grant all on *.* to backbinlog@'10.0.0.%';
Query OK, 0 rows affected (0.02 sec)

05:04:25(root@localhost) [(none)] show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      2375 | No        |
+------------------+-----------+-----------+
1 row in set (0.00 sec)

2、同步（备份服务器）

[root@Rocky8-13 ~]# mysqlbinlog -R --host=10.0.0.11 --user=backbinlog --password=123456 --raw --stop-never mysql-bin.000001
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.

删除二进制日志（可删除时间比较久的无用日志）

从 mysql-bin.000002 开始保留，mysql-bin.000002 以前的删除：

05:18:41(root@localhost) [(none)] purge master logs to 'mysql-bin.000002';
Query OK, 0 rows affected (0.00 sec)

[root@Rocky8-11 ~]# ll /data/binlog/
total 8
-rw-r----- 1 mysql mysql 495 May 17 17:17 mysql-bin.000002
-rw-r----- 1 mysql mysql  30 May 17 17:21 mysql-bin.index

清空所有日志（重新生成第000001号日志文件）

05:21:34(root@localhost) [(none)] reset master;
Query OK, 0 rows affected (0.04 sec)

[root@Rocky8-11 ~]# ll /data/binlog/
total 8
-rw-r----- 1 mysql mysql 157 May 17 17:24 mysql-bin.000001
-rw-r----- 1 mysql mysql  30 May 17 17:24 mysql-bin.index

mariadb 10.3 开启二进制日志

创建存放二进制的目录，更改权限

[root@Rocky8-12 /]# mkdir -p /data/binlog
[root@Rocky8-12 /]# chown mysql. /data/binlog/
total 0
drwxr-xr-x 2 mysql mysql 53 May 23 14:12 binlog

更改配置文件

1
2
3

[root@Rocky8-12 ~]# vim /etc/my.cnf
[mysqld]
log_bin = /data/binlog/mysql-bin

重新启动mariadb服务

[root@Rocky8-12 ~]# systemctl restart mariadb.service
[root@Rocky8-12 ~]# ll /data/binlog/
total 12
-rw-rw---- 1 mysql mysql 351 May 23 14:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 328 May 23 14:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql  60 May 23 14:17 mysql-bin.index

查询binlog状态(sql_log_bin、log_bin)

MariaDB [(none)]> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin                         | ON                           |
| log_bin_basename                | /data/binlog/mysql-bin       |
| log_bin_compress                | OFF                          |
| log_bin_compress_min_len        | 256                          |
| log_bin_index                   | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                          |
| sql_log_bin                     | ON                           |
+---------------------------------+------------------------------+
7 rows in set (0.002 sec)

MySQL

#MariaDB #MySQL

MySQL二进制日志

https://www.xcjyc.top/2023/05/11/MySQL二进制日志/

作者

XCJYC

发布于

2023年5月11日

许可协议

MySQL备份与恢复上一篇

MySQL基础知识下一篇