MySQL主从复制之原理&一主一从部署流程—2023.04

一、MySQL主从复制介绍

1、主从复制概念

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

MySQL主从复制是一个异步的复制过程，底层是基于Mysql数据库自带的 二进制日志 功能。就是一台或多台MySQL数据库（slave，即从库）从另一台MySQL数据库（master，即主库）进行日志的复制，然后再解析日志并应用到自身，最终实现从库的数据和主库的数据保持一致。

2、为什么要做主从复制

1. 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2. 做数据的热备
3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

3、主从复制原理

• 原理

1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件；
3. 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志（relay-log）中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

从库会生成两个线程,一个I/O线程,一个SQL线程;

I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;

主库会生成一个log dump线程,用来给从库I/O线程传binlog;

SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

具体步骤：

从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave

从库的IO线程和主库的dump线程建立连接。

从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。

主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。

从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中

从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

主从同步事件binlog模式有3种形式：statement、row、mixed。

statement： 会将对数据库操作的 sql 语句写入到 binlog 中。

row： 会将每一条数据的变化写入到 binlog 中。

mixed： statement 与 row 的混合。MySQL 决定什么时候写 statement 格式的，什么时候写 row 格式的 binlog。

注意：

①master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。

②slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。

③Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

④Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）

⑤master和slave两节点间时间需同步

4、主从复制形式

1. 一主多从，提高系统的读性能

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

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

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

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

4、级联复制

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

5、主从复制主要用途

1、读写分离

在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

2、数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换(主从切换)

提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据；

3、高可用（HA）

1）因为数据库服务器中的数据都是相同的，当Master挂掉后，可以指定一台Slave充当Master继续保证服务的运行，因为数据是一致性的（如果当插入时Master就挂掉，可能不一致，因为同步也需要时间）当然这种配置不是简单的把一台Slave充当Master，毕竟还要考虑后续的Slave的数据同步到Master

2）在主服务器上执行写入和更新，在从服务器上向外提供读功能，达到读写分离的效果，也可以动态地调整从服务器的数量，从而调整整个数据库的性能。

3）在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

4、架构扩展

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

二、MySQL一主一从搭建

1、准备工作

首先在两台服务器上提前部署好docker以及MySQL5.7版本。

MySQL安装流程参考这篇文章：https://blog.csdn.net/weixin_64124795/article/details/129110551

2、MySQL主从复制部署流程

1. 关闭防火墙，两台主机都操作

systemctl stop firewalld  关闭防火墙
systemctl disable firewalld 关闭开机自启

2. 修改配置文件，两台主机都操作

mysql配置文件路径：/etc/my.cnf

增加如下内容：

# MySQL 服务的唯一编号，每个 MySQL 服务的 id 需唯一
server-id = 1
# 服务端口号，默认为 3306
port = 3306
# 是否对sql语句大小写敏感，默认值为0，1表示不敏感
lower_case_table_names=1    #1代表关闭大小写区别 0代表开启大小写
default_storage_engine=InnoDB   #存储引擎  lnnoDB
# 配置默认编码为utf8
character_set_server=utf8
init_connect='SET NAMES utf8'

最终配置文件内容：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# MySQL 服务的唯一编号，每个 MySQL 服务的 id 需唯一
server-id = 1
# 服务端口号，默认为 3306
port = 3306
# 是否对sql语句大小写敏感，默认值为0，1表示不敏感
lower_case_table_names=1    #1代表关闭大小写区别 0代表开启大小写
#存储引擎  lnnoDB
default_storage_engine=InnoDB   
# 配置默认编码为utf8
character_set_server=utf8
init_connect='SET NAMES utf8'

重启mysql使配置生效

systemctl restart mysqld

默认配置文件路径：

配置文件：/etc/my.cnf

日志文件：/var/log/mysqld.log

服务启动脚本：/usr/lib/systemd/system/mysqld.service

socket文件：/var/lib/mysql/mysql.sock

3. 修改主节点配置文件，主节点操作

vim /etc/my.cnf添加以下参数：

