MySQL主从结构，一主一从，一主多从

qq_44659804

已于 2022-11-10 18:33:13 修改

阅读量417

点赞数

分类专栏： mysql 文章标签： mysql 数据库服务器

于 2022-11-09 17:32:03 首次发布

本文链接：https://blog.csdn.net/qq_44659804/article/details/127769740

版权

mysql 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

MySQL主从结构，一主一从，一主多从

服务器信息
下载MySQL
- MySQL下载地址
部署MySQL
配置主从结构
配置一主多从结构

服务器信息

ip地址	主机名	安装服务	角色
192.168.0.22	MySQL-001	MySQL	主服务器
192.168.0.21	MySQL-002	MySQL	从服务器
192.168.0.23	MySQL-003	MySQL	从服务器

下载MySQL

服务器为linux7.9，x86；
mysql为mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar

MySQL下载地址

https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar

在这里插入图片描述

部署MySQL

解压，安装

[root@mysql-001 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[root@mysql-001 ~]# yum -y install *.rpm

启动服务

[root@mysql-001 ~]# systemctl enable mysqld --now

查看初始密码

[root@mysql-001 ~]# grep password /var/log/mysqld.log
2022-11-09T03:00:44.989347Z 1 [Note] A temporary password is generated for root@localhost: /wqlLj#4ap3y

用初始密码登录MySQL

[root@mysql-001 ~]# mysql -uroot -p'/wqlLj#4ap3y'
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.39

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> alter user root@'localhost' identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

用新密码登录MySQL

[root@mysql-001 ~]# mysql -uroot -pzzz-123-ZZZ
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.39 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 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.00 sec)

mysql> use DB1;
Database changed
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_DB1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into t1 values(2,"aa");
Query OK, 1 row affected (0.00 sec)

查看数据

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
+------+------+
1 row in set (0.00 sec)

mysql> show master status;
Empty set (0.00 sec)

mysql> exit
Bye

配置主从结构

配置主服务器

配置开启bin-log日志，配置本机唯一标识

#配置前查看数据目录
[root@mysql-001 ~]# ls /var/lib/mysql
auto.cnf    ca.pem           client-key.pem  ib_buffer_pool  ib_logfile0  ibtmp1  mysql.sock       performance_schema  public_key.pem   server-key.pem
ca-key.pem  client-cert.pem  DB1             ibdata1         ib_logfile1  mysql   mysql.sock.lock  private_key.pem     server-cert.pem  sys
[root@mysql-001 ~]#
[root@mysql-001 ~]#


[root@mysql-001 ~]# vim /etc/my.cnf
log_bin=mysql-001
server_id=001
[root@mysql-001 ~]# systemctl restart mysqld
#配置后查看数据目录，mysql-001.000001
[root@mysql-001 ~]# ls /var/lib/mysql
auto.cnf    ca.pem           client-key.pem  ib_buffer_pool  ib_logfile0  ibtmp1  mysql-001.000001  mysql.sock       performance_schema  public_key.pem   server-key.pem
ca-key.pem  client-cert.pem  DB1             ibdata1         ib_logfile1  mysql   mysql-001.index   mysql.sock.lock  private_key.pem     server-cert.pem  sys

添加授权用户，授权可复制数据

[root@mysql-001 ~]# mysql -uroot -pzzz-123-ZZZ
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.39-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> grant replication slave on *.* to repluser@'%' identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql>
mysql> select user from mysql.user;
+---------------+
| user          |
+---------------+
| repluser      |
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
4 rows in set (0.00 sec)

查看bin-log日志信息


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-001.000001 |      441 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

备份MySQL数据，备份文件内写入bin-log日志信息

[root@mysql-001 ~]# mysqldump -uroot -pzzz-123-ZZZ --master-data -A > mysqlBack/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-001 ~]# ls mysqlBack/all.sql
mysqlBack/all.sql

配置从服务器

把备份文件上传到从服务器

安装从服务器MySQL

