MySql

MySQL 10_MySQL数据库Log详解

一、MySQL支持的日志简介

1.1 MySQL系统支持的日志类型

MySQL中一共七种日志，分别为：错误日志（errorlog）、一般查询日志（general query log)、慢查询日志（slow query log)、二进制日志（binlog）、中继日志（relay log）、重做日志（redo log）、回滚日志（undo log） 和 数据定义语句日志。

不同类型的日志文件，用来存储不同类型的日志，错误日志、通用查询日志、慢查询日志 和 二进制日志（binlog） 是最常用的4种。

Tips: 中继日志 和 数据定义语句日志 是在 MySQL 8.0 新增支持的两种日志.

使用这些日志文件，可以查看MySQL内部发生的事情：

  • 错误日志：记录MySQL服务的启动、运行 或 停止MySQL服务 时出现的问题，方便了解服务器的状态，从而对服务器进行维护。
  • 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
  • 慢查询日志：记录所有执行时间超过 long_query_time 的所有查询操作，方便对查询进行优化。
  • 二进制日志：记录所有更改（insert、update 和 delete 等）数据的语句，可用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
  • 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
  • 数据定义语句日志：记录数据定义语句执行的元数据操作。

上述六类型的日志除二进制日志外，其它日志都是文本文件。默认情况下，所有日志文件创建于MySQL数据目录（data dir）中。

1.2 日志的弊端

  • 日志功能会降低MySQL数据库的性能。
  • 日志会占用大量的磁盘空间。

二、MySQL错误日志(error log)

2.1 MySQL错误日志简介

错误日志 记录了 MySQL服务的启动、运行 或 停止MySQL服务 时出现的问题，通过错误日志可以查看系统的运行状态，便于及时发现故障、修复故障。如果MySQL服务出现异常，错误日志是发现问题、解决故障的首选。

在MySQL数据库中，错误日志 功能是默认开启的，而且错误日志无法被禁止。

2.2 MySQL错误日志配置、查看、删除 及 刷新

1）配置 默认情况下，错误日志存储在MySQL数据的数据文件夹下，名称默认为mysqld.log。

可以在MySQL的配置文件中配置 my.conf 错误日志的 path 和 filename，如下：

1
2

[mysqld] 
log-error=[path/[filename]] # path为日志文件所在的目录路径，filename为日志文件名

默认情况下，错误日志存储在MySQL数据的数据文件夹下，名称默认为mysqld.log

2）查看 通过如下命令可以查看MySQL 错误日志文件路径

1

mysql> SHOW VARIABLES LIKE 'log_err%';

MySQL错误日志文件是文本文件，可以直接打开查看。

3）删除\刷新日志

1
2
3
4
5
6

install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
mysqladmin -uroot -p flush-logs

- MySQL5.5.7以前的版本，flush-logs将错误日志文件重命名为filename.err_old，并创建新的日志文件。
- 从MySQL5.5.7开始，flush-logs只是重新打开日志文件，并不做日志备份和创建的操作。
- 如果日志文件不存在，MySQL启动或者执行flush-logs时会自动创建新的日志文件。重新创建错误日志，大小为0字节。

三、通用查询日志 和 慢查询日志

3.1 通用查询日志（general query log）

通用查询日志(general query log) 用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当数据发生异常时，通过查看通用查询日志，可以还原操作时的具体场景，可以帮助我们准确定位问题。

1、查看当前状态

可查看当前是否开启通用查询日志 及 通用查询日志 文件路径信息

1

mysql> SHOW VARIABLES LIKE '%general%';

2、开启通用查询日志

方式1：永久性方式 —— 修改my.conf或者my.ini配置文件来设置

1
2
3

[mysqld] 
general_log=ON 
general_log_file=[path[filename]] # 日志文件所在目录路径，filename为日志文件名

如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，hostname表示主机名。

方式2：临时性方式 —— MySQL 命令行临时开启/关闭

1
2
3
4

SET GLOBAL general_log=on; # 开启通用查询日志
SET GLOBAL general_log_file=’path/filename’; # 设置日志文件保存位置
SET GLOBAL general_log=off; # 关闭通用查询日志
SHOW VARIABLES LIKE 'general_log%'; # 查看设置后情况

3、停止日志

