mysql搭建双主双从

一、简介

一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请 求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。架构图如下

机器准备：

编号	角色	IP地址
1	Master1	192.168.56.20
2	Slave1	192.168.56.21
3	Master2	192.168.56.22
4	Slave2	192.168.56.23

---

二、双主机配置修改

1、修改Master1的my.cnf文件

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
default-authentication-plugin=mysql_native_password

log-bin=mysql-bin #开启二进制日志
server-id=1 #服务id，不可重复
#出现 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and错误解决方案，在my.cnf配置文件中设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=spdb_pzex
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

---

2、Master2配置

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
default-authentication-plugin=mysql_native_password

log-bin=mysql-bin #开启二进制日志
server-id=3 #服务id，不可重复
#出现 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and错误解决方案，在my.cnf配置文件中设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=spdb_pzex
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

---

三、双从机配置

1、Slave1配置

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
default-authentication-plugin=mysql_native_password

log-bin=mysql-bin #开启二进制日志
server-id=2 #服务id，不可重复
#出现 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and错误解决方案，在my.cnf配置文件中设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
#启用中继日志
relay-log=mysql-relay

2、Slave2配置

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
default-authentication-plugin=mysql_native_password

log-bin=mysql-bin #开启二进制日志
server-id=4 #服务id，不可重复
#出现 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and错误解决方案，在my.cnf配置文件中设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
#启用中继日志
relay-log=mysql-relay

---

四、在两台主机上建立帐户并授权 slave

1、首先操作Master1

#在主机MySQL里执行授权命令
create user 'dhapp'@'%' identified by 'Tz@202011';
grant replication slave on *.* to 'dhapp'@'%';
flush privileges;
#查询Master1的状态
show master status;

 2、再操作Master2

#在主机MySQL里执行授权命令
create user 'dhapp'@'%' identified by 'Tz@202011';
grant replication slave on *.* to 'dhapp'@'%';
flush privileges;
#查询Master1的状态
show master status;

---

五、在从机上配置需要复制的主机 Slava1 复制 Master1，Slava2 复制 Master2

1、Slave1操作

CHANGE MASTER TO MASTER_HOST='192.168.56.20',
MASTER_USER='dhapp',
MASTER_PASSWORD='Tz@202011',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=157;

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

 2、Slave2操作

CHANGE MASTER TO MASTER_HOST='192.168.56.22',
MASTER_USER='dhapp',
MASTER_PASSWORD='Tz@202011',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=157;

#启动slave1从服务器复制功能
start slave;

#查看从服务器状态
show slave status;

---

六、两个主机互相复制

1、在Master2上 复制 Master1

        192.168.56.20是master1的IP地址

CHANGE MASTER TO MASTER_HOST='192.168.56.20',
MASTER_USER='dhapp',
MASTER_PASSWORD='Tz@202011',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=157;

2、在Master1上 复制 Master2 

 192.168.56.22是master2的IP地址

CHANGE MASTER TO MASTER_HOST='192.168.56.22',
MASTER_USER='dhapp',
MASTER_PASSWORD='Tz@202011',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=157;

3、在两台主机上都执行下面的同步操作

#启动两台主服务器复制功能
start slave;
#查看从服务器状态
show slave status;

下面两个参数都是Yes，则说明主从配置成功！

# Slave_IO_Running: Yes     

# Slave_SQL_Running: Yes

其他命令：

如何停止从服务复制功能
stop slave;
如何重新配置主从
stop slave;
reset master;