MySQL 主版本配置变化

MySQL 5.6 到 5.7 的配置变化

核心参数变化

SQL模式变化
- MySQL 5.7 默认启用严格SQL模式
- 新增默认SQL模式：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
- 配置参数：sql_mode
默认存储引擎
- MySQL 5.7 仍然默认使用 InnoDB
- 但对 InnoDB 进行了大量性能优化
临时表配置
- 新增参数 tmp_table_size 和 max_heap_table_size 默认值调整
- 建议将这两个参数设置为相同值
连接相关参数
- 新增 max_connections 默认值从 151 增加到 151
- 新增 connect_timeout 默认值从 10 秒增加到 10 秒
- 新增 wait_timeout 默认值从 28800 秒增加到 28800 秒

新增参数

参数名称	说明	默认值
innodb_buffer_pool_dump_at_shutdown	关闭时是否转储缓冲池状态	ON
innodb_buffer_pool_load_at_startup	启动时是否加载缓冲池状态	ON
innodb_buffer_pool_dump_pct	转储缓冲池的百分比	25
innodb_log_file_size	重做日志文件大小	48MB
innodb_checksum_algorithm	页校验算法	crc32
binlog_checksum	二进制日志校验算法	CRC32
gtid_mode	是否启用GTID	OFF
enforce_gtid_consistency	是否强制GTID一致性	OFF

废弃参数

参数名称	替代方案
innodb_large_prefix	不再需要，默认启用
innodb_file_format	不再需要，默认使用 Barracuda
innodb_file_format_max	不再需要
innodb_stats_on_metadata	不再需要，默认关闭

MySQL 5.7 到 8.0 的配置变化

核心参数变化

SQL模式变化
- MySQL 8.0 移除了 NO_AUTO_CREATE_USER 模式
- 默认SQL模式：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
- 配置参数：sql_mode
默认认证插件
- MySQL 8.0 默认使用 caching_sha2_password 替代 mysql_native_password
- 配置参数：default_authentication_plugin
InnoDB 相关参数
- innodb_buffer_pool_size 默认值从 128MB 增加到 128MB（但内部实现优化）
- innodb_log_file_size 默认值从 48MB 增加到 50331648（约48MB）
- innodb_log_files_in_group 默认值保持为 2
二进制日志参数
- binlog_format 默认值从 STATEMENT 改为 ROW
- binlog_row_image 默认值为 FULL
- sync_binlog 默认值从 0 改为 1（更安全）

新增参数

参数名称	说明	默认值
innodb_dedicated_server	是否自动配置InnoDB参数（适合专用服务器）	OFF
innodb_deadlock_detect	是否启用死锁检测	ON
innodb_directories	InnoDB数据文件目录列表	空
innodb_redo_log_encrypt	是否加密重做日志	OFF
innodb_undo_log_encrypt	是否加密撤销日志	OFF
log_error_verbosity	错误日志详细程度	2
performance_schema_max_thread_instances	性能模式最大线程实例数	1000
tablespace_max_row_groups	表空间最大行组数量	0（无限制）

废弃参数

参数名称	替代方案
query_cache_size	已移除查询缓存功能
query_cache_type	已移除查询缓存功能
query_cache_limit	已移除查询缓存功能
innodb_large_prefix	不再需要，默认启用
innodb_file_format	不再需要，默认使用 Barracuda
innodb_file_format_max	不再需要
sql_slave_skip_counter	使用 GTID 替代

MySQL 8.0 各小版本间的配置变化

MySQL 8.0.11 变化

新增参数
- innodb_redo_log_capacity：重做日志总容量，替代 innodb_log_file_size 和 innodb_log_files_in_group
- 默认值为 1073741824（1GB）
参数调整
- innodb_log_file_size 和 innodb_log_files_in_group 仍然可用，但优先级低于 innodb_redo_log_capacity

MySQL 8.0.16 变化

新增参数
- innodb_directories：支持多个数据文件目录
- innodb_temp_tablespaces_dir：临时表空间目录
参数调整
- default_authentication_plugin 默认值保持为 caching_sha2_password

MySQL 8.0.23 变化

新增参数
- innodb_parallel_read_threads：并行读取线程数，默认值为 4
- innodb_ddl_threads：DDL 线程数，默认值为 4
参数调整
- innodb_buffer_pool_dump_pct 默认值从 25 增加到 40

MySQL 8.0.30 变化

新增参数
- innodb_redo_log_encrypt：默认值保持为 OFF
- innodb_undo_log_encrypt：默认值保持为 OFF
参数调整
- max_connections 默认值从 151 增加到 151（保持不变）
- back_log 默认值从 80 增加到 80（保持不变）

