mysql集群主从复制
准备两台安装好的mysql服务器:
192.168.3.23 (master)
192.168.3.25 (slave)
配置主服务(master), 添加如下配置
$ vi /etc/my.cnf
# 节点唯一id值
server-id=1
# 开启二进制日志
log-bin=mysql-bin
# 指定日志格式 有mixed|row|statement 推荐mixed
binlog-format=mixed
# 步进值auto_imcrement。一般有n台主MySQL就填n(可选配置)
auto_increment_increment=2
# 起始值。一般填第n台主MySQL。此时为第一台主MySQL(可选配置)
auto_increment_offset=1
# 忽略mysql库(可选配置)
binlog-ignore-db=mysql
# 忽略information_schema库(可选配置)
binlog-ignore-db=information_schema
# 要同步的数据库，默认所有库(可选配置)
#replicate-do-db=test_db
配置从服务(slave), 添加如下配置
# 节点唯一id值
server-id=2
# 开启二进制日志
log-bin=mysql-bin
# 步进值auto_imcrement。一般有n台主MySQL就填n(可选配置)
auto_increment_increment=2
# 起始值。一般填第n台主MySQL。此时为第一台主MySQL(可选配置)
auto_increment_offset=2
# 要同步的数据库，默认所有库(可选配置)
#replicate-do-db=test_db
重启 mysql, 两个节点都重启
[root@mysql8 ~]# service mysqld restart
Shutting down MySQL............ SUCCESS!
Starting MySQL.... SUCCESS!
在主节点查看 master 的状态 , 尤其是当前的日志及位置
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000013 | 155 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
在slave节点执行如下命令
注意master_log_file 是对应show master status;中file的值，master_log_pos是对应position的值
mysql> change master to
-> master_host='192.168.3.23',
-> master_user='root',
-> master_password='123456',
-> master_log_file='mysql-bin.000013',
-> master_log_pos=155;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
启动 slave 状态 ( 开始监听 msater 的变化 )
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
#重置 slave 状态
$ reset slave;
#暂停 slave 状态
$ stop slave;
查看 slave 的状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.23
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 155
Relay_Log_File: mysql8-relay-bin.000004
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 155
Relay_Log_Space: 698
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 51c0ac32-817e-11ea-8a8e-000c2923a7d6
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.03 sec)
当Slave_IO_Running: Yes和Slave_SQL_Running: Yes都为yes是说明主从复制正常
问题注意和排查
如果启动的时候遇到以下状态，出现的可能是server_id相同，可能是server_UUID相同
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.3.23
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 155
Relay_Log_File: mysql8-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 155
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13117
Last_IO_Error: 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.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 200419 11:03:37
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
出现Slave_IO_Running: No错误
碰到了"Last_IO_Error: 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." 这个错误提示
查看server_id是否相同
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.03 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.03 sec)
查看server_uuid是否相同
# 查看master节点
[root@mysql8 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=51c0ac32-817e-11ea-8a8e-000c2923a7d6
# 查看slave节点
[root@mysql8 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=51c0ac32-817e-11ea-8a8e-000c2923a7d6
经过排查，主从架构中使用了相同的UUID。
原因：我搭建集群是先在虚拟机上搭好主库环境，然后直接克隆镜像当作从库，这样会导致主从库生成相同的server-uuid。只需删除一个server-uuid并重启数据库服务即可解决问题。