[root@mysql-002 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[root@mysql-002 ~]# yum -y install *.rpm

查看备份数据中的bin-log日志信息

[root@mysql-002 ~]# grep 'mysql-001.000001' mysqlBack/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-001.000001', MASTER_LOG_POS=441;

配置从服务器唯一标识

vim /etc/my.cnf
server_id=002

如需要，可加入只读设置

vim /etc/my.cnf
read-only

启动服务

[root@mysql-002 ~]# systemctl enable mysqld --now
[root@mysql-002 ~]# systemctl status mysqld.service -l
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-11-09 12:08:12 CST; 18s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 12606 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─12606 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Nov 09 12:08:08 mysql-002 systemd[1]: mysqld.service holdoff time over, scheduling restart.
Nov 09 12:08:08 mysql-002 systemd[1]: Stopped MySQL Server.
Nov 09 12:08:08 mysql-002 systemd[1]: Starting MySQL Server...
Nov 09 12:08:12 mysql-002 systemd[1]: Started MySQL Server.
[root@mysql-002 ~]#

查看初始密码

[root@mysql-002 ~]# grep password /var/log/mysqld.log | tail -1
2022-11-09T04:08:09.747496Z 1 [Note] A temporary password is generated for root@localhost: rIUfWjvJ6:NU

用初始密码登录MySQL

[root@mysql-002 ~]# mysql -uroot -p'rIUfWjvJ6:NU'
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.39

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> alter user root@'localhost' identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

用新密码登录MySQL

[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ
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.39 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye

恢复数据

[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ < mysqlBack/all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

查看数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DB1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>
mysql>
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 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
+------+------+
1 row in set (0.00 sec)

添加主服务器信息

mysql> change master to master_host='192.168.0.22',master_user='repluser',master_password='zzz-123-ZZZ',master_log_file='mysql-001.000001',master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

开启slave进程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看状态，io线程，sql线程，yes

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.0.22
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-001.000001
          Read_Master_Log_Pos: 441
               Relay_Log_File: mysql-002-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-001.000001
             Slave_IO_Running: Connecting		#此处我的安全组没有放开3306端口,连接不上主服务器，不是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: 441
              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: 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: /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: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

主服务器插入数据

[root@mysql-001 ~]# mysql -uroot -pzzz-123-ZZZ
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 5
Server version: 5.7.39-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> insert into DB1.t1 values(23,"gg"),(13,"hh");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from DB1.t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
|   23 | gg   |
|   13 | hh   |
+------+------+
3 rows in set (0.00 sec)

mysql> exit
Bye

从服务器查看数据同步情况

数据未同步

[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ
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 8
Server version: 5.7.39 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 DB1.t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
+------+------+
1 row in set (0.00 sec)

mysql>

设置安全组后再次查看slave状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.22
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-001.000001
          Read_Master_Log_Pos: 703
               Relay_Log_File: mysql-002-relay-bin.000002
                Relay_Log_Pos: 582
        Relay_Master_Log_File: mysql-001.000001
             Slave_IO_Running: Yes	#都是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: 703
              Relay_Log_Space: 793
              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: 1
                  Master_UUID: b4007077-5fda-11ed-ac44-fa163ee0384e
             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: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

数据同步成功

mysql> select * from DB1.t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
|   23 | gg   |
|   13 | hh   |
+------+------+
3 rows in set (0.00 sec)


mysql> exit
Bye

配置一主多从结构

增加一台从服务器，配置一主多从架构

配置主服务器

备份主服务器数据，并把bin-log信息写入数据

[root@mysql-001 ~]# mysqldump -uroot -pzzz-123-ZZZ --master-data -A > mysqlBack/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

配置新的从服务器

把备份的数据上传到新的从服务器

查看bin-log信息

[root@mysql-003 ~]# grep "mysql-001" mysqlBack/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-001.000001', MASTER_LOG_POS=703;

解压，安装MySQL

[root@mysql-003 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[root@mysql-003 ~]# yum -y install *.rpm

修改配置文件，添加本机唯一标识

vim /etc/my.cnf
server_id=003

如有需要，可设置只读

vim /etc/my.cnf
read-only

启动服务

[root@mysql-003 ~]# systemctl enable mysqld --now

查看初始密码，用初始密码登录MySQL

[root@mysql-003 ~]# grep 'password' /var/log/mysqld.log  | tail -1
2022-11-09T08:18:32.727380Z 1 [Note] A temporary password is generated for root@localhost: A?nqV6m54Qwd
[root@mysql-003 ~]# mysql -uroot -p'A?nqV6m54Qwd'
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.39

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>

修改密码

mysql> alter user root@'localhost' identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> exit
Bye

用新密码重新登录MySQL

[root@mysql-003 ~]# mysql -uroot -pzzz-123-ZZZ
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.39 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit

恢复数据

[root@mysql-003 ~]# mysql -uroot -pzzz-123-ZZZ < mysqlBack/all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-003 ~]# mysql -uroot -pzzz-123-ZZZ
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.39 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 DB1.t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
|   23 | gg   |
|   13 | hh   |
+------+------+
3 rows in set (0.00 sec)

添加主服务器信息

mysql> change master to master_host='192.168.0.22',master_user='repluser',master_password='zzz-123-ZZZ',master_log_file='mysql-001.000001',master_log_pos=703;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

开启slave线程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看slave状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.22
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-001.000001
          Read_Master_Log_Pos: 703
               Relay_Log_File: mysql-003-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-001.000001
             Slave_IO_Running: Yes	#都是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: 703
              Relay_Log_Space: 531
              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: 1
                  Master_UUID: b4007077-5fda-11ed-ac44-fa163ee0384e
             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: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql>

主服务器插入数据

[root@mysql-001 ~]# mysql -uroot -pzzz-123-ZZZ
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.39-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> insert into DB1.t1 values(100,"kk"),(200,"ll"),(300,"oo");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from DB1.t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
|   23 | gg   |
|   13 | hh   |
|  100 | kk   |
|  200 | ll   |
|  300 | oo   |
+------+------+
6 rows in set (0.00 sec)

从服务器查看数据

[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ
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 9
Server version: 5.7.39 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 DB1.t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
|   23 | gg   |
|   13 | hh   |
|  100 | kk   |
|  200 | ll   |
|  300 | oo   |
+------+------+
6 rows in set (0.00 sec)


[root@mysql-003 ~]# mysql -uroot -pzzz-123-ZZZ
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.39 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 DB1.t1;
+------+------+
| id   | name |
+------+------+
|    2 | aa   |
|   23 | gg   |
|   13 | hh   |
|  100 | kk   |
|  200 | ll   |
|  300 | oo   |
+------+------+
6 rows in set (0.00 sec)