# MySQL5.7.30 双主架构

> 参考：
>
> * https://www.linuxidc.com/linux/2016-11/136928.htm
> * https://www.jianshu.com/p/777cfa081739

两台主机 A 和 B

## 1. 修改mysql的配置文件
```shell
vim /etc/my.conf
```
A 主机：在 [mysqld] 后添加

```
...
[mysqld]
#建议用ip最后一个数字
server-id=160
#开启二进制
log-bin=mysql-bin
#将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
#自增的步长
auto-increment-increment=2
#自增起始
auto-increment-offset=1
#开启gtid复制
gtid-mode=on
enforce-gtid-consistency=true
...
```
> A主机自增键都是奇数

B主机：在 [mysqld] 后添加
```
[mysqld]
#建议用ip最后一个数字
server-id=167
#开启二进制
log-bin=mysql-bin
#将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
#自增的步长
auto-increment-increment=2
#自增起始
auto-increment-offset=2
#开启gtid复制
gtid-mode=on
enforce-gtid-consistency=true
```
> B主机自增键都是偶数
>
> **二进制日志 保存在 mysql 目录的data目录下**

## 2. 配置主从同步

### 2.1 添加主从同步账户
A 主机：
```shell
mysql> grant replication slave on *.* to 'repl'@'10.18.226.167' identified by 'dx3906';
mysql> flush privileges;
```
B主机：
```shell
mysql> grant replication slave on *.* to 'repl'@'10.18.226.160' identified by 'dx3906';
mysql> flush privileges;
```
### 2.2 查看主库状态
A主机：
```shell

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      611 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```
B主机：
```shell
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      611 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


```

### 2.3 配置同步信息
A主机：
```mysql
mysql> change master to master_host='10.18.226.167',master_port=3306,master_user='repl',master_password='dx3906',master_log_file='mysql-bin.000001',master_log_pos=611;
mysql> start slave;
mysql> show slave status\G;
```
显示有如下状态则正常：
```
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
```
B主机：
```mysql
mysql> change master to master_host='10.18.226.160',master_port=3306,master_user='repl',master_password='dx3906',master_log_file='mysql-bin.000001',master_log_pos=611;
mysql> start slave;
mysql> show slave status\G;
```
显示有如下状态则正常：
```
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
```


## 3. 验证
在 A主机 上创建一个数据库测试同步效果

```mysql
#创建数据库 test01
create database test01;
```
去 B主机看一下，是否同步了数据库 test01
```mysql
show databases;
```
> 也可以在 B主机再创建一个数据库，看一下是否同步到A主机上

## 4.  开启MySQL5.6的GTID功能，实现多线程主从复制

A主机和B主机分别执行一下命令：
```mysql
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
```