印刻数据库

外观

MySQL 在线参数修改方法 ​

参数类型与特性 ​

动态参数 ​

  • 定义：可以在 MySQL 运行时修改，无需重启服务
  • 生效范围：
    • GLOBAL：全局生效，影响所有新连接
    • SESSION：会话级生效，仅影响当前连接
  • 修改方式：使用 SET 语句修改
  • 持久化：默认仅临时生效，重启后恢复默认值

静态参数 ​

  • 定义：必须重启 MySQL 服务才能生效
  • 生效范围：仅 GLOBAL 级别
  • 修改方式：修改配置文件 + 重启服务
  • 持久化：修改配置文件后永久生效

参数类型区分 ​

sql
-- 查看参数是否可动态修改
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看参数的范围和是否可动态修改
SELECT * FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'innodb_buffer_pool_size';
SELECT * FROM information_schema.SESSION_VARIABLES WHERE VARIABLE_NAME = 'innodb_buffer_pool_size';

在线参数修改语法 ​

会话级参数修改 ​

sql
-- 修改当前会话的参数
SET SESSION max_connections = 1000;

-- 或使用 @@session. 前缀
SET @@session.max_connections = 1000;

-- 简写形式（默认是 SESSION）
SET max_connections = 1000;

全局级参数修改 ​

sql
-- 修改全局参数
SET GLOBAL max_connections = 1000;

-- 或使用 @@global. 前缀
SET @@global.max_connections = 1000;

持久化参数修改 ​

MySQL 8.0 持久化参数 ​

sql
-- 持久化修改全局参数
SET PERSIST max_connections = 1000;

-- 或持久化修改全局参数
SET PERSIST_GLOBAL max_connections = 1000;

-- 查看持久化参数
SHOW PERSISTENT VARIABLES LIKE 'max_connections';

-- 查看持久化参数文件
SELECT * FROM performance_schema.persisted_variables;

MySQL 5.7 及以下版本持久化 ​

sql
-- 修改全局参数（临时生效）
SET GLOBAL max_connections = 1000;

-- 同时修改配置文件（永久生效）
-- 使用编辑器修改 my.cnf 文件
-- [mysqld]
-- max_connections = 1000

常用参数在线修改示例 ​

连接相关参数 ​

sql
-- 修改最大连接数
SET GLOBAL max_connections = 2000;

-- 修改连接超时时间
SET GLOBAL connect_timeout = 10;

-- 修改交互连接超时时间
SET GLOBAL interactive_timeout = 28800;

-- 修改非交互连接超时时间
SET GLOBAL wait_timeout = 28800;

内存相关参数 ​

sql
-- 修改 InnoDB 缓冲池大小（MySQL 5.7+ 支持动态调整）
SET GLOBAL innodb_buffer_pool_size = 16G;

-- 修改查询缓存大小（注意：MySQL 8.0 已移除查询缓存）
SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_type = 0;

-- 修改临时表最大大小
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

日志相关参数 ​

sql
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;

-- 开启通用查询日志
SET GLOBAL general_log = ON;

-- 设置二进制日志格式
SET GLOBAL binlog_format = 'ROW';

优化器相关参数 ​

sql
-- 修改优化器模式
SET GLOBAL optimizer_mode = 'NO_ENGINE_SUBSTITUTION';

-- 开启索引合并优化
SET GLOBAL optimizer_switch = 'index_merge=on';

-- 开启物化视图优化
SET GLOBAL optimizer_switch = 'materialization=on';

存储引擎相关参数 ​

sql
-- 修改 InnoDB 日志缓冲区大小
SET GLOBAL innodb_log_buffer_size = 16M;

-- 修改 InnoDB 脏页刷新比例
SET GLOBAL innodb_max_dirty_pages_pct = 75;

-- 修改 InnoDB 刷新日志的频率
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

参数修改的生效机制 ​

全局参数生效机制 ​

  1. 修改全局参数后，新的连接会使用新的参数值
  2. 已存在的连接仍使用修改前的参数值
  3. 查看全局参数使用 SHOW GLOBAL VARIABLES 或 @@global.variable_name

会话参数生效机制 ​

  1. 修改会话参数后，仅当前会话生效
  2. 新的连接不受影响
  3. 会话结束后，参数值丢失
  4. 查看会话参数使用 SHOW VARIABLES 或 @@session.variable_name

持久化参数生效机制 ​

MySQL 8.0 ​

  1. 使用 SET PERSIST 修改参数后：

    • 立即对全局生效
    • 写入到 mysqld-auto.cnf 文件
    • 重启后自动加载

  2. mysqld-auto.cnf 文件位置：

    sql
    SHOW VARIABLES LIKE 'datadir';

