CNDBA社区
MySQL单节点的安装直接参考之前的博客：
Redhat 7.7 平台 MySQL 5.7.33 安装手册（Tar包）
https://www.cndba.cn/dave/article/4509
本片主要介绍下主主复制的搭建过程。
MySQL主主服务器的思路和主从差不多，本质就是让多台MySQL服务器间互为主从。
1 当前环境描述
主1库IP：172.31.185.120
主2库IP：172.31.185.165
[mysql@www.cndba.cn_3 ~]$ cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)
[mysql@www.cndba.cn_3 ~]$
[mysql@www.cndba.cn_1 ~]$ mysql -uroot -proot
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.33-log |
+------------+
1 row in set (0.00 sec)
mysql>
2 创建同步用户
在主服务器上为从服务器建立一个连接帐户，该帐户必须授予REPLICAITON SLAVE权限。
这里2个服务器互为主从，所以都要分别建立一个同步用户。
[mysql@www.cndba.cn_1 ~]$ mysql -uroot -proot
mysql> grant replication slave, super, replication client on *.* to 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
3 修改mysql配置文件
在2个节点的配置文件中添加如下内容：
主库1：
[mysqld]
server-id = 1
log-bin=binlog
log_slave_updates=1
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
主库2：
[mysqld]
server-id = 2
log-bin = binlog
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
然后分别重启2个mysql服务：
[root@mongodb1 ~]# systemctl restart mysql
[root@mongodb2 log]# systemctl restart mysql
4 分别在查看2个主节点状态
主库A：
[mysql@www.cndba.cn_1 ~]$ mysql -uroot -proot
mysql> show master status /G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 194
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: c9aae1bf-db08-11ec-ab9b-fa163e7035c7:1-4
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
主库B：
[mysql@www.cndba.cn_2 ~]$ mysql -uroot -proot
mysql> show master status/G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 194
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: c0f6ee45-db08-11ec-9569-fa163e2997dc:1-4
1 row in set (0.02 sec)
mysql>
5 指定同步位置并启动slave 线程
分别在服务器A、B上用change master语句指定同步位置
--主库A：
mysql> change master to master_host='172.31.185.165', master_user='repl', master_password='repl', master_log_file='mysql-bin.000003', master_log_pos=194;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
--主库B：
mysql> change master to master_host='172.31.185.120', master_user='repl', master_password='repl', master_log_file='mysql-bin.000003', master_log_pos=194;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
6 分别查看从服务器状态
mysql> show slave status/G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.185.165
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show slave status /G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.185.120
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
查看以上两项的值，均为Yes则表示状态正常。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7 测试主主同步
主库1：
mysql> create database cndba;
Query OK, 1 row affected (0.02 sec)
mysql> use cndba;
Database changed
mysql> create table dave(id int,name varchar(100));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into dave value(1,'www.cndba.cn');
Query OK, 1 row affected (0.05 sec)
mysql>
主库2：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cndba |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.02 sec)
mysql> use cndba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_cndba |
+-----------------+
| dave |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from dave;
+------+--------------+
| id | name |
+------+--------------+
| 1 | www.cndba.cn |
+------+--------------+
1 row in set (0.01 sec)
mysql> insert into dave value(2,'www.cndba.cn');
Query OK, 1 row affected (0.02 sec)
mysql>
主库1：
mysql> select * from dave;
+------+--------------+
| id | name |
+------+--------------+
| 1 | www.cndba.cn |
| 2 | www.cndba.cn |
+------+--------------+
2 rows in set (0.00 sec)
mysql>
双向同步正常。
版权声明：本文为博主原创文章，未经博主允许不得转载。
