第7章 MySQL日志管理

二进制日志原理与应用

1. binlog基础

什么是binlog日志

简单来讲，binlog日志是记录你执行的SQL语句，但是注意，记录的都是涉及到创建或修改的语句：

创建：建库、建表、建字段
修改：修改数据、插入数据、删除数据

记录修改类操作（逻辑日志，类似于SQL记录）：

DML：insert、update、delete
DDL：create、drop、alter、truncate
DCL：grant、revoke

为什么需要binlog日志

主要用于恢复全备之后的增量数据。因为不能实时的备份，所以一般一天一备，但是如果白天发生了误删除，那么可以从binlog日志里恢复。主从复制传输数据就是从binlog里提取的。

binlog三种模式

Statement-Based Logging (SBL):
- 作用: 在这种模式下，binlog记录的是执行的SQL语句，而不是语句的实际结果。
- 优点: 可以减少binlog的大小，因为只记录了SQL语句。
- 缺点: 在某些情况下可能导致数据不一致，尤其是在涉及到函数如NOW()或RAND()这类非确定性函数的时候。
Row-Based Logging (RBL):
- 作用: 在这种模式下，binlog记录的是语句执行后的实际结果，即哪些行被修改了。
- 优点: 可以确保数据的一致性，适用于复制和恢复场景。
- 缺点: 可能会导致binlog的大小显著增加，尤其是在处理大量数据修改的时候。
Mixed Logging (MIXED):
- 作用: 这种模式结合了SBL和RBL的特点。默认情况下使用SBL，但在某些情况下，如当涉及到非确定性函数时，会自动切换到RBL。
- 优点: 平衡了SBL和RBL的优缺点，提供了更灵活的日志记录方式。
- 缺点: 操作更为复杂，可能需要更多的管理和监控。

举例：Statement-Based Logging可能会导致的问题

INSERT INTO t100w (num, k1, k2, dt)
VALUES (401401, 'av', 'avze', NOW());

Statement--数据不对了
INSERT INTO t100w (num, k1, k2, dt)
VALUES (401401, 'av', 'avze', NOW());

Row--数据正确
INSERT INTO t100w (num, k1, k2, dt)
VALUES (401401, 'av', 'avze', 2024-01-09 09:39:21);

在这个例子中，NOW()函数用于获取当前的日期和时间。当这条语句在主服务器上执行时，它会插入一条带有特定时间戳的记录。这条语句随后被复制到从服务器的二进制日志中。

问题出现在从服务器上执行这条语句时。如果从服务器处理这条日志记录的时间稍微晚一些，那么NOW()函数会生成一个与主服务器上不同的时间戳。结果是，尽管订单号（由于自动递增）在主从服务器上保持一致，但订单日期在两个服务器上却是不同的。这就导致了数据不一致。

这种类型的问题是SBL模式的一个主要缺点，尤其是在涉及到使用非确定性函数（比如NOW(), RAND(), UUID()等）的场景中。为了避免这类问题，可以选择使用Row-Based Logging（RBL）模式，它记录的是数据变化的实际结果，而不是导致这些变化的SQL语句。

binlog配置

配置参数：

vim /etc/my.cnf
[mysqld]
#新增加参数
server_id=51
log_bin=/data/mysql_3306/logs/mysql-bin

配置完记得要重启服务：

systemctl restart mysqld

查看是否已经生成binlog文件：

[root@db-51 ~]# ll /data/mysql_3306/logs/mysql-bin.*
-rw-r----- 1 mysql mysql 157 Jan  8 21:19 /data/mysql_3306/logs/mysql-bin.000001
-rw-r----- 1 mysql mysql  34 Jan  8 21:19 /data/mysql_3306/logs/mysql-bin.index

查看当前生效的binlog模式：

SHOW VARIABLES LIKE 'binlog_format';

2. binlog日志管理

binlog查询命令

查看当前位于哪个binlog：

show binary logs;
show master status;

查看当前的事件：

show binlog events in 'mysql-bin.000001';

刷新日志：

flush logs;

查看binlog日志详细内容：

mysqlbinlog /data/mysql_3306/logs/mysql-bin.000001|less

binlog日志内容解析

默认insert操作日志是看不到具体的内容，因为使用了base64编码：

mysqlbinlog /data/mysql_3306/logs/mysql-bin.000001

如果想查看具体的操作语句，可以使用base64解码后查看：

