Linux 环境搭建 MySQL8.0.28 主从同步环境

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

大家好，我是JiekeXu,很高兴又和大家见面了,今天和大家一起来看看保姆级教程 Linux 环境搭建 MySQL8.0.28 主从同步环境，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！

首先在介绍MySQL 主从时，我们来看看 DB-Engines 排行榜上四月数据库的变化，前三甲居然都有小幅度增长，相反增长迅猛的 PG 和 MongoDB 有所减少，一个月的增长趋势说明不了问题，下图是从 13 年开始的趋势图，Oracle 和 MySQL 渐渐趋平，学习了解 MySQL 还是很有必要的。下面开始进入正题。

（图源：http://db-engines.com/en/ranking）

主从复制的原理

（图源 知乎）

MySQL的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

（图源 深入浅出 MySQL 数据库开发、优化与管理维护）

由于资源限制没有那么多机器，本次使用一台原先装过 Oracle 和 OGG 的主机构建一主两从的MySQL 环境，通过三个不同的端口 3306、3307、3308 加以区分。

一、系统准备

0、查看系统版本

cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.6 (Maipo)

1、关闭防火墙

systemctl stop firewalld.service  或者 systemctl stop firewalld
systemctl disable firewalld.service 或者 systemctl disable firewalld
systemctl status firewalld    

2、关闭 selinux

getenforce 
setenforce 0
vim /etc/selinux/config
   
SELINUX=disabled
 
[root@Ops-11gOGG ~]# systemctl status firewalld  
? firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@Ops-11gOGG-86 ~]# getenforce 
Disabled

3、/etc/hosts解析

示例如下

192.168.77.86     Ops-11gOGG

4、配置 yum 源，安装依赖 rpm 包

yum -y groupinstall "DeveLopment tools"
yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make

5、清理系统环境

Linux7 版本的系统默认自带安装了MariaDB，需要先清理。
## 查询已安装的mariadb
rpm -qa |grep mariadb
或
yum list installed | grep mariadb
## 卸载mariadb包，文件名为上述命令查询出来的文件
rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
yum -y remove mariadb-libs.x86_64 

二、安装 MySQL8.0.28

0、创建实例所需目录

本手册中3306为一个实例，如部署多实例请按照下面目录结构创建目录。

root用户操作：
mkdir -p /mysql/data/mysql3306
mkdir -p /mysql/app/
mkdir -p /mysql/conf/
mkdir -p /mysql/data/mysql3306/data/
mkdir -p /mysql/data/mysql3306/pid/
mkdir -p /mysql/data/mysql3306/socket/
mkdir -p /mysql/data/mysql3306/log/
mkdir -p /mysql/data/mysql3306/binlog/
mkdir -p /mysql/data/mysql3306/relaylog/
mkdir -p /mysql/data/mysql3306/slowlog/
mkdir -p /mysql/data/mysql3306/tmp/
 
mkdir -p /mysql/data/mysql3307
mkdir -p /mysql/data/mysql3307/data/
mkdir -p /mysql/data/mysql3307/pid/
mkdir -p /mysql/data/mysql3307/socket/
mkdir -p /mysql/data/mysql3307/log/
mkdir -p /mysql/data/mysql3307/binlog/
mkdir -p /mysql/data/mysql3307/relaylog/
mkdir -p /mysql/data/mysql3307/slowlog/
mkdir -p /mysql/data/mysql3307/tmp/
 
mkdir -p /mysql/data/mysql3308
mkdir -p /mysql/data/mysql3308/data/
mkdir -p /mysql/data/mysql3308/pid/
mkdir -p /mysql/data/mysql3308/socket/
mkdir -p /mysql/data/mysql3308/log/
mkdir -p /mysql/data/mysql3308/binlog/
mkdir -p /mysql/data/mysql3308/relaylog/
mkdir -p /mysql/data/mysql3308/slowlog/
mkdir -p /mysql/data/mysql3308/tmp/

1、创建数据库用户和组

groupadd mysql
useradd -g mysql mysql
chown -R mysql:mysql /mysql
passwd mysql

2、上传解压安装包并重命名

如下链接，选择相关的版本和系统。

https://dev.mysql.com/downloads/mysql/8.0.html

MD5: 5be32f68d6859aace1eb61cea1d00bff

mysql用户操作：
cd /mysql/app
md5sum mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz  --检验 MD5 值和上面一样，说明包无损
tar xvf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
mv  mysql-8.0.28-linux-glibc2.12-x86_64 mysql8.0.28

3、配置 mysql 用户环境变量

vim ~/.bash_profile
MYSQL_HOME=/mysql/app/mysql8.0.28
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin

source ~/.bash_profile
which mysql 

4、创建参数文件

由于是二进制文件安装，数据库参数文件需要自己配置，以下是简单的参数配置。其他参数可依照个人需求添加。

