[root@mysql-node2 ~]# mysql -uroot -pMySQL@666
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 16
Server version: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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> desc mysql.slave_master_info;
+---------------------------------+-----------------+------+-----+---------+-------+
| Field                           | Type            | Null | Key | Default | Extra |
+---------------------------------+-----------------+------+-----+---------+-------+
| Number_of_lines                 | int unsigned    | NO   |     | NULL    |       |
| Master_log_name                 | text            | NO   |     | NULL    |       |
| Master_log_pos                  | bigint unsigned | NO   |     | NULL    |       |
| Host                            | varchar(255)    | YES  |     | NULL    |       |
| User_name                       | text            | YES  |     | NULL    |       |
| User_password                   | text            | YES  |     | NULL    |       |
| Port                            | int unsigned    | NO   |     | NULL    |       |
| Connect_retry                   | int unsigned    | NO   |     | NULL    |       |
| Enabled_ssl                     | tinyint(1)      | NO   |     | NULL    |       |
| Ssl_ca                          | text            | YES  |     | NULL    |       |
| Ssl_capath                      | text            | YES  |     | NULL    |       |
| Ssl_cert                        | text            | YES  |     | NULL    |       |
| Ssl_cipher                      | text            | YES  |     | NULL    |       |
| Ssl_key                         | text            | YES  |     | NULL    |       |
| Ssl_verify_server_cert          | tinyint(1)      | NO   |     | NULL    |       |
| Heartbeat                       | float           | NO   |     | NULL    |       |
| Bind                            | text            | YES  |     | NULL    |       |
| Ignored_server_ids              | text            | YES  |     | NULL    |       |
| Uuid                            | text            | YES  |     | NULL    |       |
| Retry_count                     | bigint unsigned | NO   |     | NULL    |       |
| Ssl_crl                         | text            | YES  |     | NULL    |       |
| Ssl_crlpath                     | text            | YES  |     | NULL    |       |
| Enabled_auto_position           | tinyint(1)      | NO   |     | NULL    |       |
| Channel_name                    | varchar(64)     | NO   | PRI | NULL    |       |
| Tls_version                     | text            | YES  |     | NULL    |       |
| Public_key_path                 | text            | YES  |     | NULL    |       |
| Get_public_key                  | tinyint(1)      | NO   |     | NULL    |       |
| Network_namespace               | text            | YES  |     | NULL    |       |
| Master_compression_algorithm    | varchar(64)     | NO   |     | NULL    |       |
| Master_zstd_compression_level   | int unsigned    | NO   |     | NULL    |       |
| Tls_ciphersuites                | text            | YES  |     | NULL    |       |
| Source_connection_auto_failover | tinyint(1)      | NO   |     | 0       |       |
| Gtid_only                       | tinyint(1)      | NO   |     | 0       |       |
+---------------------------------+-----------------+------+-----+---------+-------+
33 rows in set (0.00 sec)

mysql> desc mysql.slave_relay_log_info;
+----------------------------------------------+--------------------------------------+------+-----+---------+-------+
| Field                                        | Type                                 | Null | Key | Default | Extra |
+----------------------------------------------+--------------------------------------+------+-----+---------+-------+
| Number_of_lines                              | int unsigned                         | NO   |     | NULL    |       |
| Relay_log_name                               | text                                 | YES  |     | NULL    |       |
| Relay_log_pos                                | bigint unsigned                      | YES  |     | NULL    |       |
| Master_log_name                              | text                                 | YES  |     | NULL    |       |
| Master_log_pos                               | bigint unsigned                      | YES  |     | NULL    |       |
| Sql_delay                                    | int                                  | YES  |     | NULL    |       |
| Number_of_workers                            | int unsigned                         | YES  |     | NULL    |       |
| Id                                           | int unsigned                         | YES  |     | NULL    |       |
| Channel_name                                 | varchar(64)                          | NO   | PRI | NULL    |       |
| Privilege_checks_username                    | varchar(32)                          | YES  |     | NULL    |       |
| Privilege_checks_hostname                    | varchar(255)                         | YES  |     | NULL    |       |
| Require_row_format                           | tinyint(1)                           | NO   |     | NULL    |       |
| Require_table_primary_key_check              | enum('STREAM','ON','OFF','GENERATE') | NO   |     | STREAM  |       |
| Assign_gtids_to_anonymous_transactions_type  | enum('OFF','LOCAL','UUID')           | NO   |     | OFF     |       |
| Assign_gtids_to_anonymous_transactions_value | text                                 | YES  |     | NULL    |       |
+----------------------------------------------+--------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

mysql> select * from slave_master_info;
+-----------------+-----------------+----------------+----------------+-----------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+------+-------------+---------+-------------+-----------------------+--------------+-------------+-----------------+----------------+-------------------+------------------------------+-------------------------------+------------------+---------------------------------+-----------+
| Number_of_lines | Master_log_name | Master_log_pos | Host           | User_name | User_password | Port | Connect_retry | Enabled_ssl | Ssl_ca | Ssl_capath | Ssl_cert | Ssl_cipher | Ssl_key | Ssl_verify_server_cert | Heartbeat | Bind | Ignored_server_ids | Uuid | Retry_count | Ssl_crl | Ssl_crlpath | Enabled_auto_position | Channel_name | Tls_version | Public_key_path | Get_public_key | Network_namespace | Master_compression_algorithm | Master_zstd_compression_level | Tls_ciphersuites | Source_connection_auto_failover | Gtid_only |
+-----------------+-----------------+----------------+----------------+-----------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+------+-------------+---------+-------------+-----------------------+--------------+-------------+-----------------+----------------+-------------------+------------------------------+-------------------------------+------------------+---------------------------------+-----------+
|              33 | binlog.000005   |            157 | 192.168.88.101 | slave     | MySQL@666     | 3306 |            60 |           0 |        |            |          |            |         |                      0 |        30 |      | 0                  |      |       86400 |         |             |                     0 |              |             |                 |              0 |                   | uncompressed                 |                             3 | NULL             |                               0 |         0 |
+-----------------+-----------------+----------------+----------------+-----------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+------+-------------+---------+-------------+-----------------------+--------------+-------------+-----------------+----------------+-------------------+------------------------------+-------------------------------+------------------+---------------------------------+-----------+
1 row in set (0.01 sec)

mysql> select * from slave_relay_log_info;
+-----------------+-------------------------------------------+---------------+-----------------+----------------+-----------+-------------------+------+--------------+---------------------------+---------------------------+--------------------+---------------------------------+---------------------------------------------+----------------------------------------------+
| Number_of_lines | Relay_log_name                            | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id   | Channel_name | Privilege_checks_username | Privilege_checks_hostname | Require_row_format | Require_table_primary_key_check | Assign_gtids_to_anonymous_transactions_type | Assign_gtids_to_anonymous_transactions_value |
+-----------------+-------------------------------------------+---------------+-----------------+----------------+-----------+-------------------+------+--------------+---------------------------+---------------------------+--------------------+---------------------------------+---------------------------------------------+----------------------------------------------+
|              14 | /export/server/mysql/data/relaylog.000002 |          4608 | binlog.000005   |           4442 |         0 |                 4 |    1 |              | NULL                      | NULL                      |                  0 | STREAM                          | OFF                                         |                                              |
+-----------------+-------------------------------------------+---------------+-----------------+----------------+-----------+-------------------+------+--------------+---------------------------+---------------------------+--------------------+---------------------------------+---------------------------------------------+----------------------------------------------+
1 row in set (0.00 sec)

mysql>

dnf install vim wget rsync telnet net-tools -y

hostnamectl set-hostname mysql-node1
hostnamectl set-hostname mysql-node2
exit
su或者bash指令

[root@mysql-node1 ~]# cat /etc/NetworkManager/system-connections/ens160.nmconnection
[connection]
id=ens160
#uuid=39fc68b0-865c-3bd3-a42d-3e1998f8e2cd
type=ethernet
autoconnect-priority=-999
interface-name=ens160
timestamp=1752853607

[ethernet]

[ipv4]
#method=auto
method=manual
addresses=192.168.88.101/24
gateway=192.168.88.2
dns=192.168.88.2,114.114.114.114;

[ipv6]
#addr-gen-mode=eui64
#method=auto
method=disabled

[proxy]
[root@mysql-node1 ~]#

