MySQL学习四：MySQL双主双从

一、MySQL双主双从配置开启二进制日志

一主多从，可以缓解读的压力，但是一旦主宕机了，就不能写了;所以我们可以采用双主双从架构

双主双重架构规划：

主master 3307 —> 从slave 3309

主master 3308 —> 从slave 3310

3307 <—> 3308 互为主从

2个写节点，每个写节点下又是2个读节点;

二、配置每一台MySQL服务器的配置文件my.cnf

3307

3308

3309

3310

三、与一主多从一样，所有主服务器配置文件加上binlog配置

3307

3308

3309

3310

log-bin=mysql-bin

server-id=实例的端口(server-id要唯一)

四、第一台主服务器3307的my.cnf文件增加如下配置：(Master 3307)

auto_increment_increment=2

auto_increment_offset=1

log-slave-updates

sync_binlog=1

五、第二台主服务器3308的my.cnf文件增加如下配置：(Master 3308)

auto_increment_increment=2

auto_increment_offset=2

log-slave-updates

sync_binlog=1

六、配置项说明

auto_increment_increment，控制主键自增的自增步长，用于防止Master与Master之间复制出现重复自增字段值，通常auto_increment_increment=n，有多少台主服务器，n 就设置为多少;

auto_increment_offset=1设置自增起始值，这里设置为1，这样Master的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID

注意auto_increment_offset的设置，不同的master设置不应该一样，否则就容易引起主键冲突，比如master1的offset=1，则master2的offset=2，master3的offset=3

在双主模式中，log-slave-updates 配置项一定要配置，否则在master1(3307)上进行了更新数据，在master2(3308)和slave1(3309)上会更新，但是在slave2(3310)上不会更新

sync_binlog表示每几次事务提交，MySQL把binlog缓存刷进日志文件中，默认是0，最安全的是设置为1;

七、综上主服务器

log-bin=mysql-bin

server-id=1

auto_increment_increment=2

auto_increment_offset=1

log-slave-updates

sync_binlog=1

第二台：

log-bin=mysql-bin

server-id=2

auto_increment_increment=2

auto_increment_offset=2

log-slave-updates

sync_binlog=1

八、MySQL一主多从环境重启及MySQL双主双从数据复制验证

MySQL一主多从环境重启

进入/usr/local/mysql-5.7.18/bin目录，重启四个MySQL服务，启动时指定配置文件启动：

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3307/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3308/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3309/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3310/my.cnf &

MySQL双主双从数据复制验证

检查从服务器复制功能状态，执行命令：

mysql> show slave status \G

如果Slave_IO_Running和Slave_SQL_Running的值为Yes，即表示复制功能配置正常;

设置好双主双从复制后：

1. 一个主出现问题，可以切换到另外的主进行写数据，新主同步数据给它的从;

2. 待问题旧主恢复服务后，新的主会同步数据给它，它再同步数据给自己的从，这样不会出现数据的不同步和服务的不可用。

九、MySQL双主双从重置主从状态

1、在两台主服务器上创建复制账号并授权：(3307、3308)

使用端口、主机登录进入两台主MySQL：

./mysql -uroot -p -P3307 -h127.0.0.1
./mysql -uroot -p -P3308 -h127.0.0.1

在主服务器上创建复制数据的账号并授权：

grant replication slave on *.* to 'copy'@'%' identified by 'luck888';

2、在两台主服务器上停止复制并刷新binlog日志，也就是重置一下服务的状态：(3307、3308)

在MySQL命令行执行：

stop slave; --停止复制

reset slave; --重置从服务器的状态，把它变成初始状态

reset master; --重置主服务器的状态，把它变成初始状态

3307机器执行：
reset master

=============================================

3308机器执行：

stop slave;

reset slave;

reset master;

3、在从服务器上停止复制：(3309、3310)

在MySQL命令行执行：

stop slave;

reset slave;

4、在主服务器上查看二进制日志文件和Position值：(3307、3308)

在MySQL命令行执行：

即在3307和3308上分别执行：show master status;

5、在从服务器Slave上设置Master(相当于是4台都需要设置)

设置从服务器3308、3309，他们的主均为3307，即在

3308和3309上执行如下操作：

change master to master_host='192.168.0.26',
master_user='copy',
master_password='luck888',
master_port=3307,
master_log_file='mysql-bin.000001',
master_log_pos=154;

设置从服务器3307、3310，他们的主均为3308，

即在3307和3310上执行如下操作：

change master to master_host='192.168.0.10',
master_user='copy',
master_password='123456',
master_port=3308,
master_log_file='mysql-bin.000001',
master_log_pos=154;

6、在四台MySQL服务器上执行：start slave; (MySQL命令行执行)

执行后即开始进入主从复制状态

十、综上

两个主服务器分别执行

stop slave;
reset master;
show master status;

两个主服务器在分别执行（交叉执行）

change master to master_host='192.168.0.26',
master_user='copy',
master_password='luck888',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=154;
start slave;
 show slave status \G

change master to master_host='192.168.0.10',
master_user='copy',
master_password='luck888',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=154;
start slave;
 show slave status \G