Mysql 主主复制搭建

1. 准备工作

配置环境：centos7，mysql5.7

先安装、配置好两台MySQL服务器

server1 IP：192.168.1.1

server2 IP：192.168.1.2

mysql的安装请参考之前的文章：

mysql5.7的安装（本教程为centos6安装，cnetos7的话下载对应源即可）

2. 在server1上操作

vi /etc/my.cnf 

修改或添加下面这几行：

server-id=1

log-bin=mysql-bin # 启用二进制日志 

auto-increment-increment = 2  #每次增长2 

auto-increment-offset = 1 #设置自动增长的字段的偏移量 

两个可选参数（2选1）： 

binlog-do-db=db1,db2 #需要同步的库 

binlog-ignore-db=db1,db2 #忽略不同步的库

保存后重启：

systemctl restatus mysqld

运行mysql客户端 ：

mysql -uroot -p

创建同步账号

grant replication slave on *.* to 'master2'@'192.168.1.2' identified by 'Master@123456'; 

flush privileges;

锁表，不让数据写入

flush tables with read lock;  show master status;

mysql> show master status; 

记录下二进制日志文件名和位置

备份数据库

在mysql1上执行：

mysqldump -uroot -p db1 > back.sql；

把备份好的数据拷贝到从服务器

scp back.sql 192.168.1.2:~

3. 在server2操作

vi /etc/my.cnf 

修改或增加：

server-id=2 #这个数值不能和主一样 

log-bin=mysql-bin # 启用二进制日志 

auto-increment-increment = 2  #每次增长2 

auto-increment-offset = 2 #设置自动增长的字段的偏移量

可选参数（2选1，这两个参数设置成和主一样）：

replicate-do-db=db1,db2 

replicate-ignore-db=db1,db2

保存后重启

systemctl restatus mysqld

把在server1上备份的数据导入server2

在server2上执行：

[root@server2 ~]# mysqldump -uroot -p db1 < back.sql

运行mysql客户端

mysql -uroot -p

创建同步账号

grant replication slave on *.* to 'master1'@'192.168.1.1' identified by 'Master@123456'; 

flush privileges;


mysql> show master status;

记录下二进制日志文件名和位置

执行以下命令

stop slave;  

change master to master_host='192.168.1.1',master_user='master2',master_password='Master@123456',master_log_file='mysql-bin.000001',master_log_pos=1735; (master_log_file和master_log_pos填上刚才记录下的二进制日志文件名和位置)

start slave;

查看从服务器的状态：

show slave status\G

记得结尾加上 “\G”，别问我为什么，不加你试试？

mysql> show slave status\G
 *************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.1.1
                   Master_User: repl2
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 613
                Relay_Log_File: server2-relay-bin.000002
                 Relay_Log_Pos: 320
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

4. 回到server1上操作

运行mysql客户端：

mysql -uroot -p

取消server1上数据库的锁定

mysql> unlock tables;

执行以下命令 ：

stop slave;  
change master to master_host='192.168.1.2',master_user='master1',master_password='Master@123456',master_log_file='mysql-bin.000001',master_log_pos=1735; (master_log_file和master_log_pos填上刚才记录下的二进制日志文件名和位置)

start slave;

查看从服务器的状态：

show slave status\G

mysql> show slave status\G
 *************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.1.2
                   Master_User: repl1
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 613
                Relay_Log_File: server1-relay-bin.000002
                 Relay_Log_Pos: 320
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

5. 主主复制测试

经测试，主主复制配置成功。

 

6.错误解析

     （1）连接失败

error connecting to master 'master1@192.168.1.2:3306' - retry-time: 60  retries: 1

 这个错误一般有俩种原因：一是你的机器防火墙可能开启造成的。二是你的授权账号密码等原因造成（本人因为授权账号哪里没搞明白逻辑所以授权账号错误，导致该问题）。

  （2）同步错误

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导致，请以此运行如下命令：

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)

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

之后继续查看连接状态即可。

（3）提示某些db已经存在，导致无法同步

Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2 charset utf8'

对于此问题，我查了好久，试了多个方法皆无用，最后直接最笨方法。手动删除冲突的库，但是因为本人测试机，无真实数据。生产环境还望继续深挖。