复制是一种将数据从一个 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.15
Source_User: replication_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 1482
Relay_Log_File: relay-mysql-b.000001
Relay_Log_Pos: 4
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: No
Replica_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_Source_Log_Pos: 1482
Relay_Log_Space: 157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 0
Source_UUID:
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State:
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_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.15
Source_User: replication_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 1482
Relay_Log_File: relay-mysql-c.000001
Relay_Log_Pos: 4
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: No
Replica_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_Source_Log_Pos: 1482
Relay_Log_Space: 157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 0
Source_UUID:
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State:
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_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 event
Source_Host: 192.168.2.15
Source_User: replication_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 1482
Relay_Log_File: relay-mysql-b.000002
Relay_Log_Pos: 326
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 1482
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: d763ea33-52ad-11ee-84a8-080027cf69cc
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_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 event
Source_Host: 192.168.2.15
Source_User: replication_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 1482
Relay_Log_File: relay-mysql-c.000002
Relay_Log_Pos: 326
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 1482
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: d763ea33-52ad-11ee-84a8-080027cf69cc
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_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及以上版本的！