方式1：永久性方式

1
2

[mysqld] 
general_log=OFF

方式2：临时性方式

1
2

SET GLOBAL general_log=off;
SHOW VARIABLES LIKE 'general_log%';

3.2 慢查询日志（slow query log）

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

慢查询日志相关操作 MySQL的慢查询日志默认没有开启，可以查看一下系统变量 slow_query_log：

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息并重启MySQL服务：

1
2
3
4

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

重新启动MySQL服务器，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

慢查询日志测试： 执行如下SQL语句

1
2
3

select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec

select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时13.35sec

检查慢查询日志 ： 在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL是不会记录的；

Tips: 通过慢查询日志，可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

四、MySQL中的Binlog

4.1 Binlog简介

在 MySQL 中，Binlog 是归档日志，属于 Server 层的日志，是一种二进制形式记录的日志文件，用于记录数据库的 修改操作（数据库、表的定义、修改，表中数据的插入、更新和删除等）。它记录了数据库执行的所有DDL和DML等数据库更新语句，但是不包含没有修改任何数据的语句（如：select语句）。

Tips: Binlog 记录的内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”。

在 MySQL 中，不管使用什么存储引擎，只要表数据更新，就会产生 Binlog（归档日志）。

Binlog 的 2 大应用场景：

  • 数据恢复：如果 MySQL 意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器；
  • 数据复制：master 把它的二进制日志传递给 slaves ，从而实现 master-slove 数据的一致性。可以说MySQL数据库的数据备份、主备、主主、主从等都离不开binlog，都需要依靠binlog来同步数据，保证数据一致性。

Binlog 会记录所有涉及更新数据的逻辑操作，属于逻辑日志，并且是顺序写。

Binlog 相关的配置参数

查看默认配置参数：

1

mysql> show variables like '%log_bin%';

日志参数设置： 方式1：永久性方式

1
2
3
4
5

[mysqld] 
# 启用二进制日志 
log_bin=binlog 
binlog_expire_logs_seconds=600
max_binlog_size=100M

设置带文件夹的bin-log日志存放目录:

1
2
3
4
5

[mysqld] 
log-bin="/var/lib/mysql/binlog/atguigu-bin"

# 注意：新建的文件夹需要使用mysql用户，使用下面的命令即可
chown -R -v mysql:mysql binlog

方式2：临时性方式

1
2
3
4
5
6
7

# global 级别
mysql> set global sql_log_bin=0; 
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can not be used with SET GLOBAL 

# session级别
mysql> SET sql_log_bin=0; 
Query OK, 0 rows affected (0.01 秒)

4.2 查看/刷新/删除二进制Binlog日志

1、查看数据库服务使用的当前 binary log 的位置

1
2
3
4
5
6
7

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000915 | 13181782 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

说明：当前使用的bin log是 binlog.000915 ，位置是 13181782 .

2、查看二进制Binlog日志

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"

# 不显示binlog格式的语句
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"

# 可查看参数帮助 
mysqlbinlog --no-defaults --help 

# 查看最后100行 
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail -100 

# 根据position查找 
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A20 '4939002'

上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：

1

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
  • IN ’log_name’：指定要查询的binlog文件名（不指定就是第一个binlog文件）
  • FROM pos：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
  • LIMIT [offset]：偏移量(不指定就是0)
  • row_count：查询总条数（不指定就是所有行）
1

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

3、flush logs 命令刷新 MySql 日志 flush logs 命令的作用是刷新日志，包括错误日志、慢查询日志和二进制日志等。这将关闭当前的日志文件并打开新的日志文件，常用于日志归档和维护。

flush binary logs 命令单独用于刷新二进制日志，关闭当前的日志文件并开始一个新的。常用于日志管理和归档，特别是作为备份流程的一部分。

执行 flush logs 或 flush binary logs 命令后将关闭 MySql服务 当前使用的 binary log，然后打开一个新的 binary log 文件，binlog文件的序号加 1。

4、删除二进制Binlog日志

PURGE MASTER LOGS：删除指定日志文件

1
2

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’ 
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

RESET MASTER：删除所有二进制文件

1

RESET MASTER;

4.3 使用日志恢复数据

mysqlbinlog恢复数据的语法如下：

1

