MySQL 5.7.40如何配置双向主从同步
零 背景说明
本文档记录通过gtid来配置MySQL 5.7.40双向主从同步的详细步骤，便于后续配置作为参考。操作系统环境如下：
机器信息：
| IP | 系统版本 | 内核版本 | hostname | MySQL版本 |
|---|---|---|---|---|
| 10.0.9.216 | CentOS Linux release 7.4.1708 (Core) | 3.10.0-693.el7.x86_64 x86_64 | primary1 | 5.7.40 |
| 10.0.9.217 | CentOS Linux release 7.4.1708 (Core) | 3.10.0-693.el7.x86_64 x86_64 | primary2 | 5.7.40 |
一 主从同步配置主要步骤
- 下载MySQL 5.7.40软件；
- 安装启动MySQL数据库；
- 启动MySQL，修改root口令；
- 主库修改/etc/my.cnf配置文件，开启gtid；
- 主库创建测试数据库，初始化测试数据；
- 主库备份数据库，scp备份文件到备库；
- 主库创建数据库同步用户；
- 备库恢复数据；
- 备库开启gtid,并执行同步；
- 备库查看同步状态；
- 主库写入新数据，备库验证数据同步情况；
二 配置主从同步数据库
1下载MySQL 5.7.40软件
主、备库服务器上都执行该操作：
[root@primary1 ~]# mkdir mysql5740 && cd mysql5740 [root@primary1 mysql5740]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar --2023-09-01 16:29:31-- https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar Resolving downloads.mysql.com (downloads.mysql.com)... 184.26.255.23, 2600:140b:2:99c::2e31, 2600:140b:2:99d::2e31 Connecting to downloads.mysql.com (downloads.mysql.com)|184.26.255.23|:443... connected. HTTP request sent, awaiting response... 302 Moved Temporarily Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar [following] --2023-09-01 16:29:32-- https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar Resolving cdn.mysql.com (cdn.mysql.com)... 2.18.158.138, 2600:140b:2:99c::1d68, 2600:140b:2:9b7::1d68 Connecting to cdn.mysql.com (cdn.mysql.com)|2.18.158.138|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 557322240 (532M) [application/x-tar] Saving to: 'mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar' 100%[======================================================================================================================================================================================================================>] 557,322,240 1.15MB/s in 15m 31s 2023-09-01 16:45:07 (584 KB/s) - 'mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar' saved [557322240/557322240] [root@primary1 mysql5740]#
2安装启动MySQL数据库
主、备库服务器上都执行该操作:
[root@primary1 mysql5740]# ll total 544260 -rw-r--r--. 1 root root 557322240 Aug 31 2022 mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar [root@primary1 mysql5740]# tar -xvf mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar mysql-community-client-5.7.40-1.el7.x86_64.rpm mysql-community-common-5.7.40-1.el7.x86_64.rpm mysql-community-devel-5.7.40-1.el7.x86_64.rpm mysql-community-embedded-5.7.40-1.el7.x86_64.rpm mysql-community-embedded-compat-5.7.40-1.el7.x86_64.rpm mysql-community-embedded-devel-5.7.40-1.el7.x86_64.rpm mysql-community-libs-5.7.40-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.40-1.el7.x86_64.rpm mysql-community-server-5.7.40-1.el7.x86_64.rpm mysql-community-test-5.7.40-1.el7.x86_64.rpm [root@primary1 mysql5740]# for f in `ls *.rpm`;do rpm -ivh $f;done warning: mysql-community-client-5.7.40-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY error: Failed dependencies: mysql-community-libs(x86-64) >= 5.7.9 is needed by mysql-community-client-5.7.40-1.el7.x86_64 warning: mysql-community-common-5.7.40-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-common-5.7.40-1.e################################# [100%] warning: mysql-community-devel-5.7.40-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY ... [root@primary1 mysql5740]#
可以重复执行上述命令，直到rpm bundle包里的所有组件都安装成功。
3启动MySQL，修改root口令
主、备库服务器上都执行该操作:
[root@primary1 mysql5740]# systemctl start mysqld
[root@primary1 mysql5740]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2023-09-01 16:54:04 CST; 15s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 11714 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 11560 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 11716 (mysqld)
Tasks: 27
Memory: 346.3M
CGroup: /system.slice/mysqld.service
└─11716 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Sep 01 16:53:55 primary1.localdomain systemd[1]: Starting MySQL Server...
Sep 01 16:54:04 primary1.localdomain systemd[1]: Started MySQL Server.
[root@primary1 mysql5740]# grep 'temporary password' /var/log/mysqld.log
2023-09-01T08:54:00.488821Z 1 [Note] A temporary password is generated for root@localhost: uk/3qlrhhupK
[root@primary1 mysql5740]# 获取MySQL初始化时，给root创建的临时口令：grep ‘temporary password’ /var/log/mysqld.log
修改root口令：
[root@primary1 mysql5740]# mysql_secure_installation Securing the MySQL server deployment. Enter password for user root: #输入前面获取的临时口令：uk/3qlrhhupK The existing password for the user account root has expired. Please set a new password. New password: #输入root用户的新口令 Re-enter new password: The 'validate_password' plugin is installed on the server. The subsequent steps will run with the existing configuration of the plugin. Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : no
其它步骤，根据提示，一步一步执行。
4主库修改/etc/my.cnf配置文件，开启gtid；
修改主库的/etc/my.cnf配置文件，在其中的[mysqld]部分，添加下述信息：
# master server-id server-id=216 #主库添加gtid gtid_mode=on enforce_gtid_consistency=on
然后，重启MySQL数据库：
[root@primary1 mysql5740]# systemctl restart mysqld [root@primary1 mysql5740]# mysql -uroot -pAa_123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.40 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.00 sec) mysql>
并验证gitd参数已经生效。
5主库创建测试数据库，初始化测试数据；
主库执行下述操作，创建testdb的数据库，并且创建测试表和测试数据。
mysql> create database testdb; Query OK, 1 row affected (0.00 sec) mysql> create table testdb.t1(id int); Query OK, 0 rows affected (0.00 sec) mysql> insert into testdb.t1 values(1); Query OK, 1 row affected (0.01 sec) mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql>
6主库备份数据库，scp备份文件到备库；
执行数据库备份，–all-databases表示备份所有数据库：
[root@primary1 mysql5740]# mysqldump --all-databases -uroot -p'Aa_123456' --flush-logs >all.dmp mysqldump: [Warning] Using a password on the command line interface can be insecure. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@primary1 mysql5740]# ll -h all.dmp -rw-r--r--. 1 root root 861K Sep 1 17:04 all.dmp [root@primary1 mysql5740]#
如果数据库配置在非默认端口，可以采用类似下述命令来备份数据库：mysqldump -h127.0.0.1 -P33061 –all-databases -uroot -p’Aa_123456′ –flush-logs >all.dmp
然后，scp备份文件到备库服务器：
[root@primary1 mysql5740]# scp all.dmp root@10.0.9.217:/root/ root@10.0.9.217's password: all.dmp 100% 860KB 29.7MB/s 00:00 [root@primary1 mysql5740]#
7主库创建数据库同步用户
只在主库端执行下述操作：
[root@primary1 mysql5740]# mysql -uroot -pAa_123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.40 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER 'replica'@'10.0.9.217' identified by 'Onlyou_168'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to 'replica'@'10.0.9.217'; Query OK, 0 rows affected (0.00 sec) mysql>
8备库恢复数据
[root@primary2 mysql5740]# mysql -uroot -pAa_123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.40 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source /root/all.dmp ... Query OK, 0 rows affected (0.00 sec) mysql>
9备库开启gtid，并执行同步
备库如果没有开启gtid，在恢复完主库的备份数据之后，尝试通过执行stop slave;change master to xxx;start slave这些操作时，会遇到类似下述错误：
[root@primary2 mysql5740]# mysql -uroot -pAa_123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.40 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> stop slave; ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log. mysql>
备库开启gtid，并重启数据库：
修改备库的/etc/my.cnf文件，添加下述内容：
# slave server-id server-id=217 #备库添加gtid gtid_mode=on enforce_gtid_consistency=on
然后，执行重启：
[root@primary2 ~]# systemctl restart mysqld
备库开启同步操作：
执行的命令和步骤如下：
stop slave; change master to master_host='10.0.9.216',master_port=3306,master_user='replica',master_password='Onlyou_168',master_auto_position=1; start slave; show slave status\G;
开始执行同步：
[root@primary2 ~]# mysql -uroot -pAa_123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.40 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='10.0.9.216',master_port=3306,master_user='replica',master_password='Onlyou_168',master_auto_position=1 -> ; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.0.9.216 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: primary2-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: 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: 0 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 216 Master_UUID: 17caa530-48a5-11ee-b06c-000c29068c0d Master_Info_File: /var/lib/mysql/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: 230904 09:19:03 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified mysql>
从上，可以看到备库开启同步时报错了，Got fatal error 1236 from master when reading data from binary log: ‘Binary log is not open’，提示说是主库binary log没有开启。
这里，我们根据错误提示，开启主库的binary log，并重启主库。vi /etc/my.cnf 添加下述内容：
#主库开启binary log log_bin=on [root@primary1 ~]# systemctl restart mysqld [root@primary1 ~]#
10备库查看同步状态
备库重新开启同步：stop slave;start slave;show slave status\G;
mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> 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: 10.0.9.216 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: on.000001 Read_Master_Log_Pos: 154 Relay_Log_File: primary2-relay-bin.000003 Relay_Log_Pos: 353 Relay_Master_Log_File: on.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: 154 Relay_Log_Space: 563 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: 216 Master_UUID: 17caa530-48a5-11ee-b06c-000c29068c0d Master_Info_File: /var/lib/mysql/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: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.01 sec) mysql>
至此，看到备库已经开始和主库执行同步操作了。
11主库写入新数据，备库验证数据同步情况
备库当前数据：
mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.01 sec) mysql>
主库插入新数据：
[root@primary1 ~]# mysql -uroot -pAa_123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.40-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.04 sec) mysql> insert into testdb.t1 values(222); Query OK, 1 row affected (0.00 sec) mysql>
备库可以看到新数据：
mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | | 222 | +------+ 2 rows in set (0.00 sec) mysql>
至此，可以看到，当主库写入新数据后，备库可以看到该数据。说明主从同步已经完成。
三 配置双向同步数据库
接下来，我们可以进一步配置双向同步数据库：把原备库当作新主库，原主库当作备库。即：当我们向原备库写入数据后，原主库可以看到新数据。
我们只需要执行下述步骤即可完成：
1 原备库开启binary log；
2 原备库创建同步用户；
3 原主库开启同步；
4 原备库写入数据；
5 原主库查看数据同步情况
1 原备库开启binary log并重启数据库：
vi /etc/my.cnf添加下述参数： #备库开启binlog log_bin=on systemctl restart mysqld
2 原备库创建数据库同步用户：
mysql> select host,user from mysql.user; +-----------+---------------+ | host | user | +-----------+---------------+ | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 3 rows in set (0.00 sec) mysql> create user 'replica'@'10.0.9.216' identified by 'Onlyou_168'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave on *.* to 'replica'@'10.0.9.216'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select host,user from mysql.user; +------------+---------------+ | host | user | +------------+---------------+ | 10.0.9.216 | replica | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +------------+---------------+ 4 rows in set (0.00 sec) mysql>
3 原主库开启同步
[root@primary1 ~]# mysql -uroot -pAa_123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.40-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='10.0.9.217',master_port=3306,master_user='replica',master_password='Onlyou_168',master_auto_postion=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master_auto_postion=1' at line 1 mysql> change master to master_host='10.0.9.217',master_port=3306,master_user='replica',master_password='Onlyou_168',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> 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: 10.0.9.217 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: on.000001 Read_Master_Log_Pos: 771 Relay_Log_File: primary1-relay-bin.000002 Relay_Log_Pos: 970 Relay_Master_Log_File: on.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: 771 Relay_Log_Space: 1180 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: 217 Master_UUID: 21f2d1fe-48a5-11ee-9b16-000c295bc8a5 Master_Info_File: /var/lib/mysql/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: 21f2d1fe-48a5-11ee-9b16-000c295bc8a5:1-3 Executed_Gtid_Set: 17caa530-48a5-11ee-b06c-000c29068c0d:1, 21f2d1fe-48a5-11ee-9b16-000c295bc8a5:1-3 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified mysql>
4 原备库写入数据
[root@primary2 ~]# mysql -uroot -pAa_123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.7.40-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | | 222 | +------+ 2 rows in set (0.01 sec) mysql> insert into testdb.t1 values(3); Query OK, 1 row affected (0.02 sec) mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | | 222 | | 3 | +------+ 3 rows in set (0.00 sec) mysql>
5 原主库查看数据同步情况
mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | | 222 | | 3 | +------+ 3 rows in set (0.01 sec) mysql>
至此，说明原备库当作主库写入新的数据之后，原主库当作备库来用，已经可以同步新数据了。
