-
一、mysql启动过程
-
1.1 查看启动读取配置文件顺序
-
-
二、三种启动方式的关系（mysqld）
-
2.1 mysqld - MySQL服务器核心
-
2.2 mysqld_safe - MySQL服务器安全启动脚本
-
2.3 mysqld_multi - 管理多个MySQL实例
-
2.4 总结对比
-
-
三、mysql8.0所有动态、静态参数详解
-
3.1 动态参数（可以在运行时修改）
-
3.2 静态参数（需要重启生效）
-
3.3 实用查询：查看所有动态参数
-
3.4 参数修改示例
-
3.5 验证参数修改
-
-
四、my.cnf详细讲解
-
4.1 [client] 段
-
4.2 [mysql] 段
-
4.3 [mysqld] 段
-
-
五、MySQL各类日志介绍
-
5.1 错误日志
-
5.2 二进制日志
-
5.3 通用查询日志
-
5.4 慢查询日志
-
5.5 重做日志
-
5.6 中继日志（Relay Log）
-
5.7 回滚日志
-
5.8 总结与管理建议
-
-
六、MySQL其它文件介绍
-
6.1 socket套字节文件
-
6.2 mysql.pid文件
-
6.3 mysql表结构文件
-
-
七、innodb存储引擎表空间文件
-
7.1 系统表空间
-
7.2 独立表空间
-
7.3 通用表空间
-
7.4 临时表空间
-
7.5 Undo 表空间
-
-
八、mysql权限管理
-
8.1 权限系统架构
-
8.2 权限表详细结构
-
8.3 用户管理操作
-
8.4 权限授予操作
-
8.5 权限回收操作
-
8.6 权限查看和审计
-
8.7 安全最佳实践
-
8.8 常见权限场景
-
8.9 权限问题排查
-
8.10 权限维护脚本
-
-
九、角色管理
-
9.1 角色管理基础概念
-
9.2 角色管理完整操作流程
-
9.3 角色激活机制
-
9.4 角色管理维护操作
-
9.5 实战示例：完整的权限管理体系
-
9.6 最佳实践和注意事项
-
-
十、SSL安全证书配置
-
10.1 mysql.5.7 版本SSL配置步骤
-
10.2 mysql8.0 默认为安装了SSL，如何使用
-
-
十一、MySQL存储引擎与InnoDB体系架构
-
11.1 MySQL存储引擎介绍
-
11.2 InnoDB核心架构详解
-
11.3 InnoDB存储引擎的常用参数配置（生产环境的常用参数）
-
11.4 buffer pool 核心工作原理
-
11.5 binlog与redolog，undo之间的区别
-
-
十二、mysql事务
-
12.1 事务与 ACID 属性
-
12.2 并发事务可能遇到的问题
-
12.3 MySQL 的四种隔离级别
-
12.4 各种隔离级别的问题对比
-
12.5 实际演示
-
12.6 如何选择和设置隔离级别
-
-
十三、mysql 锁
-
13.1 锁的层级体系
-
13.2 锁的类型分类
-
13.3 重要锁相关参数详解
-
13.4 锁工作机制实战案例
-
13.5 死锁的产生与处理
-
13.6 锁监控与性能优化
-
一、mysql启动过程
1.1 查看启动读取配置文件顺序
[root@centos9 ~]# mysql --help|grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
二、三种启动方式的关系（mysqld）
简单来说，这三者的关系是层层封装和功能增强的关系：
-
mysqld：MySQL数据库服务器的核心进程，是真正干活的“发动机”。
-
mysqld_safe：一个启动脚本，负责启动 mysqld 进程，并充当其“守护者”，确保其稳定运行。
-
mysqld_multi：另一个启动脚本，用于管理多个MySQL实例。它本身不直接启动 mysqld，而是通过调用 mysqld_safe 来间接启动和管理多个 mysqld 进程。
关系可以表示为：mysqld_multi -> mysqld_safe -> mysqld
2.1 mysqld - MySQL服务器核心
这是MySQL数据库的核心后台守护进程。所有数据库的底层操作，如处理SQL查询、管理表、维护索引、处理连接等，都是由它来完成的。
-
定位：核心引擎，直接与数据和内存打交道。
- 启动方式：
# 直接启动（不推荐在生产环境中使用） ./bin/mysqld --defaults-file=/path/to/my.cnf --user=mysql & -
特点：
-
功能纯粹，只负责数据库服务本身。
-
如果进程因错误意外终止，它不会自动重启。
-
默认情况下，它不会将错误日志或运行日志重定向到文件（除非在配置中指定）。
-
直接使用 mysqld 启动，对进程的监控和管理需要依赖外部工具（如 systemd）。
-
2.2 mysqld_safe - MySQL服务器安全启动脚本
mysqld_safe 是一个 Shell 脚本，主要目标是安全、稳定地启动和守护 mysqld 进程。在旧版本的系统（没有使用 systemd）中，它是首选的启动方式。
-
定位：mysqld 的守护者和包装器。
- 启动方式：
# 常见的启动命令 ./bin/mysqld_safe --defaults-file=/path/to/my.cnf --user=mysql & -
启动过程与核心功能：
-
mysqld_safe 会持续监控 mysqld 进程的状态。
-
如果 mysqld 进程因任何原因意外崩溃或停止，mysqld_safe 会自动重新启动它。这是它最重要的功能之一。
-
检查环境：检查数据目录、权限等。
-
参数传递：读取配置文件（如 my.cnf）和命令行参数，并将这些参数传递给 mysqld。
-
启动 mysqld：使用配置好的参数，在后台 fork 并执行 mysqld 进程。
-
守护进程：
-
日志重定向：它会将 mysqld 的 stderr（标准错误）重定向到错误日志文件（默认是 host_name.err），确保错误信息被记录下来。
-
崩溃记录：如果 mysqld 崩溃，它可能会尝试记录一些崩溃信息（如堆栈跟踪）到日志文件中。
-
自身常驻：mysqld_safe 进程本身会一直保持运行，直到你明确地停止它。
-
关系小结：mysqld_safe 是 mysqld 的“保姆”，确保它好好工作，出了事能重启，并且把错误都记录下来。
2.3 mysqld_multi - 管理多个MySQL实例
mysqld_multi 也是一个 Shell 脚本，用于在一台物理服务器上方便地启动、停止和管理多个MySQL实例。每个实例都有自己的配置文件、数据目录、端口和socket文件。
-
定位：多实例管理器，是 mysqld_safe 的“调度员”。
- 启动方式：
# 启动实例组1 (GNR = Group Number) ./bin/mysqld_multi --defaults-file=/path/to/my_multi.cnf start 1 # 停止实例组2 ./bin/mysqld_multi --defaults-file=/path/to/my_multi.cnf stop 2 -
启动过程与核心功能：
-
start N：启动实例 N -
stop N：停止实例 N（通过向正确的 pid-file 发送信号） -
report N：报告实例 N 的状态
- 读取配置：它依赖于一个特殊的配置文件（如 /etc/my.cnf 或指定的文件）。在这个文件中，每个MySQL实例用一个 [mysqldN] 组来配置，其中 N 是一个数字编号（GNR）。
[mysqld1] datadir = /var/lib/mysql1 socket = /var/lib/mysql1/mysql.sock port = 3307 user = mysql pid-file = /var/lib/mysql1/mysql.pid [mysqld2] datadir = /var/lib/mysql2 socket = /var/lib/mysql2/mysql.sock port = 3308 user = mysql pid-file = /var/lib/mysql2/mysql.pid -
定位启动程序：当执行
mysqld_multi start 1时，它会根据配置找到对应的 [mysqld1] 组。 -
调用 mysqld_safe：mysqld_multi 本身并不直接启动 mysqld，而是去调用 mysqld_safe 脚本，并将 [mysqld1] 的配置选项传递给 mysqld_safe。本质上，它执行的命令类似于：
mysqld_safe --defaults-group-suffix=1 ... -
后续过程：然后，mysqld_safe 按照它自己的流程去启动和管理对应的 mysqld 进程。
-
管理功能：mysqld_multi 提供了统一的命令来管理这些实例：
-
关系小结：mysqld_multi 是 mysqld_safe 的“经理”，它自己不直接管理“工人”（mysqld），而是通过给“保镖”（mysqld_safe）下达指令来间接管理。
2.4 总结对比
|
特性 |
mysqld |
mysqld_safe |
mysqld_multi |
|---|---|---|---|
|
本质 |
二进制核心进程 |
Shell 脚本 |
Shell 脚本 |
|
角色 |
发动机/工人 |
保镖/保姆 |
经理/调度员 |
|
主要目的 |
提供数据库服务 |
安全地启动和守护 mysqld |
方便地管理多个MySQL实例 |
|
自动重启 |
否 |
是 |
通过 mysqld_safe 实现 |
|
日志重定向 |
需配置 |
自动重定向 stderr 到文件 |
通过 mysqld_safe 实现 |
|
使用场景 |
底层启动，通常不直接使用 |
传统Unix系统上的首选启动方式 |
单机多实例环境 |
|
现代替代 |
仍然是核心 |
很大程度上被 systemd 替代 |
可被多个 systemd 服务文件替代 |
三、mysql8.0所有动态、静态参数详解
3.1 动态参数（可以在运行时修改）
3.1.1 内存相关动态参数
-- 这些参数可以在运行时修改
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- InnoDB缓冲池大小
SET GLOBAL key_buffer_size = 268435456; -- MyISAM键缓存大小
SET GLOBAL query_cache_size = 0; -- 查询缓存大小（8.0已废弃）
SET GLOBAL tmp_table_size = 67108864; -- 临时表内存大小
SET GLOBAL max_heap_table_size = 67108864; -- 内存表最大大小
SET GLOBAL sort_buffer_size = 262144; -- 排序缓存大小
SET GLOBAL read_buffer_size = 131072; -- 顺序读缓存大小
SET GLOBAL read_rnd_buffer_size = 262144; -- 随机读缓存大小
SET GLOBAL join_buffer_size = 262144; -- 连接操作缓存大小
3.1.2 连接相关动态参数
SET GLOBAL max_connections = 500; -- 最大连接数
SET GLOBAL max_user_connections = 100; -- 单用户最大连接数
SET GLOBAL max_connect_errors = 100; -- 最大连接错误数
SET GLOBAL thread_cache_size = 16; -- 线程缓存大小
3.1.3 超时相关动态参数
SET GLOBAL wait_timeout = 600; -- 非交互连接超时时间（秒）
SET GLOBAL interactive_timeout = 600; -- 交互连接超时时间（秒）
SET GLOBAL connect_timeout = 10; -- 连接握手超时时间（秒）
SET GLOBAL net_read_timeout = 30; -- 网络读取超时时间（秒）
SET GLOBAL net_write_timeout = 60; -- 网络写入超时时间（秒）
SET GLOBAL lock_wait_timeout = 31536000; -- 锁等待超时时间（秒）
SET GLOBAL innodb_lock_wait_timeout = 50; -- InnoDB锁等待超时时间（秒）
3.1.4 日志相关动态参数
SET GLOBAL slow_query_log = 1; -- 启用慢查询日志
SET GLOBAL general_log = 0; -- 启用通用查询日志
SET GLOBAL log_queries_not_using_indexes = 0; -- 记录未使用索引的查询
SET GLOBAL long_query_time = 2; -- 慢查询时间阈值（秒）
SET GLOBAL binlog_format = 'ROW'; -- 二进制日志格式
SET GLOBAL expire_logs_days = 7; -- 二进制日志过期天数（8.0推荐用binlog_expire_logs_seconds）
3.1.5 InnoDB相关动态参数
SET GLOBAL innodb_flush_log_at_trx_commit = 1; -- 事务提交日志刷新方式
SET GLOBAL innodb_log_buffer_size = 16777216; -- InnoDB日志缓冲区大小
SET GLOBAL innodb_io_capacity = 200; -- InnoDB I/O容量
SET GLOBAL innodb_autoinc_lock_mode = 2; -- 自增锁模式
SET GLOBAL innodb_stats_on_metadata = 0; -- 元数据统计更新
3.1.6 复制相关动态参数
SET GLOBAL server_id = 2; -- 服务器ID
SET GLOBAL read_only = 0; -- 只读模式
SET GLOBAL super_read_only = 0; -- 超级只读模式
SET GLOBAL binlog_checksum = 'CRC32'; -- 二进制日志校验和
3.2 静态参数（需要重启生效）
3.2.1 文件路径相关静态参数
# 在 my.cnf 配置文件中设置
datadir = /var/lib/mysql # 数据目录
socket = /var/run/mysqld/mysqld.sock # Socket文件路径
tmpdir = /tmp # 临时文件目录
slave_load_tmpdir = /tmp # 从库临时目录
3.2.2 网络和端口相关静态参数
port = 3306 # 监听端口
bind-address = 0.0.0.0 # 绑定地址
server_id = 1 # 服务器ID（重启生效版本）
3.2.3 存储引擎相关静态参数
innodb_data_file_path = ibdata1:12M:autoextend # InnoDB系统表空间文件
innodb_log_files_in_group = 2 # InnoDB日志文件组数量
innodb_log_file_size = 50331648 # InnoDB日志文件大小
innodb_file_per_table = 1 # 每表独立表空间
default_storage_engine = InnoDB # 默认存储引擎
3.2.4 字符集相关静态参数
character-set-server = utf8mb4 # 服务器默认字符集
collation-server = utf8mb4_unicode_ci # 服务器默认排序规则
3.2.5 其他重要静态参数
transaction-isolation = READ-COMMITTED # 事务隔离级别
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-bin = mysql-bin # 启用二进制日志
relay-log = mysql-relay-bin # 中继日志文件名
3.3 实用查询：查看所有动态参数
-- 查看常见动态参数及其当前值
SELECT
VARIABLE_NAME,
VARIABLE_VALUE,
'Dynamic' AS PARAMETER_TYPE,
CASE
WHEN VARIABLE_NAME LIKE '%timeout%' THEN '超时控制参数'
WHEN VARIABLE_NAME LIKE '%buffer%' OR VARIABLE_NAME LIKE '%size%' THEN '内存分配参数'
WHEN VARIABLE_NAME LIKE '%log%' THEN '日志相关参数'
WHEN VARIABLE_NAME LIKE '%innodb%' THEN 'InnoDB存储引擎参数'
WHEN VARIABLE_NAME LIKE '%max%' THEN '限制类参数'
ELSE '其他动态参数'
END AS PARAMETER_CATEGORY
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
'wait_timeout', 'interactive_timeout', 'max_connections',
'innodb_buffer_pool_size', 'key_buffer_size', 'sort_buffer_size',
'read_buffer_size', 'read_rnd_buffer_size', 'join_buffer_size',
'tmp_table_size', 'max_heap_table_size', 'thread_cache_size',
'slow_query_log', 'general_log', 'long_query_time',
'innodb_flush_log_at_trx_commit', 'innodb_lock_wait_timeout',
'lock_wait_timeout', 'net_read_timeout', 'net_write_timeout'
)
ORDER BY PARAMETER_CATEGORY, VARIABLE_NAME;
3.4 参数修改示例
3.4.1 动态参数修改（立即生效）
-- 修改内存参数
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB
SET GLOBAL max_connections = 1000;
-- 修改超时参数
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;
-- 修改日志参数
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
3.4.2 静态参数修改（需要重启）
# 在 /etc/my.cnf 中添加
[mysqld]
# 内存和性能
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
# 连接设置
max_connections = 1000
# 文件路径
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
3.5 验证参数修改
-- 查看参数当前值
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
-- 查看参数是否动态
SELECT
VARIABLE_NAME,
VARIABLE_VALUE,
VARIABLE_SOURCE
FROM performance_schema.variables_info
WHERE VARIABLE_NAME IN ('innodb_buffer_pool_size', 'max_connections');
四、my.cnf详细讲解
4.1 [client] 段
port=3306 # 客户端连接默认端口
socket=/mysql/3306/mysql.sock # 客户端连接使用的 socket 文件路径
4.2 [mysql] 段
no-beep # 关闭错误提示音
prompt="\u@mysqldb \R:\m:\s [\d]> " # 设置 MySQL 客户端提示符格式
auto-rehash # 自动补全数据库、表名和列名
default-character-set=utf8mb4 # 客户端默认字符集
4.3 [mysqld] 段
4.3.1 基础设置
server-id=3306 # 服务器ID，主从复制时用于标识不同的服务器
port=3306 # MySQL 服务监听端口
user=mysql # MySQL 服务运行的系统用户
bind_address=0.0.0.0 # 绑定地址，0.0.0.0表示监听所有网络接口
basedir=/mysql/app/mysql # MySQL 安装基础目录
datadir=/mysql/3306/data # 数据文件存储目录
socket=/mysql/3306/mysql.sock # MySQL 服务的 socket 文件路径
pid-file=/mysql/3306/mysql.pid # 进程ID文件路径
character-set-server=utf8mb4 # 服务器默认字符集，支持4字节UTF-8编码
federated # 启用 FEDERATED 存储引擎支持
max_connections=800 # 最大客户端连接数
max_connect_errors=1000 # 最大连接错误数，超过则阻止主机连接
default-storage-engine=INNODB # 默认存储引擎
transaction_isolation=READ-COMMITTED # 事务隔离级别，读已提交
explicit_defaults_for_timestamp=1 # 严格控制 timestamp 字段的默认值
4.3.2 内存和缓存设置
sort_buffer_size=32M # 每个会话的排序缓冲区大小
join_buffer_size=128M # 表连接操作的缓冲区大小
tmp_table_size=72M # 临时表的最大内存大小
max_allowed_packet=16M # 服务器和客户端之间传输的最大数据包大小
interactive_timeout=1800 # 交互式连接超时时间（秒）
wait_timeout=1800 # 非交互式连接超时时间（秒）
read_buffer_size=16M # 顺序读取表的缓冲区大小
read_rnd_buffer_size=32M # 随机读取表的缓冲区大小
table_open_cache=2000 # 表描述符缓存大小
thread_cache_size=768 # 线程缓存大小，用于缓存空闲线程
key_buffer_size=32M # MyISAM 索引缓冲区大小（虽然使用InnoDB，但保留）
back_log=1024 # 连接请求队列大小
open_files_limit=65536 # MySQL 可以打开的最大文件数
table_definition_cache=1400 # 表定义信息缓存大小
4.3.3 日志设置
log-output=FILE # 日志输出方式，FILE表示写入文件
general-log=0 # 关闭通用查询日志（生产环境建议关闭）
general_log_file=/mysql/3306/log/all-general.err # 通用查询日志文件路径
slow-query-log=ON # 启用慢查询日志
slow_query_log_file=/mysql/3306/log/slow-query.err # 慢查询日志文件路径
long_query_time=10 # 慢查询时间阈值（秒）
log-error=/mysql/3306/log/log-error.err # 错误日志文件路径
log_queries_not_using_indexes=1 # 记录未使用索引的查询
log_slow_admin_statements=1 # 记录管理类的慢查询
log_slow_replica_statements=1 # 记录从库复制的慢查询（MySQL 8.0+）
log_throttle_queries_not_using_indexes=10 # 限制未使用索引查询的日志频率
binlog_expire_logs_seconds=604800 # binlog 过期时间（秒），7天（MySQL 8.0+）
log-bin=/mysql/3306/data/mysql-bin # binlog 文件路径和前缀
min_examined_row_limit=100 # 查询至少检查的行数才可能被记录为慢查询
binlog_format = row # 二进制日志的MODE
log_bin_index=/mysql/3306/binlog/mysql-bin.index # 二进制日志索引
binlog_format 说明：
-
STATEMENT：记录原始的 SQL 语句。日志量小，但某些函数（如 NOW()）可能导致主从不一致。 -
ROW：记录每一行数据的变化细节。日志量大，但能保证数据一致性。（MySQL 5.7.7 后的默认模式） -
MIXED：混合模式，一般情况下用 STATEMENT，在不安全时自动切换到 ROW。
4.3.4 InnoDB 设置
innodb_io_capacity=4000 # InnoDB 后台任务的最大I/O能力
innodb_io_capacity_max=8000 # InnoDB 后台任务的最大I/O能力上限
innodb_buffer_pool_size=5000M # InnoDB 缓冲池大小，重要性能参数
innodb_buffer_pool_instances=8 # 缓冲池实例数量，提高并发性
innodb_buffer_pool_load_at_startup=1 # 启动时加载缓冲池
innodb_buffer_pool_dump_at_shutdown=1 # 关闭时保存缓冲池状态
innodb_lru_scan_depth=2000 # 缓冲池LRU算法扫描深度
innodb_lock_wait_timeout=5 # InnoDB 行锁等待超时时间（秒）
innodb_redo_log_capacity=400M # 重做日志容量（MySQL 8.0+ 替代旧参数）
innodb_log_buffer_size=16M # 重做日志缓冲区大小
innodb_flush_neighbors=1 # 刷新相邻脏页，优化磁盘I/O
innodb_purge_threads=4 # InnoDB 清理线程数量
innodb_thread_concurrency=64 # InnoDB 并发线程数限制
innodb_print_all_deadlocks=1 # 打印所有死锁信息到错误日志
innodb_strict_mode=1 # 启用严格的InnoDB检查模式
innodb_sort_buffer_size=64M # 创建索引时的排序缓冲区大小
innodb_flush_log_at_trx_commit=1 # 事务提交时日志刷新方式（1=最安全）
innodb_autoextend_increment=64 # 表空间自动扩展增量（MB）
innodb_concurrency_tickets=5000 # 每个线程的并发票据数
innodb_old_blocks_time=1000 # 页在旧子列表中的存活时间（毫秒）
innodb_open_files=65536 # InnoDB 可打开的文件数
innodb_stats_on_metadata=0 # 关闭元数据统计信息自动更新
innodb_file_per_table=1 # 每个表使用独立的表空间文件
innodb_checksum_algorithm=0 # 校验和算法
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G # 系统表空间文件配置
innodb_temp_data_file_path=ibtmp1:200M:autoextend:max:20G # 临时表空间文件配置
innodb_buffer_pool_dump_pct=40 # 缓冲池转存百分比
innodb_page_cleaners=4 # 页面清理线程数量
innodb_purge_rseg_truncate_frequency=128 # 清理回滚段频率
binlog_gtid_simple_recovery=1 # 简化GTID恢复过程
log_timestamps=system # 日志时间戳使用系统时区
五、MySQL各类日志介绍
|
日志类型 |
默认开启 |
主要用途 |
对性能影响 |
关键性 |
|---|---|---|---|---|
|
错误日志 |
是 |
记录启动、运行、停止时的错误、警告信息 |
极低 |
高 (排障必备) |
|
二进制日志 |
否 (MySQL 8.0+ 默认ON) |
主从复制、数据恢复 (PITR) |
中 |
高 (生产环境必备) |
|
通用查询日志 |
否 |
记录所有到达 MySQL 的请求 |
高 |
低 (调试用) |
|
慢查询日志 |
否 |
记录执行时间过长的查询 |
中低 |
中高 (优化必备) |
|
重做日志 |
是 (InnoDB特有) |
事务的持久性、崩溃恢复 |
中 |
极高 (引擎核心) |
|
回滚日志 |
是 (InnoDB特有) |
事务的原子性、MVCC |
中 |
极高 (引擎核心) |
5.1 错误日志
这是诊断数据库问题的首要入口。
-
功能：记录 MySQL 服务进程（mysqld）在启动、运行、停止过程中产生的错误信息、警告信息，以及在主从复制环境中从库的启动信息。
-
内容示例：
-
服务器启动和关闭的日志。
-
运行时的严重错误（例如，表损坏）。
-
执行 GRANT 或 REVOKE 语句时的错误。
-
从库 I/O/SQL 线程的启动和状态信息。
-
- 配置参数：
log_error指定错误日志文件的路径。[mysqld] log_error = /var/log/mysql/mysql-error.log -
查看方式：
-
直接查看文件内容。
-
使用
SHOW VARIABLES LIKE 'log_error';定位文件位置。
-
-
使用场景：任何数据库无法启动、运行异常时的首要排查点。
错误日志记录级别：
mysql> show variables like '%log_error%';
+---------------------+-------------------------------+
| Variable_name | Value|
+---------------------+-------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /mysql/3306/log/log-error.err |
| log_error_verbosity | 3|
+---------------------+------
-- log_error_verbosity 设置记录错误日志的级别，默认级别为：3
-- 1：只记录错误日志
-- 2：记录错误和警告日志
-- 3：记录错误、警告、正常的信息
5.2 二进制日志
这是实现数据备份与恢复和主从复制的基石。
-
功能：以二进制格式记录所有对数据库的数据修改操作（INSERT, UPDATE, DELETE, DDL 等），但不包括 SELECT 和 SHOW 这类不修改数据的查询。
-
核心用途：
-
主从复制：主库将 Binlog 发送给从库，从库重放这些事件，从而保持数据同步。
-
数据恢复：结合全量备份，可以通过重放 Binlog 将数据库恢复到某个特定时间点（Point-in-Time Recovery, PITR）。
-
审计：用于日志挖掘。
-
-
记录模式：
-
STATEMENT：记录原始的 SQL 语句。日志量小，但某些函数（如 NOW()）可能导致主从不一致。 -
ROW：记录每一行数据的变化细节。日志量大，但能保证数据一致性。（MySQL 5.7.7 后的默认模式） -
MIXED：混合模式，一般情况下用 STATEMENT，在不安全时自动切换到 ROW。
-
- 配置参数：
[mysqld] # 启用二进制日志，并指定文件名前缀 log_bin = /var/log/mysql/mysql-bin # 设置日志格式 binlog_format = ROW # 设置单个日志文件大小 max_binlog_size = 100M # 设置日志过期时间（秒） binlog_expire_logs_seconds = 604800 log_bin_index=/mysql/3306/binlog/mysql-bin.index # 二进制日志索引文件 -
相关工具：
mysqlbinlog：官方工具，用于解析和查看 Binlog 文件内容。mysqlbinlog /var/log/mysql/mysql-bin.000001
-
使用场景：生产环境必须开启，用于数据灾难恢复和构建读写分离架构。
5.3 通用查询日志
这是一个“流水账”日志，会记录所有客户端的连接和执行的语句。
-
功能：记录所有到达 MySQL 的连接和执行的 SQL 语句，无论其对错。对性能影响较大，通常只在深度调试时开启。
-
内容：包括连接、断开、执行的所有查询。
- 配置参数：
[mysqld] # 启用通用查询日志 general_log = 1 # 指定日志文件路径（默认输出到文件） general_log_file = /var/log/mysql/mysql-general.log # 也可以输出到表（mysql.general_log） log_output = FILE, TABLE -
使用场景：用于审计、分析应用程序发送的确切 SQL，或在开发环境排查疑难杂症。生产环境一般不开启。
5.4 慢查询日志
这是数据库性能优化的利器。
-
功能：记录执行时间超过指定阈值（long_query_time）的查询，以及未使用索引的查询（如果配置了 log_queries_not_using_indexes）。
-
核心用途：找出执行效率低下的 SQL 语句，以便进行优化（如添加索引、重写 SQL）。
- 配置参数：
[mysqld] # 启用慢查询日志 slow_query_log = 1 # 指定日志文件路径 slow_query_log_file = /var/log/mysql/mysql-slow.log # 设置慢查询阈值（单位：秒） long_query_time = 2 # 记录未使用索引的查询（谨慎开启，可能日志量巨大） # log_queries_not_using_indexes = 1 # log_throttle_queries_not_using_indexes=10 # 未使用索引，每分钟只记录10次 -
分析工具：
-
mysqldumpslow：MySQL 自带的 Perl 脚本，用于汇总慢查询日志。 pt-query-digest：Percona Toolkit 中的强大工具，功能远超 mysqldumpslow，是分析慢日志的首选。pt-query-digest /var/log/mysql/mysql-slow.log
-
-
使用场景：定期分析，作为数据库性能监控和优化的常规手段。
5.5 重做日志
这是 InnoDB 存储引擎特有的、保证事务持久性（Durability）和崩溃恢复（Crash Recovery）的核心组件。
-
功能：在事务提交时，先将数据页的物理变化写入重做日志（Write-Ahead Logging, WAL 机制），而不是直接写入数据文件。当数据库崩溃重启时，InnoDB 会通过重做日志将已提交但未写入数据文件的事务重做（Redo），从而保证数据不丢失。
-
工作方式：
-
采用循环写入的方式，通常由两个文件（ib_logfile0, ib_logfile1）组成。
-
当第一个文件写满后，会切换到第二个文件；第二个文件写满后，又会覆盖第一个文件。
-
- 配置参数：
[mysqld] # 设置每个重做日志文件大小为 1GB innodb_log_file_size = 1G # 设置日志文件数量为 2 innodb_log_files_in_group = 2-
innodb_log_file_size：单个重做日志文件的大小。设置过小会导致频繁的检查点，影响性能；设置过大会增加崩溃恢复的时间。 -
innodb_log_files_in_group：日志文件组中的文件数量，默认为 2。
-
与 Binlog 的区别：
|
特性 |
重做日志 |
二进制日志 |
|---|---|---|
|
所属层级 |
InnoDB 存储引擎层 |
MySQL Server 层 |
|
记录内容 |
物理日志，记录页的修改 |
逻辑日志，记录 SQL 语句或行变化 |
|
写入时机 |
事务进行中持续写入 |
事务提交后一次性写入 |
|
主要用途 |
崩溃恢复 |
复制、PITR |
5.6 中继日志（Relay Log）
与二进制日志有点相似，用于存取从服务器的IO线程接收来自主服务器发来的变更日志。
主要作用：
-
数据中转存储：从主库接收的 binlog 事件先写入 relay log，作为临时存储缓冲区。
-
解耦接收和应用：IO 线程负责从主库拉取 binlog 并写入 relay log，SQL 线程负责从 relay log 读取并执行 SQL 事件，两个线程独立工作，提高复制效率。
-
故障恢复：网络中断时，从库可以从上次中断的位置继续复制。
-
数据一致性保证：确保从库按正确的顺序应用主库的更改。
5.7 回滚日志
同样是 InnoDB 特有的，用于保证事务的原子性（Atomicity）和实现 MVCC。
-
功能：
-
原子性：当事务需要回滚（ROLLBACK）时，InnoDB 使用回滚日志来将数据恢复到事务开始前的状态。
-
MVCC：为了实现多版本并发控制，当某个旧事务仍在读取某行数据时，InnoDB 会使用回滚日志来构建该行数据的一个旧版本快照，供该事务读取。
-
-
存储位置：在 MySQL 5.7 及之前，回滚日志存储在系统表空间的 ibdata 文件中。从 MySQL 8.0 开始，回滚日志被移至独立的撤销表空间，默认是两个文件（undo_001, undo_002），管理更加灵活。
- 配置参数（MySQL 8.0）：
[mysqld] # 启用独立表空间（8.0默认） innodb_undo_tablespaces = 2
5.8 总结与管理建议
-
必须开启的：
-
错误日志：永远开启，用于排障。
-
二进制日志：生产环境必须开启，用于备份恢复和复制。
-
慢查询日志：强烈建议开启，是性能优化的基础。
-
-
按需开启的：
-
通用查询日志：仅在需要跟踪所有 SQL 时临时开启，用完即关。
-
-
引擎核心（自动管理）：
-
重做日志和回滚日志：由 InnoDB 自动管理，DBA 主要负责设置合适的大小。
-
-
日志轮转与清理：
-
定期清理错误日志、慢查询日志。
-
为二进制日志设置合理的过期时间（
binlog_expire_logs_seconds），避免磁盘被写满。 -
使用
PURGE BINARY LOGS命令手动清理 Binlog。
-
六、MySQL其它文件介绍
6.1 socket套字节文件
核心解释：Socket 套接字文件是一种特殊类型的文件，存在于 Linux 和类 Unix 操作系统的文件系统中。它看起来像一个普通的文件，有文件名和路径（例如 /var/run/mysql/mysql.sock），但它不是用来存储数据的。它的主要作用是作为一个通信端点，为在同一台机器上运行的两个不同进程（程序）提供一种高效的进程间通信（IPC）机制。
mysql> show variables like '%sock%';
+-----------------------------------------+------------------------+
| Variable_name | Value|
+-----------------------------------------+------------------------+
| performance_schema_max_socket_classes | 10|
| performance_schema_max_socket_instances | -1|
| socket | /mysql/3306/mysql.sock |
+-----------------------------------------+------------------------+
6.2 mysql.pid文件
mysql.pid 文件是一个纯文本文件，通常位于 MySQL 的数据目录（如 /var/lib/mysql）或运行目录（如 /var/run/mysqld）下。文件名通常是 mysql.pid 或 mysqld.pid。这个文件的内容非常简单：只包含一行数字，这个数字就是 MySQL 数据库服务器进程（mysqld）的进程 ID。
主要作用：
-
进程标识与管理：操作系统和各类管理工具通过读取这个文件，可以快速、准确地知道当前正在运行的 MySQL 主进程的 PID。
-
防止服务重复启动：启动脚本会检查 pid 文件是否存在以及对应进程是否运行，避免启动第二个 MySQL 实例。
-
用于优雅地停止服务：停止命令会读取 pid 文件，向正确的 PID 发送 SIGTERM 信号，允许 MySQL 完成正在进行的操作、刷新数据到磁盘，再退出。
-
为监控工具和脚本提供便利：通过检查 pid 文件是否存在以及其对应的进程是否存活，来快速判断 MySQL 服务是否在正常运行。
工作机制：
-
启动时创建：MySQL 服务器进程成功启动后，以自己的进程 ID 为内容，在指定路径下创建 .pid 文件。
-
运行时存在：在 MySQL 服务运行期间，这个文件会一直存在。
-
关闭时删除：当 MySQL 服务器进程正常、优雅地关闭时，会在退出前主动删除这个 .pid 文件。
mysql> show variables like '%pid%';
+------------------------+-----------------------+
| Variable_name | Value|
+------------------------+-----------------------+
| log_syslog_include_pid | ON|
| pid_file | /mysql/3306/mysql.pid |
+------------------------+-----------------------+
6.3 mysql表结构文件
以 MySQL 中一个名为 tmptest 的表为例：
-
.frm文件（表结构文件）：存储表的元数据或结构定义。它描述了表长什么样，但不包含实际的数据。 -
.ibd文件（表数据文件）：存储表的实际数据和索引。这是表的“实体”，存放插入的记录。
两者如何协同工作： 当执行查询时，MySQL 首先读取 .frm 文件了解表结构，然后根据查询条件到 .ibd 文件中查找具体数据，最后根据结构定义解析二进制数据并格式化返回结果集。
七、innodb存储引擎表空间文件
表空间是 InnoDB 存储引擎逻辑结构的顶层，是所有数据的容器。
7.1 系统表空间
-
默认文件名：
ibdata1 -
位置：由
innodb_data_file_path参数定义。 -
作用：
-
存储数据字典（InnoDB 表的元数据）。
-
存储 Change Buffer（变更缓冲区）。
-
存储 Doublewrite Buffer（双写缓冲区）。
-
存储回滚段（Undo Logs）。
-
可能存储用户数据和索引（如果
innodb_file_per_table=OFF）。
-
7.1.1 MySQL 系统表空间存放的数据
-
数据字典：关于所有 InnoDB 表、列、索引、外键等对象的元信息。
- 变更缓冲区（Change Buffer）：用于缓存非唯一二级索引的变更操作（INSERT, UPDATE, DELETE），以减少随机I/O。
mysql> show variables like '%change%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | innodb_change_buffer_max_size | 25| | innodb_change_buffering | all|-
innodb_change_buffering：指定缓冲类型（all/none/inserts/deletes/changes/purges）。 -
innodb_change_buffer_max_size：变更缓冲区最多占用 InnoDB 缓冲池的百分比（默认25%）。
-
-
双写缓冲区（Doublewrite Buffer）：在将脏页写入数据文件之前，先将它们写入双写缓冲区，防止部分页写入导致的数据损坏。
-
innodb_doublewrite：控制双写缓冲区开关，生产环境应开启。
-
-
回滚段（Undo Segments）：存储回滚日志（Undo Logs），用于事务回滚和MVCC。
-
（可选）用户表数据和索引：当
innodb_file_per_table=OFF时，所有用户表的数据和索引存放在系统表空间。
7.1.2 5.7版本与8.0版本的主要区别
|
特性 |
MySQL 5.7 |
MySQL 8.0 |
区别说明和8.0的优势 |
|---|---|---|---|
|
数据字典 |
存储在系统表空间中，使用非事务性的MyISAM表和特定的存储格式。 |
完全重构，存储在单独的 mysql.ibd 表空间文件中，使用InnoDB引擎。 |
事务性：DDL操作原子化、崩溃安全；统一性：元数据由InnoDB管理；性能：信息模式查询性能显著提升。 |
|
元数据存储 |
除了数据字典，还需要 FRM 文件存储表结构。 |
废除 FRM 文件，表结构信息完全集成在数据字典中，序列化信息存储在 *.sdi 文件中。 |
简化管理，元数据存储单一化。 |
|
回滚段管理 |
存储在系统表空间 ibdata1 中。 |
可存储在独立的 Undo 表空间中（默认 undo_001, undo_002）。 |
可以单独管理、移动和截断 Undo 表空间，支持自动截断和回收，解决 ibdata1 膨胀问题。 |
|
系统表空间作用域 |
承担数据字典、Change Buffer、Doublewrite Buffer、回滚段，以及可选的用户数据。 |
职责更纯粹：主要承担 Change Buffer 和 Doublewrite Buffer。 |
系统表空间大小更可控，不会无限增长。 |
|
原子DDL |
不支持，DDL操作不是原子的。 |
支持原子DDL，得益于新的数据字典和 Undo/Redo 日志对 DDL 操作的支持。 |
崩溃安全：确保 DDL 操作要么完全成功，要么完全回滚，不会导致元数据不一致。 |
7.2 独立表空间
-
文件模式：
表名.ibd -
作用：当
innodb_file_per_table=1（默认）时，每个用户表都有独立的.ibd文件，存储该表的数据、索引和插入缓冲位图。 -
优点：
-
空间回收：DROP TABLE 或 TRUNCATE TABLE 后，操作系统可以直接回收磁盘空间。
-
传输方便：可以使用 Transportable Tablespaces 功能快速迁移单个表。
-
监控方便：可以更直观地看到每个表的大小。
-
7.3 通用表空间
-
文件模式：自定义，如
shared_tablespace.ibd -
作用：由
CREATE TABLESPACE创建，可以用于存储多个表的数据和索引。是系统表空间和独立表空间的一个折中方案。 -
适用场景：希望将多个关系密切的表集中管理，以节省元数据开销。
7.4 临时表空间
-
文件模式：
ibtmp1 -
作用：存储用户创建的临时表和磁盘内部的临时表。
-
特点：服务器启动时被创建，关闭时被销毁。如果临时表空间过大，可以通过重启 MySQL 来重置。
7.5 Undo 表空间
-
文件模式：
undo_001,undo_002等 -
作用：专门存储回滚段（Undo Logs）。从 MySQL 8.0 开始，默认创建两个 Undo 表空间，并可以在运行时动态创建和删除。
-
好处：将 Undo 日志从系统表空间中分离出来，便于管理和回收空间。
mysql> show variables like '%data%';
+---------------------------------------+----------------------------------------------------------+
| Variable_name | Value|
+---------------------------------------+----------------------------------------------------------+
| character_set_database | utf8 |
| collation_database | utf8_general_ci |
| datadir | /mysql/3306/data/|
| innodb_data_file_path | ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G |
| innodb_data_home_dir ||
| innodb_stats_on_metadata | OFF |
| innodb_temp_data_file_path | ibtmp1:200M:autoextend:max:20G |
| max_length_for_sort_data | 1024|
| metadata_locks_cache_size | 1024|
| metadata_locks_hash_instances | 8|
| myisam_data_pointer_size | 6|
| performance_schema_max_metadata_locks | -1|
| skip_show_database | OFF |
| updatable_views_with_limit | YES |
八、mysql权限管理
8.1 权限系统架构
8.1.1 权限层级结构
全局权限 (GLOBAL)
↓
数据库权限 (DATABASE)
↓
表权限 (TABLE)
↓
列权限 (COLUMN)
↓
存储程序权限 (ROUTINE)
8.1.2 权限存储位置
所有权限信息都存储在 mysql 数据库的以下系统表中：
-
user- 全局权限和用户基本信息 -
db- 数据库级权限 -
tables_priv- 表级权限 -
columns_priv- 列级权限 -
procs_priv- 存储过程和函数权限
8.2 权限表详细结构
8.2.1 user 表（全局权限）
-- 查看user表结构
DESC mysql.user;
-- 查看用户全局权限
SELECT
user AS 用户名,
host AS 主机,
authentication_string AS 密码哈希,
Select_priv AS 全局查询权限,
Insert_priv AS 全局插入权限,
Update_priv AS 全局更新权限,
Delete_priv AS 全局删除权限,
Create_priv AS 全局创建权限,
Drop_priv AS 全局删除权限,
Reload_priv AS 重载权限,
Shutdown_priv AS 关闭服务器权限,
Process_priv AS 进程查看权限,
File_priv AS 文件操作权限,
Grant_priv AS 授权权限,
References_priv AS 外键权限,
Index_priv AS 索引权限,
Alter_priv AS 表结构修改权限,
Show_db_priv AS 数据库查看权限,
Super_priv AS 超级权限,
Create_tmp_table_priv AS 临时表创建权限,
Lock_tables_priv AS 锁表权限,
Execute_priv AS 执行权限,
Repl_slave_priv AS 复制从库权限,
Repl_client_priv AS 复制客户端权限,
Create_view_priv AS 视图创建权限,
Show_view_priv AS 视图查看权限,
Create_routine_priv AS 存储过程创建权限,
Alter_routine_priv AS 存储过程修改权限,
Create_user_priv AS 用户创建权限,
Event_priv AS 事件权限,
Trigger_priv AS 触发器权限,
Create_tablespace_priv AS 表空间创建权限
FROM mysql.user
WHERE user NOT LIKE 'mysql.%';
8.2.2 db 表（数据库级权限）
-- 查看数据库级权限
SELECT
user AS 用户名,
host AS 主机,
db AS 数据库名,
Select_priv AS 查询权限,
Insert_priv AS 插入权限,
Update_priv AS 更新权限,
Delete_priv AS 删除权限,
Create_priv AS 创建权限,
Drop_priv AS 删除权限,
Grant_priv AS 授权权限,
References_priv AS 外键权限,
Index_priv AS 索引权限,
Alter_priv AS 修改权限,
Create_tmp_table_priv AS 临时表权限,
Lock_tables_priv AS 锁表权限,
Create_view_priv AS 视图创建权限,
Show_view_priv AS 视图查看权限,
Create_routine_priv AS 存储过程创建权限,
Alter_routine_priv AS 存储过程修改权限,
Execute_priv AS 执行权限,
Event_priv AS 事件权限,
Trigger_priv AS 触发器权限
FROM mysql.db
WHERE db = 'testdb';
8.2.3 tables_priv 表（表级权限）
-- 查看表级权限
SELECT
user AS 用户名,
host AS 主机,
db AS 数据库名,
table_name AS 表名,
grantor AS 授权人,
timestamp AS 授权时间,
table_priv AS 表权限,
column_priv AS 列权限
FROM mysql.tables_priv;
8.2.4 columns_priv 表（列级权限）
-- 查看列级权限
SELECT
user AS 用户名,
host AS 主机,
db AS 数据库名,
table_name AS 表名,
column_name AS 列名,
timestamp AS 授权时间,
column_priv AS 列权限
FROM mysql.columns_priv;
8.3 用户管理操作
8.3.1 创建用户
-- 基本用户创建
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- 具体示例
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!';
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'ReadOnly456!';
CREATE USER 'repl_user'@'10.0.0.%' IDENTIFIED BY 'ReplPass789!';
-- 创建用户并设置密码过期策略
CREATE USER 'temp_user'@'%'
IDENTIFIED BY 'TempPass123!'
PASSWORD EXPIRE INTERVAL 90 DAY;
-- 创建用户并锁定账户
CREATE USER 'locked_user'@'%'
IDENTIFIED BY 'LockedPass123!'
ACCOUNT LOCK;
8.3.2 修改用户
-- 修改用户名和主机
RENAME USER 'old_user'@'localhost' TO 'new_user'@'192.168.1.100';
-- 修改密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
-- 设置密码过期
ALTER USER 'username'@'host' PASSWORD EXPIRE;
-- 锁定/解锁用户
ALTER USER 'username'@'host' ACCOUNT LOCK;
ALTER USER 'username'@'host' ACCOUNT UNLOCK;
-- 修改认证插件
ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
8.3.3 删除用户
-- 删除用户
DROP USER 'username'@'host';
-- 删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'192.168.1.%';
-- 安全删除前先检查权限
SELECT user, host FROM mysql.user WHERE user = 'username';
8.4 权限授予操作
8.4.1 全局权限授予
-- 授予所有权限（类似root）
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;
-- 授予只读权限
GRANT SELECT ON *.* TO 'readonly_user'@'%';
-- 授予特定全局权限
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'app_user'@'192.168.1.%';
-- 授予管理权限
GRANT RELOAD, PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'localhost';
8.4.2 数据库级权限授予
-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON `testdb`.* TO 'db_admin'@'localhost';
-- 授予数据库读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'app_user'@'%';
-- 授予数据库只读权限
GRANT SELECT ON `testdb`.* TO 'report_user'@'10.0.0.%';
-- 授予特定数据库权限
GRANT CREATE, DROP, ALTER ON `testdb`.* TO 'dev_user'@'localhost';
8.4.3 表级权限授予
-- 授予表所有权限
GRANT ALL PRIVILEGES ON `testdb`.`employees` TO 'hr_admin'@'localhost';
-- 授予表读写权限
GRANT SELECT, INSERT, UPDATE ON `testdb`.`salaries` TO 'hr_user'@'192.168.1.%';
-- 授予表只读权限
GRANT SELECT ON `testdb`.`departments` TO 'public_user'@'%';
8.4.4 列级权限授予
-- 授予特定列权限
GRANT SELECT (emp_no, first_name, last_name) ON `testdb`.`employees` TO 'public_api'@'%';
GRANT UPDATE (salary) ON `testdb`.`salaries` TO 'hr_manager'@'localhost';
GRANT SELECT (emp_no, dept_no), UPDATE (from_date, to_date)
ON `testdb`.`dept_emp` TO 'dept_admin'@'%';
8.4.5 存储程序权限授予
-- 授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE `testdb`.`calculate_bonus` TO 'app_user'@'%';
-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION `testdb`.`get_employee_name` TO 'api_user'@'localhost';
-- 授予存储过程创建权限
GRANT CREATE ROUTINE ON `testdb`.* TO 'dev_user'@'localhost';
8.5 权限回收操作
8.5.1 权限回收语法
-- 回收所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';
-- 回收特定权限
REVOKE INSERT, UPDATE ON `testdb`.* FROM 'app_user'@'%';
-- 回收列权限
REVOKE UPDATE (salary) ON `testdb`.`salaries` FROM 'hr_user'@'localhost';
-- 回收授予权限
REVOKE GRANT OPTION ON *.* FROM 'admin_user'@'localhost';
8.5.2 权限回收示例
-- 回收用户的删除权限
REVOKE DELETE ON `testdb`.`employees` FROM 'hr_user'@'%';
-- 回收所有数据库权限但保留information_schema访问
REVOKE ALL PRIVILEGES ON `testdb`.* FROM 'readonly_user'@'%';
-- 回收存储过程权限
REVOKE EXECUTE ON PROCEDURE `testdb`.`reset_data` FROM 'temp_user'@'localhost';
8.6 权限查看和审计
8.6.1 查看用户权限
-- 查看当前用户权限
SHOW GRANTS;
-- 查看指定用户权限
SHOW GRANTS FOR 'username'@'host';
-- 查看详细的权限信息
SELECT * FROM mysql.user WHERE user = 'username' AND host = 'host';
8.6.2 权限审计查询
-- 查看所有用户及其权限概览
SELECT
u.user AS 用户名,
u.host AS 主机,
u.authentication_string AS 密码状态,
IF(u.Select_priv = 'Y', '全局只读', '') AS 全局权限,
GROUP_CONCAT(DISTINCT d.db) AS 有权限的数据库,
u.account_locked AS 是否锁定,
u.password_expired AS 密码是否过期,
u.password_last_changed AS 最后修改时间
FROM mysql.user u
LEFT JOIN mysql.db d ON u.user = d.user AND u.host = d.host
WHERE u.user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema')
GROUP BY u.user, u.host
ORDER BY u.user, u.host;
-- 查看数据库级权限详情
SELECT
d.user AS 用户名,
d.host AS 主机,
d.db AS 数据库,
d.Select_priv AS 可查询,
d.Insert_priv AS 可插入,
d.Update_priv AS 可更新,
d.Delete_priv AS 可删除,
d.Create_priv AS 可创建,
d.Drop_priv AS 可删除,
d.Grant_priv AS 可授权
FROM mysql.db d
ORDER BY d.db, d.user, d.host;
8.7 安全最佳实践
8.7.1 最小权限原则
-- 应用用户：只有必要的读写权限
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO 'app_user'@'192.168.1.%';
-- 报表用户：只读权限
CREATE USER 'report_user'@'10.0.0.%' IDENTIFIED BY 'ReportPass456!';
GRANT SELECT ON `app_db`.* TO 'report_user'@'10.0.0.%';
-- 备份用户：SELECT和LOCK TABLES权限
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass789!';
GRANT SELECT, LOCK TABLES ON *.* TO 'backup_user'@'localhost';
8.7.2 网络访问控制
-- 限制特定IP段访问
CREATE USER 'internal_user'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'InternalPass!';
-- 本地访问用户
CREATE USER 'local_admin'@'localhost' IDENTIFIED BY 'LocalAdminPass!';
-- 拒绝外部访问root用户
RENAME USER 'root'@'%' TO 'root'@'localhost';
DROP USER 'root'@'%';
8.7.3 密码策略管理
-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';
-- 设置密码策略（MySQL 8.0+）
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
-- 强制密码过期
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
8.8 常见权限场景
8.8.1 开发环境权限
-- 开发人员权限
CREATE USER 'dev_user'@'192.168.1.%' IDENTIFIED BY 'DevPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, CREATE TEMPORARY TABLES
ON `dev_db`.* TO 'dev_user'@'192.168.1.%';
GRANT SELECT ON `test_db`.* TO 'dev_user'@'192.168.1.%';
8.8.2 生产环境应用权限
-- 生产应用权限
CREATE USER 'prod_app'@'10.0.1.%' IDENTIFIED BY 'ProdAppPass456!';
GRANT SELECT, INSERT, UPDATE, DELETE ON `production`.* TO 'prod_app'@'10.0.1.%';
GRANT EXECUTE ON `production`.* TO 'prod_app'@'10.0.1.%';
8.8.3 监控用户权限
-- 监控系统权限
CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'MonitorPass789!';
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';
GRANT SELECT ON `performance_schema`.* TO 'monitor'@'localhost';
GRANT SELECT ON `sys`.* TO 'monitor'@'localhost';
8.8.4 备份用户权限
-- 备份工具权限
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'BackupPass123!';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS ON *.* TO 'backup'@'localhost';
8.9 权限问题排查
8.9.1 权限验证流程
-- 模拟权限验证
SHOW GRANTS FOR CURRENT_USER();
-- 检查特定操作权限
SELECT * FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = CONCAT(''', USER(), '''@''', HOST(), '''');
-- 检查表权限
SELECT * FROM information_schema.TABLE_PRIVILEGES
WHERE GRANTEE = CONCAT(''', USER(), '''@''', HOST(), ''')
AND TABLE_SCHEMA = 'testdb';
8.9.2 常见权限问题
-- 检查连接权限
SELECT user, host, authentication_string FROM mysql.user
WHERE user = 'username' AND host = 'client_host';
-- 检查数据库权限
SELECT * FROM mysql.db WHERE user = 'username' AND host = 'client_host';
-- 检查表权限
SELECT * FROM mysql.tables_priv WHERE user = 'username' AND host = 'client_host';
-- 刷新权限
FLUSH PRIVILEGES;
8.10 权限维护脚本
8.10.1 权限导出脚本
-- 导出所有用户权限（用于备份）
SELECT
CONCAT('CREATE USER IF NOT EXISTS ''', user, '''@''', host, ''' IDENTIFIED BY ''[PASSWORD]'';') AS create_user,
CONCAT('GRANT ',
(SELECT GROUP_CONCAT(privilege_type)
FROM information_schema.USER_PRIVILEGES up
WHERE up.GRANTEE = CONCAT(''', u.user, '''@''', u.host, ''')),
' ON *.* TO ''', u.user, '''@''', u.host, ''';') AS global_grants
FROM mysql.user u
WHERE u.user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema');
8.10.2 权限清理脚本
-- 清理无权限用户
SELECT CONCAT('DROP USER ''', user, '''@''', host, ''';') AS drop_user_sql
FROM mysql.user u
LEFT JOIN mysql.db d ON u.user = d.user AND u.host = d.host
LEFT JOIN mysql.tables_priv tp ON u.user = tp.user AND u.host = tp.host
LEFT JOIN mysql.columns_priv cp ON u.user = cp.user AND u.host = cp.host
WHERE u.user NOT IN ('root', 'mysql.sys', 'mysql.session', 'mysql.infoschema')
AND d.db IS NULL
AND tp.table_name IS NULL
AND cp.column_name IS NULL
AND u.authentication_string = ''; -- 无密码用户
九、角色管理
9.1 角色管理基础概念
9.1.1 什么是角色？
角色是权限的集合，可以理解为权限的“分组”或“模板”。创建角色后，可以将其授予给多个用户，实现权限的批量管理。
9.1.2 角色管理的优势
-
简化权限管理：一次定义，多次使用。
-
提高一致性：相同职责的用户拥有完全相同的权限。
-
便于维护：修改角色权限，所有相关用户自动继承。
-
增强安全性：减少权限分配错误。
9.1.3 版本要求
MySQL 8.0 及以上版本原生支持角色管理。
9.2 角色管理完整操作流程
9.2.1 创建角色
-- 创建单个角色
CREATE ROLE 'read_only';
CREATE ROLE 'data_analyst';
-- 创建多个角色（推荐方式）
CREATE ROLE
'app_developer',
'app_read_write',
'app_read_only',
'db_admin';
9.2.2 为角色授予权限
-- 1. 只读角色 - 全局只读权限
GRANT SELECT ON *.* TO 'read_only';
-- 2. 数据分析角色 - 特定数据库的查询和过程执行权限
GRANT SELECT, EXECUTE ON analytics.* TO 'data_analyst';
GRANT SELECT ON mysql.innodb_table_stats TO 'data_analyst'; -- 系统表查询
-- 3. 应用开发角色 - 完整应用数据库权限
GRANT ALL PRIVILEGES ON my_application.* TO 'app_developer';
GRANT SELECT ON performance_schema.* TO 'app_developer';
-- 4. 读写角色 - 基本的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON my_application.* TO 'app_read_write';
-- 5. 应用只读角色
GRANT SELECT ON my_application.* TO 'app_read_only';
-- 6. 数据库管理员 - 完整权限（除GRANT OPTION外）
GRANT ALL PRIVILEGES ON *.* TO 'db_admin';
9.2.3 创建用户并分配角色
-- 创建不同用途的用户
CREATE USER 'web_user'@'localhost' IDENTIFIED BY 'secure_pass123';
CREATE USER 'reports_user'@'%' IDENTIFIED BY 'report_pass456';
CREATE USER 'dev_user'@'192.168.1.%' IDENTIFIED BY 'dev_pass789';
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'admin_pass999';
-- 为用户分配角色
GRANT 'app_read_write' TO 'web_user'@'localhost';
GRANT 'data_analyst' TO 'reports_user'@'%';
GRANT 'app_developer' TO 'dev_user'@'192.168.1.%';
GRANT 'db_admin' TO 'admin_user'@'localhost';
9.3 角色激活机制
9.3.1 角色激活的三种方式
方式一：设置默认角色（推荐）
-- 为单个用户设置默认角色
SET DEFAULT ROLE 'app_read_write' TO 'web_user'@'localhost';
-- 为用户设置多个默认角色
SET DEFAULT ROLE 'data_analyst', 'read_only' TO 'reports_user'@'%';
-- 将所有授予的角色设为默认
SET DEFAULT ROLE ALL TO 'dev_user'@'192.168.1.%';
方式二：全局自动激活（最方便）
-- 启用全局设置（需要SUPER权限）
SET GLOBAL activate_all_roles_on_login = ON;
-- 永久生效：在my.cnf配置文件中添加
[mysqld]
activate_all_roles_on_login=ON
方式三：会话中手动激活
-- 用户登录后手动激活所有角色
SET ROLE ALL;
-- 激活指定角色
SET ROLE 'app_read_write', 'read_only';
9.3.2 验证角色激活状态
-- 查看当前激活的角色
SELECT CURRENT_ROLE();
-- 查看当前会话的有效权限
SHOW GRANTS;
9.4 角色管理维护操作
9.4.1 查看角色和权限信息
-- 查看所有角色
SELECT USER as role_name, HOST
FROM mysql.user
WHERE account_locked = 'Y'
AND authentication_string = ''
AND password_expired = 'Y';
-- 查看角色的具体权限
SHOW GRANTS FOR 'app_developer';
-- 查看用户被授予了哪些角色
SHOW GRANTS FOR 'web_user'@'localhost';
-- 详细查询用户角色关系
SELECT
FROM_USER as role_name,
TO_USER as username,
TO_HOST as host
FROM mysql.role_edges
WHERE TO_USER = 'web_user';
9.4.2 修改和撤销权限
-- 为角色添加新权限
GRANT INSERT, UPDATE ON log_db.* TO 'data_analyst';
-- 从角色撤销特定权限
REVOKE DELETE ON my_application.* FROM 'app_read_write';
-- 从用户收回角色
REVOKE 'app_developer' FROM 'dev_user'@'192.168.1.%';
-- 彻底删除角色
DROP ROLE 'obsolete_role';
9.4.3 强制角色（MySQL 8.0.19+）
强制角色会自动授予所有用户，无法撤销。
-- 设置强制角色
SET PERSIST mandatory_roles = 'read_only,monitoring_role';
-- 查看当前强制角色
SELECT VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME = 'mandatory_roles';
9.5 实战示例：完整的权限管理体系
9.5.1 创建企业级角色体系
-- 1. 创建基础角色体系
CREATE ROLE
'role_read_only',
'role_read_write',
'role_schema_manager',
'role_dba';
-- 2. 授予分层权限
-- 只读角色
GRANT SELECT ON *.* TO 'role_read_only';
-- 读写角色（排除敏感操作）
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'role_read_write';
GRANT SELECT ON config_db.* TO 'role_read_write';
-- schema管理角色
GRANT
SELECT, INSERT, UPDATE, DELETE,
CREATE, ALTER, INDEX, DROP,
CREATE TEMPORARY TABLES,
CREATE VIEW, SHOW VIEW
ON app_db.* TO 'role_schema_manager';
-- DBA角色（所有权限）
GRANT ALL PRIVILEGES ON *.* TO 'role_dba';
-- 3. 创建对应员工用户
CREATE USER 'emp_readonly'@'%' IDENTIFIED BY 'pass1';
CREATE USER 'emp_operator'@'%' IDENTIFIED BY 'pass2';
CREATE USER 'emp_developer'@'%' IDENTIFIED BY 'pass3';
CREATE USER 'emp_dba'@'localhost' IDENTIFIED BY 'pass4';
-- 4. 分配角色
GRANT 'role_read_only' TO 'emp_readonly'@'%';
GRANT 'role_read_write' TO 'emp_operator'@'%';
GRANT 'role_schema_manager' TO 'emp_developer'@'%';
GRANT 'role_dba' TO 'emp_dba'@'localhost';
-- 5. 设置默认角色
SET DEFAULT ROLE ALL TO
'emp_readonly'@'%',
'emp_operator'@'%',
'emp_developer'@'%',
'emp_dba'@'localhost';
9.5.2 验证权限配置
-- 以不同用户身份登录测试
-- 用户 emp_readonly 应该只能执行 SELECT
-- 用户 emp_operator 可以执行 DML 操作
-- 用户 emp_developer 可以执行 DDL 操作
-- 用户 emp_dba 可以执行所有操作
-- 测试命令示例
SHOW GRANTS; -- 查看当前权限
SELECT CURRENT_ROLE(); -- 查看当前角色
9.6 最佳实践和注意事项
9.6.1 命名规范
-
使用统一的前缀：
role_或r_ -
名称体现职责：
role_app_readonly、role_db_backup -
避免使用保留字
9.6.2 权限设计原则
-
最小权限原则：只授予必要的权限。
-
职责分离：不同角色负责不同功能。
-
定期审计：定期检查角色权限和用户分配。
9.6.3 常见问题解决
-- 问题：用户登录后角色未激活
-- 解决方案1：设置默认角色
SET DEFAULT ROLE ALL TO 'username'@'host';
-- 解决方案2：启用全局自动激活
SET GLOBAL activate_all_roles_on_login = ON;
-- 问题：需要临时提升权限
-- 解决方案：激活特定角色（需要该角色已授予用户）
SET ROLE 'role_dba';
十、SSL安全证书配置
10.1 mysql.5.7 版本SSL配置步骤
步骤1：环境检查和准备
# 检查 MySQL 版本，确认是 5.7 版本
mysql -V
# 输出示例：mysql Ver 14.14 Distrib 5.7.x，确认版本符合要求
# 检查 mysql_ssl_rsa_setup 工具是否存在
which mysql_ssl_rsa_setup
# 输出路径如：/usr/bin/mysql_ssl_rsa_setup，表示工具可用
# 查看工具帮助信息，了解所有可用参数
mysql_ssl_rsa_setup --help
# 显示命令的使用方法和参数说明，便于后续使用
# 检查当前 MySQL 的 SSL 状态
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"
# 查看 have_ssl 变量，初始状态通常为 DISABLED
步骤2：停止 MySQL 服务
# 停止 MySQL 服务，确保在安全状态下操作
sudo systemctl stop mysqld
# 检查服务状态，确认已停止
sudo systemctl status mysqld
# 应该显示 inactive (dead) 状态
# 检查 MySQL 进程是否完全退出
sudo ps aux | grep mysqld
# 不应该看到 mysqld 进程，如有则使用 sudo pkill mysqld 强制结束
步骤3：准备数据目录
# 确认 MySQL 数据目录存在
sudo ls -la /mysql/data/3306/
# 检查数据目录结构和权限，确保目录存在
# 如果目录不存在，创建数据目录
sudo mkdir -p /mysql/data/3306
# 设置数据目录权限，确保 MySQL 用户可以访问
sudo chown mysql:mysql /mysql/data/3306
sudo chmod 755 /mysql/data/3306
步骤4：备份现有证书（如果存在）
# 检查是否已存在证书文件
sudo ls -la /mysql/data/3306/*.pem 2>/dev/null || echo "没有现有证书文件"
# 如果存在证书文件，创建备份
sudo mkdir -p /mysql/ssl_backup/$(date +%Y%m%d)
sudo cp /mysql/data/3306/*.pem /mysql/ssl_backup/$(date +%Y%m%d)/ 2>/dev/null || true
# 确认备份成功
sudo ls -la /mysql/ssl_backup/$(date +%Y%m%d)/
步骤5：使用 mysql_ssl_rsa_setup 生成 SSL 证书
# 进入数据目录，便于操作和验证
cd /mysql/data/3306
# 使用 mysql_ssl_rsa_setup 生成 SSL 证书和 RSA 密钥对
sudo mysql_ssl_rsa_setup --datadir=/mysql/data/3306 --user=mysql --uid=mysql --verbose
# --datadir: 指定证书生成目录
# --user: 设置文件所有者用户名
# --uid: 设置文件所有者用户ID
# --verbose: 显示详细生成过程信息
# 或者使用简化版本（如果系统用户配置正确）
sudo mysql_ssl_rsa_setup --datadir=/mysql/data/3306
步骤6：验证生成的证书文件
# 列出所有生成的证书文件，确认文件完整性
sudo ls -la /mysql/data/3306/*.pem
# 应该看到以下8个文件：
# - ca-key.pem, ca.pem: CA 私钥和证书
# - server-key.pem, server-cert.pem: 服务器私钥和证书
# - client-key.pem, client-cert.pem: 客户端私钥和证书
# - private_key.pem, public_key.pem: RSA 密钥对（用于身份验证）
# 检查文件权限，确保安全性
sudo ls -la /mysql/data/3306/*.pem | awk '{print $1, $3, $4, $9}'
# 所有 .pem 文件应该是 mysql 用户所有，权限为 600 或 640
# 验证 CA 证书内容
sudo openssl x509 -in /mysql/data/3306/ca.pem -text -noout | head -20
# 验证服务器证书
sudo openssl x509 -in /mysql/data/3306/server-cert.pem -text -noout | head -20
# 检查证书链有效性
sudo openssl verify -CAfile /mysql/data/3306/ca.pem /mysql/data/3306/server-cert.pem
# 验证服务器证书是否由 CA 正确签名，输出应为 OK
步骤7：配置 MySQL 使用 SSL 证书
# 编辑 MySQL 配置文件
sudo vi /etc/my.cnf
# 在配置文件中添加或修改以下内容：
[mysqld]
# SSL 证书配置 - 指向生成的证书文件
ssl-ca = /mysql/data/3306/ca.pem # CA 证书文件路径
ssl-cert = /mysql/data/3306/server-cert.pem # 服务器证书文件路径
ssl-key = /mysql/data/3306/server-key.pem # 服务器私钥文件路径
# 可选：强制要求 SSL 连接（根据安全需求决定）
# require_secure_transport = ON # 开启后所有连接必须使用 SSL
# 可选：指定 SSL 加密套件（性能和安全平衡）
# ssl-cipher = DHE-RSA-AES256-SHA:AES128-SHA # 指定支持的加密算法
# 启用 SSL 功能
ssl = ON # 明确启用 SSL 支持
[client]
# 客户端 SSL 配置（便于管理员连接）
ssl-ca = /mysql/data/3306/ca.pem # 客户端 CA 证书路径
ssl-cert = /mysql/data/3306/client-cert.pem # 客户端证书路径
ssl-key = /mysql/data/3306/client-key.pem # 客户端私钥路径
步骤8：重启 MySQL 服务
# 重启 MySQL 服务使 SSL 配置生效
sudo systemctl start mysqld
# 检查服务状态，确认启动成功
sudo systemctl status mysqld
# 应该显示 active (running) 状态
# 查看 MySQL 错误日志，检查启动过程中是否有 SSL 相关错误
sudo tail -20 /var/log/mysqld.log
步骤9：验证 SSL 配置
-- 登录 MySQL，检查 SSL 配置是否生效
mysql -u root -p
-- 查看 SSL 相关系统变量
SHOW VARIABLES LIKE '%ssl%';
/*
期望输出：
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| have_openssl | YES | -- 支持 OpenSSL
| have_ssl | YES | -- SSL 功能已启用
| ssl_ca | /mysql/data/3306/ca.pem | -- CA 证书路径正确
| ssl_cert | /mysql/data/3306/server-cert.pem | -- 服务器证书路径正确
| ssl_key | /mysql/data/3306/server-key.pem | -- 服务器私钥路径正确
+---------------+-----------------------------+
*/
-- 查看 SSL 状态变量
SHOW STATUS LIKE 'Ssl%';
/*
关键状态变量：
- Ssl_accepts: SSL 连接接受次数（应该大于0）
- Ssl_cipher: 当前连接的加密套件（如果使用SSL）
- Ssl_version: SSL/TLS 协议版本
*/
-- 检查当前连接的 SSL 状态
\s
-- 在输出中查找 "SSL:" 行，如果显示 "Cipher in use is ..." 表示使用 SSL
步骤10：测试 SSL 连接
# 测试不使用 SSL 的连接（应该仍然可以工作）
mysql -u root -p -e "STATUS" | grep -i ssl
# 输出可能显示：SSL: Not in use
# 测试要求 SSL 的连接
mysql -u root -p --ssl-mode=REQUIRED -e "STATUS" | grep -i ssl
# 输出应该显示具体的加密套件，如：SSL: Cipher in use is DHE-RSA-AES256-SHA
# 使用 SSL 连接并查看详细状态
mysql -u root -p --ssl-mode=REQUIRED -e "\s"
# 测试客户端证书认证（高级用法）
mysql -u root -p --ssl-mode=REQUIRED --ssl-ca=/mysql/data/3306/ca.pem --ssl-cert=/mysql/data/3306/client-cert.pem --ssl-key=/mysql/data/3306/client-key.pem -e "STATUS" | grep -i ssl
步骤11：创建要求 SSL 的用户
-- 创建必须使用 SSL 连接的新用户
CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'SecurePassword123!' REQUIRE SSL;
-- REQUIRE SSL 强制该用户必须使用 SSL 连接才能访问
-- 授予基本权限
GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO 'ssl_user'@'%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 验证用户权限
SHOW GRANTS FOR 'ssl_user'@'%';
-- 创建要求 X509 证书的用户（最高安全级别）
CREATE USER 'secure_admin'@'%' IDENTIFIED BY 'AdminPass456!' REQUIRE X509;
GRANT ALL PRIVILEGES ON *.* TO 'secure_admin'@'%';
步骤12：验证不同用户的连接
# 测试普通用户不使用 SSL 连接（应该失败）
mysql -u ssl_user -pSecurePassword123! -h localhost --ssl-mode=DISABLED -e "SELECT 1;" 2>&1
# 应该显示错误：Access denied for user 'ssl_user' (Require SSL)
# 测试普通用户使用 SSL 连接（应该成功）
mysql -u ssl_user -pSecurePassword123! -h localhost --ssl-mode=REQUIRED -e "SELECT 'SSL连接成功' as result;"
# 应该成功执行，显示 "SSL连接成功"
# 测试管理员用户使用证书连接
mysql -u secure_admin -pAdminPass456! --ssl-mode=REQUIRED --ssl-ca=/mysql/data/3306/ca.pem --ssl-cert=/mysql/data/3306/client-cert.pem --ssl-key=/mysql/data/3306/client-key.pem -e "SELECT '证书连接成功' as result;"
步骤13：监控和维护
# 创建 SSL 连接监控脚本
sudo vi /usr/local/bin/monitor_ssl_connections.sh
#!/bin/bash
# MySQL SSL 连接监控脚本
echo "=== MySQL SSL 连接状态监控 ==="
echo "检查时间: $(date)"
# 检查 SSL 变量状态
echo -e "\n1. SSL 系统变量:"
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';" 2>/dev/null
# 检查当前 SSL 连接
echo -e "\n2. 当前 SSL 连接:"
mysql -u root -p -e "SELECT user, host, ssl_type, ssl_cipher FROM performance_schema.threads WHERE ssl_type IS NOT NULL;" 2>/dev/null
# 检查证书有效期
echo -e "\n3. 证书有效期检查:"
for cert in ca.pem server-cert.pem; do
expiry=$(sudo openssl x509 -in /mysql/data/3306/$cert -enddate -noout | cut -d= -f2)
echo "证书 $cert 过期时间: $expiry"
done
echo -e "\n=== 监控完成 ==="
10.2 mysql8.0 默认为安装了SSL，如何使用
10.2.1 检查 MySQL 8.0 默认 SSL 状态
步骤1：验证默认 SSL 配置
-- 查看 SSL 相关变量
SHOW VARIABLES LIKE '%ssl%';
/*
预期输出：
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| have_openssl | YES | ← 支持 OpenSSL
| have_ssl | YES | ← SSL 功能已启用
| ssl_ca | ca.pem | ← 自动生成的 CA 证书
| ssl_capath | |
| ssl_cert | server-cert.pem | ← 自动生成的服务器证书
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem | ← 自动生成的服务器私钥
+---------------------+---------------------------------+
*/
-- 查看自动生成的证书文件位置
SHOW VARIABLES LIKE 'datadir';
-- 通常证书文件在数据目录中，如：/var/lib/mysql/
步骤2：检查证书文件
# 查看自动生成的 SSL 证书文件
sudo ls -la /var/lib/mysql/*.pem
# 应该看到以下文件：
# - ca.pem: CA 证书
# - server-cert.pem: 服务器证书
# - server-key.pem: 服务器私钥
# - client-cert.pem: 客户端证书
# - client-key.pem: 客户端私钥
# - private_key.pem: 用于 caching_sha2_password 的私钥
# - public_key.pem: 用于 caching_sha2_password 的公钥
# 查看证书有效期
sudo openssl x509 -in /var/lib/mysql/ca.pem -noout -dates
# 显示证书的生效和过期时间（默认10年）
10.2.2 MySQL 8.0 SSL 的使用方式
1. 客户端连接时使用 SSL
# 1.1 默认连接（自动协商 SSL）
mysql -h localhost -u root -p
# MySQL 8.0 客户端默认会尝试使用 SSL
# 1.2 明确要求 SSL 连接
mysql -h localhost -u root -p --ssl-mode=REQUIRED
# --ssl-mode=REQUIRED: 强制使用 SSL，如果服务器不支持则连接失败
# 1.3 优先使用 SSL（推荐）
mysql -h localhost -u root -p --ssl-mode=PREFERRED
# --ssl-mode=PREFERRED: 优先使用 SSL，如果不可用则使用非加密连接
# 1.4 禁用 SSL
mysql -h localhost -u root -p --ssl-mode=DISABLED
# --ssl-mode=DISABLED: 明确禁用 SSL，使用普通连接
# 1.5 验证证书（最高安全）
mysql -h localhost -u root -p --ssl-mode=VERIFY_CA --ssl-ca=/var/lib/mysql/ca.pem
# --ssl-mode=VERIFY_CA: 验证服务器证书的 CA
# --ssl-ca: 指定 CA 证书文件
2. 检查连接 SSL 状态
-- 查看当前连接的 SSL 状态
\s
-- 或者
STATUS
/*
连接信息中会显示：
SSL: Cipher in use is TLS_AES_256_GCM_SHA384 ← 使用 SSL
或者
SSL: Not in use ← 未使用 SSL
*/
-- 详细的 SSL 状态信息
SHOW SESSION STATUS LIKE 'Ssl%';
/*
关键状态：
- Ssl_cipher: 当前使用的加密套件
- Ssl_version: SSL/TLS 版本
- Ssl_verify_depth: 证书验证深度
- Ssl_verify_mode: 验证模式
*/
10.2.3 生产环境中的 SSL 应用场景
场景1：远程管理连接
# DBA 远程管理数据库时使用 SSL
mysql -h db.example.com -u dba_admin -p --ssl-mode=REQUIRED --ssl-ca=/path/to/ca.pem
# 保证管理连接的安全性，防止密码和敏感操作被窃听
场景2：应用程序连接
# Python 应用程序连接示例
import mysql.connector
config = {
'user': 'app_user',
'password': 'password',
'host': 'mysql.example.com',
'database': 'app_db',
'ssl_ca': '/path/to/ca.pem',
'ssl_verify_cert': True,
'ssl_verify_identity': True,
}
conn = mysql.connector.connect(**config)
# 应用程序使用 SSL 连接，保护数据传输
// Java 应用程序连接示例
String url = "jdbc:mysql://mysql.example.com:3306/app_db?" +
"useSSL=true&" +
"requireSSL=true&" +
"verifyServerCertificate=true&" +
"trustCertificateKeyStoreUrl=file:/path/to/truststore&" +
"clientCertificateKeyStoreUrl=file:/path/to/keystore";
// JDBC 连接字符串中启用 SSL 验证
场景3：数据库复制
-- 主从复制配置中使用 SSL
CHANGE MASTER TO
MASTER_HOST='master.example.com',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_SSL=1,
MASTER_SSL_CA='/var/lib/mysql/ca.pem',
MASTER_SSL_CERT='/var/lib/mysql/client-cert.pem',
MASTER_SSL_KEY='/var/lib/mysql/client-key.pem';
-- 保证复制数据在传输过程中的安全性
场景4：强制特定用户使用 SSL
-- 创建必须使用 SSL 连接的用户
CREATE USER 'secure_app'@'%'
IDENTIFIED BY 'SecurePass123!'
REQUIRE SSL;
-- REQUIRE SSL: 该用户必须使用 SSL 连接
-- 创建必须使用 X.509 证书的用户
CREATE USER 'admin_user'@'%'
IDENTIFIED BY 'AdminPass456!'
REQUIRE X509;
-- REQUIRE X509: 必须提供有效的客户端证书
-- 修改现有用户要求 SSL
ALTER USER 'existing_user'@'%' REQUIRE SSL;
-- 查看用户 SSL 要求
SELECT user, host, ssl_type, ssl_cipher, x509_issuer, x509_subject
FROM mysql.user
WHERE user IN ('secure_app', 'admin_user');
场景5：全局强制 SSL 连接
-- 在配置文件中启用全局 SSL 强制
-- 编辑 /etc/my.cnf：
[mysqld]
require_secure_transport = ON
# 所有连接都必须使用 SSL 或 socket 连接
-- 或者动态设置
SET GLOBAL require_secure_transport = ON;
10.2.4 性能优化和监控
SSL 性能监控
-- 监控 SSL 连接统计
SHOW STATUS LIKE 'Ssl%';
/*
重要指标：
- Ssl_accepts: SSL 连接接受次数
- Ssl_accept_renegotiates: SSL 重新协商次数
- Ssl_session_cache_hits: SSL 会话缓存命中
- Ssl_session_cache_misses: SSL 会话缓存未命中
- Ssl_session_cache_mode: 会话缓存模式
*/
-- 查看当前 SSL 连接
SELECT
processlist_user as user,
processlist_host as host,
connection_type,
ssl_version,
ssl_cipher
FROM performance_schema.threads
WHERE ssl_version IS NOT NULL;
性能优化配置
# /etc/my.cnf 优化配置
[mysqld]
# SSL 会话缓存配置
ssl_session_cache_mode = ON
ssl_session_cache_size = 1048576 # 1MB 会话缓存
ssl_session_cache_timeout = 300 # 5分钟超时
# 使用性能更好的加密套件
ssl_cipher = ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384
# 启用会话票证（减少 SSL 握手开销）
ssl_session_tickets = ON
10.2.5 安全最佳实践
1. 用户权限分级
-- 内部网络用户（不强制 SSL）
CREATE USER 'internal_app'@'192.168.%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'internal_app'@'192.168.%';
-- 外部访问用户（强制 SSL）
CREATE USER 'external_app'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
GRANT SELECT ON app_db.* TO 'external_app'@'%';
-- 管理员用户（强制证书验证）
CREATE USER 'remote_dba'@'%' IDENTIFIED BY 'password' REQUIRE X509;
GRANT ALL PRIVILEGES ON *.* TO 'remote_dba'@'%';
2. 连接池配置
# 应用连接池配置（如 HikariCP）
spring:
datasource:
hikari:
data-source-properties:
useSSL: true
requireSSL: true
verifyServerCertificate: true
trustCertificateKeyStoreUrl: file:/path/to/truststore
clientCertificateKeyStoreUrl: file:/path/to/keystore
3. 监控和告警
#!/bin/bash
# ssl_monitor.sh - SSL 连接监控
# 检查 SSL 证书过期时间
check_cert_expiry() {
cert_file="/var/lib/mysql/server-cert.pem"
expiry_days=30
expiry_date=$(sudo openssl x509 -in $cert_file -enddate -noout | cut -d= -f2)
expiry_epoch=$(date -d "$expiry_date" +%s)
current_epoch=$(date +%s)
days_until_expiry=$(( (expiry_epoch - current_epoch) / 86400 ))
if [ $days_until_expiry -lt $expiry_days ]; then
echo "警告: SSL 证书将在 $days_until_expiry 天后过期" | mail -s "MySQL SSL 证书告警" dba@example.com
fi
}
# 检查非 SSL 连接
check_non_ssl_connections() {
non_ssl_count=$(mysql -e "SELECT COUNT(*) FROM performance_schema.threads WHERE ssl_version IS NULL AND user IS NOT NULL" -N)
if [ $non_ssl_count -gt 0 ]; then
echo "警告: 发现 $non_ssl_count 个非 SSL 连接" | mail -s "MySQL 非 SSL 连接告警" dba@example.com
fi
}
check_cert_expiry
check_non_ssl_connections
10.2.6 总结
MySQL 8.0 默认 SSL 的使用场景：
|
场景 |
配置方式 |
安全级别 |
性能影响 |
|---|---|---|---|
|
内部网络应用 | --ssl-mode=PREFERRED |
中 |
低 |
|
外部网络应用 | --ssl-mode=REQUIRED |
高 |
中 |
|
管理连接 | REQUIRE SSL
+ 证书 |
最高 |
中 |
|
数据库复制 | MASTER_SSL=1 |
高 |
中 |
|
强制合规 | require_secure_transport=ON |
最高 |
中高 |
十一、MySQL存储引擎与InnoDB体系架构
11.1 MySQL存储引擎介绍
11.1.1 核心概念：存储引擎是什么？
在MySQL中，存储引擎是底层负责存储和读取数据的组件。可以用同一个SQL语句（如 SELECT * FROM users）去查询不同存储引擎的表，但InnoDB和MyISAM在底层如何找到这条数据、如何保证数据安全、是否支持事务等，是截然不同的。
结论：对于现代应用，InnoDB是默认且唯一的选择，因为它提供：
-
事务（Transaction）：保证一组操作要么全部成功，要么全部失败。
-
行级锁（Row-level Locking）：大大提升高并发写的性能。
-
崩溃恢复（Crash Recovery）：服务器断电或崩溃后，能自动恢复数据到一致状态。
-
外键约束（Foreign Key Constraints）：保证数据间的引用完整性。
11.1.2 存储引擎分类
MySQL支持多种存储引擎，以下是几种常见的：
|
特性 |
InnoDB |
MyISAM |
Memory |
Archive |
|---|---|---|---|---|
|
事务支持 |
支持 (ACID) |
不支持 |
不支持 |
不支持 |
|
行级锁 |
支持 |
表级锁 |
表级锁 |
行级锁 |
|
外键约束 |
支持 |
不支持 |
不支持 |
不支持 |
|
MVCC |
支持 |
不支持 |
不支持 |
不支持 |
|
崩溃恢复 |
支持 |
较弱数据丢失 |
不支持 |
支持 |
|
存储限制 |
64TB |
256TB |
内存限制 |
无 |
|
压缩支持 |
是（只读） |
否 |
否 |
是 |
|
主要应用场景 |
绝大多数OLTP应用，需要事务、高并发 |
只读或读多写少，如数据仓库、报表 |
临时表、缓存、会话存储 |
日志和审计档案存储 |
MVCC 是什么？MVCC，全称“多版本并发控制”，是一种数据库管理技术。它的核心原理是：为数据库中的每一行数据保留多个历史版本。
主要作用： MVCC 最主要的作用是：完美地解决“读”与“写”之间的冲突，从而实现高并发下的非阻塞读。
在没有 MVCC 的数据库中，为了保证数据一致性，当一个事务在写数据时，通常会锁定它，阻止其他事务读取（这被称为“读锁”），这会导致性能瓶颈。
而有了 MVCC，它的工作方式如下：
-
当有事务要“写”数据时：它不会直接覆盖旧数据，而是创建一个该数据的新版本。因此，写操作可以无障碍地进行。
-
当有事务要“读”数据时：它看到的不是数据的最新版本，而是在它开始那一刻就已经存在的那个数据快照（一个历史版本）。因此，读操作完全不需要等待写操作释放锁。
最终效果就是：
-
读不阻塞写：一个事务在读取数据的同时，另一个事务可以同时修改该数据。
-
写不阻塞读：一个事务在修改数据的同时，另一个事务可以同时读取该数据的旧版本快照。
这极大地提高了数据库在读写混合负载场景下的并发处理能力和整体性能。我们常用的 READ COMMITTED（读已提交）和 REPEATABLE READ（可重复读）这两种事务隔离级别，正是通过 MVCC 机制来实现的。
11.2 InnoDB核心架构详解
InnoDB的设计哲学是：尽可能在内存中完成操作，通过一套可靠的日志机制，保证内存中的数据能安全、持久地同步到磁盘。
11.2.1 内存结构（In-Memory Structures）
这是InnoDB的“工作车间”，所有操作都在这里进行，速度极快。
1. 缓冲池（Buffer Pool）
-
它是什么？ 是InnoDB在内存中开辟的一个超大缓存区域，用来缓存从磁盘读取的表数据和索引数据。这些数据在缓冲池中以“页”（Page，通常为16KB）为单位进行管理。
-
如何工作？
-
读操作：当需要读取数据时，InnoDB首先检查数据页是否在缓冲池中。如果在（称为“逻辑读”），则直接返回；如果不在，则从磁盘加载到缓冲池再返回。
-
写操作：当需要修改数据时，直接在缓冲池中的页上进行修改。修改后，该页就变成了 “脏页”（Dirty Page），因为它与磁盘上的版本不再一致。
-
缓存管理：使用改进的LRU（最近最少使用）算法来管理。最常被访问的页留在池中，不常访问的页被淘汰，为新数据腾出空间。
-
-
重要性：缓冲池的大小（
innodb_buffer_pool_size）是影响InnoDB性能的最关键参数。将其设置为可用物理内存的50%-80%是常见做法。
2. 更改缓冲区（Change Buffer）
-
它是什么？ 一种特殊的数据结构，用于缓存对非唯一二级索引（Secondary Index）的写操作（INSERT, UPDATE, DELETE）。
-
解决什么问题？ 假设你更新了一条记录，但该记录对应的二级索引页不在缓冲池中。没有Change Buffer时，InnoDB必须立即从磁盘加载那个索引页到缓冲池才能更新，这会产生一次昂贵的随机I/O。
-
如何解决？ Change Buffer将这次“更新操作”本身缓存下来。等到未来某个时刻，因为读请求需要，那个索引页被加载到缓冲池时，再将缓存的操作“合并”到该页上。这相当于将多次随机I/O合并为一次，极大地提升了写性能。
3. 自适应哈希索引（Adaptive Hash Index）
-
它是什么？ InnoDB内部的一个自动化、透明的性能优化器。
-
如何工作？ InnoDB会监控对表上各索引的查询。如果发现某个索引值被非常频繁地用等值查询（
WHERE key = 'value'）访问，它会在内存中为这个热点页建立一个哈希索引。哈希索引的查询复杂度是O(1)，远高于B+树的O(log n)，从而极大提升点查速度。 -
注意：这是一个完全内部的行为，DBA无法控制。它只对特定的工作负载有显著效果。
4. 日志缓冲区（Log Buffer）
-
它是什么？ 一小块内存区域，用于缓存要写入到磁盘上重做日志（Redo Log）的数据。
-
目的：避免每次有小量日志产生时都直接写磁盘，从而减少磁盘I/O。日志缓冲区会定期（如每秒一次）或在事务提交时，将其内容刷新到磁盘的重做日志文件。
11.2.2 磁盘结构（On-Disk Structures）
这是数据的最终归宿，保证数据的持久性。
1. 表空间（Tablespaces）
-
系统表空间（The System Tablespace, ibdata1）：
-
存储InnoDB的元数据（数据字典）、变更缓冲区、双写缓冲区 和 撤销日志（Undo Logs）。
-
在旧版本中，它也存储所有表和索引的数据。现在不推荐这样做。
-
-
独立表空间（File-Per-Table Tablespaces, *.ibd）：
-
现代默认模式（
innodb_file_per_table=ON）。每个InnoDB表都有自己的 .ibd 文件，存储该表的数据和索引。 -
优点：可以更灵活地管理磁盘空间（如OPTIMIZE TABLE能回收空间），并且可以更方便地传输单个表。
-
2. 重做日志（Redo Log）
-
它是什么？ 一组物理日志文件（通常是 ib_logfile0 和 ib_logfile1），循环写入，记录的是对数据页的物理修改。
-
核心机制：写前日志（Write-Ahead Logging, WAL）
-
规则：在任何对缓冲池中数据页的修改（产生脏页）被刷新到磁盘数据文件之前，产生这些修改的重做日志记录必须先被持久化到磁盘的重做日志文件中。
-
为什么？ 为了崩溃恢复。如果数据库崩溃，重启后，InnoDB会检查数据文件和重做日志。它会将那些已经记录在重做日志中但尚未应用到数据文件的修改重新执行一遍（前滚），从而保证已提交事务的持久性。
-
3. 撤销日志（Undo Logs）
-
它是什么？ 存储在撤销表空间中的日志，记录了事务发生之前的数据旧版本。
-
两大核心作用： a. 事务回滚（Rollback）：当执行 ROLLBACK 时，InnoDB使用Undo Log将数据恢复到事务开始前的状态。 b. 多版本并发控制（MVCC）：这是实现读已提交（Read Committed）和可重复读（Repeatable Read）隔离级别的关键。当一个旧事务需要读取某行时，InnoDB通过Undo Log链为该行构建一个符合其事务开始时视图的历史版本，从而实现非阻塞读。
4. 双写缓冲区（Doublewrite Buffer）
-
它解决什么问题？ 部分写（Partial Page Write）问题。InnoDB的页大小（16KB）可能大于操作系统的块大小（4KB）。在将脏页写回磁盘时，如果发生崩溃，可能只写回了部分块（如4KB），导致整个数据页损坏。
-
如何解决？
-
在将脏页写入其最终的 .ibd 数据文件位置之前，InnoDB会先将它们顺序地写入系统表空间中的一个连续区域——这就是双写缓冲区。
-
然后再将页写入 .ibd 文件。
-
-
恢复：如果崩溃发生在写 .ibd 文件时，导致页损坏，InnoDB在恢复时可以用双写缓冲区中的副本来修复该页。
-
本质：这是一个用额外的写操作（先写双写缓冲区，再写数据文件）来换取数据页完整性的安全网。
11.2.3 后台线程（Background Threads）
这些线程是InnoDB的“自动驾驶系统”，负责在后台完成各种维护任务。
-
主线程（Master Thread）：核心调度者。负责将缓冲池中的脏页刷新到磁盘、确保重做日志被循环使用、触发检查点、清理不再需要的Undo Log等。
-
IO 线程（IO Thread）：专门的I/O工作者。处理不同类型的异步I/O请求，包括 read、write、log 等线程。
-
清理线程（Purge Thread）：垃圾回收工。负责回收已无用的Undo Log页，释放空间。
-
页面清理线程（Page Cleaner Thread）：专职清洁工。专门负责将缓冲池中的脏页刷新到磁盘，减轻主线程压力。
11.3 InnoDB存储引擎的常用参数配置（生产环境的常用参数）
11.3.1 核心内存相关参数
|
参数名 |
说明 |
生产配置建议 |
|---|---|---|
innodb_buffer_pool_size |
InnoDB缓冲池大小，最重要参数 |
专用服务器：可用物理内存的50%-80% |
innodb_buffer_pool_instances |
缓冲池分割实例数，减少竞争 |
当 |
innodb_log_buffer_size |
重做日志缓冲区大小 |
默认16MB，大事务可适当增加至64MB或128MB |
11.3.2 日志与持久化相关参数
|
参数名 |
说明 |
生产配置建议 |
|---|---|---|
innodb_log_file_size |
每个重做日志文件大小 |
合计应能容纳1-2小时高峰写负载，通常设为1G-4G |
innodb_log_files_in_group |
日志文件组中文件数量 |
通常保持为2 |
innodb_flush_log_at_trx_commit |
控制日志刷新策略 |
1（最安全）或2（性能折中），金融交易必须用1 |
sync_binlog |
控制binlog刷新策略 |
建议设为1，保证主从数据一致 |
11.3.3 I/O 相关参数
|
参数名 |
说明 |
生产配置建议 |
|---|---|---|
innodb_flush_method |
定义InnoDB与操作系统交互刷新数据的方法 |
Linux推荐 |
innodb_io_capacity |
告诉InnoDB磁盘子系统的I/O能力 |
机械硬盘200-400，SATA SSD 1000-5000，NVMe SSD 5000-20000 |
innodb_io_capacity_max |
后台I/O峰值限制 |
通常为 |
11.3.4 连接与线程相关参数
|
参数名 |
说明 |
生产配置建议 |
|---|---|---|
max_connections |
最大客户端连接数 |
根据应用并发需求设定，监控 Threads_connected |
thread_cache_size |
缓存空闲工作线程数量 |
监控 Threads_created，常见值50-100 |
11.3.5 其他重要参数
|
参数名 |
说明 |
生产配置建议 |
|---|---|---|
innodb_file_per_table |
每表独立表空间 |
必须设置为 ON |
transaction_isolation |
默认事务隔离级别 |
通常为 |
11.3.6 一个生产环境配置示例片段（my.cnf）
[mysqld]
# 基础
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# 内存相关
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 64M
# 日志与持久化
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# I/O 相关
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# 连接相关
max_connections = 500
thread_cache_size = 100
# 其他关键配置
innodb_file_per_table = ON
# transaction_isolation = READ-COMMITTED
# 服务器设置
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 7
11.4 buffer pool 核心工作原理
11.4.1 数据读取流程（解决“读”的问题）
当需要从数据库中读取一条数据时（例如执行 SELECT 语句）：
-
检查 Buffer Pool：InnoDB 首先检查所需的数据页是否已经在 Buffer Pool 中。
-
命中：如果在（“逻辑读”或“缓存命中”），则直接从内存返回数据，速度极快。
-
未命中：如果不在（“物理读”或“缓存未命中”），则从磁盘上的表空间文件（.ibd 文件）中，将包含该数据行的整个数据页（16KB）加载到 Buffer Pool 中，然后返回数据。
目标：通过配置足够大的 innodb_buffer_pool_size，让绝大多数（例如 99%）的读请求都能在 Buffer Pool 中完成，从而避免物理 I/O，实现极高的读取性能。
11.4.2 数据修改流程（解决“写”的问题）
当需要修改数据库中的数据时（例如执行 UPDATE, INSERT, DELETE 语句）：
-
检查 Buffer Pool：检查要修改的数据页是否在 Buffer Pool 中。如果不在，则先从磁盘加载。
-
内存中修改：直接在 Buffer Pool 中的数据页副本上进行修改。
-
标记为“脏页”：修改后，Buffer Pool 中的这个页就与磁盘上的版本不一致了，成为“脏页”。
-
延迟写入：此时，InnoDB 并不会立即将这个“脏页”写回磁盘。它会继续留在 Buffer Pool 中，以备后续可能发生的读取请求。
-
后台刷新：由 InnoDB 的后台线程在合适的时机（例如系统空闲时、检查点），将“脏页”批量、缓慢地刷新到磁盘的数据文件中。
优势：
-
将随机写变为顺序写：多个零散的修改在内存中合并，后台线程可以更高效地将其写入磁盘。
-
减少磁盘 I/O 次数：多次逻辑修改可能只对应一次物理写入。
-
提升响应速度：应用程序无需等待慢速的磁盘写入，修改在内存中完成即可返回成功。
11.4.3 关键管理机制
1. LRU 算法（最近最少使用）Buffer Pool 使用改进的LRU算法，将LRU链表分为两个子链表：
-
New Sublist（新生代/热点区）：存放最近被访问的“年轻”页面。
-
Old Sublist（老生代/冷数据区）：存放“较老”的、可能很快就不再被使用的页面。
工作流程：
-
当一个新页面第一次被读入 Buffer Pool 时，它被放在 Old Sublist 的头部。
-
如果这个页面在 Old Sublist 中短时间内再次被访问，它才会被提升到 New Sublist 的头部，成为真正的“热点数据”。
-
如果它在 Old Sublist 期间没有被再次访问，那么随着新页面的不断加入，它会被逐渐推向 LRU 链表的尾部，最终被淘汰。
这样做的好处：有效防止那些偶然被全表扫描加载进来、但之后不再使用的大量数据页，瞬间冲刷掉真正宝贵的“热点数据”。
2. 脏页刷新机制InnoDB 通过多个后台线程负责将“脏页”刷新回磁盘，主要策略包括：
-
LRU 刷新：当需要从 LRU 列表尾部淘汰一个页面时，如果这个页面是“脏页”，则必须先将它刷新到磁盘，然后才能淘汰。
-
检查点刷新：为了缩短数据库崩溃后的恢复时间，InnoDB 会定期推进一个名为 LSN 的日志序列号。在推进过程中，需要确保在某个 LSN 之前的所有“脏页”都被刷新到磁盘。这个点被称为检查点。
-
空闲刷新：当系统空闲时，主动刷新一些“脏页”。
-
激进刷新：当“脏页”的比例过高时，InnoDB 会强制加快刷新速度，以确保有足够的干净页可用。
11.4.4 生产环境重要参数
|
参数名 |
说明 |
|---|---|
innodb_buffer_pool_size |
最重要的参数，设置为可用物理内存的50%-80% |
innodb_buffer_pool_instances |
将Buffer Pool划分为多个独立实例，减少并发访问时的锁竞争 |
innodb_old_blocks_pct |
控制 Old Sublist 在 LRU 链表中所占的比例，默认37% |
innodb_old_blocks_time |
指定一个页面在 Old Sublist 中停留多久后，再次被访问才能被提升到 New Sublist |
11.5 binlog与redolog，undo之间的区别
11.5.1 Undo Log（回滚日志）= Ctrl + Z（撤销功能）
-
它是什么？ 类似Word的“撤销”功能。
-
干什么用？ 让你能反悔。
-
工作过程：你执行了一个 UPDATE 语句，把名字从“张三”改成“李四”。InnoDB 在执行前，悄悄记下了“这个名字原来是张三”。如果你马上 ROLLBACK（回滚），InnoDB 就利用这个记录，把名字恢复成“张三”。
-
核心一句话：Undo Log 是让你“后悔”用的，保证事务可以回滚。
11.5.2 Redo Log（重做日志）= Word的“自动恢复”功能
-
它是什么？ 类似Word的“自动保存”和“文档恢复”功能。
-
干什么用？ 防止你丢失工作。
-
工作过程：事务提交后，数据可能还在内存里，没来得及写到磁盘。如果此时数据库崩溃，重启后，InnoDB 会查看 Redo Log，然后把那些已经提交但还没写入磁盘的事务，重新执行一遍，保证数据不丢。
-
核心一句话：Redo Log 是防止“丢失”用的，保证已提交的数据一定能存下来，即使崩溃。
11.5.3 Binlog（二进制日志）= “版本历史”或“修改记录”
-
它是什么？ 类似Git管理代码，或者网盘的“文件版本历史”功能。
-
干什么用？ 让你能回到过去的任意一个版本，或者把同样的修改同步到别处。
-
工作过程：它记录了你执行过的所有SQL语句（比如：某时某刻，谁把张三的名字改成了李四）。主要做两件事：
-
主从复制：把主数据库的Binlog发给从数据库，从库执行一遍同样的SQL，数据就和主库一样了。
-
数据恢复：如果你不小心删了数据，可以用昨天备份的数据 + 从昨天到今天产生的Binlog，把数据“恢复”到删除前的状态。
-
-
核心一句话：Binlog 是“复制”和“时间旅行”用的，用于数据同步和按时间点恢复。
11.5.4 三者的根本区别总结
|
特性 |
Undo Log（撤销日志） |
Redo Log（重做日志） |
Binlog（二进制日志） |
|---|---|---|---|
|
目的 |
为了回滚（后悔药） |
为了崩溃恢复（自动保存） |
为了复制和恢复（版本历史） |
|
记录内容 |
数据修改前的样子（旧值） |
数据修改后的样子（物理操作） |
你执行的SQL命令（逻辑操作） |
|
何时用 |
事务回滚时 |
数据库崩溃重启时 |
搭建主从库、需要恢复数据到某个时刻时 |
|
归属 |
InnoDB 引擎私有的 |
InnoDB 引擎私有的 |
MySQL Server 层的，所有引擎都可使用 |
一个极简的例子： 执行：UPDATE users SET money = money + 100 WHERE id = 1;（给ID为1的用户加100块钱）
-
Undo Log 记录：id=1 的用户，money 原来是 500。 ← 为回滚做准备。
-
执行修改：在内存里，把他的 money 改成 600。
-
Redo Log 记录：“要把 id=1 的用户页上的 money 值改成 600” ← 为崩溃恢复做准备。
-
事务提交。
-
Binlog 记录：“执行了 UPDATE users SET money=money+100 WHERE id=1;” ← 为复制和恢复做准备。
十二、mysql事务
12.1 事务与 ACID 属性
在理解隔离级别之前，必须先理解事务的 ACID 属性：
-
A (Atomicity) 原子性：事务中的所有操作要么全部完成，要么全部不完成。
-
C (Consistency) 一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态。
-
I (Isolation) 隔离性：并发事务之间互相隔离，不应相互干扰。
-
D (Durability) 持久性：事务一旦提交，其对数据的改变就是永久性的。
隔离级别就是用来控制**隔离性(I)**的强弱程度的。
12.2 并发事务可能遇到的问题
隔离级别主要是为了解决以下并发问题：
-
脏读
-
定义：一个事务读到了另一个未提交事务修改的数据。
-
场景：事务B修改了数据但未提交，事务A读到了这个修改后的数据，然后事务B回滚了，事务A读到的就是"脏数据"。
-
-
不可重复读
-
定义：在同一个事务中，多次读取同一数据，得到的结果不一致。
-
场景：事务A第一次读取数据后，事务B修改并提交了该数据，事务A再次读取时发现数据变了。
-
-
幻读
-
定义：在同一个事务中，多次查询同一范围的数据，返回的记录数不一致。
-
场景：事务A查询某个条件的数据，事务B插入了满足该条件的新数据并提交，事务A再次查询时发现了"幻影行"。
-
注意：不可重复读针对的是已存在数据的更新，幻读针对的是新插入的数据。
12.3 MySQL 的四种隔离级别
MySQL 支持 SQL 标准定义的四种隔离级别，从宽松到严格排列：
-
READ UNCOMMITTED (读未提交)
-
描述：事务可以读取其他未提交事务的修改。
-
解决的问题：无
-
存在的问题：脏读、不可重复读、幻读
- MySQL 命令：
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-
-
READ COMMITTED (读已提交)
-
描述：事务只能读取其他已提交事务的修改。
-
解决的问题：脏读
-
存在的问题：不可重复读、幻读
- MySQL 命令：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-
-
REPEATABLE READ (可重复读)
-
描述：MySQL 的默认级别。保证在同一个事务中多次读取同一数据的结果是一致的。
-
解决的问题：脏读、不可重复读
-
存在的问题：幻读（但MySQL通过MVCC部分解决了幻读）
- MySQL 命令：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-
-
SERIALIZABLE (可串行化)
-
描述：最高的隔离级别，强制事务串行执行。
-
解决的问题：脏读、不可重复读、幻读
-
存在的问题：性能最低，并发性最差
- MySQL 命令：
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-
12.4 各种隔离级别的问题对比
|
隔离级别 |
脏读 |
不可重复读 |
幻读 |
性能 |
|---|---|---|---|---|
|
READ UNCOMMITTED |
✅ 可能 |
✅ 可能 |
✅ 可能 |
最高 |
|
READ COMMITTED |
❌ 不可能 |
✅ 可能 |
✅ 可能 |
较高 |
|
REPEATABLE READ |
❌ 不可能 |
❌ 不可能 |
⚠️ 部分可能 |
中等 |
|
SERIALIZABLE |
❌ 不可能 |
❌ 不可能 |
❌ 不可能 |
最低 |
12.5 实际演示
准备测试数据：
CREATE TABLE account (
id INT PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2)
);
INSERT INTO account VALUES (1, '张三', 1000.00), (2, '李四', 2000.00);
演示1：READ UNCOMMITTED 的脏读
-- 会话A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM account WHERE id = 1; -- 看到1000
-- 会话B
START TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE id = 1; -- 未提交
-- 会话A 再次查询
SELECT balance FROM account WHERE id = 1; -- 看到1100（脏读！）
-- 会话B
ROLLBACK; -- 回滚
-- 会话A 最终查询
SELECT balance FROM account WHERE id = 1; -- 又变回1000
演示2：REPEATABLE READ 的可重复读
-- 会话A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM account WHERE id = 1; -- 看到1000
-- 会话B
UPDATE account SET balance = balance + 100 WHERE id = 1;
COMMIT;
-- 会话A 再次查询
SELECT balance FROM account WHERE id = 1; -- 仍然看到1000（可重复读）
COMMIT;
SELECT balance FROM account WHERE id = 1; -- 提交后看到1100
演示3：幻读问题
-- 会话A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT COUNT(*) FROM account; -- 看到2条记录
-- 会话B
INSERT INTO account VALUES (3, '王五', 3000);
COMMIT;
-- 会话A 再次查询
SELECT COUNT(*) FROM account; -- 仍然看到2条记录（无幻读）
-- 但如果执行：UPDATE account SET balance = 0; 会更新3条记录！
12.6 如何选择和设置隔离级别
查看当前隔离级别：
-- 查看全局和会话级别
SELECT @@global.transaction_isolation, @@transaction_isolation;
-- 或
SHOW VARIABLES LIKE 'transaction_isolation';
设置隔离级别：
-- 设置全局级别（重启后生效，影响所有新会话）
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 设置会话级别（仅影响当前会话）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 设置下一个事务的级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
在配置文件中设置：
[mysqld]
transaction-isolation = READ-COMMITTED
十三、mysql 锁
13.1 锁的层级体系
全局锁 → 表级锁 → 行级锁 → 页面锁（InnoDB特有）
13.2 锁的类型分类
13.2.1 按锁模式分类
-
共享锁（S Lock） - 读锁
-
排他锁（X Lock） - 写锁
-
意向共享锁（IS Lock）
-
意向排他锁（IX Lock）
13.2.2 按锁粒度分类
-
全局锁 - 锁定整个数据库实例
-
表级锁 - 锁定整张表
-
行级锁 - 锁定单行记录（InnoDB特有）
-
页面锁 - 锁定数据页（InnoDB特有）
13.2.3 InnoDB 行锁算法
-
记录锁（Record Lock） - 锁定单条记录
-
间隙锁（Gap Lock） - 锁定记录之间的间隙
-
临键锁（Next-Key Lock） - 记录锁 + 间隙锁
-
插入意向锁（Insert Intention Lock） - 特殊的间隙锁
13.3 重要锁相关参数详解
13.3.1 核心参数配置
-- 查看锁相关参数
SHOW VARIABLES LIKE '%lock%';
SHOW VARIABLES LIKE '%innodb%lock%';
主要参数说明：
|
参数名 |
默认值 |
说明 |
|---|---|---|
innodb_lock_wait_timeout |
50 |
行锁等待超时时间（秒） |
lock_wait_timeout |
31536000 |
元数据锁等待超时时间（秒） |
innodb_rollback_on_timeout |
OFF |
锁超时是否回滚整个事务 |
innodb_table_locks |
ON |
启用表级锁 |
innodb_autoinc_lock_mode |
2 |
自增锁模式（0/1/2） |
transaction_isolation |
REPEATABLE-READ |
事务隔离级别 |
innodb_deadlock_detect |
ON |
死锁检测开关 |
innodb_print_all_deadlocks |
OFF |
是否打印所有死锁到错误日志 |
13.3.2 参数配置示例
[mysqld]
innodb_lock_wait_timeout = 30
innodb_rollback_on_timeout = 1
innodb_deadlock_detect = ON
innodb_print_all_deadlocks = ON
transaction_isolation = READ-COMMITTED
13.4 锁工作机制实战案例
13.4.1 案例1：基本的行锁演示
-- 创建测试表
CREATE TABLE account (
id INT PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10,2),
KEY idx_name (name)
) ENGINE=InnoDB;
INSERT INTO account VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 2000.00),
(3, 'Charlie', 3000.00);
-- 会话1：开启事务并锁定某行
SESSION1> BEGIN;
SESSION1> SELECT * FROM account WHERE id = 1 FOR UPDATE;
-- 对 id=1 的记录加 X 锁
-- 会话2：尝试更新同一行
SESSION2> BEGIN;
SESSION2> UPDATE account SET balance = 1500 WHERE id = 1;
-- 这里会阻塞，等待SESSION1释放锁
-- 会话1提交后，会话2才能继续
SESSION1> COMMIT;
13.4.2 案例2：间隙锁防止幻读
-- 在 REPEATABLE-READ 隔离级别下
SESSION1> SET SESSION transaction_isolation = 'REPEATABLE-READ';
SESSION1> BEGIN;
SESSION1> SELECT * FROM account WHERE id BETWEEN 2 AND 4 FOR UPDATE;
-- 锁定 id=2,3 的记录，以及 (1,2), (3,4), (4,+∞) 的间隙
SESSION2> BEGIN;
SESSION2> INSERT INTO account VALUES (5, 'David', 4000);
-- 可以插入，因为5不在锁定范围
SESSION2> INSERT INTO account VALUES (3, 'Duplicate', 5000);
-- 阻塞！因为id=3已被锁定
SESSION2> INSERT INTO account VALUES (2, 'Duplicate', 5000);
-- 阻塞！因为id=2已被锁定
13.4.3 案例3：不同索引条件的加锁差异
-- 场景1：使用主键索引
SESSION1> BEGIN;
SESSION1> UPDATE account SET balance = 0 WHERE id = 1;
-- 只在 id=1 上加记录锁
-- 场景2：使用非唯一索引
SESSION1> BEGIN;
SESSION1> UPDATE account SET balance = 0 WHERE name = 'Alice';
-- 在 name='Alice' 的索引上加临键锁
-- 在主键 id=1 上加记录锁
-- 场景3：无索引查询（危险！）
SESSION1> BEGIN;
SESSION1> UPDATE account SET balance = 0 WHERE balance = 1000;
-- 如果没有 balance 索引，会进行全表扫描
-- 对所有扫描到的行加临键锁，可能锁全表！
13.5 死锁的产生与处理
13.5.1 死锁产生原理
死锁产生的四个必要条件：
-
互斥条件 - 资源不能被共享
-
请求与保持条件 - 持有资源的同时请求新资源
-
不剥夺条件 - 资源不能被强制剥夺
-
循环等待条件 - 事务间形成等待环
13.5.2 经典死锁案例
-- 准备数据
CREATE TABLE resource (
id INT PRIMARY KEY,
value INT
);
INSERT INTO resource VALUES (1, 100), (2, 200);
-- 死锁发生过程
-- 时间线 | 事务T1 | 事务T2
-- T1 | BEGIN; | BEGIN;
-- T2 | UPDATE resource SET value=101 WHERE id=1; |
-- T3 | | UPDATE resource SET value=201 WHERE id=2;
-- T4 | UPDATE resource SET value=202 WHERE id=2; -- 等待T2
-- T5 | | UPDATE resource SET value=102 WHERE id=1; -- 死锁！
死锁分析：
-
T1 持有 id=1 的锁，请求 id=2 的锁
-
T2 持有 id=2 的锁，请求 id=1 的锁
-
循环等待形成死锁
13.5.3 死锁检测与处理
3.1 查看死锁信息
-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS;
-- 查看当前锁信息（MySQL 8.0+）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
-- 查看锁等待关系
SELECT * FROM sys.innodb_lock_waits;
3.2 死锁相关参数配置
-- 死锁检测开关
SET GLOBAL innodb_deadlock_detect = ON;
-- 死锁超时时间
SET GLOBAL innodb_lock_wait_timeout = 30;
-- 记录所有死锁到错误日志
SET GLOBAL innodb_print_all_deadlocks = ON;
3.3 死锁自动处理当死锁发生时，InnoDB 会：
-
自动检测死锁（如果
innodb_deadlock_detect=ON） -
选择牺牲者 - 回滚代价最小的事务
-
回滚牺牲者 - 返回 ERROR 1213 错误
-
其他事务继续
13.5.4 死锁预防策略
策略1：顺序访问资源
-- 不好的方式：不同顺序访问
-- 事务1: UPDATE ... WHERE id=1; UPDATE ... WHERE id=2;
-- 事务2: UPDATE ... WHERE id=2; UPDATE ... WHERE id=1;
-- 好的方式：统一顺序访问
-- 事务1: UPDATE ... WHERE id=1; UPDATE ... WHERE id=2;
-- 事务2: UPDATE ... WHERE id=1; UPDATE ... WHERE id=2;
策略2：使用低隔离级别
-- 降低隔离级别，减少间隙锁
SET SESSION transaction_isolation = 'READ-COMMITTED';
策略3：一次性锁定所有资源
-- 在事务开始时锁定所有需要的行
BEGIN;
SELECT * FROM account WHERE id IN (1, 2) FOR UPDATE;
-- 后续操作...
COMMIT;
策略4：使用锁超时
-- 设置较短的锁等待超时
SET SESSION innodb_lock_wait_timeout = 10;
13.6 锁监控与性能优化
13.6.1 实时锁监控
-- 查看当前事务和锁状态
SELECT * FROM information_schema.INNODB_TRX;
-- MySQL 5.7 使用
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- MySQL 8.0 使用性能模式
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
-- 使用 sys 库的简化视图
SELECT * FROM sys.innodb_lock_waits;
13.6.2 锁等待分析脚本
-- 查找锁等待的源头
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
13.6.3 性能优化建议
-
索引优化 - 确保查询使用合适的索引
-
事务优化 - 保持事务短小，尽快提交
-
访问顺序 - 统一数据访问顺序
-
隔离级别 - 根据业务选择合适的隔离级别
-
监控告警 - 设置锁等待超时告警
扫一扫
7318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
