MySQL主从复制之多主多从部署流程—2023.04

一、多主多从实现架构图

这里是2主2从，下图基本例举出来的实现的逻辑，A、C为主，B、D为从，其中B为A的从，D为C的从，且A、C互为主从关系。才能实现在任意一台主节点上写入数据，另一台主节点以及所有从节点数据跟着同步。

二、准备工作

首先准备好4台虚拟机，且都要安装好MySQL，版本必须要一致。我这里的MySQL是5.7版本，主节点是192.168.2.31与192.168.2.74，另外两台为从节点。

开始安装之前，检查防火墙是否关闭，需要关闭防火墙。

三、MySQL多主多从搭建流程

1、修改2个主节点配置文件

由于2个master要配置成互为主从的效果，所以需要修改2个master节点的配置文件，有一丢丢的差别，注意看！

• 在master1上配置/etc/my.cnf文件

[mysqld]
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
#启动二进制日志系统
log-bin=mysql-bin
#设置logbin格式
binlog_format=STATEMENT
#二进制需要同步的数据库名,如果需要同步多个库,例如要再同步westos库,再添加一行“binlog-do-db=westos”,以此类推
binlog-do-db=testdb
#禁止同步 mysql 数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=performance_schema
# 是否对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'

# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的幅度，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长初始值
auto-increment-offset=1

说明：
log-slave-updates参数必须要配置，当一台主机即为主又为从时，该参数就要写到配置文件中。目的是让该节点在作为从数据库的时候，有写入操作也要更新二进制日志文件中
auto-increment-offset=1
auto-increment-increment=2
这两个参数分别表示自增长的初始值和自增长的递增幅度

• master2上配置/etc/my.cnf文件
  master2的配置文件与master1略微有些差异，主要是server-id和auto-increment-increment，另外还要再master中增加一行relay-log=mysql-relay，表示启用中继日志，如果这里不启用中继日志，后面在执行时会报错ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

[mysqld]
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 = 2
# 服务端口号，默认为 3306
port = 3306
#启动二进制日志系统
log-bin=mysql-bin
#设置logbin格式
binlog_format=STATEMENT
#二进制需要同步的数据库名,如果需要同步多个库,例如要再同步westos库,再添加一行“binlog-do-db=westos”,以此类推
binlog-do-db=testdb
#禁止同步 mysql 数据库
binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
#binlog-ignore-db=sys
#binlog-ignore-db=performance_schema
# 是否对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'
#启用中继日志
relay-log=mysql-relay

# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates=1
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

2、修改2个从节点配置文件

两个从节点的配置文件内容除了server-id不同，其余都是一致的，如下：

• slave1配置内容

[mysqld]
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

server-id=32
#启用中继日志
relay-log=mysql-relay
# 服务端口号，默认为 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'

• slave2配置内容

[mysqld]
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

server-id=75
#启用中继日志
relay-log=mysql-relay
# 服务端口号，默认为 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'

3、2个主节点相互复制

• 在master1上的MySQL中执行：
  进入MySQL：mysql -uroot -pQWErty@123.

# 创建主从同步账号：
grant replication client,replication slave on *.* to 'test1'@'192.168.2.%' identified by 'QWErty@123.';

# 刷新
flush privileges;

# 查看用户是否创建成功
select user from mysql.user where user = '刚才创建的用户';    # mysql.user  表示mysql数据库下的user表