mysqlbinlog [option] filename|mysql –uuser -ppass;
  • filename：是日志文件名。
  • option：可选项，比较重要的两对option参数是 --start-date、--stop-date 和 --start-position、--stop-position。
    • –start-date 和 –stop-date：可以指定恢复数据库的起始时间点和结束时间点。
    • –start-position和–stop-position：可以指定恢复数据的开始位置和结束位置。

注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如atguigu-bin.000001必须在atguigu-bin.000002之前恢复。

4.4 binlog 写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把binlog cache 写到 binlog文件 中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

binlog 的write和fsync的时机，由参数sync_binlog控制，取值如下：

  • 0：每次提交事务都只写入到文件系统的 page cache，由系统自行判断什么时候执行fsync，机器宕机，page cache里面的 binlog 会丢失。
  • 1：每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。
  • N(N>1)：每次提交事务都写入到文件系统的 page cache，但累积N个事务后才fsync。如果机器宕机，会丢失最近N个事务的binlog日志。 在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

4.5 binlog与redolog对比

  • redo log 是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
  • 而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。
  • 虽然它们都属于持久化的保证，但是侧重点不同:
    • redo log 让InnoDB存储引擎拥有了崩溃恢复能力；
    • binlog保证了MySQL集群架构的数据一致性；

binlog与redolog在两个不同阶段提交 在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而 binlog只有在提交事务时才写入，所以 redo log 与 binlog的写入时机不一样。 为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用 两阶段提交 方案。 使用 两阶段提交 后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。 另一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？ 并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

五、Binlog 的工作模式简介

为了满足不同场景下的MySQL需求，Binlog 推出了三种工作模式，分别是：Statement、Row、Mixed。

5.1 查看/配置 Binlog 的工作模式

1、 查看 Binlog 模式

1
2
3
4
5
6

MySQL> show global variables like "%Binlog_format%";  
+---------------+-----------+  
| Variable_name | Value    |  
+---------------+-----------+  
| Binlog_format | STATEMENT |
+---------------+-----------+

2、配置 Binlog 模式

在my.cnf 的 MySQLd 模块中配置:

1
2
3
4
5

[mysqld]
log-bin = /data/3306/MySQL-bin  
Binlog_format="STATEMENT"
#Binlog_format="ROW"  
#Binlog_format="MIXED"

不重启，使配置在 MySQL 中生效。

1

SET global Binlog_format='STATEMENT';

5.2 Binlog 模式的切换方法

1、修改配置文件

可以通过修改 MySQL 的配置文件来切换 Binlog 模式。

编辑 MySQL 的配置文件（通常是 my.cnf 或 my.ini ），找到 MySQLd 模块中的 Binlog_format 配置参数，并将其设置为所需的模式（Statement、Row 或 Mixed）。

1

Binlog_format = ROW

然后重新启动 MySQL 服务，使新的 Binlog 模式生效。

2、使用 SQL 语句切换

另一种切换 Binlog 模式的方法，是通过执行 SQL 语句来修改 Binlog_format 参数。

1

SET GLOBAL Binlog_format = 'ROW';

这会立即将 Binlog 模式切换为指定的模式。

这种修改在 MySQL 服务重启后会失效，因此需要在重启前进行相应的配置修改。

3、动态切换

MySQL 5.6 及以上版本提供了动态切换 Binlog 模式的功能，可以在不重启 MySQL 服务的情况下进行切换。

1

SET SESSION Binlog_format = 'ROW';

这将在当前会话中将 Binlog 模式切换为指定的模式。

动态切换只对当前会话有效，不会影响其它会话 或 MySQL 服务重启后的配置。

六、Binlog 的 Statement 模式

6.1 Statement 模式的概念

Statement 是基于语句的复制模式。 Statement 模式将数据库中执行的修改操作记录为 SQL 语句，再从数据库上执行相同的 SQL 语句来实现数据同步。

6.2 Statement 模式的优点

Statement 模式的优点是简单明了，易于理解和实现。

6.3 Statement 模式的缺点