MySQL 5.7 及以下版本 ​

  1. 修改配置文件后：
    • 不会立即生效
    • 需要重启服务才能生效
    • 重启后永久生效

在线参数修改注意事项 ​

性能影响 ​

  • 内存参数：调整内存相关参数可能导致内存分配或释放，影响性能
  • 日志参数：开启日志会增加 I/O 负载
  • 优化器参数：可能改变查询执行计划，影响查询性能
  • 连接参数：调整连接数可能影响系统资源使用

稳定性风险 ​

  • 核心参数：修改核心参数（如 innodb_buffer_pool_size）可能导致服务不稳定
  • 错误值：设置错误的参数值可能导致服务崩溃
  • 不兼容参数：某些参数组合可能不兼容，导致异常

持久化风险 ​

  • 临时修改丢失：忘记持久化修改，重启后恢复默认值
  • 配置文件错误：手动修改配置文件可能引入语法错误，导致无法启动
  • 版本差异：不同版本的参数名称或默认值可能不同

在线参数修改最佳实践 ​

1. 充分测试 ​

  • 测试环境验证：在测试环境中充分测试参数修改的影响
  • 逐步调整：从小值开始逐步调整，观察系统反应
  • 监控指标：修改后密切监控系统性能指标

2. 分批修改 ​

  • 核心参数：核心参数单独修改，观察效果后再修改其他参数
  • 批量参数：相关参数可以一起修改，但不要一次修改过多参数
  • 不同类型参数：不同类型的参数分开修改，便于分析影响

3. 监控与回滚 ​

  • 实时监控：修改参数后实时监控系统性能
  • 设置回滚计划：准备好回滚方案，出现问题及时回滚
  • 记录修改过程：详细记录参数修改的时间、内容和影响

4. 持久化处理 ​

  • 及时持久化：重要参数修改后及时持久化
  • 验证持久化：验证持久化是否成功
  • 备份配置文件：修改配置文件前备份原文件

5. 文档记录 ​

  • 参数修改记录：记录参数修改的原因、时间、修改人、修改前后的值
  • 性能对比：记录修改前后的性能指标对比
  • 问题记录：记录修改过程中遇到的问题和解决方案

常用在线参数修改场景 ​

场景一：连接数调整 ​

问题描述 ​

系统出现 "Too many connections" 错误，需要增加最大连接数。

解决方案 ​

sql
-- 查看当前连接数和最大连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 临时增加最大连接数
SET GLOBAL max_connections = 2000;

-- 持久化修改（MySQL 8.0）
SET PERSIST max_connections = 2000;

-- 修改配置文件（MySQL 5.7 及以下）
-- [mysqld]
-- max_connections = 2000

场景二：慢查询日志开启 ​

问题描述 ​

需要开启慢查询日志，分析慢查询语句。

解决方案 ​

sql
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;

-- 设置慢查询日志文件
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

场景三：InnoDB 缓冲池调整 ​

问题描述 ​

系统内存充足，需要增加 InnoDB 缓冲池大小以提高性能。

解决方案 ​

sql
-- 查看当前缓冲池大小
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看缓冲池命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

-- 临时调整缓冲池大小
SET GLOBAL innodb_buffer_pool_size = 16G;

-- 持久化修改（MySQL 8.0）
SET PERSIST innodb_buffer_pool_size = 16G;

场景四：临时表大小调整 ​

问题描述 ​

系统出现大量 "The table 'xxx' is full" 错误，需要增加临时表大小。

解决方案 ​

sql
-- 查看当前临时表大小
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

-- 调整临时表大小
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

-- 持久化修改
SET PERSIST tmp_table_size = 64M;
SET PERSIST max_heap_table_size = 64M;

参数修改的监控与验证 ​

监控指标 ​

  • 连接数：Threads_connected, Max_used_connections
  • 内存使用：Innodb_buffer_pool_pages_total, Innodb_buffer_pool_pages_free
  • 查询性能：Queries, Questions, Slow_queries
  • I/O 性能：Innodb_data_reads, Innodb_data_writes
  • 锁等待：Innodb_row_lock_waits, Innodb_row_lock_time

验证方法 ​

sql
-- 查看修改后的参数值
SHOW GLOBAL VARIABLES LIKE '参数名';
SHOW VARIABLES LIKE '参数名';

-- 查看参数的生效情况
SELECT @@global.参数名;
SELECT @@session.参数名;

-- 查看持久化参数
SELECT * FROM performance_schema.persisted_variables WHERE variable_name = '参数名';

性能监控工具 ​

  • SHOW GLOBAL STATUS：查看全局状态指标
  • SHOW ENGINE INNODB STATUS：查看 InnoDB 详细状态
  • Performance Schema：详细的性能监控
  • MySQL Enterprise Monitor：商业监控工具
  • Percona Monitoring and Management (PMM)：开源监控工具

