MySQL主从架构

MySQL REPLICATION

在实际生产环境中，如果对数据库的读和写都在一个数据库服务器中操作。无论是在安全性、高可用性，还是高并发等各个方面都是完全不能满足实际需求的，因此，一般来说都是通过主从复制（master-slave）的方式来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案来进行部署与实施。

一、什么是MySQL REPLICATION（MySQL主从复制）

1、主从复制是指当master(主)库的数据发生变化的时候，变化会实时的同步到一个或多个slave(从)库。

2、默认情况下属于异步复制，无需维持长连接。

3、通过配置，可以复制所有的库或者几个库，甚至库中的一些表。

4、replication是MySQL内建的，本身自带。

二、MySQL REPLICATION的原理

简单的说就是master将数据库的更新操作写入二进制日志，slave同步这些二进制日志中的数据更新事件并写入中继日志文件中，然后读取relay日志，把二进制的日志解析成SQL语句，并执行这些SQL语句，使其与master中的数据一致。

注：

DML:SQL操作语句，update, insert，delete等数据更新操作语句。

Relay log :中继日志

三、MySQL REPLICATION的作用

1、读写分离，提供查询服务

使用主从复制，让主库负责写，从库负责读。这样，即使主库进行数据更新操作出现了锁表的情景，通过读从库也可以保证业务的正常运作。

2、实时灾备，用于故障切换。

当系统中某个节点发生故障时，可以方便的故障切换，实现高可用（HA）。例如，做数据的热备，slave作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

3、水平扩展数据库的负载能力

随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

四、MySQL REPLICATION支持的复制类型

Statement：即基于语句的复制，会将对数据库操作的sql语句写入到binlog中，效率比较高。

row：即基于行的复制，会将每一条数据的变化写入到binlog中。

mixed：即混合模型的复制，statement与row的混合，MySQL会根据执行的SQL语句选择日志保存方式。即交替使用行和语句、由MySQL服务器自行判断。

五、 MySQL REPLICATION如何工作

整体上来说，复制的工作过程有3个步骤：