[root@mysql-node1 ~]# vim /etc/hosts
在尾部追加如下内容，mysql-node2也配置同样的参数
192.168.88.101   mysql-node1   node1
192.168.88.102   mysql-node2   node2

sed  -i -r 's/SELINUX=[ep].*/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
systemctl stop firewalld &> /dev/null
systemctl disable firewalld &> /dev/null
iptables -F
iptables -t nat -F
iptables -P INPUT ACCEPT
iptables -P FORWARD ACCEPT

dnf -y install chrony
systemctl enable chronyd --now

#!/bin/bash

if rpm -q libaio &> /dev/null; then
    echo "libaio已安装，跳过安装"
else
    echo "开始安装依赖"
    dnf -y install libaio &> /dev/null
    if [ $? -ne 0 ];then
        echo "libaio安装失败"
        exit 1
    fi
fi

echo "进行解压操作"
if ls -l mysql-8.0.43-linux-glibc2.28-x86_64 &> /dev/null; then
    echo "已解压，跳过"
else
    if [ -f mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz ]; then
        tar -xf mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz
        ls -l mysql-8.0.43-linux-glibc2.28-x86_64
    fi
fi

echo "判断是否安装过MariaDB，进行清理"
rpm -qa | grep mariadb | xargs -r dnf remove -y
if [ -f /etc/my.cnf ]; then
        rm -rf /etc/my.cnf
fi

id mysql &> /dev/null
[ $? -ne 0 ] && useradd -r -s /sbin/nologin mysql

rm -rf /export/server
mkdir -p /export/server
cp -r mysql-8.0.43-linux-glibc2.28-x86_64 /export/server/mysql
chown -R mysql:mysql /export/server/mysql

echo "正在进入mysql目录，对其进行初始化操作..."
cd /export/server/mysql
bin/mysqld --initialize --user=mysql --basedir=/export/server/mysql --datadir=/export/server/mysql/data 2>&1 | tee /tmp/mysqld.log | grep password | awk '{print $NF}' > /tmp/mysql_temp_password.txt

bin/mysql_ssl_rsa_setup --datadir=/export/server/mysql/data &> /dev/null

cat >/etc/my.cnf<<EOF
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
EOF

cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server
After=network.target

[Service]
User=mysql
Group=mysql
Type=forking

# MySQL 执行命令及路径
ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid-file=/export/server/mysql/data/mysqld.pid
ExecStop=/export/server/mysql/bin/mysqladmin --defaults-file=/export/server/mysql/my.cnf shutdown

# Ensure MySQL has sufficient time to start up
TimeoutSec=600

# PID 文件路径
PIDFile=/export/server/mysql/data/mysqld.pid

# Enable these options to auto-restart the service if it crashes
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

echo "正在刷新后台服务，然后启动mysqld..."
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

#10.重置mysql管理员密码为MySQL@666
echo "正在重置mysql管理员密码..."
cd /export/server/mysql
temp_password=`cat /tmp/mysql_temp_password.txt`
bin/mysqladmin -uroot password 'MySQL@666' -p"$temp_password"

echo 'export PATH=$PATH:/export/server/mysql/bin' >> /etc/profile
source /etc/profile
mysql -V
echo "MySQL8安装成功，安装路径：/export/server/mysql，数据库初始密码：MySQL@666"

#!/bin/bash
# 作用：快速安装MySQL8.0
# 作者：传棋(Jaing)
# 日期：2025年12月11日
# 邮箱：Jaking@vip.163.com

sed  -i -r 's/SELINUX=[ep].*/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
systemctl stop firewalld &> /dev/null
systemctl disable firewalld &> /dev/null
iptables -F
iptables -t nat -F
iptables -P INPUT ACCEPT
iptables -P FORWARD ACCEPT

if rpm -q libaio &> /dev/null; then
    echo "libaio已安装，跳过安装"
else
    echo "开始安装依赖"
    dnf -y install libaio &> /dev/null
    if [ $? -ne 0 ];then
        echo "libaio安装失败"
        exit 1
    fi
fi

echo "进行解压操作"
if ls -l mysql-8.0.43-linux-glibc2.28-x86_64 &> /dev/null; then
    echo "已解压，跳过"
else
    if [ -f mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz ]; then
        tar -xf mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz
        ls -l mysql-8.0.43-linux-glibc2.28-x86_64
    fi
fi

echo "判断是否安装过MariaDB，进行清理"
rpm -qa | grep mariadb | xargs -r dnf remove -y
if [ -f /etc/my.cnf ]; then
        rm -rf /etc/my.cnf
fi

id mysql &> /dev/null
[ $? -ne 0 ] && useradd -r -s /sbin/nologin mysql

rm -rf /export/server
mkdir -p /export/server
cp -r mysql-8.0.43-linux-glibc2.28-x86_64 /export/server/mysql
chown -R mysql:mysql /export/server/mysql

echo "正在进入mysql目录，对其进行初始化操作..."
cd /export/server/mysql
bin/mysqld --initialize --user=mysql --basedir=/export/server/mysql --datadir=/export/server/mysql/data 2>&1 | tee /tmp/mysqld.log | grep password | awk '{print $NF}' > /tmp/mysql_temp_password.txt

bin/mysql_ssl_rsa_setup --datadir=/export/server/mysql/data &> /dev/null

cat >/etc/my.cnf<<EOF
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
EOF

cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server
After=network.target

[Service]
User=mysql
Group=mysql
Type=forking

# MySQL 执行命令及路径
ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid-file=/export/server/mysql/data/mysqld.pid
ExecStop=/export/server/mysql/bin/mysqladmin --defaults-file=/export/server/mysql/my.cnf shutdown

# Ensure MySQL has sufficient time to start up
TimeoutSec=600

# PID 文件路径
PIDFile=/export/server/mysql/data/mysqld.pid

# Enable these options to auto-restart the service if it crashes
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

echo "正在刷新后台服务，然后启动mysqld..."
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

#10.重置mysql管理员密码为MySQL@666
echo "正在重置mysql管理员密码..."
cd /export/server/mysql
temp_password=`cat /tmp/mysql_temp_password.txt`
bin/mysqladmin -uroot password 'MySQL@666' -p"$temp_password"

echo 'export PATH=$PATH:/export/server/mysql/bin' >> /etc/profile
source /etc/profile

echo "MySQL8安装成功，安装路径：/export/server/mysql，数据库初始密码：MySQL@666"
echo "执行 source /etc/profile 获取最新环境变量"
echo "执行 mysql -uroot -pMySQL@666 登录MySQL"

