MYSQL5.7配置双主同步

1.事先环境准备
首先确保两台mysql服务器的防火墙都关闭，mysql的root密码最好保持一致
MASTER A 192.168.1.25
MASTER A 192.168.1.27

2.配置mysql配置文件
修改MASTER A的mysql配置文件，表示通过二进制日志同步，其中server-id两台机器必须设置不同的值

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin = mysql-bin       
server-id = 2
expire_logs_days = 5
replicate-do-db = seeyon       //记录那些库需要做同步操作

重启MASTER A数据库

service mysqld restart

修改MASTER B的mysql配置文件，表示通过二进制日志同步

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin = mysql-bin       
server-id = 1
expire_logs_days = 5
replicate-do-db = seeyon       //记录那些库需要做同步操作

重启MASTER B数据库

service mysqld restart

3.配置主从同步
MASTER A上登录mysql

mysql> grant replication slave on *.* to 'slave'@'192.168.1.27' identified by '123@qwe';         
mysql> flush privileges;

查看MASTER A状态并记录相关数据，file以及position

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      690 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

MASTER B上登录mysql

mysql> grant replication slave on *.* to 'slave'@'192.168.1.25' identified by '123@qwe';         
mysql> flush privileges;

看MASTER B状态并记录相关数据，file以及position

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |    96588 | seeyon       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置MASTER A同步信息

mysql> change master to master_host='192.168.1.27',master_port=3306,
     >master_user='slave',master_password='123@qwe',master_log_file='mysql-bin.000002',master_log_pos=690;
mysql> start slave;
mysql> show slave status\G;

显示有如下状态则正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

配置MASTER B同步信息

mysql> change master to master_host='192.168.1.25',master_port=3306,
     >master_user='slave',master_password='123@qwe',master_log_file='mysql-bin.000006',master_log_pos=96588;
mysql> start slave;
mysql> show slave status\G;

显示有如下状态则正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.测试主从同步
在A数据库增加测试表，添加数据看在B数据库中是否有相关变动。
在B数据库增加测试表，添加数据看在A数据库中是否有相关变动。

4.相关异常问题：
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file’系列一：
主库添加log-bin-index 参数后，从库报这个错误：Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
Got fatal error 1236 from master when reading data from binary log: ‘could not find next log’
可以

stop slave;
reset slave;
start slave;