(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

(2) slave将master的binary log events拷贝到它的中继日志(relay log)；

(3) slave根据中继日志中的事件，对salve数据库做相应的操作，使其与master中的数据一致。

六、MySQL REPLICATION常见方案：

1、一主一从

2、一主多从

一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。

一主多从，Master负责写操作，其他slave负责读，这种架构最大问题I/O压力集中，多台slave需要从master上同步数据，影响master的IO性能。

3、级联复制（M-S-S）

级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

例如，使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates

4、双主互备 (互为主从)

双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

很多人误以为这样可以做到MySQL负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性。

5、多主一从（从5.7开始支持）

多主一从可以将多个MySQL数据库备份到一台存储性能比较好的服务器上。

七、 MySQL主从复制原理

MySQL主从复制涉及到三个线程，一个运行在主节点（binlog dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示:

l主节点 binary log dump 线程

当从节点连接主节点时，主节点会创建一个binlog dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，在发送给从节点之前，锁会被释放。

l从节点I/O线程

当从节点上执行`START REPLICA; `命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 线程发来的更新之后，保存在本地relay-log中。

l从节点SQL线程

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

注：对于每一个主从连接，都需要三个线程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 线程，而每个从节点都有自己的I/O线程，SQL线程。

主从复制的工作过程：

步骤一：主库上数据库的更新事件(update、insert、delete)被写到binlog

步骤二：从库启动之后，创建一个I/O线程，从库发起连接，连接到主库

步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库

步骤四：从库的I/O线程读取主库传过来的binlog内容并写入到relay log.

步骤五：从库还会创建一个SQL线程，从relay log里面读取内容，并执行读取到的更新事件，将更新内容写入到slave的数据库。

注：要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

八、MySQL 主从复制模式

l异步模式（MySQL async-mode）

MySQL主从复制默认是异步的模式，异步模式如下图所示，这种模式下，master事务的提交不需要经过slave的确认，slave是否接收到master的binlog，master并不关心。slave接收到master binlog后先写relay log，最后异步地去执行relay log中的sql应用到自身。这样就会有一个问题，由于master的提交不需要确保slave relay log是否被正确接受，当slave接受master binlog失败或者relay log应用失败，master无法感知。

假设master发生宕机并且binlog还没来得及被slave接收，而切换程序将slave提升为新的master，就会出现数据不一致的情况！另外，在高并发的情况下，传统的主从复制，从节点可能会与主产生较大的延迟（当然MySQL后续版本陆续做了优化，推出了并行复制，以此降低异步复制的延迟）

l半同步模式(MySQL semi-sync)

基于传统异步存在的缺陷，MySQL在5.5版本推出半同步复制。可以说半同步复制是传统异步复制的改进，在master事务的commit之前，必须确保一个slave收到relay log并且响应给master以后，才能进行事务的commit。但是slave对于relay log的应用仍然是异步进行的，原理如下图所示:

相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，所以，半同步复制最好在低延时的网络中使用。

半同步模式不是MySQL内置的，从MySQL 5.5开始集成，需要master 和slave 安装插件开启半同步模式。

l全同步模式

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

九、部署MySQL主从同步（一主一从）

1、环境准备：

主机名	IP	系统版本/MySQL版本	角色
localhost	192.168.30.11	RHEL9.4/8.4.0	Master
Cong12	192.168.30.12	RHEL9.4/8.4.0	slave

2、配置时间服务器

建立时间同步环境，在主节点上搭建时间同步服务器。

配置好本地yum源（略）。

具体搭建过程参考《Centos9使用chrony服务同步时间》文档。

3、配置主数据库服务器localhost

MySQL> create database HA;

MySQL> use HA;

MySQL> create table T1(id int,name varchar(20));

MySQL> insert into T1 values(1,'Tom1');

(1)、创建主从复制的授权用户：

MySQL> create user slave@192.168.30.12 identified by '123456';

MySQL> grant replication client,replication slave on *.* to slave@192.168.30.12;

MySQL> flush privileges;

(2) 配置my.cnf:

[root@localhost ~]# vim /etc/my.cnf 添加一下内容

[MySQLd]

………….. //省略部分内容

log-bin=/data/MySQL/log/MySQL-bin #启用二进制日志

server-id=1 #数据库服务器ID标识

binlog-do-db=HA #可以被从服务器复制的库, 即二进制需要同步的数据库名

(3) 重启MySQL

[root@localhost ~]# systemctl restart MySQLd

（4）查看master状态信息：

MySQL>SHOW BINARY LOG STATUS; //显示主服务器的当前binlog文件及事件位置

（5）查看二进制日志：

（6）导出数据库

复制前要保证同步的数据库一致

[root@localhost ~]# MySQLdump -uroot -pAbcd1234 HA >HA.sql #可以导出数据库

将导出的数据库传给从服务器

[root@localhost ~]# scp HA.sql root@192.168.30.12:~

4、配置从数据库服务器cong12

（1）两台数据库服务器MySQL版本要一致

MySQL> show variables like '%version%';

（2）测试连接到主服务器是否成功

[root@cong12 ~]# MySQL -uslave -pAbcd1234 -h 192.168.30.11

MySQL> show databases; #只有复制的权限, 是看不到其他库的。正常

（3）导入数据库，和主数据库服务器保持一致

[root@cong12 ~]# MySQL -uroot -pAbcd1234 -e "create database HA;"

[root@cong12 ~]# MySQL -uroot -pAbcd1234 HA<HA.sql

（4）修改从服务器配置文件：

从服务器没必要开启bin-log日志

（5）关闭MySQL

[root@cong12 ~]# systemctl stop MySQLd

（6）修改my.cnf配置文件

[root@cong12 ~]# vim /etc/my.cnf

server-id = 2 #从服务器ID号，不要和主ID相同，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。这些ID值能唯一识别复制服务器群集中的每个服务器实例

relay-log=/data/MySQL/log/relay-log-bin //中继日志文件的路径名称

relay-log-index=/data/MySQL/log/slave-relay-bin.index //中继日志索引文件的路径名称

（7）重启服务

[root@cong12 ~]# systemctl start MySQLd

（8）MySQL数据库里从配置

[root@cong12 ~]# MySQL -uroot -pAbcd1234

MySQL> START REPLICA; #停止slave

MySQL>CHANGE REPLICATION SOURCE TO

SOURCE_HOST='192.168.30.11',

SOURCE_USER='slave',

SOURCE_PASSWORD='123456',

SOURCE_PORT=3306,

GET_SOURCE_PUBLIC_KEY=1,

SOURCE_LOG_FILE='MySQL-bin.000001',

SOURCE_LOG_POS=909;

MySQL> START REPLICA; #启动slave

MySQL> SHOW REPLICA STATUS\G; //查看slave的状态

Slave_IO_Running ：该线程连接到master机器，master机器上的binlog dump线程会将binlog的内容发送给该I/O线程。该I/O线程接收到binlog内容后，再将内容写入到本地的relay log。

Slave_SQL_Running：该线程读取I/O线程写入的relay log。并且根据relay log的内容对slave数据库做相应的操作。

看到2个yes说明设置成功

如何在master、slave上查看上述的线程？

在主服务器上查看binlog dump线程

[root@localhost ~]# MySQL -uroot -pAbcd1234

MySQL> show processlist \G;

在从服务器上查看I/O线程和SQL线程

5、主服务器插入数据测试同步：

MySQL> use HA;

MySQL> insert into T1 values(1,'tom2');

MySQL> select * from T1;

+------+------+

| id | name |

+------+------+

| 1 | Tom1 |

| 1 | tom2 |

+------+------+

2 rows in set (0.00 sec)

在从服务器查看数据是否同步

MySQL> use HA;

MySQL> select * from T1;

+------+------+

| id | name |

+------+------+

| 1 | Tom1 |

| 1 | tom2 |

+------+------+

2 rows in set (0.00 sec)

从上面的结果可以看到数据同步成功。

配置小结：

lMaster端

1、同步Master端的原始数据到所有Slave端

2、开启binlog日志，保持server-id唯一

3、配置Slave验证授权用户，权限replication slave

Slave端

1、开启relay日志，保持server-id唯一

2、执行change master语句，生成master.info文件

3、启动Slave复制（START REPLICA; ）

Master端全备数据库同步到Slave端

在开始做主从复制之前（START REPLICA; 前），需要把Master原有的数据都先同步到所有的Slave，否则在做同步复制之时，因为原有数据不一致导致同步失败。（注意，如果使用原来备份时间点，比如昨天凌晨的全备数据同步所有Slave数据时，还需要把当前时间点之前的所有binlog增量备份同步，使在主从复制时间点时，Master和所有Slave的数据保持一致）

6、排错：记一次MySQL主从数据库不同步的排查

如果遇到MySQL的主从数据库没有同步

先上Master库：

MySQL>show processlist; 查看下进程是否Sleep太多。发现很正常。

MySQL>SHOW BINARY LOG STATUS; 也正常。

再到Slave上查看

MySQL> SHOW REPLICA STATUS\G;

Slave_IO_Running: Yes

Slave_SQL_Running: No

可见是Slave不同步

下面介绍两种解决方法：

方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况

解决：

MySQL> stop REPLICA;

#表示跳过一步错误，后面的数字可变

MySQL> set global sql_slave_skip_counter =1;

MySQL> start REPLICA;

之后再用MySQL> SHOW REPLICA STATUS\G; 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

ok，现在主从同步状态正常了。。。

方式二：重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况

解决步骤如下：

1.先进入主库，进行锁表，防止数据写入

使用命令：

MySQL> flush tables with read lock;

注意：该处是锁定为只读状态，语句不区分大小写

2.进行数据备份

#把数据备份到MySQL.bak.sql文件

[root@cong1 ~]#MySQLdump -uroot -p dbname> MySQL.bak.sql

这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失

查看master 状态

MySQL>SHOW BINARY LOG STATUS; //记下当前binlog文件和position

4.把MySQL备份文件传到从库机器，进行数据恢复

#使用scp命令

[root@localhost ~]# scp MySQL.bak.sql root@192.168.30.12:/tmp/

5.停止从库的状态

MySQL> STOP REPLICA;

6.然后到从库执行MySQL命令，导入数据备份

MySQL> source /tmp/MySQL.bak.sql

7.设置从库同步，注意该处的同步点，就是主库SHOW BINARY LOG STATUS信息里的| File| Position两项

MySQL> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.30.11',

SOURCE_USER='slave',SOURCE_PASSWORD='123456',SOURCE_PORT=3306,

GET_SOURCE_PUBLIC_KEY=1,SOURCE_LOG_FILE='MySQL-bin.000001',

SOURCE_LOG_POS=909;

8.重新开启从同步

MySQL> START REPLICA;

9.查看同步状态

MySQL> SHOW REPLICA STATUS\G; 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

好了，同步完成啦。

主库解除锁表操作

MySQL>unlock tables;

（1） I/O线程出错

1、网络连通问题

2、防火前没有放开端口

3、对应的主机 IP地址写错了

（2）SQL线程出错

主从服务器数据库结构不统一

6、部署MySQL主主双向主从复制 M-M（可选）

1、实验环境

通过MySQL主主复制：进行MySQL 双向同步数据库HA的配置

主机名	IP	系统版本/MySQL版本	角色
localhost	192.168.30.11	RHEL9.4/8.4.0	Master/slave
Cong12	192.168.30.12	RHEL9.4/8.4.0	Slave/master

在上面主从的基础上进行配置

2、配置cong12为主服务器

它有两种身份：

身份1： localhost的从。身份2： localhost的主

（1）配置my.cnf

[root@cong12 ~]# vim /etc/my.cnf

………. //省略部分内容

server-id = 2

log-bin=/data/MySQL/log/MySQL-bin

binlog-do-db=HA

（2）创建主从复制的授权用户：

MySQL> create user slave@'192.168.30.%' identified by '123456';

MySQL> grant replication client,replication slave on *.* to slave@'192.168.30.%';

MySQL> flush privileges; #记得刷新授权表

（3）重启MySQL

[root@cong12 ~]# systemctl restart MySQLd

MySQL>SHOW BINARY LOG STATUS;

注：关闭防火墙或创建允许数据库连接规则

[root@cong12 ~]# systemctl stop firewalld

[root@cong12 ~]# systemctl disable firewalld

3、配置localhost为cong12的从服务器

（1）测试从帐号登陆

[root@localhost ~]# MySQL -uslave -pAbcd1234 -h 192.168.30.12

（2）修改my.cnf

[root@localhost ~]# vim /etc/my.cnf

………. //省略部分内容

relay-log=/data/MySQL/log/relay-log-bin

relay-log-index=/data/MySQL/log/slave-relay-bin.index

（3）重启服务器

[root@localhost ~]# systemctl restart MySQLd

（4）MySQL数据库里从配置

MySQL> STOP REPLICA;

MySQL> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.100.10', SOURCE_USER='slave',SOURCE_PASSWORD='123456',SOURCE_PORT=3306,GET_SOURCE_PUBLIC_KEY=1,SOURCE_LOG_FILE='MySQL-bin.000001',SOURCE_LOG_POS=154;

MySQL> START REPLICA;

MySQL> SHOW REPLICA STATUS\G;

4、插入数据测试：

（1）在localhost上插入数据，cong12上查看

MySQL> use HA;

MySQL> insert into T1 values(4,'li4');

[root@cong12 ~]# MySQL -uroot -pAbcd1234

MySQL> use HA;

MySQL> select * from T1; #在cong12查看数据OK

（2）在cong12上插入数据，localhost上查看

MySQL> insert into T1 values(5,'li5');

MySQL> select * from T1; #在localhost上查看数据OK

7、部署M-S-S联级复制（可选）

（1）实验环境：

恢复实验快照

主机	IP	系统版本	角色
localhost	192.168.30.11	RHEL9.4/8.4.0	Master/NTP
Cong12	192.168.30.12	RHEL9.4/8.4.0	Slave中继
Cong13	192.168.30.13	RHEL9.4/8.4.0	Slave

（2）部署MASTER localhost

注：确保三台服务器时间一致，关闭防火墙。

（3）在主服务上授权用户：

[root@localhost ~]# MySQL -uroot -pAbcd1234

MySQL> create user repl@'192.168.30.%' identified by '123456';

MySQL> grant replication client,replication slave on *.* to repl@'192.168.30.%' ;

MySQL> flush privileges;

（4）创建数据库

MySQL> create database HA;

MySQL> use HA;

MySQL> create table T1(id int,name varchar(20));

MySQL> insert into T1 values(1,'tom1');

（5）修改配置文件

[root@localhost ~]# vim /etc/my.cnf

[MySQLd]

………省略部分内容

server-id=1

binlog-do-db=HA

log-bin=/data/MySQL/log/MySQL-bin-master

sync-binlog=1

binlog-format=row

参数说明：

sync-binlog：此参数表示每写缓冲多少次就同步到磁盘；sync_binlog=1表示同步写缓冲和磁盘二进制日志文件，不使用文件系统缓存，在使用innodb事务引擎时，在复制环境中，为了保证最大的可用性，都设置为“1”，但会对影响io的性能。

（6）重启服务

[root@localhost ~]# systemctl restart MySQLd

（7）查看master状态信息

MySQL>SHOW BINARY LOG STATUS; //显示主服务器的当前binlog文件及事件位置

（8）导出主服务器HA库完整备份，拷贝到中继服务器和slave服务器

[root@localhost ~]# MySQLdump -uroot -pAbcd1234 -B HA>ha.sql

[root@localhost ~]# scp ha.sql root@192.168.30.12:~

[root@localhost ~]# scp ha.sql root@192.168.30.13:~

（9）部署SLAVE中继 cong12

（10）导入数据库ha.sql

[root@cong12 ~]# MySQL -uroot -pAbcd1234 <ha.sql

（11）配置my.cnf

修改主配置文件也要开启bin-log：

[root@cong12 ~]# vim /etc/my.cnf#修改主配置文件也要开启bin-log：

………… //省略部分内容

server-id=2

log-slave-updates=1 //此参数控制slave数据库是否把从master接受到的binlog并在本slave执行的内容记录到slave的二进制日志中，在级联复制环境中，这个参数是必须的

log-bin=/data/MySQL/log/MySQL-bin-slave1

relay-log=/data/MySQL/log/relay-log-bin

relay-log-index=/data/MySQL/log/slave-relay-bin.index

#把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台slave通过中继slave读取到相应数据变化

（12）重启MySQL

[root@cong12 ~]# systemctl restart MySQLd

（13）配置从服务器

将cong12配置为localhost的从服务器

[root@cong12 ~]# MySQL -uroot -pAbcd1234

MySQL> STOP REPLICA;

MySQL>CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.100.10', SOURCE_USER='repl',SOURCE_PASSWORD='123456',SOURCE_PORT=3306,GET_SOURCE_PUBLIC_KEY=1,SOURCE_LOG_FILE='MySQL-bin-master.000001',SOURCE_LOG_POS=154;

MySQL> START REPLICA;

（14）查看中继服务的状态

MySQL> SHOW REPLICA STATUS\G;

（15）再授权一个用户给slave(cong13):

MySQL> create user repl@'192.168.30.%' identified by '123456';

MySQL> grant replication client,replication slave on *.* to repl@'192.168.30.%' ;

MySQL> flush privileges;

MySQL>SHOW BINARY LOG STATUS; //显示slave中继服务器的当前binlog文件及事件位置

（16）部署SLAVE cong13

（17）导入数据库ha.sql

[root@cong13 ~]# MySQL -uroot -pAbcd1234 <ha.sql

（18）配置my.cnf

[root@cong13 ~]# vim /etc/my.cnf

[MySQLd]

………省略部分内容

server-id = 3

relay-log=/data/MySQL/log/relay-log-bin

relay-log-index=/data/MySQL/log/slave-relay-bin.index

（19）重启MySQL

[root@cong13 ~]# systemctl restart MySQLd

（20）指定cong12中继服务作为cong13的主：

[root@cong13 ~]# MySQL -uroot -pAbcd1234

MySQL> STOP REPLICA;

MySQL>CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.30.12', SOURCE_USER='repl',SOURCE_PASSWORD='123456',SOURCE_PORT=3306,GET_SOURCE_PUBLIC_KEY=1,SOURCE_LOG_FILE='MySQL-bin-slave1.000001',SOURCE_LOG_POS=448;

MySQL> START REPLICA; ;

（21）查看从服务的状态

MySQL> SHOW REPLICA STATUS\G;