log-bin=mysql-bin #启动二进制日志系统
binlog-do-db=test #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步westos库,再添加一行“binlog-do-db=westos”,以此类推
binlog-ignore-db=mysql #禁止同步 mysql 数据库

# 重启mysql
systemctl restart mysqld

• 配置主服务器用户并清空日志
  进入MySQL

mysql -uroot -p

mysql> show privileges;
# 创建远程登录账户
## 注意这里的密码不能设置的太简单，否则会报错 ERROR 1819 (HY000): Unknown error 1819
mysql> grant replication client,replication slave on *.* to 'tongwz'@'192.168.2.%' identified by 'Tongwz123.';
mysql> reset master;
mysql> show binary logs;

4. 修改从节点配置文件，从节点操作

/etc/my.cnf中的server-id 改为2， 注意：server-id不可跟主服务器的一样

另外，加入下面一行命令，启用中继日志

#启用中继日志
relay-log=mysql-relay

# 重启mysql
systemctl restart mysqld

进入MySQL：

mysql -uroot -p

执行如下命令：

mysql> change master to master_host='192.168.2.31', master_user='tongwz', master_password='Tongwz123.', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=154;
说明：
# change master to master_host=‘主服务器ip’,
# master_user=‘刚在主服务器创建的用户名’,
# master_password=‘刚在主服务器创建的用户密码’, 
# master_port=3306, 
# master_log_file=‘刚查看主服务器下的File值’,
# master_log_pos=刚查看主服务器下的Position值，必须要一致

先查看slave状态：红框中显示是No，由于slave还没有开启，表示还没有加入主节点
mysql> show slave status \G;

正式开启slave：
mysql> start slave;
再次查看状态：此时已经显示为Yes了
mysql> show slave status \G;

注意：两个必须都显示为Yes才表示成功。如果还是显示No，就要重新执行show binary logs;命令查看master节点aster_log_pos值，再执行change master to master_host='192.168.2.31', master_user='tongwz', master_password='Tongwz123.', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=154;这段命令，然后start slave;

此时，slave已经配置好，进一步查看：

# 查看relay-log.info文件位置，进入查看
find / -name relay-log.info
cat /var/lib/mysql/relay-log.inf
# 查看master.info文件位置，进入查看
find / -name master.info
cat /var/lib/mysql/master.info

5. 测试

在master节点中数据库test里创建表test，插入数据；在slave节点查看数据是否同步。

因为前面配置文件中，写明了只同步test数据库。

master节点中创建：
mysql> use test;
mysql> create table test(id int(11));
mysql> insert into test values(1);
mysql> select * from test;

slave节点中查看：
mysql> use test;
mysql> show tables;
mysql> select * from test;

可以看到在master节点上加入的数据，这就说明主从复制成功。

6. 特别说明

1. 以上这种方式搭建的一主一从，主要实现读写分离，master节点上写入数据会自动同步到slave节点。如果反过来，在slave节点写入数据，是无法同步到master节点上的，反而还会中断主节点与从节点的连接。所以为了避免这个问题，一般只给slave节点只读的权限。

增加同步的数据库到slave

2. 上面的操作我们只同步了test数据库，如果我们想要再同步一个数据库到slave节点，该怎么操作呢？

首先，我们要修改master节点的配置文件vim /etc/my.cnf，里面增加数据库test1
master节点操作：

vim /etc/my.cnf
binlog-do-db=test  #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步westos库,再添加一行“binlog-do-db=westos”,以此类推
binlog-do-db=test1   # 增加这一行

然后重启

systemctl restart mysqld

然后，进入slave节点的mysql中，等待从节点同步主节点数据，待查看状态show slave status \G;后，两行都显示为Yes，表示已经连接成功；

其次，再master节点上的mysql中创建test1数据库，并往数据库内创建表，写入内容；

最后到slave节点查看是否同步即可。

注意：

必须先修改配置文件中的数据库名称，然后待slave与master连接后，再在master上创建test1数据库，顺序不能改变。

如果反过来，先在master节点上创建了test1数据库，然后修改配置文件、slave连接master，那么所创建的数据库就不会同步到slave。

所以，顺序很重要。

slave只同步连接master节点后，在master节点上执行的命令。

3、流程图