Statement 模式在执行涉及非确定性函数、触发器和存储过程等操作时，可能会导致不一致的结果。 1）不支持 RU、RC 隔离级别； 2）binglog 日志文件中，上一个事物的结束点是下一个事物的开始点； 3）DML、DDL 语句都会明文显示； 4）对一些系统函数不能准确复制或者不能复制； 5）主库执行 delete from t1 where c1=xxx limit 1，statement 模式下，从库也会这么执行，可能导致删除的不是同一行数据； 6）主库有 id=1 和 id=10 两行数据，从库有 id=1,2,3,10 这四行数据，主库执行 delete from t1 where id<10 命令，从库删除过多数据。

6.4 Statement 模式的应用场景

Statement 模式适用于大多数情况下的数据库复制需求。 例如： 1）一次更新大量数据，如二十万数据。反之，在复制时，从库可能会追得太慢，然后导致延时； 2）使用 pt-table-checksum 工具时。

示例一：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

## update这个事物的开始是insert这个事物结束的点at1581；
## update结束的点是commit之后的点at1842;
[root@~ logs]# MySQLBinlog --start-position=1581 --stop-position=1842 MySQL-bin.000022;
......
BEGIN
/*!*/;
# at 1729
#170408 14:40:49 server id 330622  end_log_pos 1841 CRC32 0xb443cf1e    Query   thread_id=55    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1491633649/*!*/;
update t10 set c2='bbb' where c1=1
/*!*/;
# at 1841
#170408 14:40:49 server id 330622  end_log_pos 1872 CRC32 0xd06c40f5    Xid = 1756
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*/;

示例二：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# 当查看commit之前的position点时，会看到rollback状态，说明这个截取的事物不完整：
[root@~ logs]# MySQLBinlog --start-position=1581 --stop-position=1841 MySQL-bin.000022;
BEGIN
/*!*/;
# at 1729
#170408 14:40:49 server id 330622  end_log_pos 1841 CRC32 0xb443cf1e    Query   thread_id=55    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1491633649/*!*/;
update t10 set c2='bbb' where c1=1
/*!*/;
ROLLBACK /* added by MySQLBinlog */ /*!*/;
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 的 Row 模式

7.1 Row 模式的概念

MySQL 5.7 默认的日志模式为 Row。 Row 模式是基于行的复制模式，它将数据库中实际修改的行记录写入 Binlog ，从数据库通过解析 Binlog 来逐行执行相应的修改操作。

相对 statement ，Row 模式更加精确、安全，能够确保数据的一致性。

7.2 Row 模式的优点

Row 模式能准确复制修改的行记录，避免了语句复制模式下的不确定性问题。

7.3 Row 模式的缺点

如果 Binlog 文件较大，传输成本就会很高，在某些情况下，可能会导致性能下降。

  • 在表有主键的情况下复制更加快；
  • 系统的特殊函数也能复制；
  • 更少的锁，只有行锁；
  • Binlog 文件比较大，假设单语句更新 20 万行数据，可能要半小时，也有可能把主库跑挂；
  • MySQL 5.6 之前的版本，无法从 binog 看见用户执行的 SQL 语句；
  • DDL 语句明文显示，DML 语句加密显示；
  • DML 经过 base64 加密，需要使用参数 –base64-output=decode-rows –verbose；
  • update 修改的语句可以看到历史旧数据。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

## 开启Binlog_rows_query_log_events参数,会显示执行的SQL语句，这个参数默认关闭，不显示执行的SQL
root@localhost [testdb]>set Binlog_rows_query_log_events=on;

[root@~ logs]# MySQLBinlog -vv --base64-output=decode-rows MySQL-bin.000024
......
create table t10(c1 int,c2 varchar(50))

# insert into t10 values(1,now())
### INSERT INTO `testdb`.`t10`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# insert into t10 values(2,now())

### INSERT INTO `testdb`.`t10`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# at 1033

# insert into t10 values(3,sysdate())

### INSERT INTO `testdb`.`t10`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# insert into t10 values(4,uuid())
### INSERT INTO `testdb`.`t10`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a2b570b8-1c2c-11e7-bc58-000c29c1b8a9' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# update t10 set c2='bbb' where c1=1
### UPDATE `testdb`.`t10`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbb' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */

7.4 Row 模式的应用场景

Row 模式适用于对数据一致性要求较高的场景，特别是涉及一些复杂的数据库操作和业务逻辑。例如，涉及触发器、存储过程和函数等的数据库操作。