mysqlbinlog --base64-output=decode-rows -vv /data/mysql_3306/logs/mysql-bin.000001

日志滚动

命令触发

mysql> flush logs;
shell# mysqladmin flush-logs
shell# mysql -e "flush logs"
shell# mysqldump -F

自动触发

mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
|        1073741824 |
+-------------------+

注意：重启数据库会自动触发刷新日志

默认大小

默认1个G大小，超过了自动刷新：

select @@max_binlog_size;
set max_binlog_size=字节;

日志删除方式

自动清理

注意：默认不自动清理，直到空间写满

最少设置多少天合适？参考全备时间周期。例如：全备周期是7天。可以保留8天。一般生产中保留两轮备份周期的日志，15天。

select @@expire_logs_days;
set GLOBAL expire_logs_days=7;

手工清理

#清理指定binlog之前的所有binlog文件，但是他自己会保留
PURGE BINARY LOGS TO 'mysql-bin.000014';

#清理指定时间以前的所有的binlog
PURGE BINARY LOGS BEFORE '2024-01-09 16:20:00';

重置binlog

危险操作

会清空所有的binlog并重新从000001开始

reset master;

binlog数据恢复实战

1. 基于位置的恢复

前提说明

重要前提

创建或导入数据库之前就配置并开启了binlog，否则前期修改的数据就记录不到了。

故障演练流程

开启binlog记录
创建数据库
创建表
写入数据
误删除数据或批量修改
分析binlog日志
截取binlog数据
通过binlog恢复
检查数据是否已经恢复

故障操作流程

开启binlog记录

[root@db-51 ~]# cat /etc/my.cnf
[mysqld]
port=3306
user=mysql
basedir=/opt/mysql
datadir=/data/mysql_3306

#binlog参数
server_id=51
log_bin=/data/mysql_3306/logs/mysql-bin

创建数据库

create database luffy;

创建表

use luffy;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(10) NOT NULL COMMENT 'name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

写入数据

insert into user(name) values ('user1');
insert into user(name) values ('user2');
insert into user(name) values ('user3');

误删除数据或批量修改

drop database luffy;

恢复流程

恢复思路

先洗把脸，看看从卫生间跳下去会不会摔断腿，如果超过3层，建议别跳了

binlog还在不在
binlog是否完整
如何找到要恢复的起点和终点
如何截取相应的Binlog
截取后的binlog如何恢复
恢复后如何检查数据是否完整

第一步：查看当前处于哪个binlog

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

第二步：找到binlog起点

mysql>  show binlog events in 'mysql-bin.000001';
...
| mysql-bin.000001 |  219 | Query          |        52 |         316 | create database luffy
...

第三步：找到终止位置点

...
| mysql-bin.000001 | 1038 | Query          |        52 |        1133 | drop database luffy
...

截取binlog

mysqlbinlog --start-position=219 --stop-position=1038 /data/mysql_3306/logs/mysql-bin.000001 > /tmp/backup.sql

查看恢复的binlog

注意查看是否包含drop语句：

[root@db-52 ~]# grep "drop" /tmp/backup.sql

恢复数据

[root@db-52 ~]# mysql -uroot -p123 < /tmp/backup.sql

检查数据是否恢复正常

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| luffy              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from luffy.user;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | user1 |  21 |
|  2 | user2 |  22 |
|  3 | user3 |  23 |
+----+-------+-----+
3 rows in set (0.00 sec)

重要注意事项

恢复binlog时会产生新的binlog记录，正确的做法是：

set sql_log_bin=0;    -- 临时关闭binlog记录
source /tmp/bin.sql;  -- 执行恢复
set sql_log_bin=1;    -- 重新开启binlog记录

2. 基于GTID的恢复

什么是事务

原子性、一致性、隔离性、持久性

手动提交事务演示

手动开启事务并提交：

begin;
insert into user(name) values("user1");
insert into user(name) values("user2");
insert into user(name) values("user3");
commit;

手动开启事务并回滚：

begin;
insert into user(name) values("user1");
insert into user(name) values("user2");
insert into user(name) values("user3");
rollback;

什么是GTID

GTID（Global Transaction Identifier）是全局事务ID，对每个事务进行单独编号，连续不断进行增长。

表示方法：server_uuid:N

手动提交事务和GTID的关系

手动执行事务与是否开启GTID（全局事务标识符）在MySQL中并没有直接的关系。这两个概念是相对独立的，但都与事务处理有关。