systemctl stop mysqld
pkill mysqld
rm -rf /export/server/mysql/data/*
rm -rf /tmp/mysqld.log
/export/server/mysql/bin/mysqld --initialize --user=mysql --basedir=/export/server/mysql &>/tmp/mysqld.log
grep password /tmp/mysqld.log | awk '{print $NF}'

mysqld --user=mysql --skip-grant-tables --skip-networking &

mysql

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@666';
FLUSH PRIVILEGES;
EXIT;

pkill mysqld
systemctl start mysqld
systemctl status mysqld --no-pager

mysql -uroot -p'MySQL@666'

show databases;

cat >/etc/my.cnf<<EOF
[mysqld]
# 基础路径
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306

# 字符集配置
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

# 错误日志
log-error=/export/server/mysql/master.err

# 二进制日志，用于主从复制 & 恢复
log-bin=/export/server/mysql/data/binlog
server-id=101
binlog_format=ROW
expire_logs_days=7
sync_binlog=1

# 跳过同步的系统库
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# InnoDB 引擎优化
default_storage_engine=InnoDB
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=1 # 主库一般为1，每次事务提交都写日志并立刻刷盘
innodb_file_per_table=1 # 每个表用一个独立的 .ibd 文件

# 连接配置
max_connections=500
max_connect_errors=1000000
table_open_cache=2000

# 慢查询日志
slow_query_log=1
slow_query_log_file=/export/server/mysql/slow.log
long_query_time=1

[client]
socket=/tmp/mysql.sock
default-character-set=utf8mb4
EOF

cat >/etc/my.cnf<<EOF
# ==============================================
# MySQL 主库核心配置文件（my.cnf）
# 适用于 MySQL 5.7 / 8.0 主从架构主节点
# 配置路径：/etc/my.cnf
# ==============================================

[mysqld]
# --------------------------
# 1. 基础路径与端口配置
# --------------------------
# MySQL 安装根目录
basedir=/export/server/mysql
# MySQL 数据文件存放目录（库表数据都在这里）
datadir=/export/server/mysql/data
# MySQL 本地套接字文件（本地连接使用）
socket=/tmp/mysql.sock
# MySQL 服务监听端口（默认 3306）
port=3306

# --------------------------
# 2. 字符集配置
# --------------------------
# 服务器默认字符集（utf8mb4 支持完整 emoji，是 utf8 超集）
character_set_server=utf8mb4
# 服务器默认排序规则（与字符集对应）
collation-server=utf8mb4_unicode_ci

# --------------------------
# 3. 错误日志配置
# --------------------------
# MySQL 错误日志路径（启动失败、运行异常都会记录在这里）
log-error=/export/server/mysql/master.err

# --------------------------
# 4. 二进制日志（binlog）核心配置（主从复制必备）
# --------------------------
# 开启 binlog 并指定文件前缀（binlog 记录所有数据修改操作）
log-bin=/export/server/mysql/data/binlog
# 服务器唯一 ID（主从架构中必须唯一，主库建议 101，从库 102/103...）
server-id=101
# binlog 记录格式：ROW（行模式，只记录行数据变化，主从最安全）
binlog_format=ROW
# binlog 自动清理天数（7 天自动删除，避免磁盘爆满）
expire_logs_days=7
# 每次事务提交都立即同步 binlog 到磁盘（数据最安全，主库必开）
sync_binlog=1

# --------------------------
# 5. 忽略同步的系统库（不写入 binlog，不从库同步）
# --------------------------
# 忽略系统库 information_schema
binlog-ignore-db=information_schema
# 忽略系统库 mysql（用户权限库）
binlog-ignore-db=mysql
# 忽略系统库 performance_schema
binlog-ignore-db=performance_schema
# 忽略系统库 sys
binlog-ignore-db=sys

# --------------------------
# 6. InnoDB 存储引擎优化（生产环境核心配置）
# --------------------------
# 默认存储引擎设置为 InnoDB
default_storage_engine=InnoDB
# InnoDB 缓冲池大小（建议设置为物理内存的 50%~70%，这里 1G 适合测试/小内存机器）
innodb_buffer_pool_size=1G
# InnoDB 重做日志文件大小（提高写入性能，256M 是通用优化值）
innodb_log_file_size=256M
# InnoDB 日志缓冲区大小（64M 适合中等写入量）
innodb_log_buffer_size=64M
# 事务日志刷盘策略：1 = 每次事务提交都刷盘（数据绝对安全，主库必须用 1）
innodb_flush_log_at_trx_commit=1
# 开启独立表空间（每个表单独一个 .ibd 文件，方便维护、回收空间）
innodb_file_per_table=1

# --------------------------
# 7. 连接与并发配置
# --------------------------
# MySQL 最大同时连接数（支持 500 个并发连接）
max_connections=500
# 最大连接错误次数（防止暴力破解，设大值避免正常连接被拦截）
max_connect_errors=1000000
# 打开表的缓存数量（提升多表操作性能）
table_open_cache=2000

# --------------------------
# 8. 慢查询日志（SQL 性能优化必备）
# --------------------------
# 开启慢查询日志
slow_query_log=1
# 慢查询日志存放路径
slow_query_log_file=/export/server/mysql/slow.log
# 慢查询阈值：执行时间超过 1 秒的 SQL 会被记录
long_query_time=1

# --------------------------
# 客户端连接默认配置
# --------------------------
[client]
# 客户端连接使用的套接字文件（与服务端保持一致）
socket=/tmp/mysql.sock
# 客户端默认字符集
default-character-set=utf8mb4
EOF

# 创建日志文件
touch /export/server/mysql/master.err

# 修改权限
chown -Rf mysql:mysql /export/server/mysql/

# 重启服务
systemctl restart mysqld

# 查看服务状态
systemctl status mysqld --no-pager

cat >/etc/my.cnf<<EOF
[mysqld]
# 基础路径
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306

# 字符集
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

# 错误日志
log-error=/export/server/mysql/slave.err

# 开启 binlog（从库也可以作为其他从库的主库）
# log-bin=/export/server/mysql/data/binlog

# relay log
relay-log=/export/server/mysql/data/relaylog
relay-log-index=/export/server/mysql/data/relaylog.index
read-only=1
super-read-only=1
skip-slave-start=1   # 避免宕机重启自动拉起复制线程，需手动控制

# 复制过滤（避免同步系统库）
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

# server-id 必须唯一
server-id=102

# InnoDB 参数（从库查询压力可能更大）
default_storage_engine=InnoDB
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=2   # 从库一般可设为 2，提高复制性能；每次事务提交，写日志，但不立即刷盘
innodb_file_per_table=1 # 每个表用一个独立的 .ibd 文件

# 连接配置
max_connections=500
table_open_cache=2000
max_connect_errors=1000000

# 慢查询日志（从库跑报表时有用）
slow_query_log=1
slow_query_log_file=/export/server/mysql/slow.log
long_query_time=1

# 复制优化
relay_log_recovery=1   # 防止 relay log 损坏导致复制中断
sync_relay_log=0       # 提高复制性能
sync_relay_log_info=0
sync_master_info=0

[client]
socket=/tmp/mysql.sock
default-character-set=utf8mb4
EOF

cat >/etc/my.cnf<<EOF
# ==============================================
# MySQL 从库核心配置文件（my.cnf）
# 适用于 MySQL 5.7 / 8.0 主从架构从节点
# 配置路径：/etc/my.cnf
# ==============================================

[mysqld]
# --------------------------
# 1. 基础路径与端口配置
# --------------------------
# MySQL 安装根目录
basedir=/export/server/mysql
# MySQL 数据文件存放目录
datadir=/export/server/mysql/data
# MySQL 本地套接字文件（本地连接使用）
socket=/tmp/mysql.sock
# MySQL 服务监听端口（默认 3306）
port=3306

# --------------------------
# 2. 字符集配置
# --------------------------
# 服务器默认字符集（utf8mb4 支持完整 emoji，是 utf8 超集）
character_set_server=utf8mb4
# 服务器默认排序规则（与字符集对应）
collation-server=utf8mb4_unicode_ci

# --------------------------
# 3. 错误日志配置
# --------------------------
# MySQL 错误日志路径（从节点专用日志名）
log-error=/export/server/mysql/slave.err

# --------------------------
# 4. 二进制日志（binlog）配置
# --------------------------
# 从库默认关闭 binlog（注释状态）
# 开启后从库可作为其他从库的主库（级联复制）
# log-bin=/export/server/mysql/data/binlog

# --------------------------
# 5. 从库复制核心配置（relay log 中继日志）
# --------------------------
# 中继日志文件路径（从库接收主库 binlog 后写入这里，再重放到本地）
relay-log=/export/server/mysql/data/relaylog
# 中继日志索引文件（记录所有 relay log 文件名）
relay-log-index=/export/server/mysql/data/relaylog.index
# 开启普通用户只读（禁止普通用户写入数据，保证从库数据一致性）
read-only=1
# 开启超级用户（root）只读（强制所有用户不可写，生产从库必备）
super-read-only=1
# 开机不自动启动复制线程
# 避免宕机重启后立即同步，需人工检查无误后手动启动复制
skip-slave-start=1

# --------------------------
# 6. 复制过滤规则（忽略系统库，不进行主从同步）
# --------------------------
# 忽略同步系统库 information_schema
replicate-ignore-db=information_schema
# 忽略同步系统库 mysql（用户权限库）
replicate-ignore-db=mysql
# 忽略同步系统库 performance_schema
replicate-ignore-db=performance_schema
# 忽略同步系统库 sys
replicate-ignore-db=sys

# --------------------------
# 7. 服务器唯一 ID（主从架构必须唯一）
# --------------------------
# 从库 ID（主库 101，从库建议 102/103...）
server-id=102

# --------------------------
# 8. InnoDB 存储引擎优化（从库专用）
# --------------------------
# 默认存储引擎设置为 InnoDB
default_storage_engine=InnoDB
# InnoDB 缓冲池大小（缓存数据+索引，提升查询性能）
innodb_buffer_pool_size=1G
# InnoDB 重做日志文件大小
innodb_log_file_size=256M
# InnoDB 日志缓冲区大小
innodb_log_buffer_size=64M
# 事务日志刷盘策略：2 = 每秒刷盘一次
# 从库专用配置，提升主从复制性能，数据安全性略低于 1
innodb_flush_log_at_trx_commit=2
# 开启独立表空间（每个表单独一个 .ibd 文件）
innodb_file_per_table=1

# --------------------------
# 9. 连接与并发配置
# --------------------------
# MySQL 最大同时连接数
max_connections=500
# 打开表的缓存数量（提升多表查询性能）
table_open_cache=2000
# 最大连接错误次数（防止暴力破解）
max_connect_errors=1000000

# --------------------------
# 10. 慢查询日志（从库报表/查询优化必备）
# --------------------------
# 开启慢查询日志
slow_query_log=1
# 慢查询日志存放路径
slow_query_log_file=/export/server/mysql/slow.log
# 慢查询阈值：执行时间超过 1 秒的 SQL 会被记录
long_query_time=1

# --------------------------
# 11. 主从复制性能优化（从库专用）
# --------------------------
# 中继日志自动恢复
#  relay log 损坏时自动修复，防止复制中断
relay_log_recovery=1
# 关闭 relay log 实时刷盘（提高写入性能）
sync_relay_log=0
# 关闭 relay-log-info 实时刷盘
sync_relay_log_info=0
# 关闭 master-info 实时刷盘
sync_master_info=0

# --------------------------
# 客户端连接默认配置
# --------------------------
[client]
# 客户端连接使用的套接字文件（与服务端保持一致）
socket=/tmp/mysql.sock
# 客户端默认字符集
default-character-set=utf8mb4
EOF

# 创建日志文件
touch /export/server/mysql/slave.err

# 修改权限
chown -Rf mysql:mysql /export/server/mysql/

# 重启服务
systemctl restart mysqld

# 查看服务状态
systemctl status mysqld --no-pager

mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'MySQL@666';
[root@mysql-node1 ~]# mysql -uroot -p'MySQL@666'
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'MySQL@666';
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
mysql> flush privileges;

权限的目的是为了读取二进制日志以及拉取二进制中的数据信息
REPLICATION SLAVE        让 Slave 节点连接 Master 并读取 binlog，实现数据同步。
REPLICATION CLIENT       查看复制状态信息（如 binlog 位置、延迟），用于监控和诊断。
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql -uslave -p'MySQL@666' -h192.168.88.101

[root@mysql-node1 ~]# mysql -uroot -p'MySQL@666'
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 24
Server version: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 'slave'@'mysql-node2' IDENTIFIED BY 'MySQL@666';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'mysql-node2';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 1、停止 master服务器 上的 MySQL 服务
systemctl stop mysqld

# 2、在 master 上执行 rsync 同步 data 目录（可选）
rsync -av --delete /export/server/mysql/data/ mysql-node2:/export/server/mysql/data/

# 3、分别在 master 和 slave 删除 auto.cnf 文件
rm -f /export/server/mysql/data/auto.cnf
【注释】
1、auto.cnf文件里保存的是每个数据库实例的UUID信息，代表数据库的唯一标识。
    所以这里删除掉，保证 slave 和 master 的 uuid 不会重复。

2、rsync 是指 remote sync，远程同步
    -a：即 --archive(音，阿凯伍)，表示以归档模式传输文件，递归并保留几乎所有文件属性（如权限、时间戳、属主、链接等）。
    -v：即 --verbose，启用详细输出模式

systemctl restart mysqld
systemctl status mysqld --no-pager
mysql --version

systemctl restart mysqld
systemctl status mysqld --no-pager
mysql --version

[root@mysql-node1 ~]# mysql -uroot -p'MySQL@666'
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql>

获取 read lock 全局读锁，即锁住表，只让读
    阻止所有写操作：其他会话无法执行 INSERT、UPDATE、DELETE 等修改数据的语句。
    允许读操作：其他会话仍可执行 SELECT 查询
   
补充：
-- 解锁当前会话持有的所有锁UNLOCK TABLES;
UNLOCK TABLES;

[root@mysql-node1 ~]# mysql -uroot -p'MySQL@666'
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

# 在 slave 从节点上执行
[root@mysql-node2 ~]# mysql -uroot -p'MySQL@666'
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 REPLICATION SOURCE TO 
  SOURCE_HOST='192.168.88.101',    -- 注意，这里是master主库地址
  SOURCE_USER='slave',
  SOURCE_PASSWORD='MySQL@666',
  SOURCE_LOG_FILE='binlog.000003',
  SOURCE_LOG_POS=157;

mysql> start slave;
mysql> show slave status\G
        ......
        Slave_IO_Running: Yes       代表成功连接到master并且下载日志
        Slave_SQL_Running: Yes      代表成功执行日志中的SQL语句
        Seconds_Behind_Master: 0    代表主从延迟的秒数，如果为0，代表状态最佳，主从没有延迟
        
;与\G都代表SQL的结尾，有所不同在于分号是横向展示，而\G把每一列纵向显示，适合大数据展示！

mysql> unlock tables;

create database if not exists db_itheima;
use db_itheima;

create table if not exists  students(
        id int primary key,
        name varchar(20)
) default charset=utf8;

insert into students values (1, 'Tom');
insert into students values (2, 'Rose');

select * from students;

show databases;
use db_itheima;
show tables;
select * from students;

CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show databases;
use bookdb666;

CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门到精通》','66','电子工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');

show tables;
select * from book666;

/etc/my.cnf 中配置的编码不对应
要么
    character_set_server=utf8mb4
    collation-server=utf8mb4_unicode_ci
要么
    character_set_server=utf8
    collation-server=utf8_unicode_ci

但是不能
    character_set_server=utf8mb4
    collation-server=utf8_unicode_ci

同步错误了
把
  rsync -va /export/server/mysql/data/ mysql-node2:/export/server/mysql/
改成
  rsync -va /export/server/mysql/data mysql-node2:/export/server/mysql/

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

systemctl stop firewalld
systemctl disable firewalld
iptables -F
iptables -t nat -F

-- 1. 停止复制
STOP REPLICA;
或者
stop slave;

-- 2. 完全重置复制状态（MySQL 8.0+）
RESET REPLICA ALL;  -- 注意：这会清除所有复制配置
或者
reset slave all;

-- 3. change replication source to
 CHANGE REPLICATION SOURCE TO 
  SOURCE_HOST='192.168.88.101',    -- 注意，这里是master主库地址
  SOURCE_USER='slave',
  SOURCE_PASSWORD='MySQL@666', -- 以实际为准
  SOURCE_LOG_FILE='binlog.000003', -- 以实际为准
  SOURCE_LOG_POS=157; -- 以实际为准
  
-- 4. 启动
start slave;

dnf install vim wget rsync telnet net-tools -y

hostnamectl set-hostname mysql-node1
hostnamectl set-hostname mysql-node2

su或者bash指令

[root@mysql-node1 ~]# cat /etc/NetworkManager/system-connections/ens160.nmconnection
[connection]
id=ens160
#uuid=39fc68b0-865c-3bd3-a42d-3e1998f8e2cd
type=ethernet
autoconnect-priority=-999
interface-name=ens160
timestamp=1752853607

[ethernet]

[ipv4]
#method=auto
method=manual
addresses=192.168.88.101/24
gateway=192.168.88.2
dns=192.168.88.2,114.114.114.114;

[ipv6]
#addr-gen-mode=eui64
#method=auto
method=disabled

[proxy]
[root@mysql-node1 ~]#

[root@mysql-node1 ~]# vim /etc/hosts
在尾部追加如下内容，mysql-node2也配置同样的参数
192.168.88.101   mysql-node1   node1
192.168.88.102   mysql-node2   node2

sed  -i -r 's/SELINUX=[ep].*/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
systemctl stop firewalld &> /dev/null
systemctl disable firewalld &> /dev/null
iptables -F
iptables -t nat -F
iptables -P INPUT ACCEPT
iptables -P FORWARD ACCEPT

