LAMP架构（二）

文章目录

• mysql数据库
• • mysql安装
  • mysql初始化
  • 部署phpmyadmin
  • 主从复制
  • gtid模式
  • 半同步模式
  • 延迟复制
  • 并行复制
  • mysql组复制
  • 慢查询

mysql数据库

mysql安装

• 升级cmake

新版本地址：github.com/Kitware/CMake/releases/

[root@server1 ~]# yum remove -y cmake3
[root@server1 ~]# rm -f /usr/bin/cmake
#下载新版本
[root@server1 ~]# tar zxf cmake-3.28.4-linux-x86_64.tar.gz -C /usr/local/
[root@server1 ~]# cd /usr/local/cmake-3.28.4-linux-x86_64/bin/
[root@server1 bin]# pwd
/usr/local/cmake-3.28.4-linux-x86_64/bin
#把命令加入环境变量
[root@server1 bin]# cd
[root@server1 ~]# vim .bash_profile
[root@server1 ~]# source .bash_profile
[root@server1 ~]# which cmake
/usr/local/cmake-3.28.4-linux-x86_64/bin/cmake

• 升级gcc

[root@server1 ~]# yum install -y centos-release-scl
#修改repo文件，替换为阿里云安装源
[root@server1 ~]# cd /etc/yum.repos.d/
[root@server1 yum.repos.d]# ls
CentOS-Base.repo CentOS-SCLo-scl-rh.repo openresty.repo
CentOS-SCLo-scl.repo epel.repo
[root@server1 yum.repos.d]# vim CentOS-SCLo-scl-rh.repo
[root@server1 yum.repos.d]# vim CentOS-SCLo-scl.repo
[root@server1 yum.repos.d]# yum clean all
[root@server1 yum.repos.d]# yum repolist
[root@server1 ~]# yum install -y devtoolset-9-gcc devtoolset-9-gcc-c++
[root@server1 ~]# scl enable devtoolset-9 bash
[root@server1 ~]# which gcc
/opt/rh/devtoolset-9/root/usr/bin/gcc
[root@server1 ~]# gcc -v
[root@server1 ~]# g++ -v

• 安装依赖性

官网： www.mysql.com

[root@server1 ~]# yum install -y libaio-devel patchelf
[root@server1 ~]# tar zxf mysql-boost-8.0.36.tar.gz
[root@server1 ~]# cd mysql-8.0.36/
[root@server1 mysql-8.0.36]# mkdir build && cd build
[root@server1 build]# cmake .. -DCMAKE_C_COMPILER=$(which gcc) -DCMAKE_CXX_COMPILER=$(which g++) -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DSYSCONFDIR=/etc/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DCMAKE_CXX_FLAGS="-O2" -DCMAKE_C_FLAGS="-O2" -DWITH_BOOST=../boost/boost_1_77_0 -DWITH_SYSTEMD=1 -DWITH_SSL=/usr/local/openssl -DWITH_ZLIB=bundled -DWITH_LIBAIO=1 -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DMYSQL_MAINTAINER_MODE=0 -DWITH_DEBUG=0 -DINSTALL_TESTS=0 -DINSTALL_DOCS=0
1. 基础编译环境
-DCMAKE_C_COMPILER=$(which gcc)：指定 C 编译器 gcc
-DCMAKE_CXX_COMPILER=$(which g++)：指定 C++ 编译器 g++
-DCMAKE_C_FLAGS="-O2" / -DCMAKE_CXX_FLAGS="-O2"：O2 优化编译，生产环境推荐
2. 安装路径 & 运行基础配置
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql：程序安装目录
-DMYSQL_DATADIR=/data/mysql：数据存放目录（独立磁盘推荐）
-DSYSCONFDIR=/etc/mysql：my.cnf 配置文件搜索目录
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock：socket 文件路径
-DMYSQL_TCP_PORT=3306：默认监听端口
3. 存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1：启用 InnoDB（默认必需）
-DWITH_MYISAM_STORAGE_ENGINE=1：启用 MyISAM
-DWITH_PARTITION_STORAGE_ENGINE=1：开启分区表支持
4. 功能开关
-DENABLED_LOCAL_INFILE=1：允许 LOAD DATA LOCAL INFILE 导入本地文件
-DWITH_LIBAIO=1：启用 InnoDB 异步 AIO，提升磁盘性能（必须安装libaio-devel）
-DWITH_SYSTEMD=1：编译生成 systemd 服务单元文件，便于 systemctl 管理 mysql
5. 依赖库设置
-DWITH_BOOST=../boost/boost_1_77_0
MySQL8.0 编译依赖 Boost 库，路径必须正确；8.0.28 推荐 boost1.77.0
-DWITH_SSL=/usr/local/openssl：使用系统外部编译 OpenSSL（非内置）
-DWITH_ZLIB=bundled：使用 MySQL 自带 zlib 源码，不使用系统 zlib
6. 字符集（重点！）
-DDEFAULT_CHARSET=utf8mb4
-DDEFAULT_COLLATION=utf8mb4_unicode_ci
7. 调试与精简（生产优化）
-DWITH_DEBUG=0：关闭调试模式，提升性能
-DMYSQL_MAINTAINER_MODE=0：关闭开发维护模式
-DINSTALL_TESTS=0：不安装测试套件，减少编译体积
-DINSTALL_DOCS=0：不安装文档