配置迁移最佳实践

预迁移准备

备份当前配置

bash

# 备份当前配置文件
cp /etc/my.cnf /etc/my.cnf.bak

# 导出当前参数设置
mysql -u root -p -e "SHOW GLOBAL VARIABLES;" > current_variables.txt

分析配置差异
- 使用 mysql_upgrade 工具检查配置兼容性
- 使用第三方工具（如 pt-config-diff）比较配置差异
- 手动检查官方文档中的版本变化
创建测试环境
- 在测试环境中部署目标MySQL版本
- 应用修改后的配置
- 测试应用程序兼容性

配置迁移步骤

移除废弃参数
- 识别并移除当前配置中的废弃参数
- 例如，MySQL 8.0 中移除了查询缓存相关参数
调整默认参数
- 根据目标版本的默认值调整参数
- 特别注意 SQL 模式、认证插件等核心参数
添加新增参数
- 根据需要添加目标版本的新增参数
- 参考官方文档设置合理的值
优化性能参数
- 根据目标版本的性能特性优化参数
- 例如，MySQL 8.0 中可以启用 innodb_dedicated_server 自动配置 InnoDB 参数

配置文件示例

MySQL 5.7 配置文件

ini

[mysqld]
# 基础配置
server-id = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# SQL模式
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

# 连接配置
max_connections = 1000
connect_timeout = 10
wait_timeout = 28800

# InnoDB配置
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

# 二进制日志配置
binlog_format = ROW
log_bin = mysql-bin
expire_logs_days = 7

# 其他配置
tmp_table_size = 64M
max_heap_table_size = 64M

MySQL 8.0 配置文件

ini

[mysqld]
# 基础配置
server-id = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# SQL模式
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 连接配置
max_connections = 1000
connect_timeout = 10
wait_timeout = 28800

# 认证插件
default_authentication_plugin = caching_sha2_password

# InnoDB配置
innodb_buffer_pool_size = 4G
innodb_redo_log_capacity = 1G
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_dedicated_server = ON

# 二进制日志配置
binlog_format = ROW
log_bin = mysql-bin
expire_logs_days = 7
sync_binlog = 1

# 其他配置
tmp_table_size = 64M
max_heap_table_size = 64M

# 新增参数
log_error_verbosity = 2
performance_schema_max_thread_instances = 2000

常见配置问题及解决方案

1. SQL模式不兼容

问题：应用程序在 MySQL 8.0 上运行时出现 ONLY_FULL_GROUP_BY 错误

解决方案：

修复应用程序的 SQL 查询，确保 GROUP BY 子句包含所有非聚合列
临时调整 SQL 模式，移除 ONLY_FULL_GROUP_BY（不推荐长期使用）

sql

-- 临时调整
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- 永久调整（修改配置文件）
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

2. 认证插件不兼容

问题：旧版本客户端无法连接 MySQL 8.0，提示认证插件错误

解决方案：

更新客户端到支持 caching_sha2_password 的版本
为用户设置兼容的认证插件

sql

-- 为用户设置 mysql_native_password 认证插件
ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

-- 或者修改默认认证插件（不推荐）
default_authentication_plugin = mysql_native_password

3. 查询缓存移除

问题：MySQL 8.0 中移除了查询缓存功能，应用程序依赖该功能

解决方案：

优化应用程序，减少重复查询
使用应用层缓存（如 Redis、Memcached）
优化数据库查询和索引

4. 二进制日志格式变化

问题：从 MySQL 5.7 迁移到 8.0 后，复制出现问题

解决方案：

确保主从库的 binlog_format 一致
考虑使用 GTID 复制提高复制可靠性
检查并修复不兼容的 SQL 语句

配置验证与测试

配置验证

检查配置文件语法

bash

mysql --defaults-file=/etc/my.cnf --verbose --help > /dev/null

验证参数生效

sql

-- 查看全局参数
SHOW GLOBAL VARIABLES LIKE '参数名';

-- 查看会话参数
SHOW SESSION VARIABLES LIKE '参数名';

检查错误日志
bash
```
tail -n 100 /var/log/mysqld.log
```

性能测试

基准测试

使用 sysbench 进行基准测试
比较迁移前后的性能差异

示例：

bash

# 准备测试数据
sysbench oltp_read_write --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=password --mysql-db=test --tables=10 --table-size=1000000 prepare

# 运行测试
sysbench oltp_read_write --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=password --mysql-db=test --tables=10 --table-size=1000000 --threads=16 --time=60 run

应用程序测试
- 运行应用程序的功能测试
- 检查应用程序的响应时间
- 监控数据库的 CPU、内存和 I/O 使用率