vim  my3306.cnf
[mysqld]
# basic settings #
server_id = 863306
basedir = /mysql/app/mysql8.0.28
datadir = /mysql/data/mysql3306/data/
socket = /mysql/data/mysql3306/socket/mysql3306.sock
pid_file = /mysql/data/mysql3306/pid/mysqld3306.pid
port = 3306
default-time_zone = '+8:00'
character_set_server = utf8mb4
explicit_defaults_for_timestamp = 1
autocommit = 1
transaction_isolation = READ-COMMITTED
secure_file_priv = "/mysql/data/mysql3306/tmp/"
max_allowed_packet = 64M
lower_case_table_names = 1
default_authentication_plugin = mysql_native_password
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
 
# connection #
back_log = 500
interactive_timeout = 300
wait_timeout = 300
lock_wait_timeout = 300
max_user_connections = 800
skip_name_resolve = 1
max_connections = 3000
max_connect_errors = 1000
 
#table cache performance settings
#table_open_cache = 1024
#table_definition_cache = 1024
#table_open_cache_instances = 16
 
#session memory settings #
#read_buffer_size = 16M
#read_rnd_buffer_size = 32M
#sort_buffer_size = 32M
#tmp_table_size = 64M
#join_buffer_size = 128M
#thread_cache_size = 256
 
# log settings #
slow_query_log = ON
slow_query_log_file = /mysql/data/mysql3306/slowlog/slow3306.log
log_error = /mysql/data/mysql3306/log/mysqld3306.log
log_error_verbosity = 3
log_bin = /mysql/data/mysql3306/binlog/mysql_bin
log_bin_index = /mysql/data/mysql3306/binlog/mysql_binlog.index
# general_log_file = /data/mysql/mysql57_3306/generallog/general.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
#log_slow_slave_statements = 1
#expire_logs_days = 15
binlog_expire_logs_seconds = 2592000
long_query_time = 2
min_examined_row_limit = 100
log_throttle_queries_not_using_indexes = 1000
#log_bin_trust_function_creators = 1
log_slave_updates = 1
mysqlx_port = 33060
mysqlx_socket = /mysql/data/mysql3306/socket/mysqlx.sock
 
# innodb settings #
innodb_buffer_pool_size = 512M
#innodb_buffer_pool_instances = 16
innodb_log_buffer_size = 100M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 20
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_purge_threads = 4
innodb_thread_concurrency = 200
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 32M
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 1G
innodb_open_files = 4096
innodb_buffer_pool_dump_pct = 25
innodb_page_cleaners = 16
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
innodb_flush_log_at_trx_commit = 1
 
# replication settings #
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log_recovery = 1
relay_log = /mysql/data/mysql3306/relaylog/relay.log
relay_log_index = /mysql/data/mysql3306/relaylog/mysql_relay.index
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
binlog_gtid_simple_recovery = 1
slave_preserve_commit_order = 1
binlog_rows_query_log_events = 1
slave_transaction_retries = 10
log_timestamps = system
report_host = 192.168.75.86
report_port = 3306

--report_host复制副本注册期间要报告给源库的复制副本的主机名或IP地址。此值显示在源服务器上显示副本的输出中。如果不希望复制副本向源注册，请将该值保留为未设置。

其他两节点参数文件 my3307.cnf、my3308.cnf中将上述文件中的 3306 全部替换为 3307、3308 即可。

5、数据库初始化

mysql 用户操作，注意同主机参数文件名 my3306.cnf 各不相同，间隔约两分钟分别初始化三个 MySQL 实例。 

mysqld  --defaults-file=/mysql/conf/my3306.cnf --initialize --user=mysql --basedir=/mysql/app/mysql8.0.28  --datadir=/mysql/data/mysql3306/data
 
mysqld  --defaults-file=/mysql/conf/my3307.cnf --initialize --user=mysql --basedir=/mysql/app/mysql8.0.28  --datadir=/mysql/data/mysql3307/data
 
mysqld  --defaults-file=/mysql/conf/my3308.cnf --initialize --user=mysql --basedir=/mysql/app/mysql8.0.28  --datadir=/mysql/data/mysql3307/data

6、启动三个数据库实例

mysqld_safe --defaults-file=/mysql/conf/my3306.cnf --user=mysql &
 
mysqld_safe --defaults-file=/mysql/conf/my3307.cnf --user=mysql &
 
mysqld_safe --defaults-file=/mysql/conf/my3308.cnf --user=mysql &
 
--关闭数据库
mysqladmin -uroot -p -h 127.0.0.1 -P 3306 shutdown

7、查看初始化 root 密码并修改

more /mysql/data/mysql3306/log/mysqld3306.log|grep password 
more /mysql/data/mysql3306/log/mysqld3307.log|grep password 
more /mysql/data/mysql3306/log/mysqld3308.log|grep password

如下图所示，3306 端口 root@localhost 密码为“GW*QXYwSs7;f”，初始化密码比较复杂，这里由于是测试环境，将其修改为“root”;

[mysql@Ops-11gOGG ~]$ mysql -uroot -p  -P 3306 -S /mysql/data/mysql3306/socket/mysql3306.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28
 
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 'root';
Query OK, 0 rows affected (0.01 sec)
 
mysql> create user root@'%' identified by 'root';
Query OK, 0 rows affected (0.02 sec)
 
