mysql配置主从复制

主服务器master配置

#vi /etc/my.cnf

[mysqld]

log-bin=mysql-bin # [必须]启用二进制日志

server-id=1 # [必须]服务器唯一ID，默认是1，一般取IP最后一段

sync-binlog=1

log_bin=mysql-bin

# 读写都可以

 

read-only = 0

#设置忽略的数据库

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

binlog-ignore-db=mysql

 

修改从服务器slave配置

#vi /etc/my.cnf

[mysqld]

log-bin=mysql-bin //[必须]启用二进制日志

server-id=2//[必须]服务器唯一ID，默认是1，一般取IP最后一段

sync-binlog=1

#指定中继日志名称

relay_log=mysql-relay-bin

#开启数据库只读

 

read_only=1

#设置忽略的数据库

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

 

binlog-ignore-db=mysql

重启两台服务器的mysql

systemctl restart mysqld

在主服务器上建立帐户并授权slave

create user 'fengyi'@'%' identified by '123456';

GRANT REPLICATION SLAVE ON *.* to 'fengyi'@'%' identified by '123456';

 

登录主服务器的mysql，查询master的状态

show master status;

或者重置master

reset master;

配置从服务器Slave

stop slave;

reset slave;

change master to master_host='192.168.2.1',master_port=3306,master_user='fengyi',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=456  for channel '1';

start slave;

检查从服务器复制功能状态

show slave status\G

Slave_IO_Running: Yes //此状态必须YES

Slave_SQL_Running: Yes //此状态必须YES

以上两项必须为yes才行

如果遇到错误：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

可参考文章https://blog.csdn.net/sunbocong/article/details/81634296

 

问题分析

问题提示主从使用了相同的server UUID，一个个的检查：

检查主从server_id

主库：

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.01 sec)

从库：

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.01 sec)

server_id不一样，排除。

检查主从状态：

主库：

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

从库：

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

File一样，排除。

最后检查发现他们的auto.cnf中的server-uuid是一样的。。。

[root@localhost ~]# vim /var/lib/mysql/auto.cnf

[auto]
server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f

问题解决
停止从库的mysqld服务，删除他的auto.cnf文件，再启动数据库服务即可：

[root@localhost mysql]# systemctl stop mysqld.service

[root@localhost mysql]# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak

[root@localhost mysql]# systemctl start mysqld.service

此时再去查看从库auto.cnf，已自动生成新的server-uuid：

[root@localhost mysql]# vim /var/lib/mysql/auto.cnf

[auto]
server-uuid=2682888d-994a-11e8-aaf0-000c298cdafc

再查看从库状态，已正常

log_bin=mysql-bin