MySQL主从复制（基于binlog日志方式）

一、什么是主从复制？

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

主从复制的作用
1.做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2.架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3.读写分离，使数据库能支撑更大的并发。

• a.从服务器可以执行查询工作(就是我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压）
• b.从服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全）

总结：
实时灾备，用于故障切换；
读写分离，提供查询服务；
备份，避免影响业务。
主从复制必要的条件：
主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连同主库

二、主从复制原理、存在问题和解决方法

2.1.主从复制原理

原理：实现整个主从复制，需要由slave服务器上的IO进程和Sql进程共同完成；要实现主从复制，首先必须打开Master端的binary log（bin-log）功能，因为整个MySQL 复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己本地（slave端）按照执行日志中所记录的顺序，全部操作一遍。

1. 在主库上把有数据更改的（DDL DML DCL）sql语句都记录到二进制日志（Binary Log）中。
2. 备库的I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。
3. 备库的SQL线程读取中继日志中的事件，将其重放到备库数据库之上。

master 负责写 -----A
slave relay-log -----B
I/o 负责通信读取binlog日志
SQL 负责写数据

步骤一：主库开启binlog日志，开启后主库的更新事件(update、insert、delete)被写到binlog
步骤二：从库发起连接，连接到主库
步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库
步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
步骤五：还会创建一个SQL线程，从relay log里面读取内容，将更新内容写入到slave的db

2.2.主从复制存在的问题以及解决办法

• 问题
  1.主库宕机之后，数据可能会丢失
  2.从库只有一个sql Thread，主库写压力大，复制很可能延时
• 解决方法
  解决数据丢失的问题：使用半同步复制的复制模型来解决
  解决从库复制延时的问题：使用并行复制的方式来解决

2.3.主从复制的同步模型

• MySQL中的主从复制模式包括异步复制、同步复制和半同步复制。
• 异步复制(Asynchronous Replication)
  主库将变更写入二进制日志(Binary Log)后就直接返回成功。不关心从库是否真正应用了变更。
  主库和从库之间有延迟，可能会丢失数据。
  性能好，主库无需等待从库。适合可以承受一定数据丢失的场景。
• 同步复制(Synchronous Replication)
  主库写入二进制日志后，需要等待从库回复已经成功应用了变更后，才返回成功。
  主从间延迟很低，数据不会丢失。
  主库性能会受到一定影响，需要等待从库的反馈。
• 半同步复制(Semisynchronous Replication)
  主库写入二进制日志后，等待至少一个从库回复接收到日志后再返回。
  既保证了一定的数据安全性，也不会严重影响主库性能。
  即使从库不回复，也会在超时后改为异步复制，保证主库不会永远阻塞。

总之，需要根据业务需求，平衡一致性、可用性和性能来选择合适的复制模式。

2.4.拓展—Mysql并行复制

• 并行复制的演进
  MySQL最早的主备复制只有两个线程，IO 线程负责从主库接收 binlog 日志，并保存在本地的 relaylog 中，SQL线程负责解析和重放 relaylog 中的 event。当主库并行写入压力较大时，备库 IO 线程一般不会产生延迟，因为写 relaylog 是顺序写，但是 SQL线程重放的速度经常跟不上主库写入的速度，会造成主备延迟。如果延迟过大，relaylog 一直在备库堆积，还可能把磁盘占满。
  在官方的5.6版本之前，MySQL只支持单线程复制，因此在主库并发高，TPS高时就会出现严重的主备延迟问题。从单线程复制到最新版本的多线程复制，中间的演化经历了好几个版本。
  TPS是"Transactions Per Second"的缩写，表示每秒处理的事务数量。在数据库领域中，TPS是衡量数据库处理能力和性能的重要指标之一。它表示数据库系统每秒能够执行的事务操作的数量，包括读取和写入操作。较高的TPS值通常表示数据库具有更高的并发处理能力，可以处理更多的事务请求。
  并行复制(Parallel Replication)指的是在一主多从的MySQL复制结构下，配置多个SQL线程去并发请求主库的二进制日志事件，实现从库并行重放日志来提高复制效率，缩短复制延迟。

• 并行复制的工作原理是：
  主库将二进制日志打包成文件片段传输给从库
  从库的多个I/O线程并行请求不同日志片段
  从库根据文件名排序后写入relay log
  多个SQL线程并行读取各自relay log位置并执行

• MySQL并行复制是指在MySQL数据库中，可以同时复制多个事务到不同的slave实例上，以提高复制过程的效率。实现并行复制需要满足以下几个条件：
  主服务器（master）上的日志事件必须按照顺序进行复制，不能进行并行复制。
  同一事务中的多个日志事件必须按照顺序进行复制，不能进行并行复制。
  不同事务之间的日志事件可以进行并行复制到不同的slave实例上。

• 在MySQL 5.6版本之后，MySQL引入了并行复制的功能，可以通过设置相关参数来启用并行复制。下面是一个配置mysql并行复制的示例代码：

首先，需要在主服务器和从服务器上都开启并行复制功能：
在主服务器的my.cnf文件中添加以下配置：

server-id = 1
log-bin = mysql-bin
binlog-format = row
slave-parallel-workers = 2 
1
2
3
4

在从服务器的my.cnf文件中添加以下配置：

server-id = 2
relay-log = mysql-relay-bin
binlog-format = row
slave-parallel-workers = 2
1
2
3
4

然后，在从服务器上设置复制主服务器的相关信息：

mysql> CHANGE MASTER TO
  MASTER_HOST='master-server'，
  MASTER_USER='replication'，
  MASTER_PASSWORD='password'，
  MASTER_LOG_FILE='mysql-bin.000001'，
  MASTER_LOG_POS=12345;
1
2
3
4
5
6

通过以上配置，可以使得从服务器上的复制进程可以并行复制来自主服务器的事务日志，提高数据复制的效率。

三、主从复制之基于binlog日志方式

3.1.bin-log日志简介

• Binlog是MySQL数据库中的二进制日志，用于记录数据库中所有修改操作，包括增删改等操作。binlog以二进制格式保存，可以通过解析binlog文件来查看数据库的操作历史记录。binlog日志可以用于数据恢复、数据备份、数据同步等场景。
• 在MySQL数据库中，binlog有三种模式：statement模式、mixed模式和row模式。statement模式记录的是SQL语句，row模式记录的是每一行数据的变化；mixed模式是自动组合 STATEMENT 和 ROW 模式，按照最优方式来记录日志。Binlog日志的开启和关闭可以通过设置MySQL的配置文件实现。
• Binlog的作用非常重要，它可以用来进行数据恢复和备份，也可以用来进行数据同步和复制。在进行数据恢复时，可以使用Binlog来恢复数据到某个时间点或某个操作之前的状态，从而保证数据的完整性。在进行数据备份时，可以将Binlog文件备份到另一台服务器上，以便在主服务器出现问题时，可以快速地将备份服务器恢复到与主服务器相同的状态。
• 除了数据恢复和备份外，Binlog还可以用来进行数据同步和复制。在进行数据同步时，可以将Binlog文件传输到其他服务器上，从而将数据同步到其他服务器中。在进行数据复制时，可以将Binlog文件传输到备份服务器上，从而将备份服务器上的数据与主服务器上的数据保持一致。

3.2.bin-log的使用

3.2.1.开启binlog

首先查看mysql是否开启binlog同步功能

mysql> show variables like 'log_bin';
1

默认是关闭的，此时需要开启，就要编辑mysql的配置文件，正常是在etc目录下
如果没有就先用 which mysqld查看位置
修改my.cnf配置

[root@localhost ~]# vim /etc/my.cnf
//[mysqld]开启binlog
log-bin = mysql-bin
1
2
3

也可以通过SET SQL_LOG_BIN=1命令来启用 binlog，通过SET SQL_LOG_BIN=0命令停用 binlog。
不过启用 binlog 之后须重启MySQL才能生效

3.2.2.常用的binlog命令

是否启用binlog日志show variables like 'log_bin';
查看详细的binlog日志配置信息show global variables like '%log%';
查看binlog的目录show global variables like "%log_bin%";
查看binlog文件日志列表show binary logs;
查看最新一个binlog日志文件名称和Position（操作事件pos结束点）show master status;
刷新log日志，自此刻开始产生一个新编号的binlog日志文件
每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F选项也会刷新binlog日志；flush logs;
查看第一个binlog文件内容show binlog events
查看具体一个binlog文件的内容show binlog events in 'master.000001';
重置(清空)所有binlog日志reset master;
删除slave的中继日志reset slave;
删除指定日期前的日志索引中binlog日志文件purge master logs before '2022-02-22 00：00：00';
删除指定日志文件purge master logs to 'master.000001';

3.2.3.使用binlog

1.使用mysqlbinlog自带查看命令法：
注： binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看，binlog日志在同目录的data下
比如我刚刚建了一个表

mysql> CREATE TABLE IF NOT EXISTS `fungmu_table`(   `id` INT UNSIGNED AUTO_INCREMENT，   `name` VARCHAR(100) NOT NULL，   `age` VARCHAR(40) NOT NULL，   `create_date` DATE，   PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;
1

然后使用以下命令查看二进制日志

[root@localhost ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001
1

2.mysql命令行
直接查看binlog日志的话全文内容较多，不容易分辨查看pos点信息，这时候可以使用mysql命令行

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset，] row_count];
1

IN ‘log_name’ 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset，] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)
例如：
Log_name： mysql-bin.000001
pos起始点：Pos： 11197
事件类型：QueryEvent_type： Query

