Linux 之 Mysql 主主复制

一、 Mysql1配置

vi /etc/my.cnf.d/server.cnf
 
[mysqld]
#作为主从复制的唯一标识，集群中，不能重复
server-id = 1
#开启二进制日志
log-bin = master-log
#开启中继日志
relay-log = slave-log
 
auto_increment_offset = 1
 
auto_increment_increment = 2

二、MySQL2配置

vi /etc/my.cnf.d/server.cnf
 
[mysqld]
#作为主从复制的唯一标识，集群中，不能重复
server-id = 2
#开启二进制日志 
log-bin = master-log
#开启中继日志
relay-log = slave-log
 
auto_increment_offset = 2
 
auto_increment_increment = 2

三、重启MySQL服务

systemctl  restart mysqld

四、MySQL1为主，MySQL2为从配置

　　在MySQL1 中执行sql>>

mysql> create user 'lan'@'192.168.11.%' identified by '666666';
mysql> grant replication slave on *.* to 'lan'@'192.168.11.%';
show master status;

　　在MySQL2 中执行sql>>

#log_file和log_pos的数据是mariad1上show master status得到的数据
change master to master_host='192.168.11.247', master_user='lan', master_password='666666', master_log_file='master-log.000001', master_log_pos=498;
start slave;
show slave statusG;

五、MySQL2为主，MySQL1为从配置

　　在MySQL2中执行sql >>

mysql> create user 'lan'@'192.168.11.%' identified by '666666';
mysql> grant replication slave on *.* to 'lan'@'192.168.11.%';
show master status;

　　在MySQL1中执行sql>>

#log_file和log_pos的数据是mariad2上show master status得到的数据
change master to master_host='192.168.11.246', master_user='lan', master_password='666666', master_log_file='master-log.000001', master_log_pos=498;
start slave;
show slave statusG;

　　至此, 主主同步复制配置完成!!!

六、错误信息

191114 17:24:32 [ERROR] Failed to open the relay log './mysql-relay-bin.000003' (relay_log_pos 934)
191114 17:24:32 [ERROR] Could not find target log during relay log initialization
191114 17:24:32 [ERROR] Failed to initialize the master info structure
191114 17:24:32 [Note] Event Scheduler: Loaded 0 events
191114 17:24:32 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.64-mysql'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Mysql Server
191114 17:25:32 [Warning] IP address '192.168.11.247' could not be resolved: Name or service not known

解决方法：

　　在sql上运行一下命令

reset slave;