#自动读取服务器CPU逻辑核心总数，多线程并行编译
[root@server1 build]# make -j$(nproc)
[root@server1 build]# make install
[root@server1 build]# du -sh /usr/local/mysql/
1.4G /usr/local/mysql/

mysql初始化

[root@server1 build]# useradd -M -d /usr/local/mysql/ -s /sbin/nologin mysql
[root@server1 build]# mkdir -p /data/mysql
[root@server1 build]# mkdir /etc/mysql
[root@server1 build]# chown mysql.mysql /data/mysql/
[root@server1 build]# vim /etc/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql # MySQL程序安装目录
datadir=/data/mysql # 数据库数据存放目录
socket=/data/mysql/mysql.sock # 本地连接socket文件路径
port=3306 # MySQL监听端口
user=mysql # 运行mysqld进程的系统用户
pid-file=/data/mysql/mysql.pid # 进程pid文件保存路径
character-set-server=utf8mb4 # 服务端默认字符集，支持emoji
collation-server=utf8mb4_unicode_ci # 默认字符排序规则
log-error=/data/mysql/mysql-error.log # 错误日志路径
innodb_buffer_pool_size=1G # InnoDB缓冲池，缓存数据与索引
max_connections=1000 # 数据库最大并发连接数

[client]
socket=/data/mysql/mysql.sock # 客户端本地连接优先使用socket
[root@server1 build]# cd /usr/local/mysql/bin/
[root@server1 bin]# pwd
/usr/local/mysql/bin
[root@server1 bin]# vim ~/.bash_profile
[root@server1 bin]# source ~/.bash_profile
[root@server1 bin]# which mysqld
/usr/local/mysql/bin/mysqld
[root@server1 bin]# /usr/local/mysql/bin/mysqld –defaults-file=/etc/mysql/my.cnf –initialize
[root@server1 bin]# grep "temporary password" /data/mysql/mysql-error.log
2026-07-29T04:08:24.201283Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kdpU.YQYR1Mg
[root@server1 bin]# cd
[root@server1 ~]# cd mysql-8.0.36/build/scripts/
[root@server1 scripts]# cp mysqld.service /usr/lib/systemd/system
[root@server1 scripts]# systemctl daemon-reload
[root@server1 scripts]# systemctl enable –now mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

• 安全初始化

[root@server1 scripts]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:#输入上面生成的临时密码

The existing password for the user account root has expired. Please set a new password.

New password:#输入两边新密码

Re-enter new password:

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No:#是否激活密码插件，直接回车
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :#是否再次修改管理员密码，直接回车

... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
#是否删除匿名用户，输入 y

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
#是否禁用root远程登录，输入 y
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y#是否删除test数据库，输入 y
– Dropping test database...
Success.

– Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
#刷新授权表
All done!

• 登录数据库

[root@server1 scripts]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 13
Server version: 8.0.36 Source distribution

Copyright (c) 2000, 2024, 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.01 sec)