mysql> grant all  privileges on *.* to root@'%'  with grant option;
Query OK, 0 rows affected (0.00 sec)

其他两台也是如此，并创建远程 root 登录账号。

三、构建主从环境

1、主库 3306 创建复制账号 rep

create user rep@'%' identified by 'rep';
grant REPLICATION CLIENT,REPLICATION SLAVE on *.* to rep@'%';

我这里需要置空 gtid 信息。

reset master;
show master status;

2、快速构建主从

1）登录3307

mysql -uroot -p  -P 3307 -S /mysql/data/mysql3307/socket/mysql3307.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28 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>

change master to master_host='192.168.75.86',master_port=3306,master_user='rep',master_password='rep',master_auto_position=1;

show slave status\G
start slave;
show slave status\G

2）启动从库复制并查看

mysql> start slave;
Query OK, 0 rows affected, 1 warning (1.04 sec)
 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.75.86
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 1486
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 373
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No  ----这里没有启动 SQL 线程。如下错误代码为 1396
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1396
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '3ba436e0-aa7c-11ec-bba1-0050568a6bf6:2' at master log mysql_bin.000002, end_log_pos 730. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 157
              Relay_Log_Space: 1902
              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: 1396
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '3ba436e0-aa7c-11ec-bba1-0050568a6bf6:2' at master log mysql_bin.000002, end_log_pos 730. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 863306
                  Master_UUID: 3ba436e0-aa7c-11ec-bba1-0050568a6bf6
             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: 220323 16:03:06
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 3ba436e0-aa7c-11ec-bba1-0050568a6bf6:1-5
            Executed_Gtid_Set: 3ba436e0-aa7c-11ec-bba1-0050568a6bf6:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

3、修复问题

1）登录 3307

show master status;
stop slave;
reset slave all;
reset master;

2）登录 3306

mysql -uroot -p  -P 3306 -S /mysql/data/mysql3306/socket/mysql3306.sock
show master status;
reset master;
show master status;

4、重新构建主从

1）登录 3307

[mysql@Ops-11gOGG conf]$ mysql -uroot -p  -P 3307 -S /mysql/data/mysql3307/socket/mysql3307.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.28 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> change master to master_host='192.168.75.86',master_port=3306,master_user='rep',master_password='rep',master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.05 sec)
 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.75.86
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 373
        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: 157
              Relay_Log_Space: 573
              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: 863306
                  Master_UUID: 3ba436e0-aa7c-11ec-bba1-0050568a6bf6
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

2）登录3308

[mysql@Ops-11gOGG conf]$ mysql -uroot -p  -P 3308 -S /mysql/data/mysql3308/socket/mysql3308.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 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> 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> change master to master_host='192.168.75.86',master_port=3306,master_user='rep',master_password='rep',master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.35 sec)
 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.75.86
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 373
        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: 157
              Relay_Log_Space: 573
              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: 863306
                  Master_UUID: 3ba436e0-aa7c-11ec-bba1-0050568a6bf6
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

3）登录主库 3306 查看

show slave hosts;

[mysql@Ops-11gOGG conf]$ mysql -uroot -p  -P 3306 -S /mysql/data/mysql3306/socket/mysql3306.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.28 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 slave hosts;
+-----------+-------------+------+-----------+--------------------------------------+
| Server_id | Host        | Port | Master_id | Slave_UUID                           |
+-----------+-------------+------+-----------+--------------------------------------+
|    863308 | 192.168.75.86 | 3308 |    863306 | efeadd1c-aa7c-11ec-a0e3-0050568a6bf6 |
|    863307 | 192.168.75.86 | 3307 |    863306 | e0d081ae-aa7c-11ec-9b41-0050568a6bf6 |
+-----------+-------------+------+-----------+--------------------------------------+
2 rows in set, 1 warning (0.00 sec)
 
mysql> show processlist;
+----+-----------------+-------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
| Id | User            | Host              | db   | Command          | Time | State                                                           | Info             |
+----+-----------------+-------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost         | NULL | Daemon           | 1467 | Waiting on empty queue                                          | NULL             |
| 11 | rep             | 192.168.75.86:50360 | NULL | Binlog Dump GTID |  232 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 12 | rep             | 192.168.75.86:50420 | NULL | Binlog Dump GTID |  109 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 13 | root            | localhost         | NULL | Query            |    0 | init                                                            | show processlist |
+----+-----------------+-------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

4）两从库分别修改参数限制只读模式

show variables like '%read_only%';
set global read_only=1;
set global super_read_only=1;

[mysql@Ops-11gOGG conf]$ mysql -uroot -p  -P 3308 -S /mysql/data/mysql3308/socket/mysql3308.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.28 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 master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |      544 |              |                  | 3ba436e0-aa7c-11ec-bba1-0050568a6bf6:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
 
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.02 sec)
 
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global super_read_only=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.01 sec)
 
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |      544 |              |                  | 3ba436e0-aa7c-11ec-bba1-0050568a6bf6:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，你关心谁就分享给谁，一起学习共同进步~~~

❤️ 欢迎关注我的公众号，来一起玩耍吧！！！

————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————