MySql 之 Binglog 复制

复制是一种将数据从一个 MySQL 数据库服务器异步复制到另一个的技术。使用 MySQL 复制选项，您可以复制所有数据库、选定的数据库甚至选定的表，具体取决于您的使用情况。

前提条件

确保在源服务器上启用了二进制日志记录。
确保复制配置中的所有服务器都有唯一的服务器 ID（不能重复）。
确保复制配置中的所有服务器都有唯一的 UUID（不能重复）。
为复制创建一个单独的用户，以便对源进行身份验证，读取复制的二进制日志。

检查MySQL状态

[root@mysql-a ~]# systemctl status mysqld

配置参数

更改配置文件，加入 binlog 复制所需的 MySQL 复制参数。停止 MySQL 守护进程，编辑 /etc/my.cnf 文件以包含参数，然后启动 MySQL 守护进程。

>>>>>>>>>>>>>>>>> mysql-a <<<<<<<<<<<<<<<<
[root@mysql-a ~]# systemctl stop mysqld
[root@mysql-a ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysql-a ~]#
[root@mysql-a ~]# vi /etc/my.cnf
[root@mysql-a ~]# cat /etc/my.cnf
[mysqld]
# MySQL Replication parameters
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id=1
binlog-format=ROW
innodb-flush-log-at-trx-commit=1
sync-binlog=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysql-a ~]#
[root@mysql-a ~]# systemctl start mysqld
>>>>>>>>>>>>>>>>> mysql-b <<<<<<<<<<<<<<<<
[root@mysql-b ~]# systemctl stop mysqld
[root@mysql-b ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysql-b ~]#
[root@mysql-b ~]# vi /etc/my.cnf
[root@mysql-b ~]# cat /etc/my.cnf
[mysqld]
# MySQL Replication parameters
server-id=2
relay-log=relay-mysql-b
relay-log-index=relay-mysql-b.index
skip-slave-start
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysql-b ~]#
[root@mysql-b ~]# systemctl start mysqld
>>>>>>>>>>>>>>>>> mysql-c <<<<<<<<<<<<<<<<
[root@mysql-c ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysql-c ~]#
[root@mysql-c ~]# systemctl stop mysqld
[root@mysql-c ~]# vi /etc/my.cnf
[root@mysql-c ~]# cat /etc/my.cnf
[mysqld]
server-id=3
relay-log=relay-mysql-c
relay-log-index=relay-mysql-c.index
skip-slave-start
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysql-c ~]#
[root@mysql-c ~]# systemctl start mysqld

注意复制配置中的每台服务器的 server-id 都应不同。

SKIP_NETWORKING

确保将 SKIP_NETWORKING 变量设置为 OFF。如果设置为 ON，副本就无法与源进行通信。在所有三个服务器（mysql-a、mysql-b 和 mysql-c）上使用以下示例参考进行设置：

>>>>>>>>>>>>>>>>> mysql-a <<<<<<<<<<<<<<<<
[root@mysql-a ~]# su - mysql
Last login: Wed Sep 13 22:23:01 CDT 2023 on pts/0
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.02 sec)

创建用户

创建名为 replication_user 的用户，用于从源复制到每个副本，并授予复制从属权限。

>>>>>>>>>>>>>>>>> mysql-a <<<<<<<<<<<<<<<<
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> create user 'replication_user'@'192.168.2.20' identified by 'WElcome_1234#';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'replication_user'@'192.168.2.20';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'replication_user'@'192.168.2.25' identified by 'WElcome_1234#';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'replication_user'@'192.168.2.25';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------------------+--------------+
| user             | host         |
+------------------+--------------+
| replication_user | 192.168.2.20 |
| replication_user | 192.168.2.25 |
| mysql.infoschema | localhost    |
| mysql.session    | localhost    |
| mysql.sys        | localhost    |
| root             | localhost    |
+------------------+--------------+
6 rows in set (0.00 sec)

获取主MySQL的binlog信息

从主MySQL获取二进制日志坐标。在此之前，请先获取读锁定。

>>>>>>>>>>>>>>>>> mysql-a <<<<<<<<<<<<<<<<
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
|File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set  |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |  1482    |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

记下 binlog 文件名和 binlog 位置，以便以后在副本上配置复制源时使用。

已有数据的备份（源MySQL）