mysql>quit
[root@server1 scripts]# netstat -antlp|grep :3306
tcp6 0 0 :::33060 :::* LISTEN 62287/mysqld
tcp6 0 0 :::3306 :::* LISTEN 62287/mysqld

部署phpmyadmin

下载地址：https://www.phpmyadmin.net/

[root@server1 ~]# unzip phpMyAdmin-5.2.3-all-languages.zip -d /usr/local/nginx/html/
[root@server1 ~]# cd /usr/local/nginx/html/
[root@server1 html]# ln -s phpMyAdmin-5.2.3-all-languages/ phpmyadmin

访问：192.168.117.133/phpmyadmin/index.php

• 修改nginx默认首页

[root@server1 html]# vim /usr/local/nginx/conf/nginx.conf
[root@server1 html]# nginx -s reload

访问：192.168.117.133/phpmyadmin/

• 登录出现问题

• 解决办法

[root@server1 html]# cd /usr/local/nginx/
[root@server1 nginx]# cd html/phpmyadmin/
[root@server1 phpmyadmin]# cp config.sample.inc.php config.inc.php
[root@server1 phpmyadmin]# vim config.inc.php

刷新

或者修改php-fpm配置

[root@server1 phpmyadmin]# vim /usr/local/php/etc/php.ini
[root@server1 phpmyadmin]# systemctl reload php-fpm

成功

主从复制

• master配置

[root@server1 ~]# ldd /usr/local/mysql/bin/mysql |grep selinux
[root@server1 ~]# systemctl stop mysqld.service
[root@server1 ~]# cd /usr/local/
[root@server1 local]# scp -rp mysql server2:/usr/local/
[root@server1 local]# cat /etc/passwd

• slave配置

#先关闭其他服务
[root@server2 ~]# systemctl disable –now httpd.service
[root@server2 ~]# systemctl disable –now memcached.service
[root@server2 local]# groupadd -g 1002 mysql
[root@server2 local]# useradd -u 1002 -g mysql -M -d /usr/local/mysql/ -s /sbin/nologin mysql
[root@server2 local]# id mysql
uid=1002(mysql) gid=1002(mysql) groups=1002(mysql)
[root@server2 local]# mkdir -p /data/mysql
[root@server2 local]# mkdir /etc/mysql
[root@server2 local]# chown mysql.mysql /data/mysql/

master配置

[root@server1 local]# cd /etc/mysql/
[root@server1 mysql]# ls
my.cnf
[root@server1 mysql]# scp my.cnf server2:/etc/mysql/
[root@server1 mysql]# scp /usr/lib/systemd/system/mysqld.service server2:/usr/lib/systemd/system/

slave配置

#添加环境变量
[root@server2 local]# cd
[root@server2 ~]# vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@server2 ~]# source .bash_profile
[root@server2 ~]# which mysql
/usr/local/mysql/bin/mysql
#初始化
[root@server2 ~]# /usr/local/mysql/bin/mysqld –defaults-file=/etc/mysql/my.cnf –initialize
[root@server2 ~]# grep "temporary password" /data/mysql/mysql-error.log
2026-07-29T13:19:47.413523Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: P<L;*yqq)0f+
[root@server2 ~]# systemctl start mysqld
[root@server2 ~]# mysql_secure_installation
#登录
[root@server2 ~]# mysql -pZjy031106

master

[root@server1 mysql]# vim /etc/mysql/my.cnf
#添加
server-id=1
[root@server1 mysql]# systemctl restart mysqld.service

[root@server1 mysql]# mysql -pZjy031106
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> SHOW MASTER STATUS\\G;

配置测试：

slave

[root@server2 ~]# vim /etc/mysql/my.cnf
#添加
server-id=2
[root@server2 ~]# systemctl restart mysqld.service
[root@server2 ~]# mysql -pZjy031106
mysql> CHANGE MASTER TO MASTER_HOST='192.168.117.133', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=691;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
mysql> start slave;
mysql> show slave status\\G;

主从测试

浏览器访问：192.168.117.133/phpmyadmin/index.php并登录，在数据库里新建数据库，填好内容，浏览

从机测试

