MySQL是如何保证主从一致的

又欠

已于 2022-11-02 10:10:54 修改

阅读量1.2w

点赞数 8

分类专栏： MySQL 文章标签： mysql sql 数据库 1024程序员节 java

于 2022-11-02 10:02:15 首次发布

本文链接：https://blog.youkuaiyun.com/qq_48157004/article/details/127624329

版权

MySQL 专栏收录该内容

23 篇文章

订阅专栏

一：什么是binlog

Binary log(二进制日志)，简称Binlog。 Binlog是记录所以数据表结构变更以及表数据修改的二进制日志，不会记录select和show这类操作。Binlog是以事件形式记录，还包括语句所执行的消耗时间。Binlog是MySql Server自己的日志，但是Redo Log是基于InnoDB引擎所特有的日志。它是一个逻辑日志。
开启Binlog日志的最重要使用场景：

主从复制

在主库开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。

数据恢复

通过mysql binlog工具来恢复数据。

知道了binlog的作用，那你知道它是怎样进行主从复制？怎样保证主从一致？怎样进行数据恢复？接下来就从bin log的三种模式去进行详解。

至于MySQL怎么保证数据不丢失，大家可以点击MySQL是怎么保证数据不丢的进行详解。

二：第一种格式statement格式（每一条会修改数据的 SQL 都会记录在 binlog 中。）

1. 查看当前是MySQL中bin log是什么格式

show variables like ‘%binlog_format%’;

如果不是starement可以通过命令进行设置 set global binlog_format=‘STATEMENT’

2.执行一条删除语句，看看statement是怎么进行记录的
在这里插入图片描述

执行SQL 语句

delete from ts where age>=1 and time<=‘2022-11-10’ limit 1;

查看日志
在这里插入图片描述

show binary logs；查看所有日志
show binlog events in ‘mysql-bin.000005’; 查看特定日志

| mysql-bin.000005 | 3302 | Query          |         1 |        3389 | BEGIN                                                                                                                                                                                                 |
| mysql-bin.000005 | 3389 | Query          |         1 |        3533 | use `test`; delete   from ts   where age>=1 and time<='2022-11-10' limit 1                                                                                                                            |
| mysql-bin.000005 | 3533 | Xid            |         1 |        3564 | COMMIT /* xid=216 */

第一行：BEGIN 代表开启了一个事务
第二行：use test; delete from ts where age>=1 and time<=‘2022-11-10’ limit 1 记录的是执行SQL语句
第三行: 则是事务提交commit

注意事项：这里的XID是用来联系bin log和redo log的。比如redo log里面有一个事务是prepare状态，但是不知道是不是commit状态，那就可以用XID去bin log里面查询该事务到底有没有提交。有提交则是commit状态，若没有提交则回滚该事务。

3.存在哪些缺点？
如果执行 delete from ts where age>=3 and time<=‘2022-11-10’ limit 1这条语句，会存在哪些问题？

如果主库执行的是索引 age ，备库执行的索引是 time 这时候就会出主备现数据不一致问题。主库会删除id是4的这一行，备库会删除id=5的这一行

4.为什么同一个SQL语句，会选用不同的索引？
至于为什么大家可以参考这篇文章，MySQL为什么会选错索引

三：第二种格式row格式（不记录 SQL 语句上下文信息，仅保存哪条记录被修改）

1. 执行一条SQL语句
同样还是执行该SQL语句

delete from ts where age>=1 and time<=‘2022-11-10’ limit 1;

| mysql-bin.000005 | 5486 | Query          |         1 |        5566 | BEGIN                                                                                                                                                                                                 |
| mysql-bin.000005 | 5566 | Table_map      |         1 |        5614 | table_id: 115 (test.ts)                                                                                                                                                                               |
| mysql-bin.000005 | 5614 | Delete_rows    |         1 |        5662 | table_id: 115 flags: STMT_END_F                                                                                                                                                                       |
| mysql-bin.000005 | 5662 | Xid            |         1 |        5693 | COMMIT /* xid=500 */

