mysql的redis主从复制（数据相同和不同），双主复制

最新推荐文章于 2024-01-09 09:00:07 发布

光年之外～

最新推荐文章于 2024-01-09 09:00:07 发布

阅读量527

点赞数

文章标签： mysql的redis主从复制（数据相同和不同），双主复制

本文链接：https://blog.csdn.net/qq_41671415/article/details/97617628

版权

请注意这里用的是mysql8.0.16

在两个完全一致的环境下

(1)初始化环境。
同步时间：

yum -y install ntp ntpdate
ntpdate cn.pool.ntp.org
hwclock --systohc

关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

环境规划：
主机：

在主库上：

修改主库配置文件：开启log-bin功能，并指定server_id为1.这里log-bin必须指定，
不然的话bin-log日志会根据主机名来命名，假如主库的主机名改变了，那主从复制就失效了。
添加

vim /etc/my.cnf
[mysqld]
server_id=27
log-bin=mysql-bin
log-bin-index=master-bin.index

重启服务：

[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# systemctl start mysql

创建一个用于让从数据库连接的用户

mysql> create user 'copy'@'%' identified with mysql_native_password by 'Cloudbu@123';
mysql> grant replication slave on *.* to 'copy'@'%';

刷新授权表信息

mysql> flush privileges;

获取主节点当前binary log文件名和位置（position）

mysql> show master status;

在从库上：
修改配置文件，必须指定中继日志的名称
[mysqld]

server_id=66
relay-log=relay-log
relay-log-index=relay-log.index

[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# systemctl start mysql

在从（Slave）节点上设置主节点参数

CHANGE MASTER TO
MASTER_HOST='192.168.42.175',
MASTER_USER='copy',
MASTER_PASSWORD='Cloudbu@123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=825;

查看主从同步状态

mysql> show slave status\G

*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.42.175
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 155
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
1 row in set (0.00 sec)

开启主从同步

start slave;

再次查看主从同步状态

mysql> show slave status\G

*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.42.175
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 155
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-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: 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: 27
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: 190721 20:19:32
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
1 row in set (0.00 sec)

排错：

mysql> show variables like 'server_id';

±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 66 |
±--------------±------+
1 row in set (0.01 sec)

mysql> show variables like 'server_id';

±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 27 |
±--------------±------+
1 row in set (0.03 sec)

[root@localhost ~]# cat /opt/mysql-8.0.15-linux-glibc2.12-x86_64/data/auto.cnf
[auto]
server-uuid=cf2e5d91-aac5-11e9-a898-000c292cbd4e

主从库UUID相同

处理方法：

[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# mv  /opt/mysql-8.0.15-linux-glibc2.12-x86_64/data/auto.cnf auto.cnf.back
[root@localhost ~]# systemctl start mysql
[root@localhost ~]# cat /opt/mysql-8.0.15-linux-glibc2.12-x86_64/data/auto.cnf

mysql> show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.42.175
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 155
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000001
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: 524
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: 27
Master_UUID: cf2e5d91-aac5-11e9-a898-000c292cbd4e
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
1 row in set (0.00 sec)

问题已解决

测试：
主库上：

[root@localhost ~]# mysql -ucopy -pCloudbu@123

mysql> create database bbs;
Query OK, 1 row affected (0.01 sec)

mysql> create table bbs.t1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into bbs.t1 values(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from bbs.t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

从库上

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| data_test1         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| uplooking          |
| xingyun            |
+--------------------+

在两个数据不一致的环境下