手动执行事务：手动执行事务涉及显式地使用BEGIN或START TRANSACTION来开启一个事务，然后执行一系列的数据库操作，最后通过COMMIT来提交事务，或使用ROLLBACK来撤销事务。这个过程是独立于GTID的。无论是否启用GTID，都可以在MySQL中手动执行事务。

GTID（全局事务标识符）： GTID是MySQL在复制过程中用于标识事务的一种机制。每个事务都会被分配一个唯一的标识符，这使得复制过程中的事务管理更加简单和可靠。启用GTID的主要优势在于它提高了主从复制和故障转移的易用性和一致性。它确保了即使在复杂的复制拓扑中，每个事务也都是唯一且一致的。

因此，手动执行事务是一种独立于复制机制的数据库操作方法，而GTID主要用于复制环境中事务的追踪和同步。即使在启用GTID的系统中，手动执行事务的方式不会改变，也不会受到GTID设置的影响。相反，GTID的存在可以帮助保证在复制过程中事务的一致性和完整性。

GTID配置

查看参数：

show variables like '%GTID%';

设置参数：

vim /etc/my.cnf
gtid_mode=ON                  #开关
enforce_gtid_consistency=ON   #强制GTID一致性
log_slave_updates=ON          #强制从库更新binlog

建议

5.7版本以后，都开启GTID。最好是搭建环境就开启。

模拟环境

create database gtdb charset utf8mb4;
show master status ;
use gtdb;
create table t1(id int);
show master status ;
begin;
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
commit;
show master status ;

通过GTID方式截取日志

以下截取方式会出现问题，为什么？

mysqlbinlog --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-3' /data/3306/logs/mysql-bin.000016 >/tmp/gt.sql

答案是gtid有"幂等性"检查。GTID的生成，通过Set gtid_next命令实现的。例如： SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:1'执行Set命令时，自动检查当前系统是否包含这个GTID信息，如果有就跳过。

正确的截取方式：

mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-3' /data/3306/logs/mysql-bin.000016 >/tmp/gt1.sql

拓展使用

跳过指定的GTID：

mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-10' --exclude-gtids='202628e9-9265-11ea-b4a0-000c29248f69:5'

跨文件截取：bin001 bin002 bin003

mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-10'  bin001 bin002  bin003

GTID环境下模拟误删除故障恢复实例

数据准备

create database gtdb charset utf8mb4;
show master status ;
use gtdb;
create table t1(id int);
show master status ;
begin;
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
commit;
show master status ;
drop database gtdb;
show master status ;

截取binlog日志

mysqlbinlog --skip-gtids --include-gtids='xxx' /data/3306/logs/mysql-bin.000016 > /tmp/gt1.sql

3. 复杂场景恢复案例

场景介绍

刚才的简单场景只是在一个binlog之间截取恢复数据，实际工作中可能会更复杂，以下是几个场景：

只恢复相关的库表操作

因为binlog是全局记录的，所以所有的库操作都会被记录，但是我们误操作的可能只是其中一个库，那么如何从一堆的Binlog中只提取我们需要恢复的库就成为了关键。

跨多个binlog

但是如果操作的数据时间跨度比较大，涉及到了跨多个binlog记录，那么恢复的时候也得从多个Binlog里恢复数据。

从binlog文件中只恢复指定的库

构造实验数据：

创建库A
创建库B
创建A库的表
创建B库的表
A库的表插入语句
B库的表插入语句
删除A库

理想的恢复情况：只导出与A库相关的日志

创建A库
创建A库的表
A库的表插入语句
删除A库

从binlog中截取指定的库：

mysqlbinlog -d luffy mysql-bin.000001 > /tmp/bin.sql

从多个binlog中恢复数据

操作流程：

flush logs;

#mysql-bin.000002
show master status ;
create database tongdian charset=utf8mb4;
use tongdian
create table t1 (id int);
flush logs;

#mysql-bin.000003
show master status ;
insert into t1 values(1),(2),(3);
commit;
flush logs;

#mysql-bin.000004
show master status ;
create table t2(id int);
insert into t2 values(1),(2),(3);
commit;
flush logs;

#mysql-bin.000005
show master status ;
insert into t2 values(11),(22),(33);
commit;
drop database tongdian;

查看binlog：