第一行：BEGIN 代表开启了一个事务
第二行：Table_map | 1 | 5614 | table_id: 115 (test.ts) 代表执行的是Table_map 中库 test 表ts
第三行： Delete_rows | 1 | 5662 | table_id: 115 flags: STMT_END_F 执行了删除操作
第四行: 则是事务提交commit

2.row格式没有记录数据详情？怎么恢复数据？

执行命令查看row 格式 binlog 示例的详细信息

www/server/mysql/bin/mysqlbinlog -vv /www/server/data/mysql-bin.000005

在这里插入图片描述
图中红色是特定的版本号，黄色是执行的特定SQL语句。 @1=1 代表数据库字段id=1 @2=1 代表数据库字段age=1 @3=1668009600则代表time的时间搓。

所以即使我执行的是 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成insert，把被错删的数据插入回去就可以恢复了。

如果你是执行错了 insert 语句呢？那就更直接了。row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。

3.存在哪些缺点
当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除对应的行，不会有主备删除不同行的问题。

如果你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

三：第三种格式Mixed格式（Statement 和 Row 的混合体）

1. 执行一条SQL语句
同样还是执行该SQL语句

delete from ts where age=4;

| mysql-bin.000005 | 19097 | Anonymous_Gtid |         1 |       19162 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                  |
| mysql-bin.000005 | 19162 | Query          |         1 |       19241 | BEGIN                                                                                                                                                                                                 |
| mysql-bin.000005 | 19241 | Query          |         1 |       19343 | use `test`; delete  from ts  where age=4                                                                                                                                                              |
| mysql-bin.000005 | 19343 | Xid            |         1 |       19374 | COMMIT /* xid=1605 */

你可以看到他实际使用的是statemet，记录的是SQL语句。

2.查看详细信息
执行命令查看mixed 格式 binlog 示例的详细信息

www/server/mysql/bin/mysqlbinlog -vv /www/server/data/mysql-bin.000005

在这里插入图片描述
和之前格式不一样，它多了一个SET TIMESTAMP=1667313708是什么意思？

SET TIMESTAMP 命令约定了接下来的 now() 函数的返回时间。因此，不论这个 binlog 是 1 分钟之后被备库执行，还是 3 天后用来恢复这个库的备份，这个 insert 语句插入的行，值都是固定的。也就是说，通过这条 SET TIMESTAMP 命令，MySQL 就确保了主备数据的一致性。

3.Mixed格式什么时候用statement格式？什么时候用row格式？到底哪个格式最好？

1.因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。

2.但 row 格式的缺点是，很占空间

3.所以MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

但是我还是个人建议设置成row格式，ow格式有助于误操作后的数据恢复。

四：问题回答

1.MySQL是怎样进行主从复制的？