dnf -y install chrony
systemctl enable chronyd --now

#!/bin/bash

if rpm -q libaio &> /dev/null; then
    echo "libaio已安装，跳过安装"
else
    echo "开始安装依赖"
    dnf -y install libaio &> /dev/null
    if [ $? -ne 0 ];then
        echo "libaio安装失败"
        exit 1
    fi
fi

echo "进行解压操作"
if ls -l mysql-8.0.43-linux-glibc2.28-x86_64 &> /dev/null; then
    echo "已解压，跳过"
else
    if [ -f mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz ]; then
        tar -xf mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz
        ls -l mysql-8.0.43-linux-glibc2.28-x86_64
    fi
fi

echo "判断是否安装过MariaDB，进行清理"
rpm -qa | grep mariadb | xargs -r dnf remove -y
if [ -f /etc/my.cnf ]; then
        rm -rf /etc/my.cnf
fi

id mysql &> /dev/null
[ $? -ne 0 ] && useradd -r -s /sbin/nologin mysql

rm -rf /export/server
mkdir -p /export/server
cp -r mysql-8.0.43-linux-glibc2.28-x86_64 /export/server/mysql
chown -R mysql:mysql /export/server/mysql

echo "正在进入mysql目录，对其进行初始化操作..."
cd /export/server/mysql
bin/mysqld --initialize --user=mysql --basedir=/export/server/mysql --datadir=/export/server/mysql/data 2>&1 | tee /tmp/mysqld.log | grep password | awk '{print $NF}' > /tmp/mysql_temp_password.txt