版本升级后的配置优化

1. 利用新特性优化

MySQL 8.0 优化建议：
- 启用 innodb_dedicated_server 自动配置 InnoDB 参数
- 使用 innodb_redo_log_capacity 替代传统的重做日志配置
- 启用 GTID 复制提高复制可靠性
- 使用 SET PERSIST 动态修改参数，永久生效

示例：

sql

-- 动态修改参数并永久保存
SET PERSIST innodb_buffer_pool_size = 8G;
SET PERSIST innodb_redo_log_capacity = 2G;
SET PERSIST max_connections = 2000;

2. 调整性能相关参数

根据目标版本的性能特性，调整以下参数：

参数名称	建议值	说明
innodb_buffer_pool_size	物理内存的 50-70%	InnoDB 缓冲池大小
innodb_redo_log_capacity	1-4G	重做日志总容量
innodb_flush_log_at_trx_commit	1 或 2	日志刷新策略（1 最安全，2 性能更好）
innodb_flush_method	O_DIRECT	数据文件刷新方法
sync_binlog	1 或 100	二进制日志同步策略
max_connections	根据实际需求	最大连接数
tmp_table_size	64M-256M	临时表大小
max_heap_table_size	64M-256M	内存表大小

3. 监控与调优

部署监控系统（如 Prometheus + Grafana）监控数据库性能
定期分析慢查询日志，优化查询语句
使用 Performance Schema 和 Sys Schema 分析数据库运行状态
定期检查和优化表结构和索引

常见问题（FAQ）

Q1: 从 MySQL 5.7 升级到 8.0 后，为什么查询变慢了？

A1: 查询变慢可能有以下原因：

SQL 模式变化导致查询执行计划改变
查询缓存被移除
二进制日志格式从 STATEMENT 改为 ROW
新的默认参数设置不适合您的工作负载

解决方案：

分析慢查询日志，找出具体的慢查询
优化查询语句和索引
调整 MySQL 参数以适应您的工作负载
考虑使用查询重写或计划管理功能

Q2: 如何处理 MySQL 8.0 中移除的查询缓存功能？

A2: 处理查询缓存移除的方法：

优化应用程序，减少重复查询
使用应用层缓存（如 Redis、Memcached）
优化数据库查询和索引
考虑使用 MySQL 8.0 的查询重写或计划管理功能

Q3: MySQL 8.0 中的 `innodb_dedicated_server` 参数有什么作用？

A3: innodb_dedicated_server 参数可以让 MySQL 根据服务器的可用内存自动配置 InnoDB 相关参数：

innodb_buffer_pool_size：根据可用内存自动调整
innodb_log_buffer_size：根据可用内存自动调整
innodb_redo_log_capacity：根据可用内存自动调整
innodb_flush_method：自动设置为 O_DIRECT

这个参数适合将 MySQL 部署在专用服务器上的场景，可以简化配置并获得较好的性能。

Q4: 如何在 MySQL 8.0 中启用 GTID 复制？

A4: 在 MySQL 8.0 中启用 GTID 复制的步骤：

在主库和从库的配置文件中添加以下参数：
ini
```
gtid_mode = ON
enforce_gtid_consistency = ON
```
重启主库和从库

在从库上配置复制：

sql

CHANGE MASTER TO
  MASTER_HOST = 'master_host',
  MASTER_USER = 'replication_user',
  MASTER_PASSWORD = 'replication_password',
  MASTER_PORT = 3306,
  MASTER_AUTO_POSITION = 1;

START SLAVE;

Q5: 从 MySQL 5.6 升级到 8.0，是否需要先升级到 5.7？

A5: 是的，MySQL 不支持跨主版本直接升级。从 5.6 升级到 8.0 需要经过以下步骤：

从 5.6 升级到 5.7
在 5.7 上运行 mysql_upgrade 工具
测试应用程序兼容性
从 5.7 升级到 8.0
在 8.0 上运行 mysql_upgrade 工具
再次测试应用程序兼容性

Q6: 如何处理 MySQL 8.0 中 `NO_AUTO_CREATE_USER` SQL 模式的移除？

A6: MySQL 8.0 中移除了 NO_AUTO_CREATE_USER SQL 模式，同时也移除了 GRANT 语句自动创建用户的功能。现在，您需要先使用 CREATE USER 语句创建用户，然后再使用 GRANT 语句授予权限。

示例：

sql

-- 推荐方式
CREATE USER 'user'@'host' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'user'@'host';

-- 不再支持的方式（会报错）
GRANT ALL PRIVILEGES ON database.* TO 'user'@'host' IDENTIFIED BY 'password';