根据使用情况创建数据快照。您可以选择复制所有现有数据或数据子集。在本例中，我们将使用 --all-databases 参数复制所有现有数据以及触发器、例程和事件。逻辑备份完成后，解锁表。使用 mysqldump 进行逻辑备份。逻辑备份名为 replication_db_dump.db。

>>>>>>>>>>>>>>>>> mysql-a <<<<<<<<<<<<<<<<
-bash-4.2$ mysqldump -uroot -p --all-databases --triggers --routines --events --source-data --set-gtid-purged=OFF > replication_db_dump.db
Enter password:
-bash-4.2$ ls -ltr
total 1256
-rw-rw-r-- 1 mysql mysql 1283914 Oct  6 10:46 replication_db_dump.db
-bash-4.2$
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

恢复数据（副本MySQL）

复制源服务器 (mysql-a) 上的逻辑备份，并将其导入副本服务器 (mysql-b 和 mysql-c)。在下面的示例中，我们将逻辑备份 replication_db_dump.db 复制到 mysql-b 和 mysql-c。

>>>>>>>>>>>>>>>>> mysql-a <<<<<<<<<<<<<<<<
-bash-4.2$ ls -ltr
total 1256
-rw-rw-r-- 1 mysql mysql 1283914 Oct  6 10:46 replication_db_dump.db
-bash-4.2$ scp -r replication_db_dump.db 192.168.2.20:/home/mysql/.
The authenticity of host '192.168.2.20 (192.168.2.20)' can't be established.
ECDSA key fingerprint is SHA256:YpDYEJtpp16FvKQ/X2muJuFwk0iL9YG2fRJWnQLaxGE.
ECDSA key fingerprint is MD5:77:b3:32:b9:5f:74:27:6d:df:1c:0f:c9:76:16:7c:cb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.20' (ECDSA) to the list of known hosts.
mysql@192.168.2.20's password:
replication_db_dump.db                 100% 1254KB  73.0MB/s   00:00
-bash-4.2$
-bash-4.2$ scp -r replication_db_dump.db 192.168.2.25:/home/mysql/.
The authenticity of host '192.168.2.25 (192.168.2.25)' can't be established.
ECDSA key fingerprint is SHA256:YpDYEJtpp16FvKQ/X2muJuFwk0iL9YG2fRJWnQLaxGE.
ECDSA key fingerprint is MD5:77:b3:32:b9:5f:74:27:6d:df:1c:0f:c9:76:16:7c:cb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.25' (ECDSA) to the list of known hosts.
mysql@192.168.2.25's password:
replication_db_dump.db                 100% 1254KB  73.0MB/s   00:00
-bash-4.2$
>>>>>>>>>>>>>>>>> mysql-b <<<<<<<<<<<<<<<<
-bash-4.2$ ls -ltr
total 1256
-rw-r--r-- 1 mysql mysql 1283914 Oct  6 10:53 replication_db_dump.db
-bash-4.2$
-bash-4.2$ mysql -uroot -p < replication_db_dump.db
Enter password:
-bash-4.2$
>>>>>>>>>>>>>>>>> mysql-c <<<<<<<<<<<<<<<<
-bash-4.2$ ls -ltr
total 1256
-rw-r--r-- 1 mysql mysql 1283914 Oct  6 10:53 replication_db_dump.db
-bash-4.2$
-bash-4.2$ mysql -uroot -p < replication_db_dump.db
Enter password:

校验配置

验证复制配置（mysql-a、mysql-b、mysql-c）中每台服务器的服务器 ID 是否不同，并确保每台服务器都有不同的 UUID。如果服务器 UUID 相同，请使用以下步骤修改服务器 UUID：

