- 1微信云开发基础_微信云开发技术
- 2密集检索：我们应该使用什么样的检索粒度？（如何提升召回率）_基于字符串长度和基于段落的切片需要搭配检索策略才能提升召回率
- 3git远程仓库使用方法/本地文件中的git仓库和远程git仓库（gitee中的）连接步骤_怎么设置本地文件链接git仓库
- 4Linux入门：Linux历史介绍_linux最早是由芬兰的一位研究生
- 5基于Python的旅游网站数据爬虫分析_爬取旅游网站数据并进行分析
- 6Inpaint如何去水印？Inpaint图片去水印教程_inpaint可以去除水印吗
- 7Unity XR Interaction Toolkit 踩坑记录
- 8【微信小程序知识点】获取微信昵称_微信小程序获取用户昵称
- 9Python项目-微信消息自动回复【附源码】_python自动回复微信消息
- 10【云原生】Docker部署Bitwarden
mysql 双主复制_配置步骤、遇到的问题及解决办法_mysql双主复制
赞
踩
MySQL主主复制结构区别于主从复制结构。在主主复制结构中，两台服务器的任何一台上面的数据库存发生了改变都会同步到另一台服务器上，这样两台服务器互为主从，并且都能向外提供服务。
配置步骤：
一、修改配置文件
（一）服务器A(192.168.115.119)配置如下（该配置需要写到 [mysqld]区域内）
server-id=10
log-bin=master-bin
log-slave-updates=true
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 1
（二）服务器B(192.168.115.120)配置如下（该配置需要写到 [mysqld]区域内）
server-id=20
log-bin=master-bin
log-slave-updates=true
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 2
注：二都只有server-id不同和 auto-increment- offset不同
auto-increment-offset是用来设定数据库中自动增长的起点的，回为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突
replicate-do-db 可以指定同步的数据库
另：auto-increment-increment的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2
（三）两台服务器都重启
systemctl restart mysqld
二、相互授权用户(在A服务器授权一个允许B访问的用户，反之亦然)
（一）在服务器A(192.168.115.119)上
grant replication slave on *.* to 'slave'@'192.168.115.%' identified by '123.com';
flush privileges;
（二）在服务器B(192.168.115.120)上
grant replication slave on *.* to 'slave'@'192.168.115.%' identified by '123.com';
flush privileges;
三、互告bin-log信息
（一）在服务器A(192.168.115.119)上
show master status;
（二）在服务器B(192.168.115.120)上
show master status;
（三）在服务器A(192.168.115.119)上指定master
change master to master_host='192.168.115.120',master_user='slave',master_password='123.com',master_log_file='master-bin.000001',master_log_pos=612;
（四）在服务器B(192.168.115.120)上指定master
change master to master_host='192.168.115.119',master_user='slave',master_password='123.com',master_log_file='master-bin.000001',master_log_pos=612;
四、在两服务器都执行以下命令
start slave;
五、查看状态
show slave status\G;
（一）在服务器A(192.168.115.119)上显示
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.115.120
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 612
Relay_Log_File: bogon-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
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: 612
Relay_Log_Space: 154
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: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /usr/local/mysql/data/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: 230727 10:23:39
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:
1 row in set (0.00 sec)
（二）在服务器B(192.168.115.120)上显示
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.115.120
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 612
Relay_Log_File: bogon-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
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: 612
Relay_Log_Space: 154
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: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /usr/local/mysql/data/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: 230727 10:23:35
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:
1 row in set (0.00 sec)
（三）错误解析
ERROR:
No query specified
此时看到两台服务器显示相同的错误，错误解析
（四）解决问题
1、在主服务器上运行以下命令，获取当前的服务器 ID：
SHOW VARIABLES LIKE 'server_id';
2、在从服务器上运行以下命令，获取当前的服务器 ID：
SHOW VARIABLES LIKE 'server_id';
此时可以看到两台服务器的ID是一样的，因此，更改的两台服务器 MySQL 配置文件/etc/my.cnf，找到 `server-id` 参数，并将其设置为一个不同于主服务器的唯一值。(分别将两个配置文件中最后的`server-id`注释掉)
3、重启两台服务器，确保设置生效
systemctl restart mysqld
4、两台服务器再次进入mysql，使用以下命令启动复制和复制线程：
stop slave;
start slave;
（五）执行完此命令后
在服务器A(192.168.115.119)上显示
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.115.120
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 612
Relay_Log_File: bogon-relay-bin.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
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: 612
Relay_Log_Space: 154
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: 1593
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: 20
Master_UUID:
Master_Info_File: /usr/local/mysql/data/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: 230727 10:45:35
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:
1 row in set (0.00 sec)
ERROR:
No query specified
错误编号 1593 是由于主服务器和从服务器具有相同的 MySQL 服务器 UUID 导致的问题。错误消息 “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.” 指出，为了使复制工作正常，主服务器和从服务器的 MySQL 服务器 UUID 必须不同。
MySQL 服务器 UUID 是一个用于唯一标识服务器的值，用于复制过程中的身份验证和标识。由于两个服务器的 UUID 相同，从服务器的 IO 线程停止工作，无法继续进行复制。
（六）问题描述：
集群搭建时克隆主服务的镜像导致所有节点的服务UUID都一致，此时在集群中添加节点时会提示UUID冲突报错。
（七）解决方案
1、利用uuid函数生成新的uuid
mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 18093dc8-2c8f-11ee-bf5e-000c2973c1d9 |
+--------------------------------------+
1 row in set (0.00 sec)
2、查看配置文件目录
mysql> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.01 sec)
3、编辑配置文件目录
vim /usr/local/mysql/data/auto.cnf
4、uuid修改新生成的uuid
server-uuid=18093dc8-2c8f-11ee-bf5e-000c2973c1d9
5、重启服务
service mysqld restart
之后再次进入mysql，两台服务器分别执行show master status；发现下图所示数据发生改变，然后在两台服务器上再次指定master，开启slave，查看slave状态
直到看到了两个yes，即：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明已经配置成功了
六、测试是否同步
（一）在服务器A(192.168.115.119)上操作
create database jx;
show databases;
（二）在服务器B(192.168.115.120)上操作
show databases;
（三）在服务器B(192.168.115.120)上操作
create database jx1;
show databases;
（四）在服务器A(192.168.115.119)上操作
show databases;
在测试的过程当中，我也遇到一些问题主要是两台机器互相通信的问题
请注意，一定要保持两台的服务器的mysql端口都向对方打开，要不然是不能成功同步的。