bin/mysql_ssl_rsa_setup --datadir=/export/server/mysql/data &> /dev/null

cat >/etc/my.cnf<<EOF
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
EOF

cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server
After=network.target

[Service]
User=mysql
Group=mysql
Type=forking

# MySQL 执行命令及路径
ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid-file=/export/server/mysql/data/mysqld.pid
ExecStop=/export/server/mysql/bin/mysqladmin --defaults-file=/export/server/mysql/my.cnf shutdown

# Ensure MySQL has sufficient time to start up
TimeoutSec=600

# PID 文件路径
PIDFile=/export/server/mysql/data/mysqld.pid

# Enable these options to auto-restart the service if it crashes
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

echo "正在刷新后台服务，然后启动mysqld..."
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

#10.重置mysql管理员密码为MySQL@666
echo "正在重置mysql管理员密码..."
cd /export/server/mysql
temp_password=`cat /tmp/mysql_temp_password.txt`
bin/mysqladmin -uroot password 'MySQL@666' -p"$temp_password"

echo 'export PATH=$PATH:/export/server/mysql/bin' >> /etc/profile
source /etc/profile
mysql -V
echo "MySQL8安装成功，安装路径：/export/server/mysql，数据库初始密码：MySQL@666"

cat >/etc/my.cnf<<EOF
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
EOF
systemctl stop mysqld
pkill mysqld

