mysql基于GTID主从搭建

Hoboson0316

于 2023-11-21 16:53:27 发布

阅读量75

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 GTID复制 Mysql主从搭建

本文链接：https://blog.csdn.net/qq_30831237/article/details/134535960

版权

Mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏



#************************master 添加配置(XXX.XXX.XXX.204) ************************
#开启二进制日志
bin-log=master-bin
#开启gtid模块
gtid-mode=on
#开启强制gtid一致性，
enforce-gtid-consistency=on 
#指定日志的记录模式为row(行记录)，确保数据的一致性
binlog-format=row
#开启从服务器日志更新，从服务器中的操作记录也将写入日志文件中
log-slave-updates=1         
#启用自动开启主从复制
skip-slave-start=1 

service mysqld restart 

grant replication slave on *.* to 'slave'@'%' identified by 'passwd';



#************************ slave 添加配置(XXX.XXX.XXX.201) ************************
#server-id=3
gtid-mode=on
#开启强制gtid一致性，
enforce-gtid-consistency=on 
#指定日志的记录模式为row(行记录)，确保数据的一致性
binlog-format=row
#开启从服务器日志更新，从服务器中的操作记录也将写入日志文件中
log-slave-updates=1         
#启用自动开启主从复制
skip-slave-start=1 



stop slave;
reset slave all;
change master to master_host="XXX.XXX.XXX.204",master_user="slave",master_password="passwd",master_auto_position=1;
start slave; 
 #查看状态
 show slave status\G;
 报错
                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要不一样否则也会有问题
查看uuid是否一致 
(XXX.XXX.XXX.201)和 (XXX.XXX.XXX.204)uuid一样
 show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 542a25ae-4d5f-11ee-a6d7-000c2995a003 |
+---------------+--------------------------------------+
1 row in set (0.05 sec)

show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 542a25ae-4d5f-11ee-a6d7-000c2995a003 |
+---------------+--------------------------------------+
1 row in set (0.05 sec)
				
修改uuid重启报错
[ERROR] Garbage characters found at the end of the server_uuid value in auto.cnf file. It should be of length '36' (UUID_LENGTH). Clear it and restart the server. 
2023-11-21T08:46:15.916691Z 0 [ERROR] Initialization of the server's UUID failed because it could not be read from the auto.cnf file. If this is a new server, the initialization failed because it was not possible to generate a new UUID.
2023-11-21T08:46:15.916694Z 0 [ERROR] Aborting
删除重启
cd /usr/local/mysql5.7/data				
rm -rf auto.cnf 
service mysqld restart
 start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: XXX.XXX.XXX.204
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000018
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 361
        Relay_Master_Log_File: binlog.000018
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 


总结：server-id 不一样  uuid不一样