3.3.bin-log日志类型详解

记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

• STATEMENT：基于SQL语句的复制（statement-based replication， SBR）
• ROW：基于行的复制（row-based replication， RBR）
• MIXED：混合模式复制（mixed-based replication， MBR）

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED

3.3.1.Statement格式类型

• MySOL5.1之前的版本都采用这种方式，顾名思义，日志中记录的都是sql语句(statement），每一条对数据造成修改的SOL 语句都会记录在日志中，通过mysqlbinlog工具、可以清晰地看到每条语句的文本。每一条会修改数据的sql都会记录在binlog中
• 主从复制的时候，从库(slave)会将日志解析为原文本，并在从库重新执行一次。
• 优点是不需要记录每一行的变化，减少了binlog日志量，节约了IO， 提高了性能。
• 缺点是在某些情况下slave 的日志复制会出错。因为Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。

3.3.2.Row格式类型

• 5.1.5版本的MySQL才开始支持 row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
• 它将每一行的变更记录到日志中，而不是SOL语句。比加一个简单的更新 SOL： update emp set name=‘abc’，如果是STATENENT格式，日志中会记录一行SQL文本；
• 但是如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，如果是一个100 万行的大表，则日志中会记录 100 万条记录的变化情况。日志量大大增加。这种格式的优点是会记录每一行数据的变化细节，不会出现某些情况下无法复制的问题。
• 优点是binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
• 缺点是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

3.3.3.Mixed格式类型

• 从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。
• 在 Mixed 模式下，系统会自动判断 该 用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。
• Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。

3.3.4.如何选用bin-log的格式类型

关于这三中格式的binlog，我们在使用的时候到底应该使用哪一种？

• 如果我们的磁盘空间和服务器性能比较OK的情况下，尽量使用Row模式，因为这种模式能够最大程度的保证安全性，虽然产生的日志量很多，但是当你误删数据的时候，你就会感受到binlog给你带来的温暖。
• 当我们对一些不太重要的业务库(例如一些log库)进行数据主从复制的时候，尽量使用statement来执行，因为它的速度快，日志量小，而且不牵扯使用函数，是简单的数据同步。
• 如果有一些场景需要尽量保证性能，但是又没有十分严格的要求时，我们可以设置为Mixed格式，它可以在statement和Row之间进行切换，保证了业务的写入性能。
• 最后一点，在RC和RU隔离界别下，不能使用statement格式的binlog日志。

说明：RC 和 RU 是指 MySQL 的事务隔离级别

• RC：可重复读(Repeatable Read)
• RU：可串行化(Serializable)
• 在这两个隔离级别下，使用 STATEMENT 格式会出现问题，比如导致主从数据不一致。
• 因为 RC 和 RU 下的事务存在各种间隙锁、Next-Key Lock等机制，这会导致同一事务的后续语句看到的是之前语句修改过的数据版本。但这种读已修改数据的语句无法在 STATEMENT 模式下正确复制。
• 所以建议在 RC 和 RU 隔离级别下使用 ROW 格式，以确保完全正确地复制主从数据。

3.3.5.bin-log的常用场景

1、mysql主从复制
2、mysql数据备份和恢复
3、数据同步，比如基于Canal投递MySQL Binlog到kafka、elasticsearch

3.3.6.bin-log和redo-log

binlog 和 redolog，这两者有什么区别呢？

• binlog是MySQL Server层的日志，而redolog是MySQL引擎InnoDB层的日志
• 另外一个不同是两者写入时机不同，redolog是prepare阶段每执行sql语句就写redo了，而binlog是在prepare完commit前写的。

作用范围不同：

• binlog 记录所有修改数据的 SQL 语句，用于主从复制。
• redo log 只记录对 InnoDB 引擎表的修改，用于崩溃恢复。

日志内容不同：

• binlog 包含可重播的 SQL 语句或行更改信息。
• redo log 包含对页面和行的物理修改信息。

日志格式不同：

• binlog 是二进制格式，人工不可读。
• redo log 是明文记录，人工可读。

日志大小不同：

• binlog 会非常大，特别是行格式。
• redo log 的大小有限制，通常较小。

存储位置不同：

• binlog 存储在磁盘上。
• redo log 存储在内存中，偶尔刷新到磁盘。

日志管理不同：

• binlog 需要人工定期清理过期日志。
• redo log 通过循环使用空间，无需人工管理。

3.4.基于binlog的主从复制实战操作

3.4.1.准备环境

1、准备环境两台机器，关闭防火墙和selinux；两台机器环境必须一致。时间也得一致
192.168.221.136 mysql-master
192.168.221.138 mysql-slave
​2、两台机器安装mysql5.7(略)建议使用相同的安装方式
注意：
对主库已有的数据库不会进行自动同步。
主从同步之前，主库上已有数据库备份，需要在从库上手动导入同步。

3.4.2.配置master主服务

在主服务器上，必须启用二进制日志记录并配置唯一的服务器ID（server-id）；修改配置文件后，需要重启mysql。
编辑主服务器的配置文件 my.cnf，添加如下内容

[root@mysql-master ~]# vim /etc/my.cnf    //添加配置
[mysqld]
log-bin=/usr/local/mysql/logs/mysql-bin/mylog
server-id=1
1
2
3
4

创建日志目录并赋予权限

[root@mysql-master ~]# mkdir /usr/local/mysql/logs/mysql-bin/mylog  -p
[root@mysql-master ~]# chown -R mysql:mysql /usr/local/mysql/
1
2

重启服务

[root@mysql-master ~]# systemctl restart mysqld
1

3.4.3.在主服务器上查看binlog以及POS点

mysql> show master status\G
*************************** 1. row ***************************
             File: mylog.000001
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
​
#记录下
File： mylog.000001
Position：154   Position：位置
1
2
3
4
5
6
7
8
9
10
11
12

3.4.4.创建主从同步的用户

mysql> GRANT REPLICATION SLAVE ON *.*  TO  'repl'@'%'  identified by 'JiannLt@123';
mysql> flush privileges;
1
2

3.4.5.从服务配置slave

my.cnf配置文件，注意server-id不能相同

[root@mysql-slave ~]# vim /etc/my.cnf
[mysqld]
server-id=2
​
//修改完配置文件重启mysql服务
[root@mysql-slave ~]# systemctl restart mysqld.service
1
2
3
4
5
6

3.4.6.从库配置

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.221.136',
MASTER_USER='repl',
MASTER_PASSWORD='JiannLt@123',
MASTER_LOG_FILE='mylog.000001',
MASTER_LOG_POS=154;

#参数解释：
MASTER_HOST='master2.example.com',      #主服务器ip
MASTER_USER='replication',              #主服务器用户
MASTER_PASSWORD='password',             #用户密码
MASTER_PORT=3306,                       #端口
MASTER_LOG_FILE='master2-bin.001',      #binlog日志文件名称
MASTER_LOG_POS=4,                       #日志位置

mysql> start slave;
mysql> show slave status\G
#查看状态，验证sql和IO是不是yes。
说明成功：
Slave_IO_Running： Yes
Slave_SQL_Running： Yes
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.4.7.数据同步测试

#主库新建数据库，从库检查同步情况
#主库新建库
mysql> create database testdb;
Query OK， 1 row affected (0.00 sec)
​
#从库查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.4.8.故障切换

mysql主从，master宕机，如何进行切换？
主机故障或者宕机：
1.在salve执行：

mysql> stop slave;
mysql> reset master;
1
2

2.查看是否只读模式：

mysql> show variables like 'read_only';
1

只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。
或者不重启使用命令临时关闭只读，但下次重启后失效：set global read_only=off;
​3.查看

mysql> show slave status \G;
1

​4.在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常

3.4.9.故障排错

UUID一致，导致主从复制I/O线程不是yes
Fatal error： The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work
致命错误：由于master和slave具有相同的mysql服务器uuid，导致I/O线程不进行；这些uuid必须不同才能使复制工作。

​问题提示主从使用了相同的server UUID，一个个的检查：
​检查主从server_id
检查主从状态：

#主库：
mysql> show master status
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#从库：
mysql> show slave status
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      306 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#File一样，排除。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

最后检查发现他们的auto.cnf中的server-uuid是一样的。

[root@localhost ~]# vim /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f
//修改uuid并重启服务
1
2
3
4

3.4.10.重设从库

#主库查看binlog，pos
mysql> show master status \G;
*************************** 1. row ***************************
             File： mylog.000003
         Position： 348
     Binlog_Do_DB： 
 Binlog_Ignore_DB： 
1 row in set (0.00 sec)
​
#从库上操作
mysql> stop slave;
mysql> reset slave;
mysql> reset master; 
#从库的binlog已经无效了，所以要执行这个命令清空binlog
​
CHANGE MASTER TO
MASTER_HOST='192.168.221.136',
MASTER_USER='slave',
MASTER_PASSWORD='JiannLt@123',
MASTER_LOG_FILE='mylog.000003',
MASTER_LOG_POS=348;
​
mysql> start slave;
mysql> show slave status\G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

如果在没有故障的情况下进行从库的重设操作，那么进行change master后，会发现SQL线程不正常为NO
解决办法：（就是搞点错误出来测试）
将前面同步的库删掉，再执行上面的操作：stop slave/reset slave/reset master/change master……
建议：非故障的情况下不要随意重设从库操作