#查看master状态
show master status;
```master status;

file：binlog日志的名字
Position ：接入点 （从机从接入点开始复制）
Binlog_Do_DB ：需要复制的数据库
Binlog_Ignore_DB ：不需要复制的数据库

• 在master2上的MySQL中执行：
  进入MySQL：mysql -uroot -pQWErty@123.

# 创建主从同步账号：
grant replication client,replication slave on *.* to 'test1'@'192.168.2.%' identified by 'QWErty@123.';

# 刷新
flush privileges;

# 查看用户是否创建成功
select user from mysql.user where user = '刚才创建的用户';    # mysql.user  表示mysql数据库下的user表

#查看master状态
show master status;

下面将master1与master2相互复制

• • master1上执行：

change master to master_host='192.168.2.74', 
master_user='test1', 
master_password='QWErty@123.', 
master_port=3306, 
master_log_file='mysql-bin.000002', 
master_log_pos=154;

注意：这里的信息都是主节点的信息。其中，
master_log_file是在192.168.2.74主机上使用show master status查看的File名称
master_log_pos是在192.168.2.74主机上使用show master status查看的Position

# 启动从服务器复制功能
start slave；
# 查看从服务器状态
show slave status\G;

• • master2上执行：
    在master2上复制master1的内容

change master to master_host='192.168.2.31', 
master_user='test1', 
master_password='QWErty@123.', 
master_port=3306, 
master_log_file='mysql-bin.000002', 
master_log_pos=154;

# 启动从服务器复制功能
start slave；
# 查看从服务器状态
show slave status\G;

master1与master2都显示为2个绿色的Yes，表示两台主机复制成功

4、2个从节点分别复制主节点

• slave1操作：
  slave1IP：192.168.2.32，对应主节点：192.168.2.31

change master to master_host='192.168.2.31', 
master_user='test1', 
master_password='QWErty@123.', 
master_port=3306, 
master_log_file='mysql-bin.000002', 
master_log_pos=154;

# 启动从服务器复制功能
start slave；
# 查看从服务器状态
show slave status\G;

• slave2操作：
  slave2IP：192.168.2.75，对应主节点：192.168.2.74

change master to master_host='192.168.2.74', 
master_user='test1', 
master_password='QWErty@123.', 
master_port=3306, 
master_log_file='mysql-bin.000002', 
master_log_pos=154;

# 启动从服务器复制功能
start slave；
# 查看从服务器状态
show slave status\G;

两台从节点都显示了2个绿色的Yes说明复制成功

注意：

change master to master_host='192.168.2.XX', 
master_user='test1', 
master_password='QWErty@123.', 
master_port=3306, 
master_log_file='mysql-bin.000002', 
master_log_pos=154;

如果执行下面这段命令，报错了，需要去对应的主节点查看master_log_file和master_log_pos是否发生了变化
然后停止slave的复制：stop slave;
在执行上面的change master to……这一串命令
然后开启复制：start slave;
再看slave状态show slave status\G;

5、测试

在master1上创建数据库testdb，因为我们配置文件里配置的同步数据库就是testdb，在testdb中创建表bsm，并写入内容；

mysql> create database testdb;
mysql> use testdb；
mysql> create table bsm(id int(10),where varchar(20));
mysql> insert bsm values(1,2.74)；

在master2、slave1、slave2节点查看，可以看到masetr1增加的数据；

同样的，在master2上往表里写入数据，其他节点也可以同步数据。

记录：

Slave_IO_Running: Connecting

1. 问题原因：
   （1）网络不通
   （2）防火墙端口未开放
   （3）mysql账户密码错误
   （4）mysql主从机配置文件写错
   （5）配置从机连接语法错误
   （6）主机未开放账户连接权限

2. 错误：MySQL error code 1872 (ER_SLAVE_RLI_INIT_REPOSITORY): Slave failed to initialize relay log info structure from the repository
   执行完change master to……，后start slave爆出这个错误。
   是找不到中继日志的仓库，需要检查变量relay log的位置是否设置
   mysql> show variables like 'relay%';
   如果没有配置，在配置文件中加上；如果配置了，执行reset slave，之后再change master to……，最后start slave

3. 在执行change master to……一定要看master节点的status，查看file的名称和postion的数字书否变化，即使变更。

参考文章：
https://www.jb51.net/article/213992.htm
https://www.bloghome.com.cn/post/mysqlshuang-zhu-zhu-zhu-jia-gou-fang-an.html
https://blog.csdn.net/qq_42094345/article/details/108191985