十六、mysql主从同步结构模式 ----主主结构（又称作互为主从 双向复制）
配置互为主从
主库 192.168.1.10 互为 主库 192.168.4.11
1.配置主库（192.168.1.10）
1.1修改配置文件，启动启用binlog日志
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=10 #指定server_id
log_bin=master10
binlog_format="mixed"
[root@Mysql-master ~]# systemctl restart mysqld
1.2,登录数据库创建用户并设置权限
[root@Mysql-master ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"192.168.1.10" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant replication slave on *.* to repluser@"192.168.1.20" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看用户授权
mysql> select user,host from mysql.user where user="repluser";
+----------+-------------------+
| user | host |
+----------+-------------------+
| repluser | 192.168.1.11 |
| repluser | 192.168.1.12 |
+----------+-------------------+
1.2查看主库状态信息
mysql> show master status\G
*************************** 1. row ***************************
File: master10.000002 #日志名
Position: 1961 #偏移量
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> change master to master_host="192.168.1.11",master_user="user1",master_password="123456",master_log_file="master11.000002",master_log_pos=2100;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.11
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master11.000002
Read_Master_Log_Pos: 2100
Relay_Log_File: Mysql-master-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master11.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
配置主库2
1.配置主库（192.168.1.11）
1.1修改配置文件，启动启用binlog日志
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=11 #指定server_id
log_bin=master11
binlog_format="mixed"
[root@Mysql-master ~]# systemctl restart mysqld
1.2,登录数据库创建用户并设置权限
[root@Mysql-master ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to user1@"192.168.1.10" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看用户授权
mysql> select user,host from mysql.user where user="repluser";
+----------+-------------------+
| user | host |
+----------+-------------------+
| repluser | 192.168.1.10 |
+----------+-------------------+
1.2查看主库状态信息
mysql> show master status\G
*************************** 1. row ***************************
File: master10.000002 #日志名
Position: 1961 #偏移量
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> change master to master_host="192.168.1.11",master_user="user1",master_password="123456",master_log_file="master11.000002",master_log_pos=2100;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master11.000002
Read_Master_Log_Pos: 2100
Relay_Log_File: Mysql-master-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master11.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