Q7: MySQL 8.0 中的二进制日志格式默认是 ROW，这会影响性能吗？

A7: ROW 格式的二进制日志比 STATEMENT 格式更安全，但可能会增加日志大小和复制延迟。然而，MySQL 8.0 对 ROW 格式的二进制日志进行了大量优化，性能影响已经很小。

如果您的应用程序有大量的批量更新或插入操作，可以考虑：

优化应用程序，减少批量操作的大小
调整 binlog_row_image 参数为 MINIMAL 减少日志大小
增加复制带宽和从库性能

Q8: 如何在不同 MySQL 版本之间迁移配置文件？

A8: 在不同 MySQL 版本之间迁移配置文件的步骤：

备份当前配置文件
识别并移除目标版本中废弃的参数
调整目标版本中默认值变化的参数
添加目标版本中新增的参数（根据需要）
测试修改后的配置文件语法
在测试环境中部署并测试
在生产环境中部署并监控

建议使用第三方工具（如 pt-config-diff）比较不同版本的配置差异，以便更准确地进行配置迁移。

MySQL 主版本配置变化 ​

MySQL 5.6 到 5.7 的配置变化 ​

核心参数变化 ​

新增参数 ​

废弃参数 ​

MySQL 5.7 到 8.0 的配置变化 ​

核心参数变化 ​

新增参数 ​

废弃参数 ​

MySQL 8.0 各小版本间的配置变化 ​

MySQL 8.0.11 变化 ​

MySQL 8.0.16 变化 ​

MySQL 8.0.23 变化 ​

MySQL 8.0.30 变化 ​

配置迁移最佳实践 ​

预迁移准备 ​

配置迁移步骤 ​

配置文件示例 ​

MySQL 5.7 配置文件 ​

MySQL 8.0 配置文件 ​

常见配置问题及解决方案 ​

1. SQL模式不兼容 ​

2. 认证插件不兼容 ​

3. 查询缓存移除 ​

4. 二进制日志格式变化 ​

配置验证与测试 ​

配置验证 ​

性能测试 ​

版本升级后的配置优化 ​

1. 利用新特性优化 ​

2. 调整性能相关参数 ​

3. 监控与调优 ​

常见问题（FAQ） ​

Q1: 从 MySQL 5.7 升级到 8.0 后，为什么查询变慢了？ ​

Q2: 如何处理 MySQL 8.0 中移除的查询缓存功能？ ​

Q3: MySQL 8.0 中的 innodb_dedicated_server 参数有什么作用？ ​

Q4: 如何在 MySQL 8.0 中启用 GTID 复制？ ​

Q5: 从 MySQL 5.6 升级到 8.0，是否需要先升级到 5.7？ ​

Q6: 如何处理 MySQL 8.0 中 NO_AUTO_CREATE_USER SQL 模式的移除？ ​

Q7: MySQL 8.0 中的二进制日志格式默认是 ROW，这会影响性能吗？ ​

Q8: 如何在不同 MySQL 版本之间迁移配置文件？ ​

MySQL 主版本配置变化

MySQL 5.6 到 5.7 的配置变化

核心参数变化

新增参数

废弃参数

MySQL 5.7 到 8.0 的配置变化

核心参数变化

新增参数

废弃参数

MySQL 8.0 各小版本间的配置变化

MySQL 8.0.11 变化

MySQL 8.0.16 变化

MySQL 8.0.23 变化

MySQL 8.0.30 变化

配置迁移最佳实践

预迁移准备

配置迁移步骤

配置文件示例

MySQL 5.7 配置文件

MySQL 8.0 配置文件

常见配置问题及解决方案

1. SQL模式不兼容

2. 认证插件不兼容

3. 查询缓存移除

4. 二进制日志格式变化

配置验证与测试

配置验证

性能测试

版本升级后的配置优化

1. 利用新特性优化

2. 调整性能相关参数

3. 监控与调优

常见问题（FAQ）

Q1: 从 MySQL 5.7 升级到 8.0 后，为什么查询变慢了？

Q2: 如何处理 MySQL 8.0 中移除的查询缓存功能？

Q3: MySQL 8.0 中的 `innodb_dedicated_server` 参数有什么作用？

Q4: 如何在 MySQL 8.0 中启用 GTID 复制？

Q5: 从 MySQL 5.6 升级到 8.0，是否需要先升级到 5.7？

Q6: 如何处理 MySQL 8.0 中 `NO_AUTO_CREATE_USER` SQL 模式的移除？

Q7: MySQL 8.0 中的二进制日志格式默认是 ROW，这会影响性能吗？

Q8: 如何在不同 MySQL 版本之间迁移配置文件？