![在这里插入图片描述](https://img-blog.csdnimg.cn/4c1247a9df6b442ba4fd7d68d6852467.png

1.在备库 salve 上通过 change master 命令，设置主库 master 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
2.在备库 salve 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
3.主库 master 校验完用户名、密码后，开始按照备库slave 传过来的位置，从本地读取 binlog，发给 salve 。
4.备库 salve 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。sql_thread 读取中转日志，解析出日志里的命令，并执行。

2.MySQL是怎样保证主从一致？
使用binlog 的三种格式来保证主从一致：

Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

3.怎样进行数据恢复？

查看日志执行情况

www/server/mysql/bin/mysqlbinlog --no-defaults www/server/data/mysql-bin.000005

BEGIN
/*!*/;
# at 21964      在这里我新增了一条数据
#221102  9:30:04 server id 1  end_log_pos 22012 CRC32 0x1fd34036        Table_map: `test`.`ts` mapped to number 115
# at 22012
#221102  9:30:04 server id 1  end_log_pos 22060 CRC32 0xda1f06dd        Write_rows: table id 115 flags: STMT_END_F

BINLOG '
HMhhYxMBAAAAMAAAAPxVAAAAAHMAAAAAAAEABHRlc3QAAnRzAAMDAxEBAAI2QNMf
HMhhYx4BAAAAMAAAACxWAAAAAHMAAAAAAAEAAgAD//gDAAAAAwAAAGNtpZjdBh/a
'/*!*/;
# at 22060
#221102  9:30:04 server id 1  end_log_pos 22091 CRC32 0xf86624ae        Xid = 2598
COMMIT/*!*/;
# at 22091
#221102  9:31:01 server id 1  end_log_pos 22156 CRC32 0x9343948a        Anonymous_GTID  last_committed=73      sequence_number=74      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 22156
#221102  9:31:01 server id 1  end_log_pos 22228 CRC32 0x18531a7f        Query   thread_id=606   exec_time=0    error_code=0
SET TIMESTAMP=1667352661/*!*/;
BEGIN
/*!*/;
# at 22228    在这里我删除了一条数据
#221102  9:31:01 server id 1  end_log_pos 22276 CRC32 0xc24fdaa4        Table_map: `test`.`ts` mapped to number 115
# at 22276
#221102  9:31:01 server id 1  end_log_pos 22324 CRC32 0xbcd40edc        Delete_rows: table id 115 flags: STMT_END_F

BINLOG '
VchhYxMBAAAAMAAAAARXAAAAAHMAAAAAAAEABHRlc3QAAnRzAAMDAxEBAAKk2k/C
VchhYyABAAAAMAAAADRXAAAAAHMAAAAAAAEAAgAD//gDAAAAAwAAAGNtpZjcDtS8
'/*!*/;
# at 22324
#221102  9:31:01 server id 1  end_log_pos 22355 CRC32 0xcea7881a        Xid = 2615
COMMIT/*!*/;
mysqlbinlog: File ' ' not found (Errcode: 2 - No such file or directory)
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*/;

2.执行命令恢复数据
start-position表示恢复起始点，stop-position表示恢复结束点，注意：起始和结束点要选择的正确，要定在插入前，和删除前，要不然是恢复不了数据的。

www/server/mysql/bin/mysqlbinlog --no-defaults  --start-position=21964 --stop-position=22228  /www/server/data/mysql-bin.000005   | mysql -u root -p

除此之外，我们还可以指定开始和结束时间，来恢复数据

www/server/mysql/bin/mysqlbinlog  --no-defaults  --start-datetime="2022-11-02 9:30:04" --stop-datetime="2022-11-02 9:31:01" /www/server/data/mysql-bin.000005   | mysql -u root -p

手动恢复数据
执行命令

www/server/mysql/bin/mysqlbinlog -vv /www/server/data/mysql-bin.000005

SET TIMESTAMP=1667352661/*!*/;
BEGIN
/*!*/;
# at 22228
#221102  9:31:01 server id 1  end_log_pos 22276 CRC32 0xc24fdaa4        Table_map: `test`.`ts` mapped to number 115
# at 22276
#221102  9:31:01 server id 1  end_log_pos 22324 CRC32 0xbcd40edc        Delete_rows: table id 115 flags: STMT_END_F

BINLOG '
VchhYxMBAAAAMAAAAARXAAAAAHMAAAAAAAEABHRlc3QAAnRzAAMDAxEBAAKk2k/C
VchhYyABAAAAMAAAADRXAAAAAHMAAAAAAAEAAgAD//gDAAAAAwAAAGNtpZjcDtS8
'/*!*/;
### DELETE FROM `test`.`ts`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1668130200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 22324
#221102  9:31:01 server id 1  end_log_pos 22355 CRC32 0xcea7881a        Xid = 2615
COMMIT/*!*/;
# at 22355
#221102  9:30:04 server id 1  end_log_pos 22420 CRC32 0x06fefb6b        Anonymous_GTID  last_committed=74      sequence_number=75      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 22420
#221102  9:30:04 server id 1  end_log_pos 22488 CRC32 0xea590592        Query   thread_id=616   exec_time=240  error_code=0