使用Row 模式时需注意，Row 模式可能导致 Binlog 文件较大，需要合理设置 Binlog 文件大小和保留时间。

八、Binlog 的 Mixed 模式

8.1 Mixed 模式的概念

Mixed 模式（混合模式）是将语句复制模式和行复制模式结合起来使用。

大多数的修改操作，通常使用 Statement 模式记录对应的 SQL 语句。

一些特殊的操作，涉及非确定性函数和存储过程等，则使用 Row 模式记录修改的行记录。

8.2 Mixed 模式的优缺点

Mixed 模式综合了语句复制模式和行复制模式的优点，能够在大多数情况下高效地记录修改操作，并在需要时使用行复制模式确保数据的准确性。

但 Mixed 模式对一些特殊操作的处理可能会很复杂，需要特别注意下配置和管理。

简单总结下：

1、innodb 引擎，如果隔离级别是 RU、RC，则 Mixed 模式会转成 Row 模式存储。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


set  tx_isolation='read-committed';
set  Binlog_format='mixed';
flush logs;
create table t10(c1 int,c2 varchar(50));
insert into t10 values(1,now());
insert into t10 values(2,now());
insert into t10 values(3,sysdate());
insert into t10 values(4,uuid());
update t10 set c2='bbb' where c1=1;
[root@~ logs]# MySQLBinlog -vv --base64-output=decode-rows MySQL-bin.000028
......
### UPDATE `testdb`.`t10`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 18:34:08' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbb' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
......

2、在以下几种情况下，Mixed 模式会自动将 Binlog 的模式 SBR 转化成 RBR 模式：

  • 当更新一个 NDB 表时；
  • 当函数包含 uuid() 函数时；
  • 2个及以上包含 auto_increment 字段的表被更新时；
  • 视图中必须要求使用 RBR 时。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

set  tx_isolation='repeatable-read';
set  Binlog_format='mixed';
flush logs;
create table t10(c1 int,c2 varchar(50));
insert into t10 values(1,now());
insert into t10 values(2,now());
insert into t10 values(3,sysdate());
insert into t10 values(4,uuid());
update t10 set c2='bbb' where c1=1;
[root@~ logs]# MySQLBinlog MySQL-bin.000029
......
update t10 set c2='bbb' where c1=1
......

8.3 Mixed 模式的应用场景

Mixed 模式适用于大多数情况下的数据库复制需求，尤其适合需要兼顾效率和准确性的场景。

在使用 Mixed 模式时，需要注意对特殊操作进行测试和验证，确保数据的一致性和正确性。

Tips: Binlog 模式选型思路参考

  • 使用 MySQL 特殊功能较少，例如存储过程、触发器、函数等，用 Statement 模式。
  • 使用 MySQL 特殊功能较多，用 Mixed 模式。
  • 使用 MySQL 特殊功能较多，同时希望数据最大化一致，用 Row 模式。

九、中继日志(relay log)

9.1 中继日志(relay log)简介

中继日志(relay log) 只在主从服务器架构的从服务器上存在，它是一个中介临时的日志文件，用于存储从 master节点 同步到 slave节点 的 binlog 日志内容：

从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。

从服务器 I/O 线程将主服务器的二进制日志(binlog)读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

主从同步数据:

  • master 主库将此次更新的事件类型写入到主库的 binlog 文件中
  • master 创建 log dump 线程通知 slave 需要更新数据
  • slave 向 master 节点发送请求，将该 binlog 文件内容读取并存到本地的 relaylog 中
  • slave 开启 sql 线程读取 relaylog 中的内容，将其中的内容在本地重新执行一遍，完成主从数据同步

同步策略：

  • 全同步复制：主库强制同步日志到从库，等全部从库执行完才返回客户端，性能差；
  • 半同步复制：主库收到至少一个从库确认就认为操作成功，从库写入日志成功返回 ack 确认；

Tips: 恢复的典型错误 如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能会导致从服务器名称与之前不同。 而中继日志里是包含 从服务器名 的，在这种情况下，就可能导致恢复从服务器的时候，无法从宕机前的中继日志里读取数据，以为是日志文件损坏了，其实是从服务器名称不对了。 解决的方法也很简单，把从服务器的名称改回之前的名称。