Mysql基础——Mysql-8.0.35源码部署
#############################################################################
一、Mysql-8.0.35源码部署方案
#############################################################################
## BCLinux8.2部署mysql-boost-8.0.35
系统版本
BCLinux8.2
内核版本
4.19.0-240.23.15.e18 2.bclinux.x86_64
gcc版本
libgcc-8.3.1-5.0.1.1.el8_2.bclinux.x86_64
gcc-8.3.1-5.0.1.an8.x86_64
gcc-c++-8.3.1-5.0.1.an8.x86_64
问题描述
业务侧BCLinux8.2系统部署mysql-boost-8.0.35.tar.gz时，cmake源码编译涉及gcc大量的依赖问题，业务侧需要gcc10以上版本依赖包，请研发协助提供；
目前业务侧述求：
1、早期业务环境成功部署案例
mysql-boost-8.0.35
gcc-8.3.1-5.0.1.an8.x86_64
gcc-c++-8.3.1-5.0.1.an8.x86_64
经过多次测试环境部署，由于boost源码部署涉及大量的跨gcc版本的检查模块无法跳过，卡在cmake编译阶段；
2、寻求苏研后端提供gcc10以上版本
#############################################################################
#############################################################################
8.0 重点注意事项（踩坑提醒）
#############################################################################
MySQL8 不再支持 PASSWORD() 函数，修改密码必须使用 ALTER USER
默认认证插件 caching_sha2_password，老旧客户端连接失败时，可以创建用户指定插件：
sql
CREATE USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'Pass@123';
所有语句末尾必须带 ;
生产环境禁止随意执行不带 WHERE 条件的 UPDATE / DELETE
#############################################################################
cd /root/mysql-8.0.35/extra/libevent/libevent-2.1.11-stable
# 注释array-parameter检测
sed -i 's/MY_CHECK_C_COMPILER_FLAG("-Warray-parameter" HAVE_ARRAY_PARAMETER)/#MY_CHECK_C_COMPILER_FLAG("-Warray-parameter" HAVE_ARRAY_PARAMETER)/' CMakeLists.txt
sed -i 's/IF(HAVE_ARRAY_PARAMETER)/#IF(HAVE_ARRAY_PARAMETER)/' CMakeLists.txt
sed -i 's/TARGET_COMPILE_OPTIONS(event_core PRIVATE "-Wno-array-parameter")/#TARGET_COMPILE_OPTIONS(event_core PRIVATE "-Wno-array-parameter")/' CMakeLists.txt
sed -i 's/ENDIF()/#ENDIF()/' CMakeLists.txt
# 替换struct linger检测代码
sed -i 's|CHECK_TYPE_SIZE(struct linger EVENT__HAVE_STRUCT_LINGER)|set(CMAKE_EXTRA_INCLUDE_FILES sys/socket.h)\nCHECK_TYPE_SIZE(struct linger EVENT__HAVE_STRUCT_LINGER)\nunset(CMAKE_EXTRA_INCLUDE_FILES)|' CMakeLists.txt
# 安装依赖问题
yum install -y gcc gcc-c++ make cmake glibc-devel glibc-headers \
libstdc++-devel libaio-devel openssl-devel zlib-devel ncurses-devel \
kernel-headers kernel-devel libtirpc-devel rpcgen
# 安装gcc-toolset-12
# 配置el8 yum源（参考附录A 配置yum源）
yum install gcc-toolset-12-gcc gcc-toolset-12-gcc-c++ gcc-toolset-12-binutils gcc-toolset-12-annobin-annocheck
# 切换gcc-toolset-12 bash环境
scl enable gcc-toolset-12 bash
# 清理构建目录
cd /root/mysql-8.0.35
rm -rf build
mkdir build && cd build
# 执行cmake
cmake .. \
-DCMAKE_INSTALL_PREFIX=/apps/svr/mysql/ \
-DWITH_BOOST=../boost \
-DWITH_LIBEVENT=bundled \
-DWITH_AUTHENTICATION_LDAP=OFF \
-DWITH_SASL=OFF
# 最终编译参数
cmake .. -DCMAKE_INSTALL_PREFIX=/apps/svr/mysql/ \
-DWITH_BOOST=../boost \
-DWITH_LIBEVENT=bundled \
-DWITH_AUTHENTICATION_LDAP=OFF \
-DWITH_SASL=OFF \
-DCMAKE_C_COMPILER=/opt/rh/gcc-toolset-12/root/usr/bin/gcc \
-DCMAKE_CXX_COMPILER=/opt/rh/gcc-toolset-12/root/usr/bin/g++
# 编译和安装
make -j8 && make install
# 增加全局环境变量
echo export PATH=$PATH:/apps/svr/mysql/bin/ >> /etc/profile
source /etc/profile
# 修改安装目录权限
# 初始化权限
chown -R mysql:mysql /apps/svr/mysql/data
# 数据库初始化数据目录
/apps/svr/mysql/data/
# 初始化数据库账号和登入密码(新数据库初始化)
## 生成/apps/svr/mysql/data下的数据
## 生成登入mysql数据库root和默认密码
## 初始化密码保存日志文件/apps/svr/mysql/log/error.log
## 初始化生成密码日志信息[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: k?E(uWr=7na8
### 登入数据库要求修改新密码：Jonj5pbS@8%t0SoM1%11
[root@sysmt bin]# /apps/svr/mysql/bin/mysqld --defaults-file=/etc/my.cnf.d/my.cnf --initialize --user=mysql
2026-07-29T10:02:08.901346Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2026-07-29T10:02:08.901353Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2026-07-29T10:02:08.901360Z 0 [Warning] [MY-011068] [Server] The syntax 'log_slave_updates' is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2026-07-29T10:02:08.901443Z 0 [System] [MY-013169] [Server] /apps/svr/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 1820
2026-07-29T10:02:08.928348Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=4294967296. Please use innodb_redo_log_capacity instead.
2026-07-29T10:02:08.940799Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2026-07-29T10:02:22.317704Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2026-07-29T10:02:24.917680Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: k?E(uWr=7na8
## 查看日志/apps/svr/mysql/log/
/apps/svr/mysql/bin/mysqld --defaults-file=/etc/my.cnf.d/my.cnf --initialize --user=mysql
# 旧数据库初始化
## /apps/svr/mysql/data不为空，初始化失败，检查/apps/svr/mysql/log/error.log
## 参考“附录B——报错——问题6”
# 初始化root和默认秘钥登入
mysql -uroot -p'k?E(uWr=7na8'
#配套服务启停命令
# 前台启动（调试用，常见无法启动mysql进程丢失或者卡死（ps -ef | grep "mysql"））
## 检查/apps/svr/mysql/log/error.log报错（临时启动mysql进程，ps -ef | grep mysql）
/apps/svr/mysql/bin/mysqld --defaults-file=/etc/my.cnf.d/my.cnf --user=mysql
# 后台常驻启动命令（前台验证成功后使用）
/apps/svr/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf.d/my.cnf --user=mysql &
# 正常启动日志
2026-07-30T02:28:23.442008Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2026-07-30T02:28:23.442085Z 0 [System] [MY-010116] [Server] /apps/svr/mysql/bin/mysqld (mysqld 8.0.35) starting as process 2074
2026-07-30T02:28:23.449233Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=4294967296. Please use innodb_redo_log_capacity instead.
2026-07-30T02:28:23.450835Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2026-07-30T02:28:27.583879Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2026-07-30T02:28:28.612095Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2026-07-30T02:28:28.612146Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2026-07-30T02:28:28.635158Z 0 [System] [MY-010931] [Server] /apps/svr/mysql/bin/mysqld: ready for connections. Version: '8.0.35' socket: '/apps/svr/mysql/mysql.sock' port: 3306 Source distribution.
2026-07-30T02:28:28.635247Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[root@sysmt log]# ps -ef | grep "mysql"
mysql 2074 1621 5 10:28 pts/0 00:00:06 /apps/svr/mysql/bin/mysqld --defaults-file=/etc/my.cnf.d/my.cnf --user=mysql
root 2275 2137 0 10:30 pts/1 00:00:00 grep --color=auto mysql
[root@sysmt log]# netstat -anltp | grep "mysql"
tcp6 0 0 :::33060 :::* LISTEN 2074/mysqld
tcp6 0 0 :::3306 :::* LISTEN 2074/mysqld
# 日常巡检技巧
ps -ef | grep mysqld
ls -l /apps/svr/mysql/mysql.sock
# 优雅关闭数据库
## 检查/apps/svr/mysql/log/error.log报错
/apps/svr/mysql/bin/mysqladmin -uroot -p shutdown
[root@sysmt mysql_bak]# /apps/svr/mysql/bin/mysqladmin -uroot -p shutdown
Enter password:
2026-07-29T08:48:03.946002Z 12 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.35).
2026-07-29T08:48:04.999324Z 0 [System] [MY-010910] [Server] /apps/svr/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.35) Source distribution.
[1]+ Done /apps/svr/mysql/bin/mysqld --user=mysql --datadir=/apps/svr/mysql/data (wd: /apps/svr/mysql/bin)
(wd now: ~/mysql_bak)
# 登入数据库
mysql -uroot -p'Jonj5pbS@8%t0SoM1%11'
# 查看现有数据库用户
SELECT user,host FROM mysql.user;
####生产环境安全提醒#####
不建议直接开放 root@%，尽量新建普通业务账号，仅授予对应数据库权限！
CREATE USER 'biz'@'%' IDENTIFIED WITH mysql_native_password BY 'Biz@123456';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bizdb.* TO 'biz'@'%';
FLUSH PRIVILEGES;
# 指定端口、socket登录
/apps/svr/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock
# 额外坑预警
授权完成后防火墙放行 3306 端口，否则外部依然连不上：
# firewalld放行端口
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload
#数据库root用户密码
-- 1.首次登录，修改临时密码（初始化必做）
# MySQL8.0 不支持弱密码，密码建议大小写 + 数字 + 符号。
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Jonj5pbS@8%t0SoM1%11';
-- 2.允许root远程访问（按需，生产谨慎开放）
CREATE USER 'root'@'%' IDENTIFIED BY 'Jonj5pbS@8%t0SoM1%11';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
-- 3.新建业务用户
CREATE USER 'appuser'@'%' IDENTIFIED BY 'Jonj5pbS@8%t0SoM1%11';
-- 授权指定库
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON testdb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;
# 如果你需要兼容旧客户端（Navicat 低版本），创建时指定 native 密码插件：
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的强密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# 补充说明
区分两个账号：
'root'@'localhost' 本地登录
'root'@'%' 任意 IP 远程登录
两者是互相独立账号，密码可以不一样
如果你只想修改已有远程 root 的密码
ALTER USER 'root'@'%' IDENTIFIED BY '新密码';
#数据库操作
-- 创建数据库 指定字符集（推荐utf8mb4 完整支持emoji）
CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 查看所有库
SHOW DATABASES;
-- 切换数据库
USE testdb;
-- 删除数据库
DROP DATABASE IF EXISTS testdb;
# 数据表操作
-- 创建表示例
CREATE TABLE `user_info` (
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
username VARCHAR(50) NOT NULL COMMENT '用户名',
phone VARCHAR(20) COMMENT '手机号',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 查看当前库所有表
SHOW TABLES;
-- 查看表结构
DESC user_info;
-- 完整建表语句
SHOW CREATE TABLE user_info;
-- 添加字段
ALTER TABLE user_info ADD email VARCHAR(100) COMMENT '邮箱';
-- 修改字段
ALTER TABLE user_info MODIFY COLUMN phone VARCHAR(30);
-- 删除字段
ALTER TABLE user_info DROP COLUMN email;
-- 删除表
DROP TABLE IF EXISTS user_info;
# DML增删改查（常用）
-- 插入
INSERT INTO user_info(username,phone) VALUES ('zhangsan','13800138000');
-- 查询
SELECT * FROM user_info;
SELECT id,username FROM user_info WHERE id=1;
SELECT * FROM user_info LIMIT 10;
-- 更新（务必加WHERE，否则全表更新！）
UPDATE user_info SET phone='13900139000' WHERE id=1;
-- 删除（务必加WHERE）
DELETE FROM user_info WHERE id=1;
-- 清空表（重置自增）
TRUNCATE TABLE user_info;
# 索引管理
-- 创建普通索引
CREATE INDEX idx_username ON user_info(username);
-- 唯一索引
CREATE UNIQUE INDEX uk_phone ON user_info(phone);
-- 查看索引
SHOW INDEX FROM user_info;
-- 删除索引
DROP INDEX idx_username ON user_info;
# 常用运维指令
-- 查看当前连接
SHOW PROCESSLIST;
-- 查看mysql参数
SHOW VARIABLES LIKE '%datadir%';
SHOW VARIABLES LIKE '%character%';
-- 查看引擎状态
SHOW ENGINE INNODB STATUS;
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看当前登录用户
SELECT USER();
# shell环境 导入和导出sql
# 导出整个库
/apps/svr/mysql/bin/mysqldump -uroot -p testdb > testdb_back.sql
# 导入sql文件（两种方式）
## 方式1 shell
/apps/svr/mysql/bin/mysql -uroot -p testdb < testdb_back.sql
## 方式2 mysql客户端内
mysql> source /root/testdb_back.sql;
##############################################################################
附录A
# 配置yum源
[root@sysmt yum.repos.d]# cat bak2/BCLinux-8.repo
[AppStream]
name=BC-Linux-8 - AppStream
baseurl=http://mirrors.cmecloud.cn/bclinux/el8/AppStream/$basearch/os/
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-BCLinux-8
[BaseOS]
name=BC-Linux-8 - Base
baseurl=http://mirrors.cmecloud.cn/bclinux/el8/BaseOS/$basearch/os/
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-BCLinux-8
# 安装gcc-toolset-12
[root@sysmt yum.repos.d]# yum install gcc-toolset-12-gcc gcc-toolset-12-gcc-c++ gcc-toolset-12-binutils gcc-toolset-12-annobin-annocheck gcc-toolset-12-annobin-plugin-gcc
Last metadata expiration check: 0:01:34 ago on Wednesday, July 29, 2026 PM12:32:17 CST.
Dependencies resolved.
==============================================================================================================================================================================================================
Package Architecture Version Repository Size
==============================================================================================================================================================================================================
Installing:
gcc-toolset-12-annobin-annocheck x86_64 11.08-1.an8 AppStream 196 k
gcc-toolset-12-annobin-plugin-gcc x86_64 11.08-1.an8 AppStream 917 k
gcc-toolset-12-binutils x86_64 2.38-17.an8 AppStream 6.5 M
gcc-toolset-12-gcc x86_64 12.2.1-7.4.0.1.an8 AppStream 44 M
gcc-toolset-12-gcc-c++ x86_64 12.2.1-7.4.0.1.an8 AppStream 13 M
Installing dependencies:
gcc-toolset-12-annobin-docs noarch 11.08-1.an8 AppStream 119 k
gcc-toolset-12-binutils-gold x86_64 2.38-17.an8 AppStream 871 k
gcc-toolset-12-libstdc++-devel x86_64 12.2.1-7.4.0.1.an8 AppStream 3.4 M
gcc-toolset-12-runtime x86_64 12.0-6.an8 AppStream 26 k
Transaction Summary
==============================================================================================================================================================================================================
Install 9 Packages
Total download size: 69 M
Installed size: 199 M
Is this ok [y/N]: y
Downloading Packages:
(1/9): gcc-toolset-12-annobin-docs-11.08-1.an8.noarch.rpm 1.5 MB/s | 119 kB 00:00
(2/9): gcc-toolset-12-annobin-annocheck-11.08-1.an8.x86_64.rpm 2.1 MB/s | 196 kB 00:00
(3/9): gcc-toolset-12-annobin-plugin-gcc-11.08-1.an8.x86_64.rpm 8.6 MB/s | 917 kB 00:00
(4/9): gcc-toolset-12-binutils-gold-2.38-17.an8.x86_64.rpm 4.9 MB/s | 871 kB 00:00
(5/9): gcc-toolset-12-binutils-2.38-17.an8.x86_64.rpm 9.8 MB/s | 6.5 MB 00:00
(6/9): gcc-toolset-12-gcc-c++-12.2.1-7.4.0.1.an8.x86_64.rpm 19 MB/s | 13 MB 00:00
(7/9): gcc-toolset-12-libstdc++-devel-12.2.1-7.4.0.1.an8.x86_64.rpm 14 MB/s | 3.4 MB 00:00
(8/9): gcc-toolset-12-runtime-12.0-6.an8.x86_64.rpm 792 kB/s | 26 kB 00:00
(9/9): gcc-toolset-12-gcc-12.2.1-7.4.0.1.an8.x86_64.rpm 36 MB/s | 44 MB 00:01
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 53 MB/s | 69 MB 00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : gcc-toolset-12-runtime-12.0-6.an8.x86_64 1/9
Running scriptlet: gcc-toolset-12-runtime-12.0-6.an8.x86_64 1/9
Installing : gcc-toolset-12-annobin-docs-11.08-1.an8.noarch 2/9
Installing : gcc-toolset-12-binutils-gold-2.38-17.an8.x86_64 3/9
Running scriptlet: gcc-toolset-12-binutils-gold-2.38-17.an8.x86_64 3/9
Installing : gcc-toolset-12-binutils-2.38-17.an8.x86_64 4/9
Running scriptlet: gcc-toolset-12-binutils-2.38-17.an8.x86_64 4/9
Installing : gcc-toolset-12-libstdc++-devel-12.2.1-7.4.0.1.an8.x86_64 5/9
Installing : gcc-toolset-12-annobin-annocheck-11.08-1.an8.x86_64 6/9
Installing : gcc-toolset-12-gcc-12.2.1-7.4.0.1.an8.x86_64 7/9
Installing : gcc-toolset-12-annobin-plugin-gcc-11.08-1.an8.x86_64 8/9
Running scriptlet: gcc-toolset-12-annobin-plugin-gcc-11.08-1.an8.x86_64 8/9
Installing : gcc-toolset-12-gcc-c++-12.2.1-7.4.0.1.an8.x86_64 9/9
Running scriptlet: gcc-toolset-12-gcc-c++-12.2.1-7.4.0.1.an8.x86_64 9/9
Verifying : gcc-toolset-12-annobin-annocheck-11.08-1.an8.x86_64 1/9
Verifying : gcc-toolset-12-annobin-docs-11.08-1.an8.noarch 2/9
Verifying : gcc-toolset-12-annobin-plugin-gcc-11.08-1.an8.x86_64 3/9
Verifying : gcc-toolset-12-binutils-2.38-17.an8.x86_64 4/9
Verifying : gcc-toolset-12-binutils-gold-2.38-17.an8.x86_64 5/9
Verifying : gcc-toolset-12-gcc-12.2.1-7.4.0.1.an8.x86_64 6/9
Verifying : gcc-toolset-12-gcc-c++-12.2.1-7.4.0.1.an8.x86_64 7/9
Verifying : gcc-toolset-12-libstdc++-devel-12.2.1-7.4.0.1.an8.x86_64 8/9
Verifying : gcc-toolset-12-runtime-12.0-6.an8.x86_64 9/9
Installed:
gcc-toolset-12-annobin-annocheck-11.08-1.an8.x86_64 gcc-toolset-12-annobin-docs-11.08-1.an8.noarch gcc-toolset-12-annobin-plugin-gcc-11.08-1.an8.x86_64
gcc-toolset-12-binutils-2.38-17.an8.x86_64 gcc-toolset-12-binutils-gold-2.38-17.an8.x86_64 gcc-toolset-12-gcc-12.2.1-7.4.0.1.an8.x86_64
gcc-toolset-12-gcc-c++-12.2.1-7.4.0.1.an8.x86_64 gcc-toolset-12-libstdc++-devel-12.2.1-7.4.0.1.an8.x86_64 gcc-toolset-12-runtime-12.0-6.an8.x86_64
Complete!
##############################################################################
####################################################################
附录B——报错
问题1、验证环境开发包依赖缺失问题
# 正常应该输出存在，不报错
echo "#include <sys/socket.h>" | gcc -xc - -c -o /dev/null
echo "#include <arpa/inet.h>" | gcc -xc - -c -o /dev/null
echo "#include <pthread.h>" | gcc -xc - -c -o /dev/null -pthread
[root@sysmt build]# echo "#include <sys/socket.h>" | gcc -xc - -c -o /dev/null
In file included from /usr/include/sys/socket.h:33,
from <stdin>:1:
/usr/include/bits/socket.h:393:10: fatal error: asm/socket.h: No such file or directory
#include <asm/socket.h>
^~~~~~~~~~~~~~
compilation terminated.
[root@sysmt build]# echo "#include <arpa/inet.h>" | gcc -xc - -c -o /dev/null
In file included from /usr/include/sys/socket.h:33,
from /usr/include/netinet/in.h:23,
from /usr/include/arpa/inet.h:22,
from <stdin>:1:
/usr/include/bits/socket.h:393:10: fatal error: asm/socket.h: No such file or directory
#include <asm/socket.h>
^~~~~~~~~~~~~~
compilation terminated.
解决办法：
[root@sysmt yum.repos.d]# yum install kernel-headers
Last metadata expiration check: 0:00:10 ago on Wednesday, July 29, 2026 PM12:00:48 CST.
Dependencies resolved.
==============================================================================================================================================================================================================
Package Architecture Version Repository Size
==============================================================================================================================================================================================================
Installing:
kernel-headers x86_64 4.19.0-240.23.36.el8_2.bclinux BaseOS 4.0 M
Transaction Summary
==============================================================================================================================================================================================================
Install 1 Package
Total download size: 4.0 M
Installed size: 4.8 M
Is this ok [y/N]: y
Downloading Packages:
kernel-headers-4.19.0-240.23.36.el8_2.bclinux.x86_64.rpm 12 MB/s | 4.0 MB 00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 12 MB/s | 4.0 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : kernel-headers-4.19.0-240.23.36.el8_2.bclinux.x86_64 1/1
Verifying : kernel-headers-4.19.0-240.23.36.el8_2.bclinux.x86_64 1/1
Installed:
kernel-headers-4.19.0-240.23.36.el8_2.bclinux.x86_64
Complete!
问题2、No mysys timer support detected
CMake Error at configure.cmake:336 (MESSAGE):
No mysys timer support detected!
Call Stack (most recent call first):
CMakeLists.txt:1570 (INCLUDE)
-- Configuring incomplete, errors occurred!
See also "/root/mysql-8.0.35/build/CMakeFiles/CMakeOutput.log".
See also "/root/mysql-8.0.35/build/CMakeFiles/CMakeError.log".
解决办法：
[root@sysmt build]# rm -f /usr/include/asm
[root@sysmt build]# ln -sf /usr/src/kernels/4.19.0-240.23.36.el8_2.bclinux.x86_64/include/uapi/asm-generic /usr/include/asm
[root@sysmt build]# ls -l /usr/include/asm
lrwxrwxrwx 1 root root 79 Jul 29 12:21 /usr/include/asm -> /usr/src/kernels/4.19.0-240.23.36.el8_2.bclinux.x86_64/include/uapi/asm-generic
[root@sysmt build]# ls /usr/include/asm/socket.h
/usr/include/asm/socket.h
[root@sysmt build]# echo "#include <sys/socket.h>" | gcc -xc - -c -o /dev/null
[root@sysmt build]# scl enable gcc-toolset-10 bash
问题3、依赖缺失问题Cannot find RPC development libraries
CMake Warning at cmake/rpc.cmake:40 (MESSAGE):
Cannot find RPC development libraries. You need to install the required
packages:
Debian/Ubuntu: apt install libtirpc-dev
RedHat/Fedora/Oracle Linux: yum install libtirpc-devel
SuSE: zypper install glibc-devel
Call Stack (most recent call first):
cmake/rpc.cmake:96 (WARN_MISSING_SYSTEM_TIRPC)
plugin/group_replication/libmysqlgcs/cmake/configure.cmake:34 (MYSQL_CHECK_RPC)
plugin/group_replication/libmysqlgcs/CMakeLists.txt:31 (INCLUDE)
CMake Error at cmake/rpc.cmake:97 (MESSAGE):
Could not find rpc/rpc.h in /usr/include or /usr/include/tirpc
Call Stack (most recent call first):
plugin/group_replication/libmysqlgcs/cmake/configure.cmake:34 (MYSQL_CHECK_RPC)
plugin/group_replication/libmysqlgcs/CMakeLists.txt:31 (INCLUDE)
-- Configuring incomplete, errors occurred!
See also "/root/mysql-8.0.35/build/CMakeFiles/CMakeOutput.log".
See also "/root/mysql-8.0.35/build/CMakeFiles/CMakeError.log".
问题4、依赖问题Cannot find rpcgen executable
CMake Warning at cmake/rpc.cmake:29 (MESSAGE):
Cannot find rpcgen executable. You need to install the required packages:
Debian/Ubuntu: apt install rpcsvc-proto
RedHat/Fedora/Oracle Linux: yum install rpcgen
SuSE: zypper install glibc-devel
Call Stack (most recent call first):
plugin/group_replication/libmysqlgcs/cmake/rpcgen.cmake:112 (WARN_MISSING_RPCGEN_EXECUTABLE)
plugin/group_replication/libmysqlgcs/CMakeLists.txt:50 (INCLUDE)
CMake Error at plugin/group_replication/libmysqlgcs/cmake/rpcgen.cmake:113 (MESSAGE):
Could not find rpcgen
Call Stack (most recent call first):
plugin/group_replication/libmysqlgcs/CMakeLists.txt:50 (INCLUDE)
-- Configuring incomplete, errors occurred!
See also "/root/mysql-8.0.35/build/CMakeFiles/CMakeOutput.log".
See also "/root/mysql-8.0.35/build/CMakeFiles/CMakeError.log".
问题5、编译阶段报错 缺少头文件
/root/mysql-8.0.35/storage/innobase/buf/buf0buf.cc: In function ‘void buf_pool_create(buf_pool_t*, ulint, ulint, std::mutex*, dberr_t&)’:
/root/mysql-8.0.35/storage/innobase/buf/buf0buf.cc:1222:44: error: ‘SYS_gettid’ was not declared in this scope
1222 | setpriority(PRIO_PROCESS, (pid_t)syscall(SYS_gettid), -20);
| ^~~~~~~~~~
[ 91%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/clone/clone0api.cc.o
[ 91%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/clone/clone0clone.cc.o
make[2]: *** [storage/innobase/CMakeFiles/innobase.dir/build.make:219: storage/innobase/CMakeFiles/innobase.dir/buf/buf0buf.cc.o] Error 1
make[2]: *** Waiting for unfinished jobs....
make[1]: *** [CMakeFiles/Makefile2:4427: storage/innobase/CMakeFiles/innobase.dir/all] Error 2
make: *** [Makefile:163: all] Error 2
解决办法：
sed -i '1i #include <sys/syscall.h>' /root/mysql-8.0.35/storage/innobase/buf/buf0buf.cc
你已经编译到 91%！不需要删除 build 文件夹、不需要重新 cmake，优先用方案一补丁，直接接着编译，节省大量时间
问题6、数据库初始化报错“[ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.”
2026-07-30T02:10:19.441384Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2026-07-30T02:10:19.441664Z 0 [System] [MY-013169] [Server] /apps/svr/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 1852
2026-07-30T02:10:19.443202Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2026-07-30T02:10:19.443237Z 0 [ERROR] [MY-013236] [Server] The designated data directory /apps/svr/mysql/data/ is unusable. You can remove all files that the server added to it.
2026-07-30T02:10:19.443284Z 0 [ERROR] [MY-010119] [Server] Aborting
2026-07-30T02:10:19.443389Z 0 [System] [MY-010910] [Server] /apps/svr/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.35) Source distribution.
解决办法：
#判断数据库进程正在运行优雅退出
# 优雅关闭数据库
/apps/svr/mysql/bin/mysqladmin -uroot -p shutdown
#备份原有数据库文件和初始化文件
mv /apps/svr/mysql/data /apps/svr/mysql/data_bak
#创建数据库data目录
mkdir /apps/svr/mysql/data
#修改数据数据库属主和属组为mysql
chown -R mysql:mysql /apps/svr/mysql/data
#数据库初始化/apps/svr/mysql/data生成数据
[root@sysmt tmp]# ls -al /apps/svr/mysql/
total 704
drwxrwxr-x 17 mysql mysql 4096 Jul 30 10:25 .
drwxr-xr-x 3 root root 19 Jul 28 16:30 ..
drwxr-xr-x 2 mysql mysql 4096 Jul 29 14:33 bin
drwxr-xr-x 2 mysql mysql 173 Jul 30 10:11 binlog
drwxr-xr-x 2 mysql mysql 6 Jul 30 10:25 data
drwx------ 7 mysql mysql 4096 Jul 30 10:11 data_bak
drwxr-xr-x 2 mysql mysql 69 Jul 29 14:33 docs
drwxr-xr-x 3 mysql mysql 282 Jul 29 14:33 include
drwxr-xr-x 6 mysql mysql 207 Jul 29 14:33 lib
-rw-r--r-- 1 mysql mysql 279355 Oct 12 2023 LICENSE
-rw-r--r-- 1 mysql mysql 114748 Oct 12 2023 LICENSE.router
-rw-r--r-- 1 mysql mysql 279355 Oct 12 2023 LICENSE-test
drwx------ 2 mysql mysql 39 Jul 29 18:02 log
drwxr-xr-x 4 mysql mysql 30 Jul 29 14:33 man
-rw-r--r-- 1 mysql mysql 1622 Jul 29 13:02 mysqlrouter-log-rotate
drwxr-xr-x 10 mysql mysql 4096 Jul 29 14:33 mysql-test
-rw-r--r-- 1 mysql mysql 666 Oct 12 2023 README
-rw-r--r-- 1 mysql mysql 679 Oct 12 2023 README.router
-rw-r--r-- 1 mysql mysql 666 Oct 12 2023 README-test
drwxr-xr-x 2 mysql mysql 6 Jul 29 17:53 relaylog
drwxrwxr-x 2 mysql mysql 6 Jul 29 14:33 run
drwxr-xr-x 28 mysql mysql 4096 Jul 29 14:33 share
drwxr-xr-x 2 mysql mysql 77 Jul 29 14:33 support-files
drwxr-xr-x 3 mysql mysql 17 Jul 29 14:33 var
[root@sysmt tmp]# /apps/svr/mysql/bin/mysqld --defaults-file=/etc/my.cnf.d/my.cnf --initialize --user=mysql
[root@sysmt tmp]# ls /apps/svr/mysql/data
auto.cnf client-key.pem ibdata1 mysql.ibd server-cert.pem undo_002
ca-key.pem '#ib_16384_0.dblwr' '#innodb_redo' performance_schema server-key.pem
ca.pem '#ib_16384_1.dblwr' '#innodb_temp' private_key.pem sys
client-cert.pem ib_buffer_pool mysql public_key.pem undo_001
问题7、服务端本地账户root登入失败，由于mysql默认使用/tmp/mysql.sock与/etc/my.cnf.d/my.cnf定义socket字段定义文件路劲不一致；
[root@sysmt log]# mysql -uroot -p'k?E(uWr=7na8'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@sysmt log]# cat /etc/my.cnf.d/my.cnf | grep -i "socket"
socket = /apps/svr/mysql/mysql.sock
解决办法：
i.建立软连（推荐运维环境）
# 注意：mysql 重启后 socket 文件重建，软链接永久生效。
ln -s /apps/svr/mysql/mysql.sock /tmp/mysql.sock
ii.使用/apps/svr/mysql/mysql.sock路径登入
/apps/svr/mysql/bin/mysql -uroot -p -S /apps/svr/mysql/mysql.sock
# 登入成功返回结果：
[root@sysmt log]# /apps/svr/mysql/bin/mysql -uroot -p -S /apps/svr/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35
Copyright (c) 2000, 2023, 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>
问题8：mysql工具默认使用/tmp/mysql.sock，与/apps/svr/mysql/mysql.sock不一致；
[root@sysmt log]# /apps/svr/mysql/bin/mysqladmin -uroot -p shutdown
Enter password:k?E(uWr=7na8
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
解决办法：
#1.建立软链接
ln -s /apps/svr/mysql/mysql.sock /tmp/mysql.sock
问题9：mysqladmin工具不支持过期密码登入（MySQL 8.0 初始化生成的临时 root 密码默认过期！）
[root@sysmt log]# /apps/svr/mysql/bin/mysqladmin -uroot -p shutdown
Enter password:k?E(uWr=7na8
mysqladmin: connect to server at 'localhost' failed
error: 'Your password has expired. To log in you must change it using a client that supports expired passwords.'
[root@sysmt log]# /apps/svr/mysql/bin/mysqladmin -uroot -p -S /apps/svr/mysql/mysql.sock shutdown
Enter password:k?E(uWr=7na8
mysqladmin: connect to server at 'localhost' failed
error: 'Your password has expired. To log in you must change it using a client that supports expired passwords.'
解决办法：
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Jonj5pbS@8%t0SoM1%11';
问题9：
[root@sysmt log]# /apps/svr/mysql/bin/mysql -uroot -p -h127.0.0.1 -P3306
Enter password:
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
[root@sysmt log]# /apps/svr/mysql/bin/mysql -uroot -p -h192.168.190.131 -P3306
Enter password:
ERROR 1130 (HY000): Host '192.168.190.131' is not allowed to connect to this MySQL server
问题10：消除脚本中明文传输告警
mysql: [Warning] Using a password on the command line interface can be insecure.
# 原因：当前脚本还是明文账号密码传参，必须切换 mysql_config_editor --login-path 方案，彻底消除这条告警。
# 创建官方加密凭据（消除明文警告）
/apps/svr/mysql/bin/mysql_config_editor set \
--login-path=monitor \
--user=root \
--socket=/apps/svr/mysql/mysql.sock \
--password
结果：输入秘钥生成密文文件/root/.mylogin.cnf
# 权限加固
chmod 600 /root/.mylogin.cnf
# 测试连通
/apps/svr/mysql/bin/mysql --login-path=monitor
问题11：MySQL8.0 8.0.19 之后移除了 INNODB_LOCK_WAITS！旧表废弃，需要改用新视图 sys.innodb_lock_waits
ERROR 1109 (42S02) Unknown table 'INNODB_LOCK_WAITS' in information_schema
MySQL8.0 8.0.19 之后移除了 INNODB_LOCK_WAITS！
旧表废弃，需要改用新视图 sys.innodb_lock_waits
注意：原 SQL 直接失效，需要重写锁等待查询语句。
##############################################################################
#2.登录数据库
/apps/svr/mysql/bin/mysql -uroot -p -S /apps/svr/mysql/mysql.sock
##############################################################################
附录C——运维基础脚本
[root@sysmt bin]# cat /apps/svr/mysql/bin
bin/ binlog/
[root@sysmt bin]# cat /apps/svr/mysql/bin/start_process.sh
#!/bin/bash
/apps/svr/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf.d/my.cnf --user=mysql &
[root@sysmt bin]# cat /apps/svr/mysql/bin/stop_process.sh
#!/bin/bash
/apps/svr/mysql/bin/mysqladmin -uroot -p'Jonj5pbS@8%t0SoM1%11' shutdown
[root@sysmt bin]# cat /apps/svr/mysql/bin/init_process.sh
#!/bin/bash
/apps/svr/mysql/bin/mysqld --defaults-file=/etc/my.cnf.d/my.cnf --initialize --user=mysql
[root@sysmt bin]# chmod +x /apps/svr/mysql/bin/*
[root@sysmt bin]# chown -R mysql:mysql /apps/svr/mysql/
##############################################################################
##############################################################################
附录D——基础运维技巧
#❌ 禁止行为
不要一次性批量执行大量 DELETE 后忘记 commit（长时间持有行锁，引发阻塞）
会话断开风险：会话异常断开、窗口关闭，未提交事务会自动 ROLLBACK（特性，有利有弊）
1、关闭自动提交事务
核心目的：生产环境禁止隐式事务自动提交，所有 DML（INSERT/UPDATE/DELETE）必须人工执行 COMMIT 才生效，防止误删、误更新引发重大事故；出错随时 ROLLBACK。
2、应用程序保持 autocommit=1；仅 DBA 人工操作客户端强制 autocommit=0
实现方式：
my.cnf 不修改全局 autocommit=0（默认 1，保障业务正常运行）
DBA 登录脚本封装，每次连接自动执行：
# dba_mysql.sh 封装脚本
/apps/svr/mysql/bin/mysql -uroot -p -S /apps/svr/mysql/mysql.sock -e "SET SESSION autocommit=0;"
密码：Jonj5pbS@8%t0SoM1%11
# 检查mysql数据库会话和全局autocommit自动提交事务是否开启？
mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql> SHOW SESSION VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
# 致命大坑：全局 autocommit=0 不代表客户端一定生效！
# 很多可视化工具、mysql 客户端连接建立后强制 SET SESSION autocommit=1，覆盖全局配置。
# 注意：需要 SYSTEM_VARIABLES_ADMIN 权限。
优势：无论客户端怎么设置，连上数据库立刻强制关闭自动提交。
DELIMITER //
CREATE TRIGGER trg_set_autocommit AFTER LOGON ON ALL SCHEMAS
BEGIN
SET SESSION autocommit = 0;
END //
DELIMITER ;
3、规范客户端连接参数（运维强制要求）
/apps/svr/mysql/bin/mysql -uroot -p -S /apps/svr/mysql/mysql.sock --skip-autocommit
可视化工具配置：
DBeaver：连接属性 → 驱动属性 → autocommit=0
Navicat：连接 → 高级 → 取消勾选【自动提交】
4、开发 / DBA 操作标准事务模板（生产强制规范）
# 安全标准写法（autocommit=0 环境下）
BEGIN;
-- autocommit=0环境无需手动begin；当然加上更清晰
START TRANSACTION;
UPDATE table SET col='xxx' WHERE id=100;
DELETE FROM table WHERE create_time < '2024-01-01';
-- 1. 先查询核对变更数据，确认无误再提交
SELECT * FROM table WHERE id=100;
-- 确认正确执行提交
COMMIT;
-- 发现错误立刻回滚
-- ROLLBACK;
5、运维配套风险补偿策略（重点！）
# 禁止长时间未提交事务（长事务灾难）
增加巡检脚本监控未提交事务，纳入你之前的 mysql 巡检 shell：
SELECT
trx_id,
trx_state,
trx_started,
trx_query,
TIME_TO_SEC(TIMEDIFF(NOW(),trx_started)) AS trx_duration
FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(),trx_started)) > 60; -- 超过60s未提交事务告警
# 权限隔离
普通业务账号禁止直接 DELETE 不带 WHERE；
DBA 操作数据变更必须走工单，禁止直连生产删改。
# 开启 binlog + 定时备份
即使开启手动 commit，依然定期全量备份 + binlog，作为最后恢复手段。
6、用msyql密文密码登入数据库
【进阶】使用 mysql_config_editor 生成加密登录凭据文件 .mylogin.cnf（二进制密文，不可直接读取）
# 1：创建官方加密凭据（消除明文警告）
/apps/svr/mysql/bin/mysql_config_editor set \
--login-path=monitor \
--user=root \
--socket=/apps/svr/mysql/mysql.sock \
--password
输入密码：
# 权限加固
chmod 600 /root/.mylogin.cnf
# 测试连通
/apps/svr/mysql/bin/mysql --login-path=monitor
7、通过上述密文密码测试巡检脚本
#!/bin/bash
# MySQL8.0巡检脚本 修复版
# 特性：mysql_config_editor密文登录、适配8.0锁等待视图、过滤系统库碎片
MYSQL_BIN="/apps/svr/mysql/bin/mysql"
MYSQL_SOCK="/apps/svr/mysql/mysql.sock"
LOGIN_PATH="monitor"
#阈值
LONG_TRX_SEC=60
MAX_CONN_ALERT=800
FRAG_RATE_THRESHOLD=20
LOG_FILE="/var/log/mysql_monitor.log"
DATE=$(date "+%Y-%m-%d %H:%M:%S")
log_print(){
echo "[$DATE] $1"
echo "[$DATE] $1" >> ${LOG_FILE}
}
log_print "===================== 开始MySQL巡检 ====================="
# 检测socket与进程
if [ ! -S ${MYSQL_SOCK} ];then
log_print "【严重告警】MySQL socket文件不存在，数据库未启动！"
else
log_print "【正常】Socket文件存在：${MYSQL_SOCK}"
fi
PID_CHECK=$(pgrep mysqld)
if [ -z "${PID_CHECK}" ];then
log_print "【严重告警】mysqld进程不存在！"
else
log_print "【正常】mysqld进程PID: ${PID_CHECK}"
fi
# 数据库执行函数（密文登录，无明文密码告警）
mysql_exec(){
${MYSQL_BIN} --login-path=${LOGIN_PATH} -N -e "$1"
}
# 连通性校验
TEST_CONN=$(mysql_exec "SELECT 1;")
if [ "${TEST_CONN}" != "1" ];then
log_print "【致命错误】数据库连接失败，请检查mysql_config_editor凭据！"
log_print "===================== MySQL巡检终止 =====================
"
exit 1
fi
## 1、autocommit参数检查
AUTO_COMMIT_GLOBAL=$(mysql_exec "SELECT @@GLOBAL.autocommit;")
if [ "${AUTO_COMMIT_GLOBAL}" = "0" ];then
log_print "【配置检查】全局autocommit=0（手动提交模式）"
else
log_print "【提示】全局autocommit=1，DBA操作请使用mysql-dba.sh关闭会话自动提交"
fi
## 2、连接数监控
MAX_CONN=$(mysql_exec "SELECT @@max_connections;")
CUR_CONN=$(mysql_exec "SELECT COUNT(*) FROM information_schema.PROCESSLIST;")
log_print "【连接统计】当前连接数:${CUR_CONN} / 最大连接上限:${MAX_CONN}"
if [[ -n ${CUR_CONN} && ${CUR_CONN} -gt ${MAX_CONN_ALERT} ]];then
log_print "【告警】连接数超过阈值${MAX_CONN_ALERT}"
fi
ACTIVE_CONN=$(mysql_exec "SELECT COUNT(*) FROM information_schema.PROCESSLIST WHERE command!='Sleep';")
SLEEP_CONN=$(mysql_exec "SELECT COUNT(*) FROM information_schema.PROCESSLIST WHERE command='Sleep';")
log_print "【会话细分】活跃会话:${ACTIVE_CONN} | 休眠会话:${SLEEP_CONN}"
##3、长事务检测
log_print "----------------【长事务检测】----------------"
LONG_TRX=$(mysql_exec "
SELECT
trx_id,
trx_started,
TIME_TO_SEC(TIMEDIFF(NOW(),trx_started)) duration_sec,
trx_state,
trx_query,
trx_mysql_thread_id
FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(),trx_started)) > ${LONG_TRX_SEC};
")
if [ -z "${LONG_TRX}" ];then
log_print "【正常】无超过${LONG_TRX_SEC}秒的未提交事务"
else
log_print "【告警】发现长时间未提交事务！"
echo "${LONG_TRX}" >> ${LOG_FILE}
echo "${LONG_TRX}"
fi
##4、锁阻塞检测【适配MySQL8.0新视图 sys.innodb_lock_waits】
log_print "----------------【锁阻塞检测】----------------"
LOCK_WAIT=$(mysql_exec "
SELECT
waiting_trx_id,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query
FROM sys.innodb_lock_waits;
")
if [ -z "${LOCK_WAIT}" ];then
log_print "【正常】当前无锁等待阻塞"
else
log_print "【告警】存在SQL锁竞争阻塞！"
echo "${LOCK_WAIT}" >> ${LOG_FILE}
echo "${LOCK_WAIT}"
fi
##5、InnoDB碎片检测【过滤系统库，避免大量系统表干扰】
log_print "----------------【业务表碎片检测】----------------"
FRAG_TABLE=$(mysql_exec "
SELECT
TABLE_SCHEMA,
TABLE_NAME,
DATA_LENGTH,
DATA_FREE,
ROUND(DATA_FREE/(DATA_LENGTH+DATA_FREE)*100,2) frag_rate
FROM information_schema.TABLES
WHERE ENGINE='InnoDB'
AND DATA_LENGTH>0
AND ROUND(DATA_FREE/(DATA_LENGTH+DATA_FREE)*100,2) > ${FRAG_RATE_THRESHOLD}
AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY frag_rate DESC LIMIT 10;
")
if [ -z "${FRAG_TABLE}" ];then
log_print "【正常】无碎片率大于${FRAG_RATE_THRESHOLD}%的业务表"
else
log_print "【提示】存在高碎片业务表，业务低峰择期执行OPTIMIZE TABLE"
echo "${FRAG_TABLE}" >> ${LOG_FILE}
echo "${FRAG_TABLE}"
fi
##6、慢查询配置
SLOW_LOG=$(mysql_exec "SELECT @@slow_query_log;")
SLOW_TIME=$(mysql_exec "SELECT @@long_query_time;")
log_print "【慢日志】开关:${SLOW_LOG} 慢查询阈值:${SLOW_TIME}秒"
##7、双主复制状态
log_print "----------------【复制状态检查（双主）】----------------"
REPL_STATUS=$(mysql_exec "SHOW SLAVE STATUS\G")
IO_RUN=$(echo "${REPL_STATUS}" | grep 'Slave_IO_Running:' | awk '{print $2}')
SQL_RUN=$(echo "${REPL_STATUS}" | grep 'Slave_SQL_Running:' | awk '{print $2}')
if [ -z "${IO_RUN}" ];then
log_print "【提示】本机未配置从库复制（单机模式）"
else
log_print "IO线程状态:${IO_RUN} SQL线程状态:${SQL_RUN}"
if [ "${IO_RUN}" != "Yes" ] || [ "${SQL_RUN}" != "Yes" ];then
log_print "【告警】复制线程异常！"
fi
fi
log_print "===================== MySQL巡检结束 =====================
"
8、
##############################################################################
浙公网安备 33010602011771号