[root@db-52 ~]# ll /data/mysql_3306/mysql-*
-rw-r----- 1 mysql mysql 2259 Jan  8 23:09 /data/mysql_3306/logs/mysql-bin.000001
-rw-r----- 1 mysql mysql  559 Jan  8 23:09 /data/mysql_3306/logs/mysql-bin.000002
-rw-r----- 1 mysql mysql  472 Jan  8 23:09 /data/mysql_3306/logs/mysql-bin.000003
-rw-r----- 1 mysql mysql  642 Jan  8 23:09 /data/mysql_3306/logs/mysql-bin.000004
-rw-r----- 1 mysql mysql  594 Jan  8 23:09 /data/mysql_3306/logs/mysql-bin.000005
-rw-r----- 1 mysql mysql  170 Jan  8 23:09 /data/mysql_3306/logs/mysql-bin.index

恢复方法：

方法1：分段截取

--start-position    --stop-position

方法2：按时间戳截取

第一步：找到建库的时间点

show binlog events in 'mysql-bin.000002';

| mysql-bin.000002 | 219 | Query          |        52 |         341 | create database tongdian charset=utf8mb4 |

第二步：通过位置点过滤出时间戳

[root@db-52 ~]# mysqlbinlog /data/mysql_3306/logs/mysql-bin.000002 |grep -A 1 '^\#\ at\ 219'

# at 219

#240108 23:09:39 server id 52  end_log_pos 341 CRC32 0x2f596607 	Query	thread_id=8	exec_time=0	error_code=0

第三步：找出删库中止点

mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000005 |   4 | Format_desc    |        52 |         123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids |        52 |         154 |                                       |
| mysql-bin.000005 | 154 | Anonymous_Gtid |        52 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000005 | 219 | Query          |        52 |         295 | BEGIN                                 |
| mysql-bin.000005 | 295 | Table_map      |        52 |         344 | table_id: 112 (tongdian.t2)           |
| mysql-bin.000005 | 344 | Write_rows     |        52 |         394 | table_id: 112 flags: STMT_END_F       |
| mysql-bin.000005 | 394 | Xid            |        52 |         425 | COMMIT /* xid=87 */                   |
| mysql-bin.000005 | 425 | Anonymous_Gtid |        52 |         490 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000005 | 490 | Query          |        52 |         594 | drop database tongdian                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

第四步：根据位置点找出时间戳

[root@db-52 ~]# mysqlbinlog /data/mysql_3306/logs/mysql-bin.000005 |grep -A 1 '^\#\ at\ 490'

# at 490

#240108 23:09:40 server id 52  end_log_pos 594 CRC32 0x4a74e87a   Query thread_id=8 exec_time=0 error_code=0

第五步：根据时间戳截取日志范围

mysqlbinlog  --start-datetime="2024-01-08 23:09:39"  --stop-datetime="2024-01-08 23:09:40" mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 >/tmp/data.sql

疑问点

恢复的时候，binlog会不会记录？

重要注意事项

答案是：会

正确的步骤：

恢复前：临时关闭binlog记录
恢复后：再开启

临时关闭方法：

set sql_log_bin=0;
source /tmp/bin.sql;
set sql_log_bin=1;

如果同一个库下删了一个表？

全备+binlog → 测试服务器 → 导出删除的表 → 导入到生产服务器

binlog中100w个事件，怎么快速找到drop？

通过三剑客命令grep过滤关键词drop database

假如删除的库，建库是在2年前操作的，binlog已经清理了，这种情况怎么办？

需要从全备开始恢复，再结合现有binlog进行增量恢复。

更新: 2024-07-19 08:46:57

二进制日志原理与应用​

1. binlog基础​

什么是binlog日志​

为什么需要binlog日志​

binlog三种模式​

binlog配置​

2. binlog日志管理​

binlog查询命令​

binlog日志内容解析​

日志滚动​

日志删除方式​

binlog数据恢复实战​

1. 基于位置的恢复​

前提说明​

故障演练流程​

故障操作流程​

恢复流程​

2. 基于GTID的恢复​

什么是事务​

手动提交事务演示​

什么是GTID​

手动提交事务和GTID的关系​

GTID配置​

模拟环境​

通过GTID方式截取日志​

拓展使用​

GTID环境下模拟误删除故障恢复实例​

3. 复杂场景恢复案例​

场景介绍​

从binlog文件中只恢复指定的库​

从多个binlog中恢复数据​

疑问点​