• 再添加一个slave，采用：A -> B -> C线性方式

master（server1）

[root@server1 mysql]# cd
[root@server1 ~]# yum install -y rsync
[root@server1 ~]# rsync -a /usr/local/mysql server3:/usr/local/

slave（server3）

[root@server3 ~]# cd /usr/local/
[root@server3 local]# yum install -y rsync.x86_64
[root@server3 local]# cd mysql/
[root@server3 mysql]# groupadd -g 1002 mysql
[root@server3 mysql]# useradd -u 1002 -g mysql -M -d /usr/local/mysql/ -s /sbin/nologin mysql
[root@server3 mysql]# id mysql
uid=1002(mysql) gid=1002(mysql) groups=1002(mysql)
[root@server3 mysql]# mkdir -p /data/mysql
[root@server3 mysql]# mkdir /etc/mysql
[root@server3 mysql]# chown mysql.mysql /data/mysql/

slave（server2）

[root@server2 mysql]# scp /etc/mysql/my.cnf server3:/etc/mysql/
[root@server2 mysql]# scp /usr/lib/systemd/system/mysqld.service server3:/usr/lib/systemd/system/

slave（server3）

#添加环境变量
[root@server3 mysql]# cd
[root@server3 ~]# vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@server3 ~]# source .bash_profile
[root@server3 ~]# which mysql
/usr/local/mysql/bin/mysql
#初始化
[root@server3 ~]# /usr/local/mysql/bin/mysqld –defaults-file=/etc/mysql/my.cnf –initialize
[root@server3 ~]# grep "temporary password" /data/mysql/mysql-error.log
2026-07-29T15:24:58.478404Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: yfww8j-kiMjA
[root@server3 ~]# systemctl enable –now mysqld
[root@server3 ~]# mysql_secure_installation
#登录
[root@server3 ~]# mysql -pZjy031106

若遇到以下情况，可能是有其他冲突

查看并解决

[root@server3 ~]# rpm -qa|grep mariadb
mariadb-5.5.68-1.el7.x86_64
mariadb-libs-5.5.68-1.el7.x86_64
[root@server3 ~]# yum remove -y mariadb-5.5.68-1.el7.x86_64
[root@server3 ~]# logout

• 备份数据server1

[root@server1 ~]# mysqldump -uroot -pZjy031106 test > dump.db
[root@server1 ~]# scp dump.db server3:

• 同步从机server3

[root@server3 ~]# vim /etc/mysql/my.cnf
server-id=3
[root@server3 ~]# systemctl restart mysqld.service
[root@server3 ~]# mysql -pZjy031106
mysql> create database test;
[root@server3 ~]# mysql -uroot -pZjy031106 test < dump.db
[root@server3 ~]# mysql -pZjy031106

• server2

[root@server2 mysql]# mysql -pZjy031106
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

测试

• server3

[root@server3 ~]# mysql -pZjy031106
mysql> CHANGE MASTER TO MASTER_HOST='192.168.117.134', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1802;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\\G;

主从测试：server1浏览器图形上创建

server1上可查

server2同步

server3同步

gtid模式

​ GTID（Global Transaction ID，全局事务标识符），是基于事务 ID 的主从复制模式，用来替代传统「binlog 文件名 + position 位点」复制。给每一个事务分配全局唯一 ID，直接按事务集合同步。

• master配置

[root@server1 ~]# vim /etc/mysql/my.cnf
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server1 ~]# systemctl restart mysqld.service

• slave配置

[root@server2 ~]# vim /etc/mysql/my.cnf
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server2 ~]# systemctl restart mysqld.service
[root@server2 ~]# mysql -pZjy031106
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.117.133', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\\G;

其他节点以此类推

• 在phpmyadmin的图形界面删除旧表信息，添加id表头并设置为主键且唯一。写入数据时在从机上就可以看到自动分配的Retrieved_Gtid_Set和Executed_Gtid_Set

• 报错：Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

解决办法：

主从机都修改/etc/mysql/my.cnf文件，在其中加入密码插件：

default_authentication_plugin=mysql_native_password

主机修改用户（A、B）

mysql> ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'westos';
mysql> FLUSH PRIVILEGES;