常见错误与解决方案 ​

错误 1：参数无法动态修改 ​

ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable

解决方案：

  • 检查参数是否为静态参数
  • 确认 MySQL 版本是否支持该参数的动态修改
  • 对于静态参数，需要修改配置文件并重启服务

错误 2：参数值超出范围 ​

ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '100000' (out of range)

解决方案：

  • 查看参数的有效范围
  • 参考官方文档确定合理的参数值
  • 考虑系统资源限制，不要设置过大的值

错误 3：参数名称错误 ​

ERROR 1193 (HY000): Unknown system variable 'wrong_variable_name'

解决方案：

  • 检查参数名称是否拼写正确
  • 确认参数名称在当前 MySQL 版本中是否存在
  • 查看官方文档确认正确的参数名称

错误 4：权限不足 ​

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

解决方案：

  • 使用具有 SUPER 或 SYSTEM_VARIABLES_ADMIN 权限的用户
  • 授予用户相应的权限
  • 考虑使用角色管理权限

错误 5：配置文件语法错误 ​

2023-01-01T00:00:00.000000Z 0 [ERROR] [MY-010129] [Server] Error in system table processor.
2023-01-01T00:00:00.000000Z 0 [ERROR] [MY-010098] [Server] Error in defaults handling. Program aborted.

解决方案：

  • 检查配置文件的语法错误
  • 恢复备份的配置文件
  • 使用 mysqld --validate-config 验证配置文件

不同版本的参数差异 ​

MySQL 5.7 vs MySQL 8.0 ​

新增参数 ​

  • MySQL 8.0：
    • innodb_dedicated_server
    • persist_only
    • sql_mode 默认值变化
    • 移除了 query_cache_* 系列参数

参数名称变化 ​

  • log_bin_trust_function_creators：在 MySQL 8.0 中仍可用
  • binlog_checksum：默认值从 CRC32 变为 NONE
  • default_authentication_plugin：从 mysql_native_password 变为 caching_sha2_password

持久化机制 ​

  • MySQL 5.7：仅支持通过配置文件持久化
  • MySQL 8.0：支持 SET PERSIST 命令持久化到 mysqld-auto.cnf

MariaDB 与 MySQL 的差异 ​

  • 参数名称差异：部分参数名称在 MariaDB 中不同
  • 默认值差异：相同参数的默认值可能不同
  • 新增参数：MariaDB 新增了一些特有参数
  • 移除参数：MariaDB 移除了一些 MySQL 中的参数

在线参数修改工具 ​

1. MySQL 命令行 ​

sql
-- 直接在命令行中修改参数
mysql> SET GLOBAL max_connections = 2000;

-- 查看参数
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';

2. MySQL Workbench ​

  • Server Status：查看服务器状态和变量
  • Options File：图形化修改配置文件
  • Performance Schema：查看详细的性能指标

3. 第三方工具 ​

  • Percona Toolkit：包含 pt-variable-advisor 工具，提供参数建议
  • MySQLTuner：自动分析 MySQL 配置并提供建议
  • Innotop：实时监控和管理 MySQL

4. 自动化脚本 ​

bash
#!/bin/bash
# 在线修改 MySQL 参数的脚本

# 配置信息
MYSQL_USER="root"
MYSQL_PASSWORD="password"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"

# 参数配置
PARAMETER_NAME="max_connections"
PARAMETER_VALUE="2000"

# 修改参数
echo "Modifying $PARAMETER_NAME to $PARAMETER_VALUE..."
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -e "SET GLOBAL $PARAMETER_NAME = $PARAMETER_VALUE;"
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -e "SET PERSIST $PARAMETER_NAME = $PARAMETER_VALUE;"

# 验证修改
echo "Verifying modification..."
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -e "SHOW GLOBAL VARIABLES LIKE '$PARAMETER_NAME';"
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -e "SELECT * FROM performance_schema.persisted_variables WHERE variable_name = '$PARAMETER_NAME';"

echo "Parameter modification completed."

常见问题（FAQ） ​

Q1: 如何判断参数是动态还是静态？ ​

A1: 可以通过以下方法判断：

  • 查看官方文档，参数说明中会标明是否可动态修改
  • 使用 SET GLOBAL 参数名 = 值 尝试修改，失败则为静态参数
  • 查看 information_schema.GLOBAL_VARIABLES，如果存在则可全局修改

Q2: 修改全局参数后，如何让现有连接生效？ ​

A2: 全局参数修改后，现有连接不会自动生效，需要：

  • 等待现有连接断开重连
  • 手动重启应用服务，重建连接
  • 对于支持的参数，可以使用 SET SESSION 参数名 = 值 单独修改现有连接的参数

