【MySQL】主从复制

一.应用场景

主从复制是数据集群中最常见的架构模式，当系统的负载随着业务的发展越来越大时，就需要把负载分散到不同的数据库服务器，不同的数据库服务器上保存的数据应当一致，以便对外提供相同的数据服务，同时对数据的备份与恢复要求也越来越高，当有以下需求时可以使用主从复制部署数据库服务，从而提高系统的可用性、可靠性和可扩展性：

1）数据备份和恢复：通过主从复制，可以实现实时数据备份，确保数据安全。当主服务器出现问题时，从服务器可以随时接管服务，保证数据的一致性和业务的连续性；

2）读写分离：将写操作分发到主服务器，读操作分发到从服务器，减轻主服务器的压力，提高系统并发处理能力。主从复制支持大规模高并发读写，同时有效地保护了物理服务器宕机场景的数据备份；

3）负载均衡：由于主从数据节点都有完整的数据，可以通过一定的策略或规则，将负载分摊到多个不同的数据节点，提高系统的负载均衡能力。

补充：

系统的可用性：当主数据库出现故障时，从数据库可以立即接管工作，保证系统的正常运行。
系统的可靠性：通过将数据复制到多个数据库中，可以避免单点故障，提高系统的可靠性。
系统的可扩展性：通过对等复制或分区复制，可以将一个大型数据库分成多个小型数据库，提高系统的可扩展性。

二.主从复制模式

1.一主一从

一台主服务器主要负责读写操作，一台从服务器主要负责读操作或备份：

2.一主多从

一台主服务器负责写操作，多台从服务器负责读操作和备份。从节点从主节点同步数据：

一台主服务器负责写操作，多台从服务器负责读操作和备份。其中一个从节点从主节点同步数据，并为其他从节点提供数据同步服务，这样可以降低主节点数据同步的压力：

3.多主多从

三.主库配置

这里我们举例使用的一主多从的模式。

1.创建主服务器

# 创建⽬录
mkdir -p /mysql/master/conf
mkdir -p /mysql/master/mysql

# 安装MySQL镜像
docker run -d \
-p 53306:3306 \
-v /mysql/master/conf:/etc/mysql/conf.d \
-v /mysql/master/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-master \
mysql:8.0.38

2.进入Docker容器

# 进⼊Docker容器，env LANG=C.UTF-8避免中⽂乱码问题
docker exec -it mysql-master env LANG=C.UTF-8 /bin/bash
# 运⾏Mysql客⼾端
mysql -uroot -p

同时修改MySQL root的密码：

SET PASSWORD = '123456';

3.创建配置文件

在宿主机的主服务器配置映射目录 /mysql/master/conf 中创建配置文件 my.cnf ，并完成以下配置：

# MySQL服务器节点
[mysqld]
# 服务器唯⼀id, 默认值为1
server-id=13803306
# ⼆进制⽇志基本名, Linux下默认为binlog, window下默认为"机器名-bin"
# ⽣产环境推荐⾃定义⽇志路径
log-bin=binlog
# ⼆进制⽇志的格式，默认为ROW，⽣产环境也推荐⽤ROW
binlog_format=ROW
# ⼆进制⽇志过期时间，指定10天，默认30天
binlog_expire_logs_seconds=864000
# 在事务提交之前把⼆进制⽇志写⼊磁盘
sync-binlog=1

# 指定需要复制的数据库，默认复制全部数据库
# binlog-do-db=db_name
# 设置不需要复制的数据库，每⾏指定⼀个数据库，可以配置多⾏
# binlog-ignore-db=db_name1
# binlog-ignore-db=db_name2

重启主库MySQL容器，使配置生效：

docker restart mysql-master

4.配置主服务器

1）为从服务器创建访问账号并设置密码，用于从服务器登录主服务器

-- 创建⼀个⽤于主从复制的⽤⼾，指定密码加密⽅式为mysql_native_password
create user 'slave'@'%' identified with mysql_native_password by '123456';

2）授予复制权限

-- 为⽤⼾赋予复制权限
grant REPLICATION SLAVE on *.* to 'slave'@'%';

3）刷新权限

flush privileges;

4）检验是否生效

select u.host, u.user, u.Repl_slave_priv from mysql.user u;

5.查看主服务器状态

show master status;

记录 File 和 Position 的值，之后要在从服务器中配置中用到这两个值，表示从哪个日志文件中的哪个位置开始同步数据，所以在整个主从配置完成之前不要再操作主服务器，防止主服务器发生变化。