从机添加用户

mysql> stop slave;
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> start slave;

半同步模式

工作流程：主库收到客户端 COMMIT 提交事务，用户线程先把事务写入本地 binlog，同时把这条事务推入 ACK请求队列。Dump 线程推送 binlog 事件给从库 IO 线程，从库 IO 线程接收日志，把收到的 binlog 事件写入本机 Relay Log（中继日志），写完立刻给主库回复一条 ACK（附带收到的 binlog 文件名 + 位置）。主库等待 ACK，用户线程阻塞等待，Dump 线程收到从库发回来的 ACK 之后，唤醒等待的用户线程。用户线程执行引擎层提交事务，最后向应用返回执行成功 OK。

补充：如果网络卡顿，等待 ACK 超时后，半同步会自动降级为异步复制，避免主库大量会话卡死。

• master配置：

#安装半同步模块
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#查询插件状态
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
–> FROM INFORMATION_SCHEMA.PLUGINS
–> WHERE PLUGIN_NAME LIKE '%semi%';
+———————-+—————+
| PLUGIN_NAME | PLUGIN_STATUS |
+———————-+—————+
| rpl_semi_sync_master | ACTIVE |
+———————-+—————+
1 row in set (0.00 sec)

#激活master半同步模式
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#查看半同步参数
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+——————————————-+————+
| Variable_name | Value |
+——————————————-+————+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+——————————————-+————+
6 rows in set (0.00 sec)

#查看半同步状态
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+——————————————–+——-+
| Variable_name | Value |
+——————————————–+——-+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+——————————————–+——-+
14 rows in set (0.00 sec)

#半同步参数写入配置文件，确保重启后依然生效
[root@server1 mysql]# vim /etc/mysql/my.cnf
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
[root@server1 mysql]# systemctl restart mysqld.service

• slave配置：

#安装半同步模块
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#激活slave半同步模式
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
#需要重启IO线程，slave端的半同步才能生效
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
#查看半同步参数
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+———————————+——-+
2 rows in set (0.02 sec)

#查看半同步状态
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| Rpl_semi_sync_slave_status | ON |
+—————————-+——-+
1 row in set (0.00 sec)
#半同步参数写入配置文件，确保重启后依然生效
[root@server2 ~]# vim /etc/mysql/my.cnf
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_slave_enabled=1
[root@server2 ~]# systemctl restart mysqld.service

• 测试：

图形界面master写入数据，查看

停止所有slave节点的IO线程：

mysql> STOP SLAVE IO_THREAD;

master节点再次写入数据：

mysql> INSERT INTO `users` (`id`, `username`, `password`) VALUES ('6', 'user6', '555');

等待默认超时时间10秒后，mysql自动切换为异步模式

所有slave节点再次启动IO线程，mysql会自动切回半同步模式

mysql> START SLAVE IO_THREAD;

延迟复制

​ 原理：从库收到事件存入 relaylog 之后，不会立刻执行，强制等待 60 秒，再交给 SQL 线程执行这条 SQL 事件。

​ 作用：误删数据时，有缓冲时间，立刻停止 SQL 线程，防止错误操作同步到从库。

在其中一个slave中执行

#暂停从库的SQL线程（停止执行主库同步过来的SQL事件，IO线程仍然继续接收主库binlog）
mysql> STOP REPLICA SQL_THREAD;
#配置延迟复制：从库执行日志时延迟60秒（延时从库，用于误操作数据恢复场景）
mysql> CHANGE MASTER TO MASTER_DELAY =60;
#启动从库SQL线程，开始应用binlog，所有同步事件将延迟60s执行
mysql> START REPLICA SQL_THREAD;

在master上插入数据后，slave不会立即更新，而是需要等待指定的延迟后才会执行。

• 补充：在从库关闭延迟复制命令（有需要时可以参考，我没执行哦）

#建议先暂停SQL线程（规范操作，避免执行中变更配置）
STOP REPLICA SQL_THREAD;
#MASTER_DELAY 设置为 0 代表关闭延迟复制
CHANGE MASTER TO MASTER_DELAY = 0;
#重启SQL线程，恢复实时同步
START REPLICA SQL_THREAD;