Q3: 如何持久化 MySQL 5.7 的参数修改？ ​

A3: MySQL 5.7 不支持 SET PERSIST 命令，需要：

  1. 使用 SET GLOBAL 参数名 = 值 临时修改
  2. 手动编辑配置文件（my.cnf 或 my.ini）
  3. 重启 MySQL 服务使其永久生效

Q4: 在线修改参数会影响正在执行的事务吗？ ​

A4: 一般情况下，在线修改参数不会影响正在执行的事务，但：

  • 某些存储引擎参数可能会影响事务执行
  • 修改事务隔离级别会影响后续的事务
  • 修改日志相关参数可能会影响事务的日志记录

Q5: 如何批量修改多个参数？ ​

A5: 可以使用以下方法批量修改参数：

  • 多个 SET 语句依次执行
  • 使用存储过程批量修改
  • 编写脚本自动化修改
  • 使用配置管理工具（如 Ansible、Puppet）批量部署

Q6: 如何回滚参数修改？ ​

A6: 回滚参数修改的方法：

  • 对于临时修改，使用 SET GLOBAL 参数名 = 原 value 改回原值
  • 对于持久化修改，需要同时修改全局参数和持久化配置
  • 如果已经重启服务，需要从备份恢复配置文件并重启

Q7: 如何查看参数修改历史？ ​

A7: MySQL 本身不记录参数修改历史，需要：

  • 手动记录参数修改过程
  • 使用配置管理工具（如 Git）管理配置文件版本
  • 使用第三方监控工具记录参数变化

Q8: 在线修改参数会导致锁表吗？ ​

A8: 一般情况下，在线修改参数不会锁表，但：

  • 修改某些存储引擎参数可能会导致短暂的锁等待
  • 修改缓冲池大小可能会导致内存分配，影响性能
  • 修改日志参数可能会影响 I/O 性能

Q9: 如何优化参数修改的性能影响？ ​

A9: 优化参数修改性能影响的方法：

  • 在低峰期进行参数修改
  • 从小值开始逐步调整
  • 密切监控系统性能指标
  • 准备好回滚方案

Q10: 如何确保参数修改的安全性？ ​

A10: 确保参数修改安全性的方法：

  • 在测试环境充分测试
  • 遵循最小权限原则，使用具有适当权限的用户
  • 详细记录修改过程
  • 准备好回滚方案
  • 定期备份配置文件

性能优化案例 ​

案例 1：连接数优化 ​

问题描述 ​

系统频繁出现 "Too many connections" 错误，影响业务正常运行。

分析过程 ​

  1. 查看当前连接数和最大连接数：

    sql
    SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

  2. 发现当前连接数已接近最大连接数，需要增加最大连接数。

解决方案 ​

  1. 临时增加最大连接数：

    sql
    SET GLOBAL max_connections = 2000;

  2. 持久化修改（MySQL 8.0）：

    sql
    SET PERSIST max_connections = 2000;

  3. 监控连接数变化，确保系统稳定。

优化效果 ​

  • 解决了 "Too many connections" 错误
  • 系统连接数稳定在 1500-1800 之间
  • 业务运行正常，没有出现连接相关的错误

案例 2：缓冲池大小优化 ​

问题描述 ​

系统 I/O 利用率高，查询响应时间长，怀疑是 InnoDB 缓冲池大小不足。

分析过程 ​

  1. 查看缓冲池使用情况：

    sql
    SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

  2. 计算缓冲池命中率，发现命中率低于 95%，需要增加缓冲池大小。

解决方案 ​

  1. 临时调整缓冲池大小：

    sql
    SET GLOBAL innodb_buffer_pool_size = 16G;

  2. 持久化修改：

    sql
    SET PERSIST innodb_buffer_pool_size = 16G;

  3. 监控缓冲池使用情况和 I/O 性能。

优化效果 ​

  • 缓冲池命中率提升至 98% 以上
  • 磁盘 I/O 利用率降低 60%
  • 查询响应时间平均降低 40%
  • 系统吞吐量提升 35%

案例 3：慢查询日志优化 ​

问题描述 ​

需要分析系统中的慢查询，但开启慢查询日志可能影响性能。

解决方案 ​

  1. 调整慢查询阈值，只记录真正的慢查询：

    sql
    SET GLOBAL long_query_time = 1;

  2. 开启慢查询日志，但限制日志文件大小：

    sql
    SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL max_slowlog_size = 100M;

  3. 使用日志轮转，避免日志文件过大：

    sql
    SET GLOBAL log_output = 'FILE';

优化效果 ​

  • 成功开启了慢查询日志，收集了有用的慢查询信息
  • 对系统性能的影响很小（I/O 使用率增加不到 5%）
  • 通过分析慢查询，优化了多个查询语句，提升了系统性能