四.从库配置

可以配置多台从服务器，比如slave1、slave2...，以配置slave1为例。

1.创建从服务器

# 创建⽬录
mkdir -p /mysql/slave1/conf
mkdir -p /mysql/slave1/mysql

# 安装MySQL镜像
docker run -d \
-p 53307:3306 \
-v /mysql/slave1/conf:/etc/mysql/conf.d \
-v /mysql/slave1/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-slave1 \
mysql:8.0.38

2.进入Docker容器

# 进⼊Docker容器，env LANG=C.UTF-8避免中⽂乱码问题
docker exec -it mysql-slave1 env LANG=C.UTF-8 /bin/bash
# 运⾏Mysql客⼾端
mysql -uroot -p

同时修改MySQL root的密码：

SET PASSWORD = '123456';

3.创建配置文件

在宿主机的从服务器配置映射目录 /mysql/slave1/conf 中创建配置文件 my.cnf ，并完成以下配置：

# MySQL服务器节点
[mysqld]
# 服务器唯⼀id, 默认值为1
server-id=13803307
# ⼆进制⽇志基本名, Linux下默认为binlog, window下默认为"机器名-bin"
# ⽣产环境推荐⾃定义⽇志路径
log-bin=binlog
# ⼆进制⽇志的格式，默认为ROW，⽣产环境也推荐⽤ROW
binlog_format=ROW
# ⼆进制⽇志过期时间，指定10天，默认30天
binlog_expire_logs_seconds=864000
# 在事务提交之前把⼆进制⽇志写⼊磁盘
sync-binlog=1

########################### 从服务器配置 ##################################
# 中继⽇志基本名，默认为"机器名-relay-bin"，也可以指定⾃定义路径
relay-log=relay-bin
# 服务器启动时不启动复制，稍后⼿动启动使⽤start REPLICA语句。
# MySQL8.0.26之前⽤skip-slave-start=ON，稍后⼿动启动使⽤start SLAVE语句。
skip-replica-start=ON
# SQL线程执⾏的更新写⼊副本⾃⼰的⼆进制⽇志，MySQL8.0.26之前⽤log_slave_updates=ON
# 如果要实现 "A服务器 <-- B服务器 <-- C服务器" 这种链式⽇志同步，则B服务器即是主⼜是
从，需要开启该选项
# log-replica-updates=ON

# 禁⽤⼆进制⽇志
# skip-log-bin

###########################################################################
# 指定需要复制的数据库，默认复制全部数据库
# binlog-do-db=db_name
# 设置不需要复制的数据库，每⾏指定⼀个数据库，可以配置多⾏
# binlog-ignore-db=db_name1
# binlog-ignore-db=db_name2

重启Slave1 MySQL容器，使配置生效：

docker restart mysql-slave1

在MySQL中执行下面命令查看是否配置成功：

select @@server_id;

4.在从服务器上配置主从关系

前面查看主服务器状态的时候让大家记录 File（binlog.000003） 和 Position（829） 的值，现在用上了：

CHANGE MASTER TO
    MASTER_HOST = '192.168.80.138',
    MASTER_PORT = 53306,
    MASTER_USER = 'slave',
    MASTER_PASSWORD = '123456',
    MASTER_LOG_FILE = 'binlog.000003',
    MASTER_LOG_POS = 829;

最后，重复上面的步骤，再配置一台从服务器。

五.启动主从复制

1.启动主从复制功能

这个命令是在从服务器上执行的，每一个从服务器都要执行一遍

START REPLICA;

2.查看从服务器的状态

SHOW REPLICA STATUS\G

3.查看主服务器进程列表

show processlist\G

4.查看从服务器进程列表

show processlist\G

5.并行复制的线程数

查看默认的设复制线程数，也可以根据实际需求使用SET语句进行调整

show variables like '%replica_parallel_workers%';

六.常用命令

-- 查看已连接到主服务器的从服务器节点，在主服务器执⾏
SHOW SLAVE HOSTS | SHOW REPLICAS;

-- 查看线程列表
SHOW PROCESSLIST;

-- 停⽌从服务器的复制功能，在从机上执⾏ MySQL8.0.22之后使⽤stop replica;
stop {replica | slave};

-- 删除当前从服务器上的relaylog⽂件，并重新启⽤新的relaylog⽂件
reset {replica | slave};

-- 删除并重置主服务器上的所有binlog⽂件和索引，并重新启⽤新的binlog⽂件索引
-- 需要要主服务器上执⾏
reset master