并行复制

​ 原理：开启多个 SQL 工作线程，并发执行中继日志里的事务，加快回放速度，降低主从延迟。

​ 注意：IO 线程永远只有 1 个；并行复制优化的是【SQL 回放阶段】。

默认slave节点sql单线程回放，会造成数据同步延时较高

• 主库

[root@server1 ~]# vim /etc/mysql/my.cnf
server-id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin#开启二进制日志，文件名前缀mysql-bin
binlog_format=ROW#binlog存储格式：ROW行模式，记录数据变更行，推荐生产使用
transaction_write_set_extraction=XXHASH64#WRITESET并行复制依赖：使用XXHASH64算法计算事务写入集合哈希值
binlog_transaction_dependency_tracking=WRITESET#主库组提交并行策略：开启WRITESET方式
binlog_transaction_dependency_history_size=50000#WRITESET模式下，保存冲突事务哈希历史数量上限
[root@server1 ~]# systemctl restart mysqld.service

• 从库（server2）

[root@server2 ~]# vim /etc/mysql/my.cnf
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
replica_parallel_type=LOGICAL_CLOCK#并行复制分发策略：LOGICAL_CLOCK 逻辑时钟
replica_parallel_workers=16#并行复制SQL工作线程数量，开启16个并发回放事务
replica_preserve_commit_order=ON#并行复制时，强制从库保持与主库相同事务提交顺序，避免主从数据不一致
relay_log_recovery=ON#崩溃自动恢复中继日志，从库异常重启保障relay log安全
relay_log_info_repository=TABLE#中继日志信息持久化到数据表
master_info_repository=TABLE#master连接信息持久化到数据表，替代文件存储
[root@server2 ~]# systemctl restart mysqld.service
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@server2 ~]# mysql -pZjy031106
mysql> show processlist;

mysql组复制

• server1配置

#首先停止数据库
[root@server1 ~]# systemctl stop mysqld.service

#清除数据
[root@server1 ~]# cd /data/mysql
[root@server1 mysql]# rm -fr *

[root@server1 mysql]# vim /etc/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
port=3306
user=mysql
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
log-error=/data/mysql/mysql-error.log
innodb_buffer_pool_size=1G
max_connections=1000
default_authentication_plugin=mysql_native_password # 用户默认认证插件，兼容旧客户端连接（MySQL8默认caching_sha2_password）

[client]
socket=/data/mysql/mysql.sock

#初始化
[root@server1 mysql]# mysqld –defaults-file=/etc/mysql/my.cnf –initialize

#启动服务
[root@server1 mysql]# systemctl start mysqld.service

