MySQL日志简介
| 日志类型 | 介绍 |
|---|---|
| 错误日志 | 记录在启动，运行或停止mysqld时遇到的问题 |
| 查询日志 | 记录建立的客户端连接和执行的语句 |
| 二进制日志 | 记录更改数据的语句 |
| 中继日志 | 从复制主服务器接收的数据更改 |
| 慢查询日志 | 记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询 |
| 元数据日志 | 元数据操作由DDL语句执行 |
本文主要介绍二进制日志binlog
binlog简介
binlog它记录了所有的DDL和DML语句，以事件形式记录，包含语句所执行的时间以及消耗的时间，binlog的主要目的是复制和恢复。
开启binlog
请参考MySQL数据库增量备份这篇文章。
查看是否binlog变量
mysql> 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.01 sec)
log_bin=ON表示开启binlog，log_bin_index是binlog索引文件
binlog参数
| 参数 | 用途 |
|---|---|
| log-bin | binlog的名字，生成的binlog名字为binlog.000002 |
| binlog_format | 规定binlog的格式，binlog有三种格式statement，row以及mixed。在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。 |
| expire_logs_days | 过期时间，大家根据业务实际情况设置 |
| sync_binlog | 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。 |
| max_binlog_cache_size | 表示的是binlog 能够使用的最大cache内存大小 |
| binlog_cache_size | binlog日志的缓存，默认32768 |
sync_binlog大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把sync_binlog 配置成 1。为了追求高并发，提升性能，可以设置为 100-200 或直接用 0。而和innodb_flush_log_at_trx_commit一样，对于支付服务这样的应用，还是比较推荐sync_binlog = 1。 参数innodb_flush_log_at_trx_commit介绍
-
0：事务提交时，不将重做日志缓冲写入磁盘，而是依靠InnoDB的主线程每秒执行一次刷新到磁盘，因此如果 MySQL 发生宕机，那么就有可能丢失一部分事务。
-
1：事务提交时，会将重做日志缓冲写入磁盘，并且立即刷新fsync()。
-
2：事务提交时，会将重做日志缓冲写入磁盘，但是不会立即进行刷新操作，因此只是写到了操作系统的缓冲区，此时若操作系统发生宕机而没有即使的同步，也可能会丢失一部分数据。
可以看到，只有1才能真正地保证事务的持久性，但是对性能影响很大，0和2能获得更高的性能，但是有丢失数据的风险。
binlog格式
STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
ROW：基于行的复制（row-based replication, RBR）
MIXED：混合模式复制（mixed-based replication, MBR）
binlog_row_image介绍
在MySql5.7 版本以后除了binlog-format参数用来调整binlog日志的格式外，还多了另外一个参数：binlog_row_image。设置binlog-format为ROW后，MySql会记录每一行数据修改的细节日志，在此之上，binlog_row_image则是对ROW的配置进行了更下一步的细分。binlog_row_image可设置的值如下：
- FULL：记录数据变更的所有前镜像和后镜像
- MINIMAL：binlog日志的前镜像只记录唯一识别列(唯一索引列、主键列)，后镜像只记录修改列。
- NOBLOB：binlog记录所有的列，就像full格式一样。但对于BLOB或TEXT格式的列，如果他不是唯一识别列(唯一索引列、主键列)，或者没有修改，那就不记录。
mysqlbinlog命令
-- 查看主库当前正在使用的binlog文件是那个文件
show master status
show master logs where log_name like '%000018%';
-- 查看从库正在使用的binlog文件
show slave status
-- 查看当前Mysql配置
-- 查看当前MySql binLog的format值
show variables like 'binlog_format';
-- 查看当前MySql是否开启binlog
show variables like 'log_bin';
-- 查看log相关的配置
show variables like '%log_%';
# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
# 查看 binlog 内容
show binlog events;
# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'binlog.000011';
# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=30;
# 删除当前的binlog文件
reset master;
# 删除slave的中继日志
reset slave;
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2022-06-21 11:00:00';
# 删除指定日志文件
purge master logs to 'binlog.000011';
# mysqlbinlog 的执行格式
mysqlbinlog [options] log_file ...
# 查看bin-log二进制文件（shell方式）
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/binlog.000011
# 查看bin-log二进制文件（带查询条件）
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/binlog.000011\
--start-datetime="2022-06-11 00:00:00" \
--stop-datetime="2022-06-20 00:00:00" \
--start-position="5000" \
--stop-position="20000"
查看binglog开头的MySql配置
show variables like '%binlog_%';
查看日志
[root@localhost mysql]# mysqlbinlog -v --base64-output=decode-rows binlog.000011
# 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
#220621 9:08:06 server id 1 end_log_pos 126 CRC32 0x255e293a Start: binlog v 4, server v 8.0.29 created 220621 9:08:06 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 126
#220621 9:08:06 server id 1 end_log_pos 157 CRC32 0x8e2edaa8 Previous-GTIDs
# [empty]
# at 157
#220621 9:10:49 server id 1 end_log_pos 236 CRC32 0x5db51524 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1655773849808989 immediate_commit_timestamp=1655773849808989 transaction_length=302
# original_commit_timestamp=1655773849808989 (2022-06-21 09:10:49.808989 CST)
# immediate_commit_timestamp=1655773849808989 (2022-06-21 09:10:49.808989 CST)
/*!80001 SET @@session.original_commit_timestamp=1655773849808989*//*!*/;
/*!80014 SET @@session.original_server_version=80029*//*!*/;
/*!80014 SET @@session.immediate_server_version=80029*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 236
#220621 9:10:49 server id 1 end_log_pos 459 CRC32 0x64fdaf2d Query thread_id=8 exec_time=0 error_code=0 Xid = 34
SET TIMESTAMP=1655773849/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
/* ApplicationName=DBeaver 21.3.5 - Main */ CREATE SCHEMA `f-sell`
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci
/*!*/;
# at 459
#220621 9:11:21 server id 1 end_log_pos 538 CRC32 0x8b8fe87d Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1655773882012715 immediate_commit_timestamp=1655773882012715 transaction_length=1233
# original_commit_timestamp=1655773882012715 (2022-06-21 09:11:22.012715 CST)
# immediate_commit_timestamp=1655773882012715 (2022-06-21 09:11:22.012715 CST)
/*!80001 SET @@session.original_commit_timestamp=1655773882012715*//*!*/;
/*!80014 SET @@session.original_server_version=80029*//*!*/;
/*!80014 SET @@session.immediate_server_version=80029*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 538
#220621 9:11:21 server id 1 end_log_pos 1692 CRC32 0x6378d933 Query thread_id=10 exec_time=1 error_code=0 Xid = 100
use `f-sell`/*!*/;
SET TIMESTAMP=1655773881/*!*/;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;
SET @@session.sql_mode=524288/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 21.3.5 - SQLEditor <Script-5.sql> */ CREATE TABLE `finance_flow` (
`id` bigint NOT NULL DEFAULT '0' COMMENT '主键',
`order_id` bigint NOT NULL DEFAULT '0' COMMENT '订单ID',
`amount` bigint NOT NULL DEFAULT '0' COMMENT '金额',
`in_out` tinyint NOT NULL DEFAULT '0' COMMENT '收入支出',
`order_in_amount` bigint NOT NULL DEFAULT '0' COMMENT '订单实际收入',
`remark` varchar(127) NOT NULL DEFAULT '' COMMENT '备注',
`is_check` tinyint NOT NULL DEFAULT '0' COMMENT '是否对账',
`create_id` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
`create_name` varchar(15) DEFAULT '' COMMENT '创建人',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_id` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
`update_name` varchar(15) DEFAULT '' COMMENT '修改人',
`update_time` datetime NOT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='财务流水'
/*!*/;
- position: 位于文件中的位置，即第一行的（# at 538）,说明该事件记录从文件第538个字节开始
- timestamp: 事件发生的时间戳，即第二行的（#220621 9:11:21）
- server id: 服务器标识（1）
- end_log_pos 表示下一个事件开始的位置（end_log_pos 1692）
- thread_id: 执行该事件的线程id（thread_id=10）
- exec_time: 事件执行的花费时间(1)
- error_code: 错误码，0意味着没有发生错误
- Xid: 事务id
测试恢复数据
CREATE TABLE `sys_dict` (
`id` bigint NOT NULL COMMENT '主键',
`code` varchar(32) NOT NULL DEFAULT '' COMMENT '编号',
`name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
`is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除 （0 未删除 , 1 删除）',
`create_id` bigint DEFAULT '0' COMMENT '创建人id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`create_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '创建人姓名',
`update_id` bigint DEFAULT '0' COMMENT '修改人id',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
`update_name` varchar(32) DEFAULT NULL COMMENT '修改人姓名',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_code` (`code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据字典';
INSERT INTO sys_dict
(id, code, name, is_delete, create_id, create_time, create_name, update_id, update_time, update_name)
VALUES(8550957391874, 'yes_no', '是否', 0, 0, '2022-05-20 10:10:05', NULL, 0, '2022-05-20 10:10:05', NULL);
INSERT INTO sys_dict
(id, code, name, is_delete, create_id, create_time, create_name, update_id, update_time, update_name)
VALUES(8991911358465, 'enable_status', '启用状态', 0, 0, '2022-05-20 12:11:03', NULL, 0, '2022-05-20 12:11:03', NULL);
-- 错误操作删除表
drop table sys_dict ;
- 1 定位日志
# 最新的日志文件
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000011 | 13675 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查询binlog.000011日志的前后5行相关数据
[root@localhost mysql]# mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000011 |grep -C 5 sys_dict
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 11695
#220621 14:02:29 server id 1 end_log_pos 12722 CRC32 0x3d7f75bf Query thread_id=10 exec_time=0 error_code=0 Xid = 378
SET TIMESTAMP=1655791349/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 21.3.5 - SQLEditor <Script-5.sql> */ CREATE TABLE `sys_dict` (
`id` bigint NOT NULL COMMENT '主键',
`code` varchar(32) NOT NULL DEFAULT '' COMMENT '编号',
`name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
`is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除 （0 未删除 , 1 删除）',
`create_id` bigint DEFAULT '0' COMMENT '创建人id',
--
#220621 14:04:04 server id 1 end_log_pos 12886 CRC32 0x44bb5417 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1655791444/*!*/;
BEGIN
/*!*/;
# at 12886
#220621 14:04:04 server id 1 end_log_pos 12967 CRC32 0x0f1dc316 Table_map: `f-sell`.`sys_dict` mapped to number 111
# at 12967
#220621 14:04:04 server id 1 end_log_pos 13054 CRC32 0xf69b972a Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `f-sell`.`sys_dict`
### SET
### @1=8550957391874
### @2='yes_no'
### @3='是否'
### @4=0
--
#220621 14:04:07 server id 1 end_log_pos 13249 CRC32 0x3cb5543c Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1655791447/*!*/;
BEGIN
/*!*/;
# at 13249
#220621 14:04:07 server id 1 end_log_pos 13330 CRC32 0x216d62fa Table_map: `f-sell`.`sys_dict` mapped to number 111
# at 13330
#220621 14:04:07 server id 1 end_log_pos 13430 CRC32 0x619d70da Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `f-sell`.`sys_dict`
### SET
### @1=8991911358465
### @2='enable_status'
### @3='启用状态'
### @4=0
--
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 13538
#220621 14:05:32 server id 1 end_log_pos 13675 CRC32 0xc4bbf94c Query thread_id=10 exec_time=0 error_code=0 Xid = 399
SET TIMESTAMP=1655791532/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
DROP TABLE `sys_dict` /* generated by server */
/*!*/;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
- 确定位置到日志是11695-13538范围，开始恢复
mysqlbinlog --start-position=11695 --stop-position=13538 binlog.000011 | mysql --login-path=dumppath
- 查看是否已恢复
mysql> select * from sys_dict;
+---------------+---------------+--------------+-----------+-----------+---------------------+-------------+-----------+---------------------+-------------+
| id | code | name | is_delete | create_id | create_time | create_name | update_id | update_time | update_name |
+---------------+---------------+--------------+-----------+-----------+---------------------+-------------+-----------+---------------------+-------------+
| 8550957391874 | yes_no | 是否 | 0 | 0 | 2020-05-20 10:10:05 | NULL | 0 | 2020-05-20 10:10:05 | NULL |
| 8991911358465 | enable_status | 启用状态 | 0 | 0 | 2020-05-20 12:11:03 | NULL | 0 | 2020-05-20 12:11:03 | NULL |
+---------------+---------------+--------------+-----------+-----------+---------------------+-------------+-----------+---------------------+-------------+
2 rows in set (0.00 sec)