rm -rf /export/server/mysql/data/*
rm -rf /tmp/mysqld.log

/export/server/mysql/bin/mysqld --initialize --user=mysql --basedir=/export/server/mysql &>/tmp/mysqld.log
grep password /tmp/mysqld.log | awk '{print $NF}'

systemctl start mysqld
mysql -uroot -p'初始密码'

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@666';
FLUSH PRIVILEGES;
EXIT;

mysql -uroot -p'MySQL@666'

注意：如果没有拿到初始密码，请按照以下方法来设置密码！

systemctl stop mysqld
pkill mysqld

mysqld --user=mysql --skip-grant-tables --skip-networking &

mysql

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@666';
FLUSH PRIVILEGES;
EXIT;

pkill mysqld
systemctl start mysqld
systemctl status mysqld --no-pager

mysql -uroot -p'MySQL@666'

[root@mysql-node2 ~]# grep read /etc/my.cnf
read-only=1
super-read-only=1
[root@mysql-node2 ~]# vim /etc/my.cnf
[root@mysql-node2 ~]# cat /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306

character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
[root@mysql-node2 ~]# systemctl stop mysqld
[root@mysql-node2 ~]# pkill mysqld
[root@mysql-node2 ~]# mysqld --user=mysql --skip-grant-tables --skip-networking &
[1] 39313
[root@mysql-node2 ~]# 2025-11-27T08:03:19.304049Z 0 [System] [MY-010116] [Server] /export/server/mysql/bin/mysqld (mysqld 8.0.43) starting as process 39313
2025-11-27T08:03:19.318454Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-11-27T08:03:19.499254Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-11-27T08:03:19.854084Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2025-11-27T08:03:19.854749Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2025-11-27T08:03:19.880081Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /tmp/mysqlx.sock
2025-11-27T08:03:19.880607Z 0 [System] [MY-010931] [Server] /export/server/mysql/bin/mysqld: ready for connections. Version: '8.0.43'  socket: '/tmp/mysql.sock'  port: 0  MySQL Community Server - GPL.
[root@mysql-node2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@666';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> EXIT;
Bye
[root@mysql-node2 ~]# mysql -uroot -pMySQL@666
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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> ^DBye
[root@mysql-node2 ~]#

# 1、停止 master服务器 上的 MySQL 服务（可选）
systemctl stop mysqld

# 2、在 master 上执行 rsync 同步 data 目录（可选）
rsync -av --delete /export/server/mysql/data/ mysql-node2:/export/server/mysql/data/

# 3、分别在 master 和 slave 删除 auto.cnf 文件
rm -f /export/server/mysql/data/auto.cnf

cat >/etc/my.cnf<<EOF
[mysqld]
# 基础路径
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306

# 日志
log-error=/export/server/mysql/master.err
log-bin=/export/server/mysql/data/binlog
server-id=101

# 字符集
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

# GTID 配置
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
binlog_format=ROW
binlog_row_image=FULL
sync_binlog=1
expire_logs_days=7

# 复制过滤：不同步的系统库
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# InnoDB 优化
default_storage_engine=InnoDB
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1

# 连接配置
max_connections=500
max_connect_errors=1000000
table_open_cache=2000

# 慢查询日志
slow_query_log=1
slow_query_log_file=/export/server/mysql/slow.log
long_query_time=1

[client]
socket=/tmp/mysql.sock
default-character-set=utf8mb4
EOF

cat >/etc/my.cnf<<EOF
# ==============================================
# MySQL 核心配置文件 /etc/my.cnf
# 作用：定义 MySQL 服务启动、运行、存储、复制、优化等所有规则
# ==============================================

[mysqld]
# --------------------------
# 1. 基础路径与端口配置
# --------------------------
# MySQL 安装根目录
basedir=/export/server/mysql
# MySQL 数据存放目录（库表文件都在这里）
datadir=/export/server/mysql/data
# MySQL 本地连接套接字文件（Linux 本地快速连接用）
socket=/tmp/mysql.sock
# MySQL 对外服务端口（默认 3306）
port=3306

# --------------------------
# 2. 日志与主从复制基础配置
# --------------------------
# MySQL 错误日志路径（启动失败、运行异常都在这里看）
log-error=/export/server/mysql/master.err
# 开启二进制日志 binlog，用于主从复制 + 数据恢复
log-bin=/export/server/mysql/data/binlog
# 服务器唯一 ID（主从架构必须不同，不能重复）
server-id=101

# --------------------------
# 3. 字符集配置（支持表情符号）
# --------------------------
# 服务端默认字符集：utf8mb4 完整支持 emoji/特殊符号
character_set_server=utf8mb4
# 排序规则（统一字符集排序）
collation-server=utf8mb4_unicode_ci

# --------------------------
# 4. GTID 主从复制高级配置（高可用必备）
# --------------------------
# 开启 GTID 模式（自动管理主从复制位置，不用手动找点位）
gtid_mode=ON
# 强制 GTID 一致性（保证主从数据绝对一致）
enforce_gtid_consistency=ON
# 从库也记录 binlog（级联复制/双主架构必须开启）
log_slave_updates=ON
# binlog 格式：ROW 行模式（只记录变更行，主从最安全）
binlog_format=ROW
# 记录完整行数据（更新前后都记录，数据恢复最完整）
binlog_row_image=FULL
# 每次事务都刷写 binlog 到磁盘（数据最安全，不丢日志）
sync_binlog=1
# binlog 自动清理天数：7 天后自动删除，避免占满磁盘
expire_logs_days=7

# --------------------------
# 5. 复制过滤：不需要同步的系统库
# --------------------------
# 不同步 information_schema 系统库
binlog-ignore-db=information_schema
# 不同步 mysql 权限库
binlog-ignore-db=mysql
# 不同步 performance_schema 性能库
binlog-ignore-db=performance_schema
# 不同步 sys 系统库
binlog-ignore-db=sys

# --------------------------
# 6. InnoDB 存储引擎优化（核心性能配置）
# --------------------------
# 默认存储引擎：InnoDB（支持事务、外键、崩溃恢复）
default_storage_engine=InnoDB
# InnoDB 缓冲池大小（建议 = 服务器内存的 50%~70%）
innodb_buffer_pool_size=1G
# InnoDB 日志文件大小（越大刷盘越少，性能越好）
innodb_log_file_size=256M
# InnoDB 日志缓冲区
innodb_log_buffer_size=64M
# 每次事务提交都刷日志到磁盘（ACID 强一致，最安全）
innodb_flush_log_at_trx_commit=1
# 每个库一个独立表空间文件（方便维护、迁移、回收空间）
innodb_file_per_table=1

# --------------------------
# 7. 连接与并发配置
# --------------------------
# 最大同时连接数（支持多少客户端同时连 MySQL）
max_connections=500
# 最大允许错误连接次数（防暴力破解，设大一点避免误拦截）
max_connect_errors=1000000
# 打开表缓存数量（提升多表查询性能）
table_open_cache=2000

# --------------------------
# 8. 慢查询日志（SQL 优化必备）
# --------------------------
# 开启慢查询日志
slow_query_log=1
# 慢查询日志存放路径
slow_query_log_file=/export/server/mysql/slow.log
# 超过 1 秒的 SQL 会被记录（方便定位慢 SQL）
long_query_time=1

# --------------------------
# 客户端连接配置
# --------------------------
[client]
# 客户端本地连接使用的 socket 文件（和 mysqld 保持一致）
socket=/tmp/mysql.sock
# 客户端默认字符集
default-character-set=utf8mb4
EOF

cat >/etc/my.cnf<<EOF
[mysqld]
# 基础路径
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306

# 日志
log-error=/export/server/mysql/slave.err
log-bin=/export/server/mysql/data/binlog
relay-log=/export/server/mysql/data/relaylog
relay-log-index=/export/server/mysql/data/relaylog.index
server-id=102

# 字符集
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

# GTID 配置
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
binlog_format=ROW
binlog_row_image=FULL
sync_binlog=1
expire_logs_days=7

# 复制过滤：不同步的系统库
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

# 从库只读
read_only=ON
super_read_only=ON
skip-slave-start=1   # 避免重启时自动启动复制线程，需手动控制

# 复制优化
relay_log_recovery=1
sync_relay_log=0
sync_relay_log_info=0
sync_master_info=0

# InnoDB 优化（适合从库，查询压力大）
default_storage_engine=InnoDB
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=2   # 从库可设置为 2，提高复制与查询性能
innodb_file_per_table=1

# 连接配置
max_connections=500
max_connect_errors=1000000
table_open_cache=2000

# 慢查询日志（从库常用于报表分析）
slow_query_log=1
slow_query_log_file=/export/server/mysql/slow.log
long_query_time=1

[client]
socket=/tmp/mysql.sock
default-character-set=utf8mb4
EOF

cat >/etc/my.cnf<<EOF
[mysqld]
# ==========================
# 基础路径配置
# ==========================
# MySQL 安装根目录
basedir=/export/server/mysql
# 数据文件存储目录
datadir=/export/server/mysql/data
# 本地连接套接字文件
socket=/tmp/mysql.sock
# 服务端口
port=3306

# ==========================
# 日志与主从复制标识
# ==========================
# 从库错误日志
log-error=/export/server/mysql/slave.err
# 开启二进制日志（从库也建议开启，用于级联复制/恢复）
log-bin=/export/server/mysql/data/binlog
# 中继日志：从库接收主库 binlog 后写入的日志（复制核心文件）
relay-log=/export/server/mysql/data/relaylog
# 中继日志索引文件
relay-log-index=/export/server/mysql/data/relaylog.index
# 服务器唯一 ID，主从架构必须唯一，主库 101，从库 102
server-id=102

# ==========================
# 字符集配置
# ==========================
# 服务端默认字符集，支持表情符号
character_set_server=utf8mb4
# 排序规则
collation-server=utf8mb4_unicode_ci

# ==========================
# GTID 主从复制配置（高可用必备）
# ==========================
# 开启 GTID 模式，自动管理复制位置
gtid_mode=ON
# 强制 GTID 一致性，保证主从不丢数据
enforce_gtid_consistency=ON
# 从库接收主库日志后，也写入自己的 binlog（级联复制必备）
log_slave_updates=ON
# binlog 格式：行模式，主从最安全
binlog_format=ROW
# 记录完整行数据，便于数据恢复
binlog_row_image=FULL
# 每次事务刷写 binlog 到磁盘，保证日志安全
sync_binlog=1
# 二进制日志自动清理周期 7 天
expire_logs_days=7

# ==========================
# 复制过滤：从库不同步的系统库
# ==========================
# 不同步系统库 information_schema
replicate-ignore-db=information_schema
# 不同步权限库 mysql
replicate-ignore-db=mysql
# 不同步性能库 performance_schema
replicate-ignore-db=performance_schema
# 不同步系统库 sys
replicate-ignore-db=sys

# ==========================
# 从库安全配置（核心！）
# ==========================
# 普通用户只读，无法写入
read_only=ON
# 超级管理员也只读，彻底防止从库被误写入
super_read_only=ON
# 开机/重启 MySQL 时，不自动启动复制线程，必须手动启动，防止异常复制
skip-slave-start=1

# ==========================
# 从库复制性能优化
# ==========================
# 中继日志自动修复，崩溃后不损坏复制
relay_log_recovery=1
# 不实时刷盘中继日志，提升从库性能（从库专用优化）
sync_relay_log=0
# 不同步记录复制位置信息，提高性能
sync_relay_log_info=0
# 不同步记录主库信息，提高性能
sync_master_info=0

# ==========================
# InnoDB 存储引擎优化
# ==========================
# 默认存储引擎 InnoDB
default_storage_engine=InnoDB
# InnoDB 缓冲池，提升查询性能
innodb_buffer_pool_size=1G
# InnoDB 日志文件大小
innodb_log_file_size=256M
# InnoDB 日志缓冲区
innodb_log_buffer_size=64M
# 从库专用优化：每秒刷一次日志到磁盘，比主库更快，允许极少量丢失风险
innodb_flush_log_at_trx_commit=2
# 每个表独立表空间，方便维护
innodb_file_per_table=1

# ==========================
# 连接与并发配置
# ==========================
# 最大连接数
max_connections=500
# 最大错误连接数，防止被误拦截
max_connect_errors=1000000
# 打开表缓存，提升多表查询速度
table_open_cache=2000

# ==========================
# 慢查询日志（从库常用于查询/报表）
# ==========================
# 开启慢查询日志
slow_query_log=1
# 慢查询日志路径
slow_query_log_file=/export/server/mysql/slow.log
# 执行超过 1 秒的 SQL 记录为慢查询
long_query_time=1

# ==========================
# 客户端连接配置
# ==========================
[client]
# 客户端本地连接 socket
socket=/tmp/mysql.sock
# 客户端默认字符集
default-character-set=utf8mb4
EOF

# master 主服务器
touch /export/server/mysql/master.err
chown -Rf mysql:mysql /export/server/mysql
systemctl restart mysqld
systemctl status mysqld --no-pager

# slave 从服务器
touch /export/server/mysql/slave.err
chown -Rf mysql:mysql /export/server/mysql
systemctl restart mysqld
systemctl status mysqld --no-pager

mysql -uroot -p'MySQL@666'
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'MySQL@666';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
flush privileges;
[root@mysql-node1 ~]# mysql -uroot -p'MySQL@666'
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'MySQL@666';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>
[root@mysql-node2 ~]# mysql -uslave -pMySQL@666 -h192.168.88.101
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql>

在 slave 从库上执行的命令，语法参考
mysql> CHANGE REPLICATION SOURCE TO
          SOURCE_HOST = host,
          SOURCE_PORT = port,
          SOURCE_USER = user,
          SOURCE_PASSWORD = password,
          SOURCE_AUTO_POSITION = 1;

[root@mysql-node2 ~]# mysql -uroot -p'MySQL@666'
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 replication source to
  source_host='192.168.88.101',
  source_port=3306,
  source_user='slave',
  source_password='MySQL@666',
  source_auto_position=1;

在从库上执行，
mysql> start replica; # 这是新命令，start slave; 依然可用，只是未来版本可能会逐步废除。

在从库上执行，
mysql> show slave status\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
...

create database if not exists db_itheima;
show databases;
use db_itheima;

create table if not exists students(
        id int primary key,
        name varchar(20)
) default charset=utf8;

insert into students values (10, 'lisa');
insert into students values (11, 'Rose');

select * from students;
show databases;

mysql> show databases;
mysql> use db_itheima;
mysql> show tables;
mysql> select * from students;

mysql> select * from mysql.gtid_executed;

CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show databases;
use bookdb666;

CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门到精通》','66','电子工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');

show tables;
select * from book666;
show databases;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.88.101
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 1961
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 2171
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys
           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: 1961
              Relay_Log_Space: 2374
              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: 101
                  Master_UUID: 48da8bfd-9c4d-11f0-96cb-000c29377627
             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: 48da8bfd-9c4d-11f0-96cb-000c29377627:1-7
            Executed_Gtid_Set: 48da8bfd-9c4d-11f0-96cb-000c29377627:1-7
                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.00 sec)

mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 48da8bfd-9c4d-11f0-96cb-000c29377627 |              1 |            2 |
| 48da8bfd-9c4d-11f0-96cb-000c29377627 |              4 |            6 |
| 48da8bfd-9c4d-11f0-96cb-000c29377627 |              7 |            7 |
+--------------------------------------+----------------+--------------+
3 rows in set (0.00 sec)

mysql>

source_uuid:transaction_id

source_uuid                          interval_start  interval_end
48da8bfd-...                             1                2
48da8bfd-...                             4                6
48da8bfd-...                             7                7

48da8bfd-...:1-2,4-6,7

mysqlbinlog --base64-output=DECODE-ROWS -v /path/to/binlog.000004 | sed -n '1,200p'

SHOW REPLICA STATUS\G
SELECT @@GLOBAL.gtid_executed;
SELECT * FROM mysql.gtid_executed\G
SELECT @@server_uuid;

SHOW MASTER STATUS\G
SELECT @@server_uuid;
mysqlbinlog --base64-output=DECODE-ROWS -v /export/server/mysql/data/binlog.000004 | less
或者
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v /export/server/mysql/data/binlog.000004

create database db_itheima;
use db_itheima;

create table students(
        id int primary key,
        name varchar(20)
) default charset=utf8;

insert into students values (1, 'Tom');
insert into students values (2, 'Rose');

在从库中，
删除 read-only=on
然后重启 mysqld
systemctl restart mysqld 
在从库中，
use db_itheima;
insert into students values (3, 'Jack');

insert into students values (3, 'Jennifer');

show slave status\G

mysql> show slave status \G
...
Slave_IO_Running: Yes
Slave_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: 1146
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7' at master log mysql-bin.000011, end_log_pos 868. 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: 550
Relay_Log_Space: 1285
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: 1146
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7' at master log mysql-bin.000011, end_log_pos 868. 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: 1
Master_UUID: f1b88047-a5ea-11ed-8ee1-246e9657f7a0
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: 230227 14:53:19
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-7
Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-6
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)

Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-7
Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-6
事务接收了1-7，但7没有执行成功。f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7

mysql> create table test02_01(id int ,name varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into test02_01 values(1,'jkl');
Query OK, 1 row affected (0.00 sec)

mysql> show replica status\G
...
Slave_IO_Running: Yes
Slave_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: 1146
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7' at master log mysql-bin.000011, end_log_pos 868. 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: 550
Relay_Log_Space: 1851
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: 1146
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7' at master log mysql-bin.000011, end_log_pos 868. 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: 1
Master_UUID: f1b88047-a5ea-11ed-8ee1-246e9657f7a0
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: 230227 14:53:19
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-9
Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-6
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.00 sec)

Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-9
Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-6

mysql> STOP REPLICA;

mysql> SET @@SESSION.GTID_NEXT= 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7'

案例演示（实际改成你们自己的事务）：
set @@SESSION.GTID_NEXT='这个位置到底如何编写';

Retrieved_Gtid_Set: 0883a39c-eb54-11ef-879d-000c29d9e0c0:1-12
Executed_Gtid_Set: 08817f5d-eb54-11ef-9fcd-000c296d8526:1-2
第一步：找两者差异
接收到1-12，实际执行1-2，从第3个事务开始同步异常，所以要尝试跳过事务编号3的事务
第二步：找主机uuid
主机uuid主要看接收端uuid编号 => Retrieved_Gtid_Set
set @@SESSION.GTID_NEXT='0883a39c-eb54-11ef-879d-000c29d9e0c0:3';

mysql> BEGIN; COMMIT;

mysql> SET SESSION GTID_NEXT = AUTOMATIC;

mysql> START REPLICA;

mysql> show tables;

一般在从库上执行下面命令，重置(reset)所有配置，再change，再启动服务，并查看记过

mysql> stop slave;
或者
mysql> stop replica;

mysql> reset slave;        # 一般在从库执行，如果遇到很难搞定的问题，在主库和从库都要执行
或者
mysql> reset replica;

或者
mysql> reset slave all;    # 一般在从库执行，如果遇到很难搞定的问题，在主库和从库都要执行

或者
mysql> reset replica all;

mysql> change replication source to
  source_host='192.168.88.101',
  source_port=3306,
  source_user='slave',
  source_password='MySQL@666',
  source_auto_position=1;
  
mysql> start slave;
或者
mysql> start replica;

mysql> show slave status\G
或者
mysql> show replica status\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.88.101
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 2777
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 411
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1051
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '8d18488c-3fb6-11f1-ba96-0050562cce4c:27' at source log binlog.000005, end_log_pos 2554. 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: 2332
              Relay_Log_Space: 1059
              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: 1051
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '8d18488c-3fb6-11f1-ba96-0050562cce4c:27' at source log binlog.000005, end_log_pos 2554. 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: 101
                  Master_UUID: 8d18488c-3fb6-11f1-ba96-0050562cce4c
             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: 260424 20:48:51
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 8d18488c-3fb6-11f1-ba96-0050562cce4c:27-28
            Executed_Gtid_Set: 4daa2ed9-3fb7-11f1-8500-0050563e2b77:1-3,
8d18488c-3fb6-11f1-ba96-0050562cce4c:1-26
                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.00 sec)

mysql>

Retrieved_Gtid_Set: 8d18488c-3fb6-11f1-ba96-0050562cce4c:27-28
            Executed_Gtid_Set: 4daa2ed9-3fb7-11f1-8500-0050563e2b77:1-3,
8d18488c-3fb6-11f1-ba96-0050562cce4c:1-26

-- 1. 停止从库复制线程（IO线程 + SQL线程），确保当前无复制操作执行
STOP REPLICA;

-- 2. 指定下一个要执行的GTID为出错事务：8d18488c-3fb6-11f1-ba96-0050562cce4c:27
-- 作用：强制让MySQL认为下一个执行的就是这个出错的GTID
SET GTID_NEXT='8d18488c-3fb6-11f1-ba96-0050562cce4c:27';

-- 3. 手动开启一个空事务并提交
-- 作用：空执行该GTID，不做任何数据修改，仅将此GTID标记为已执行，从而跳过出错事务
BEGIN;
COMMIT;

-- 4. 恢复GTID自动分配模式（必须执行，否则后续复制无法正常工作）
SET GTID_NEXT=AUTOMATIC;

-- 5. 重新启动从库复制线程，继续同步主库数据
START REPLICA;

-- 6. 查看从库复制状态，验证是否修复成功（重点查看 Replica_SQL_Running、Last_Error 字段）
SHOW REPLICA STATUS\G

STOP REPLICA;

SET GTID_NEXT='8d18488c-3fb6-11f1-ba96-0050562cce4c:28';
BEGIN;
COMMIT;

SET GTID_NEXT=AUTOMATIC; # 恢复GTID自动分配模式

START REPLICA;

SHOW REPLICA STATUS\G

CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show databases;
use bookdb666;

CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门到精通》','66','电子工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');

show tables;
select * from book666;

mysqldump -uroot -p'MySQL@666' \
--all-databases \
--single-transaction \
--master-data=2 \
--set-gtid-purged=ON \
--triggers --routines --events \
> full.sql

scp full.sql root@从库IP:/root/

STOP REPLICA;
RESET REPLICA ALL;

RESET MASTER;

mysql -uroot -p'MySQL@666' < full.sql

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.88.101',
  SOURCE_PORT=3306,
  SOURCE_USER='slave',
  SOURCE_PASSWORD='MySQL@666',
  SOURCE_AUTO_POSITION=1;

START REPLICA;

SHOW REPLICA STATUS\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Source: 0（或很小）

主库 mysqldump
        ↓
传输 SQL 文件
        ↓
从库 RESET + 清空
        ↓
导入数据
        ↓
CHANGE MASTER
        ↓
START REPLICA

CREATE DATABASE heima666;
show databases;
use heima666;
CREATE TABLE table666(bTypeId int,bName char(16),price int,publishing char(16));

INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('1','Linux','66','DZ');
INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('2','CLD','68','RM');
INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('3','SYS','90','JX');
INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('4','MySQL1','71','QH');
INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('5','MySQL2','72','QH');
INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('6','MySQL3','73','QH');
select * from table666;

ERROR: MASTER_AUTO_POSITION requires GTID

Executed_Gtid_Set 混乱

SET GLOBAL read_only=1;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.88.101
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 197
               Relay_Log_File: relaylog.000004
                Relay_Log_Pos: 407
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys
           Replicate_Do_Table:

[root@mysql-node1 ~]# mysql -uroot -pMySQL@666
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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.01 sec)

mysql> CREATE USER 'slave2'@'%' IDENTIFIED BY 'MySQL@666';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave            | %         |
| slave2           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+-------------------------------------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                         |
+---------------+----------+--------------+-------------------------------------------------+-------------------------------------------+
| binlog.000007 |      513 |              | information_schema,mysql,performance_schema,sys | 9022134e-12f0-11f1-b122-0050563756ff:1-18 |
+---------------+----------+--------------+-------------------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> ^DBye
[root@mysql-node1 ~]# mysqlbinlog --no-defaults -uroot -pMySQL@666 --database=mysql --base64-output=DECODE-ROWS -v /export/server/mysql/data/binlog.000007 | grep -E "CREATE USER|slave|GRANT"
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
[root@mysql-node1 ~]# ls /export/server/mysql/data/
 auto.cnf        bookdb666            ibdata1              public_key.pem
 binlog.000001   ca-key.pem           ibtmp1               server-cert.pem
 binlog.000002   ca.pem              '#innodb_redo'        server-key.pem
 binlog.000003   client-cert.pem     '#innodb_temp'        sys
 binlog.000004   client-key.pem       mysql                undo_001
 binlog.000005   db_ithuang           mysqld.pid           undo_002
 binlog.000006  '#ib_16384_0.dblwr'   mysql.ibd
 binlog.000007  '#ib_16384_1.dblwr'   performance_schema
 binlog.index    ib_buffer_pool       private_key.pem
[root@mysql-node1 ~]# ls -lht /export/server/mysql/data/binlog.0000*
-rw-r-----. 1 mysql mysql  513 Feb 26 18:32 /export/server/mysql/data/binlog.000007
-rw-r-----. 1 mysql mysql  220 Feb 26 18:21 /export/server/mysql/data/binlog.000006
-rw-r-----. 1 mysql mysql  220 Feb 26 18:18 /export/server/mysql/data/binlog.000005
-rw-r-----. 1 mysql mysql 4.9K Feb 26 18:18 /export/server/mysql/data/binlog.000004
-rw-r-----. 1 mysql mysql  180 Feb 26 16:53 /export/server/mysql/data/binlog.000003
-rw-r-----. 1 mysql mysql  833 Feb 26 16:45 /export/server/mysql/data/binlog.000002
-rw-r-----. 1 mysql mysql  180 Feb 26 16:44 /export/server/mysql/data/binlog.000001
[root@mysql-node1 ~]# mysqlbinlog --no-defaults -uroot -pMySQL@666 --base64-output=DECODE-ROWS -v /export/server/mysql/data/binlog.000007 | grep -E "CREATE USER|slave|GRANT"
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
CREATE USER 'slave2'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$-!|[[fa\n\"j:zemHvdrzzCPq9egoj83qt2EKkZtXmxCIWWZGfs70.Sy2/'
[root@mysql-node1 ~]# mysql -uroot -pMySQL@666                                 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: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave            | %         |
| slave2           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> CREATE USER 'slave3'@'%' IDENTIFIED BY 'MySQL@666';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave            | %         |
| slave2           | %         |
| slave3           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+-------------------------------------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                         |
+---------------+----------+--------------+-------------------------------------------------+-------------------------------------------+
| binlog.000007 |      827 |              | information_schema,mysql,performance_schema,sys | 9022134e-12f0-11f1-b122-0050563756ff:1-19 |
+---------------+----------+--------------+-------------------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> ^DBye
[root@mysql-node1 ~]# mysqlbinlog --no-defaults -uroot -pMySQL@666 --base64-output=DECODE-ROWS -v /export/server/mysql/data/binlog.000007 | grep -E "CREATE USER|slave|GRANT"
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
CREATE USER 'slave2'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$-!|[[fa\n\"j:zemHvdrzzCPq9egoj83qt2EKkZtXmxCIWWZGfs70.Sy2/'
CREATE USER 'slave3'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$ B |0B
   R)H0MTzXq3fkLbhZ2ClQ1i6i3BYLxTZnZ/olBQTXQ8GvYnYKDA'
[root@mysql-node1 ~]# mysql -uroot -pMySQL@66
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@mysql-node1 ~]# mysql -uroot -pMySQL@666
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 13
Server version: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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> use mysql
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_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| ndb_binlog_index                                     |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
38 rows in set (0.00 sec)

mysql> CREATE TABLE userbook666 (ID int,Name char(16),Price int,Publishing char(16));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO userbook666(ID,Name,Price,Publishing) VALUES('1','《Linux从 入门到精通》','66','电子工业出版社');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO userbook666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| ndb_binlog_index                                     |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
| userbook666                                          |
+------------------------------------------------------+
39 rows in set (0.00 sec)

mysql> select * from userbook666;
+------+-------------------------------+-------+-----------------------+
| ID   | Name                          | Price | Publishing            |
+------+-------------------------------+-------+-----------------------+
|    1 | 《Linux从入门到精通》         |    66 | 电子工业出版社        |
|    2 | 《云计算趋势》                |    68 | 人民邮电出版社        |
+------+-------------------------------+-------+-----------------------+
2 rows in set (0.00 sec)

mysql> ^DBye
[root@mysql-node1 ~]# mysqlbinlog --no-defaults -uroot -pMySQL@666 --base64-output=DECODE-ROWS -v /export/server/mysql/data/binlog.000007 | grep -E "CREATE USER|slave|GRANT"
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
CREATE USER 'slave2'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$-!|[[fa\n\"j:zemHvdrzzCPq9egoj83qt2EKkZtXmxCIWWZGfs70.Sy2/'
CREATE USER 'slave3'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$ B |0B
   R)H0MTzXq3fkLbhZ2ClQ1i6i3BYLxTZnZ/olBQTXQ8GvYnYKDA'
[root@mysql-node1 ~]#
[root@mysql-node2 ~]# mysql -uroot -pMySQL@666
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 19
Server version: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.88.101
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 197
               Relay_Log_File: relaylog.000004
                Relay_Log_Pos: 407
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys
           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: 197
              Relay_Log_Space: 860
              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: 101
                  Master_UUID: 9022134e-12f0-11f1-b122-0050563756ff
             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: 9022134e-12f0-11f1-b122-0050563756ff:1-17
            Executed_Gtid_Set: 9022134e-12f0-11f1-b122-0050563756ff:1-17
                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.00 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave            | %         |
| slave2           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave            | %         |
| slave2           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave            | %         |
| slave2           | %         |
| slave3           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)

mysql> use mysql
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_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| ndb_binlog_index                                     |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
38 rows in set (0.00 sec)

mysql>