#抓取临时密码
[root@server1 mysql]# grep temporary mysql-error.log
2026-07-30T12:47:37.125757Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: oG;af!*)(32r

[root@server1 mysql]# mysql -p
#首先修改临时密码
mysql> alter user root@localhost identified by 'westos';
修改本地root用户密码
mysql> SET SQL_LOG_BIN=0;
关闭当前会话二进制日志，本条会话操作不记录binlog
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
创建主从复制专用用户，允许任意地址访问
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
授予复制从库权限
mysql> GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
授予连接管理权限（MySQL8必备）
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
授予备份相关管理权限
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
授予组复制流权限
mysql> FLUSH PRIVILEGES;
刷新权限，使授权立即生效
mysql> SET SQL_LOG_BIN=1;
恢复当前会话二进制日志记录

#退出数据库登录，并修改my.cnf 配置
[root@server1 mysql]# vim /etc/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
port=3306
user=mysql
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
log-error=/data/mysql/mysql-error.log
innodb_buffer_pool_size=1G
max_connections=1000
default_authentication_plugin=mysql_native_password

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"# 禁用非InnoDB存储引擎
server_id=1# 数据库实例唯一ID，集群内不可重复
gtid_mode=ON# 开启GTID事务模式
enforce_gtid_consistency=ON# 强制保证GTID事务一致性
binlog_checksum=NONE# 关闭binlog校验和，减少性能开销
log_bin=binlog # 开启二进制日志，定义binlog文件名前缀
log_replica_updates=ON# 从节点中继日志写入binlog，多层复制必备

plugin_load_add='group_replication.so'# 加载组复制插件
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"# 组复制集群统一UUID
group_replication_start_on_boot=off 开机不自动启动组复制
group_replication_local_address= "192.168.117.133:33061"#当前节点组复制通信地址端口
group_replication_group_seeds= "192.168.117.133:33061,192.168.117.134:33061,192.168.117.135:33061" #集群种子节点列表
group_replication_bootstrap_group=off# 关闭自动引导集群，首次启动手动引导
group_replication_ip_whitelist="192.168.117.0/24,127.0.0.1/8"# 组复制通信IP白名单
group_replication_single_primary_mode=OFF# 关闭单主模式，开启多主集群
group_replication_enforce_update_everywhere_checks=ON# 多主模式强制检测，防止冲突操作

[client]
socket=/data/mysql/mysql.sock

#重启mysqld服务
[root@server1 mysql]# systemctl restart mysqld.service

#登录数据库
[root@server1 mysql]# mysql -p

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
配置组复制恢复通道账号，用于节点故障重加入集群时同步缺失数据
mysql> SET GLOBAL group_replication_bootstrap_group=ON;#只在server1执行
仅在集群第一个节点执行，初始化引导组复制集群
mysql> START GROUP_REPLICATION;
启动当前实例的组复制
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;#只在server1执行
引导完成立即关闭引导模式，只在server1执行
mysql> SELECT * FROM performance_schema.replication_group_members;
查询组复制集群内所有成员状态信息
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
| group_replication_applier | d7036ca5-8c14-11f1-816d-000c29890ec5 | server1 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
1 row in set (0.00 sec)

• server2配置

#首先停止数据库
[root@server2 ~]# systemctl stop mysqld

#清除数据
[root@server2 ~]# cd /data/mysql
[root@server2 mysql]# rm -fr *

[root@server2 mysql]# vim /etc/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
port=3306
user=mysql
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
log-error=/data/mysql/mysql-error.log
innodb_buffer_pool_size=1G
max_connections=1000
default_authentication_plugin=mysql_native_password

[client]
socket=/data/mysql/mysql.sock

#初始化
[root@server2 mysql]# mysqld –defaults-file=/etc/mysql/my.cnf –initialize

#启动服务
[root@server2 mysql]# systemctl start mysqld

#抓取临时密码
[root@server2 mysql]# grep temporary mysql-error.log
2026-07-30T13:57:27.560067Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: wr<Xs(lLJ6wi

[root@server2 mysql]# mysql -p
#首先修改临时密码
mysql> alter user root@localhost identified by 'westos';

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

mysql> GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';

mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';

mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

#退出数据库登录，并修改my.cnf 配置
[root@server2 mysql]# vim /etc/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
port=3306
user=mysql
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
log-error=/data/mysql/mysql-error.log
innodb_buffer_pool_size=1G
max_connections=1000
default_authentication_plugin=mysql_native_password

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_replica_updates=ON

plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.117.134:33061"
group_replication_group_seeds= "192.168.117.133:33061,192.168.117.134:33061,192.168.117.135:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.117.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

[client]
socket=/data/mysql/mysql.sock

#重启mysqld服务
[root@server2 mysql]# systemctl restart mysqld
[root@server2 mysql]# mysql -p
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

mysql> START GROUP_REPLICATION;

mysql> SELECT * FROM performance_schema.replication_group_members;
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
| group_replication_applier | 98721ba5-8c1e-11f1-b6dc-000c2975e8d7 | server2 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | d7036ca5-8c14-11f1-816d-000c29890ec5 | server1 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
2 rows in set (0.03 sec)

• server3配置

#首先停止数据库
[root@server3 ~]# systemctl stop mysqld

#清楚数据
[root@server3 ~]# cd /data/mysql
[root@server3 mysql]# rm -fr *

[root@server3 mysql]# vim /etc/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
port=3306
user=mysql
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
log-error=/data/mysql/mysql-error.log
innodb_buffer_pool_size=1G
max_connections=1000
default_authentication_plugin=mysql_native_password

[client]
socket=/data/mysql/mysql.sock

#初始化
[root@server3 mysql]# mysqld –defaults-file=/etc/mysql/my.cnf –initialize

#启动服务
[root@server3 mysql]# systemctl start mysqld

#抓取临时密码
[root@server3 mysql]# grep temporary mysql-error.log
2026-07-30T14:05:09.909879Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Gx2dlHv>12pe

[root@server3 mysql]# mysql -p
#首先修改临时密码
mysql> alter user root@localhost identified by 'westos';

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

mysql> GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';

mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';

mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

#退出数据库登录，并修改my.cnf 配置
[root@server3 mysql]# vim /etc/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
port=3306
user=mysql
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
log-error=/data/mysql/mysql-error.log
innodb_buffer_pool_size=1G
max_connections=1000
default_authentication_plugin=mysql_native_password

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_replica_updates=ON

plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.117.135:33061"
group_replication_group_seeds= "192.168.117.133:33061,192.168.117.134:33061,192.168.117.135:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.117.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

[client]
socket=/data/mysql/mysql.sock

#重启mysqld服务
[root@server2 mysql]# systemctl restart mysqld
[root@server2 mysql]# mysql -p
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

mysql> START GROUP_REPLICATION;

mysql> SELECT * FROM performance_schema.replication_group_members;
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
| group_replication_applier | 98721ba5-8c1e-11f1-b6dc-000c2975e8d7 | server2 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | ac180f38-8c1f-11f1-b5c4-000c29414997 | server3 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | d7036ca5-8c14-11f1-816d-000c29890ec5 | server1 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
+—————————+————————————–+————-+————-+————–+————-+—————-+—————————-+
3 rows in set (0.00 sec)

• 测试

#所有节点都可以读写数据
server1：
mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);

mysql> INSERT INTO t1 VALUES (1, 'Luis');

mysql> SELECT * FROM t1;
+—-+——+
| c1 | c2 |
+—-+——+
| 1 | Luis |
+—-+——+
1 row in set (0.00 sec)

server2：
mysql> select * from test.t1;
+—-+——+
| c1 | c2 |
+—-+——+
| 1 | Luis |
+—-+——+
1 row in set (0.00 sec)

mysql> INSERT INTO test.t1 VALUES (2, 'wxh');

server3：
mysql> select * from test.t1;
+—-+——+
| c1 | c2 |
+—-+——+
| 1 | Luis |
| 2 | wxh |
+—-+——+
2 rows in set (0.00 sec)

mysql> INSERT INTO test.t1 VALUES (3, 'westos');

server1：
mysql> select * from test.t1;
+—-+——–+
| c1 | c2 |
+—-+——–+
| 1 | Luis |
| 2 | wxh |
| 3 | westos |
+—-+——–+
3 rows in set (0.00 sec)

慢查询

​ 慢查询日志：MySQL 提供的日志功能，用来记录执行时间超过指定阈值的 SQL 语句。凡是执行耗时 ≥ 预设时长的 SQL，都会被写入慢查询日志，方便我们定位低效 SQL、优化性能。

主、从库都可以配置，我在从库server2

#全局开启慢查询日志，生效后新建立的数据库连接生效
mysql> set global slow_query_log=ON;

#查看慢查询时间阈值相关变量 long_query_time
mysql> show variables like "long%";
+—————–+———–+
| Variable_name | Value |
+—————–+———–+
| long_query_time | 10.000000 |
+—————–+———–+
1 row in set (0.02 sec)

#设置慢查询阈值为5秒：SQL执行耗时超过5秒会被记录到慢查询日志
#注意：不加global，仅对当前会话生效，新连接还是沿用全局配置
mysql> set long_query_time=5;

#强制会话阻塞5秒，模拟一条执行时长超限的慢SQL，用于测试慢日志是否正常记录
mysql> select sleep(5);#模拟慢查询语句
+———-+
| sleep(5) |
+———-+
| 0 |
+———-+

#查询所有slow开头系统变量，查看慢查询日志开启状态、日志文件路径
mysql> show variables like "slow%";
+———————+——————————+
| Variable_name | Value |
+———————+——————————+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/server2-slow.log |
+———————+——————————+
3 rows in set (0.00 sec)