>>>>>>>>>>>>>>>>> mysql-a <<<<<<<<<<<<<<<<
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          1  |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| d763ea33-52ad-11ee-84a8-080027cf69cc |
+--------------------------------------+
1 row in set (0.00 sec)
>>>>>>>>>>>>>>>>> mysql-b <<<<<<<<<<<<<<<<
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          2  |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| d763ea33-52ad-11ee-84a8-080027cf69cc |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
-bash-4.2$ exit
logout
[root@mysql-b ~]# systemctl stop mysqld
[root@mysql-b ~]# cd /var/lib/mysql
[root@mysql-b mysql]# ls -ltr *auto*
-rw-r----- 1 mysql mysql 56 Sep 13 22:21 auto.cnf
[root@mysql-b mysql]#
[root@mysql-b mysql]# mv auto.cnf /tmp/.
[root@mysql-b mysql]#
[root@mysql-b mysql]# ls -ltr *auto*
ls: cannot access *auto*: No such file or directory
[root@mysql-b mysql]#
[root@mysql-b mysql]# systemctl start mysqld
[root@mysql-b mysql]#
[root@mysql-b mysql]# ls -ltr *auto*
-rw-r----- 1 mysql mysql 56 Oct  6 11:04 auto.cnf
[root@mysql-b mysql]#
[root@mysql-b mysql]# cat auto.cnf
[auto]
server-uuid=1169b107-6462-11ee-b802-080027cf69cc
[root@mysql-b mysql]#
>>> Notice a new UUID is generated
>>>>>>>>>>>>>>>>> mysql-c <<<<<<<<<<<<<<<<
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          3  |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| d763ea33-52ad-11ee-84a8-080027cf69cc |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
-bash-4.2$ exit
logout
[root@mysql-c ~]# systemctl stop mysqld
[root@mysql-c ~]# ls -ltr /var/lib/mysql/auto.cnf
-rw-r----- 1 mysql mysql 56 Sep 13 22:21 /var/lib/mysql/auto.cnf
[root@mysql-c ~]#
[root@mysql-c ~]# mv /var/lib/mysql/auto.cnf /tmp/.
[root@mysql-c ~]#
[root@mysql-c ~]# ls -ltr /var/lib/mysql/auto.cnf
ls: cannot access /var/lib/mysql/auto.cnf: No such file or directory
[root@mysql-c ~]#
[root@mysql-c ~]# systemctl start mysqld
[root@mysql-c ~]# ls -ltr /var/lib/mysql/auto.cnf
-rw-r----- 1 mysql mysql 56 Oct  6 11:07 /var/lib/mysql/auto.cnf
[root@mysql-c ~]#
[root@mysql-c ~]# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=832d4368-6462-11ee-bcb2-080027cf69cc

副本服务器的配置

在副本上配置必须从哪个复制源复制数据，以及必须从哪个 binlog 文件和位置开始复制。从之前步骤获取 binlog 文件名和文件位置。

>>>>>>>>>>>>>>>>> mysql-b <<<<<<<<<<<<<<<<
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> CHANGE REPLICATION SOURCE TO-> SOURCE_HOST='192.168.2.15',-> SOURCE_USER='replication_user',-> SOURCE_PASSWORD='WElcome_1234#',-> SOURCE_LOG_FILE='mysql-bin.000001',-> SOURCE_LOG_POS=1482,-> GET_SOURCE_PUBLIC_KEY=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show replica status\G;
*************************** 1. row ***************************Replica_IO_State:Source_Host: 192.168.2.15Source_User: replication_userSource_Port: 3306Connect_Retry: 60Source_Log_File: mysql-bin.000001Read_Source_Log_Pos: 1482Relay_Log_File: relay-mysql-b.000001Relay_Log_Pos: 4Relay_Source_Log_File: mysql-bin.000001Replica_IO_Running: NoReplica_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Source_Log_Pos: 1482Relay_Log_Space: 157Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File:Source_SSL_CA_Path:Source_SSL_Cert:Source_SSL_Cipher:Source_SSL_Key:Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Source_Server_Id: 0Source_UUID:Source_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State:Source_Retry_Count: 86400Source_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Source_SSL_Crl:Source_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Source_TLS_Version:Source_public_key_path:Get_Source_public_key: 1Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
>>>>>>>>>>>>>>>>> mysql-c <<<<<<<<<<<<<<<<
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> CHANGE REPLICATION SOURCE TO-> SOURCE_HOST='192.168.2.15',-> SOURCE_USER='replication_user',-> SOURCE_PASSWORD='WElcome_1234#',-> SOURCE_LOG_FILE='mysql-bin.000001',-> SOURCE_LOG_POS=1482,-> GET_SOURCE_PUBLIC_KEY=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show replica status\G;
*************************** 1. row ***************************Replica_IO_State:Source_Host: 192.168.2.15Source_User: replication_userSource_Port: 3306Connect_Retry: 60Source_Log_File: mysql-bin.000001Read_Source_Log_Pos: 1482Relay_Log_File: relay-mysql-c.000001Relay_Log_Pos: 4Relay_Source_Log_File: mysql-bin.000001Replica_IO_Running: NoReplica_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Source_Log_Pos: 1482Relay_Log_Space: 157Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File:Source_SSL_CA_Path:Source_SSL_Cert:Source_SSL_Cipher:Source_SSL_Key:Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Source_Server_Id: 0Source_UUID:Source_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State:Source_Retry_Count: 86400Source_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Source_SSL_Crl:Source_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Source_TLS_Version:Source_public_key_path:Get_Source_public_key: 1Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified

开始复制

在每个复制服务器上发布启动复制语句，开始复制。

>>>>>>>>>>>>>>>>> mysql-b <<<<<<<<<<<<<<<<
-bash-4.2$ hostname
mysql-b
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> start replica;
Query OK, 0 rows affected (0.01 sec)
mysql> show replica status\G;
*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 192.168.2.15Source_User: replication_userSource_Port: 3306Connect_Retry: 60Source_Log_File: mysql-bin.000001Read_Source_Log_Pos: 1482Relay_Log_File: relay-mysql-b.000002Relay_Log_Pos: 326Relay_Source_Log_File: mysql-bin.000001Replica_IO_Running: YesReplica_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Source_Log_Pos: 1482Relay_Log_Space: 534Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File:Source_SSL_CA_Path:Source_SSL_Cert:Source_SSL_Cipher:Source_SSL_Key:Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Source_Server_Id: 1Source_UUID: d763ea33-52ad-11ee-84a8-080027cf69ccSource_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesSource_Retry_Count: 86400Source_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Source_SSL_Crl:Source_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Source_TLS_Version:Source_public_key_path:Get_Source_public_key: 1Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
>>>>>>>>>>>>>>>>> mysql-c <<<<<<<<<<<<<<<<
-bash-4.2$ hostname
mysql-c
-bash-4.2$ mysql -u root -p
Enter password:
mysql> start replica;
Query OK, 0 rows affected (0.02 sec)
mysql> show replica status\G;
*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 192.168.2.15Source_User: replication_userSource_Port: 3306Connect_Retry: 60Source_Log_File: mysql-bin.000001Read_Source_Log_Pos: 1482Relay_Log_File: relay-mysql-c.000002Relay_Log_Pos: 326Relay_Source_Log_File: mysql-bin.000001Replica_IO_Running: YesReplica_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Source_Log_Pos: 1482Relay_Log_Space: 534Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File:Source_SSL_CA_Path:Source_SSL_Cert:Source_SSL_Cipher:Source_SSL_Key:Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Source_Server_Id: 1Source_UUID: d763ea33-52ad-11ee-84a8-080027cf69ccSource_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesSource_Retry_Count: 86400Source_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Source_SSL_Crl:Source_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Source_TLS_Version:Source_public_key_path:Get_Source_public_key: 1Network_Namespace:
1 row in set (0.00 sec)
ERROR:

测试

在源 mysql-a 中创建几个数据库和表，并验证它们是否复制到了副本 mysql-b 和 mysql-c。

>>>>>>>>>>>>>>>>> mysql-a <<<<<<<<<<<<<<<<
-bash-4.2$ hostname
mysql-a
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
4 rows in set (0.00 sec)
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
mysql> create database db2;
Query OK, 1 row affected (0.01 sec)
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create table tab1 (no int);
Query OK, 0 rows affected (0.02 sec)
mysql> create table tab2 (no int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table tab3 (no int);
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tab1          |
| tab2          |
| tab3          |
+---------------+
3 rows in set (0.00 sec)
mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| db1                 |
| db2                 |
| db3                 |
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
7 rows in set (0.00 sec)
>>>>>>>>>>>>>>>>> mysql-b <<<<<<<<<<<<<<<<
-bash-4.2$ hostname
mysql-b
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| db1                 |
| db2                 |
| db3                 |
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
7 rows in set (0.00 sec)
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tab1          |
| tab2          |
| tab3          |
+---------------+
3 rows in set (0.00 sec)
>>>>>>>>>>>>>>>>> mysql-c <<<<<<<<<<<<<<<<
-bash-4.2$ hostname
mysql-c
-bash-4.2$ mysql -uroot -p
Enter password:
mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| db1                 |
| db2                 |
| db3                 |
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
7 rows in set (0.00 sec)
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tab1          |
| tab2          |
| tab3          |
+---------------+
3 rows in set (0.00 sec)

以上便是基于binlog复制的步骤，需要注意的时，以上是基于MySQL 8.0.24及以上版本的！