印刻数据库

外观

MySQL 配置参数参考 ​

基本配置参数 ​

字符集与排序规则 ​

character_set_server ​

  • 描述：服务器默认字符集
  • 默认值：
    • MySQL 5.6：latin1
    • MySQL 5.7：latin1
    • MySQL 8.0：utf8mb4
  • 建议值：utf8mb4
  • 使用场景：设置数据库服务器的默认字符集，支持emoji等特殊字符
  • 注意事项：确保与应用程序字符集一致

collation_server ​

  • 描述：服务器默认排序规则
  • 默认值：
    • MySQL 5.6：latin1_swedish_ci
    • MySQL 5.7：latin1_swedish_ci
    • MySQL 8.0：utf8mb4_0900_ai_ci
  • 建议值：utf8mb4_unicode_ci 或 utf8mb4_general_ci
  • 使用场景：设置字符比较和排序的规则
  • 注意事项：utf8mb4_unicode_ci 更准确但性能稍低，utf8mb4_general_ci 性能更好但准确性稍低

character_set_client ​

  • 描述：客户端连接的字符集
  • 默认值：utf8mb4 (MySQL 8.0)
  • 建议值：与character_set_server一致
  • 使用场景：确保客户端发送的数据使用正确的字符集

character_set_results ​

  • 描述：服务器返回结果的字符集
  • 默认值：utf8mb4 (MySQL 8.0)
  • 建议值：与character_set_server一致
  • 使用场景：确保服务器返回的数据使用正确的字符集

时区设置 ​

default_time_zone ​

  • 描述：服务器默认时区
  • 默认值：SYSTEM
  • 建议值：+08:00 或 UTC
  • 使用场景：统一数据库和应用程序的时区
  • 注意事项：使用UTC便于跨时区管理

连接配置参数 ​

连接数控制 ​

max_connections ​

  • 描述：允许的最大并发连接数
  • 默认值：
    • MySQL 5.6：151
    • MySQL 5.7：151
    • MySQL 8.0：151
  • 建议值：根据服务器资源和业务需求调整，一般设置为500-2000
  • 使用场景：控制数据库服务器的最大连接数，防止资源耗尽
  • 注意事项：设置过高可能导致内存不足，设置过低可能导致连接失败

max_user_connections ​

  • 描述：每个用户允许的最大连接数
  • 默认值：0（无限制）
  • 建议值：根据业务需求设置，如100
  • 使用场景：限制单个用户的连接数，防止单个用户占用过多资源

back_log ​

  • 描述：MySQL监听队列的最大长度
  • 默认值：
    • MySQL 5.6：50
    • MySQL 5.7：50
    • MySQL 8.0：50
  • 建议值：128-512
  • 使用场景：当连接请求超过max_connections时，请求会进入监听队列

连接超时 ​

wait_timeout ​

  • 描述：非交互式连接的超时时间（秒）
  • 默认值：28800（8小时）
  • 建议值：300-7200
  • 使用场景：释放长时间空闲的连接
  • 注意事项：设置过短可能导致频繁的连接重建，设置过长可能导致连接泄漏

interactive_timeout ​

  • 描述：交互式连接的超时时间（秒）
  • 默认值：28800（8小时）
  • 建议值：300-7200
  • 使用场景：释放长时间空闲的交互式连接

connect_timeout ​

  • 描述：连接建立的超时时间（秒）
  • 默认值：10
  • 建议值：5-30
  • 使用场景：控制连接建立的最大时间

InnoDB配置参数 ​

缓冲池配置 ​

innodb_buffer_pool_size ​

  • 描述：InnoDB缓冲池大小
  • 默认值：
    • MySQL 5.6：128MB
    • MySQL 5.7：128MB
    • MySQL 8.0：128MB
  • 建议值：占系统内存的50-70%
  • 使用场景：缓存InnoDB数据和索引，减少磁盘I/O
  • 注意事项：设置过大可能导致系统内存不足，设置过小可能导致频繁的磁盘I/O

innodb_buffer_pool_instances ​

  • 描述：缓冲池实例数量
  • 默认值：
    • MySQL 5.6：1
    • MySQL 5.7：1
    • MySQL 8.0：8
  • 建议值：根据CPU核心数调整，每个实例至少1GB
  • 使用场景：减少缓冲池的锁竞争，提高并发性能

innodb_buffer_pool_dump_at_shutdown ​

  • 描述：关闭时是否保存缓冲池状态
  • 默认值：ON (MySQL 5.7+)
  • 建议值：ON
  • 使用场景：加速数据库重启后的缓冲池预热

innodb_buffer_pool_load_at_startup ​

  • 描述：启动时是否加载缓冲池状态
  • 默认值：ON (MySQL 5.7+)
  • 建议值：ON
  • 使用场景：加速数据库重启后的缓冲池预热

日志配置 ​

innodb_log_file_size ​

  • 描述：每个InnoDB重做日志文件的大小
  • 默认值：
    • MySQL 5.6：48MB
    • MySQL 5.7：48MB
    • MySQL 8.0：512MB
  • 建议值：256MB-2GB
  • 使用场景：影响InnoDB的写入性能和恢复时间
  • 注意事项：设置过大可能导致恢复时间过长，设置过小可能导致频繁的日志切换

innodb_log_files_in_group ​

  • 描述：InnoDB重做日志文件的数量
  • 默认值：2
  • 建议值：2-4
  • 使用场景：多个日志文件循环使用，提高写入性能

innodb_flush_log_at_trx_commit ​

  • 描述：控制事务提交时日志的刷新策略
  • 默认值：1
  • 可选值：
    • 0：每秒刷新一次日志，可能丢失1秒的数据
    • 1：每次事务提交都刷新日志，最安全
    • 2：每次事务提交都写入日志，但每秒刷新一次
  • 建议值：
    • 生产环境：1（最安全）
    • 性能优先：2（平衡安全和性能）
  • 使用场景：权衡数据安全性和写入性能

innodb_flush_method ​

  • 描述：InnoDB数据文件和日志文件的刷新方法
  • 默认值：
    • Linux：fsync
    • Windows：unbuffered
  • 建议值：O_DIRECT (Linux)
  • 使用场景：控制InnoDB如何向磁盘写入数据和日志
  • 注意事项：O_DIRECT可以减少操作系统缓存，提高写入性能

并发配置 ​

innodb_thread_concurrency ​

  • 描述：限制InnoDB使用的并发线程数
  • 默认值：
    • MySQL 5.6：0
    • MySQL 5.7：0
    • MySQL 8.0：0
  • 建议值：0（自动调整）
  • 使用场景：防止InnoDB线程过多导致CPU资源耗尽
  • 注意事项：0表示不限制，由InnoDB自动调整

innodb_concurrency_tickets ​

  • 描述：线程进入InnoDB内核的次数
  • 默认值：5000
  • 建议值：5000-10000
  • 使用场景：控制线程在InnoDB内核中的执行时间

innodb_adaptive_hash_index ​

  • 描述：是否启用自适应哈希索引
  • 默认值：ON
  • 建议值：ON
  • 使用场景：提高等值查询的性能
  • 注意事项：对于OLAP工作负载，可能需要关闭

innodb_lock_wait_timeout ​

  • 描述：InnoDB行锁等待超时时间（秒）
  • 默认值：50
  • 建议值：根据业务需求调整，一般设置为30-120
  • 使用场景：防止长时间的锁等待，避免死锁

文件配置 ​

innodb_file_per_table ​

  • 描述：是否为每个表使用独立的表空间文件
  • 默认值：
    • MySQL 5.6：OFF
    • MySQL 5.7：ON
    • MySQL 8.0：ON
  • 建议值：ON
  • 使用场景：便于表的管理和优化，如TRUNCATE、DROP等操作
  • 注意事项：关闭时所有表共享一个表空间文件

innodb_data_file_path ​

  • 描述：InnoDB共享表空间文件的配置
  • 默认值：ibdata1:12M:autoextend
  • 建议值：根据数据量调整，如ibdata1:1G:autoextend
  • 使用场景：配置共享表空间文件的大小和增长方式

innodb_temp_data_file_path ​

  • 描述：InnoDB临时表空间文件的配置
  • 默认值：ibtmp1:12M:autoextend:max:500M
  • 建议值：根据临时表需求调整
  • 使用场景：配置临时表空间文件的大小和增长方式

性能配置参数 ​

查询优化 ​

query_cache_type ​

  • 描述：查询缓存类型
  • 默认值：
    • MySQL 5.6：ON
    • MySQL 5.7：ON
    • MySQL 8.0：已移除
  • 可选值：
    • 0：关闭查询缓存
    • 1：开启查询缓存，除了SELECT SQL_NO_CACHE
    • 2：按需开启查询缓存，只有SELECT SQL_CACHE
  • 建议值：0（关闭）
  • 使用场景：缓存查询结果，提高查询性能
  • 注意事项：查询缓存在高并发场景下可能成为瓶颈，MySQL 8.0已移除

query_cache_size ​

  • 描述：查询缓存大小
  • 默认值：
    • MySQL 5.6：16MB
    • MySQL 5.7：16MB
    • MySQL 8.0：已移除
  • 建议值：0（关闭查询缓存时）
  • 使用场景：设置查询缓存的大小

排序与临时表 ​

sort_buffer_size ​

  • 描述：每个会话的排序缓冲区大小
  • 默认值：
    • MySQL 5.6：2MB
    • MySQL 5.7：2MB
    • MySQL 8.0：2MB
  • 建议值：4MB-8MB
  • 使用场景：用于ORDER BY、GROUP BY等排序操作
  • 注意事项：每个会话都会分配，设置过大会导致内存不足

read_buffer_size ​

  • 描述：每个会话的顺序读取缓冲区大小
  • 默认值：
    • MySQL 5.6：128KB
    • MySQL 5.7：128KB
    • MySQL 8.0：128KB
  • 建议值：256KB-1MB
  • 使用场景：用于全表扫描的顺序读取

read_rnd_buffer_size ​

  • 描述：每个会话的随机读取缓冲区大小
  • 默认值：
    • MySQL 5.6：256KB
    • MySQL 5.7：256KB
    • MySQL 8.0：256KB
  • 建议值：512KB-2MB
  • 使用场景：用于ORDER BY后的随机读取

tmp_table_size ​

  • 描述：内存临时表的最大大小
  • 默认值：
    • MySQL 5.6：16MB
    • MySQL 5.7：16MB
    • MySQL 8.0：16MB
  • 建议值：64MB-256MB
  • 使用场景：控制内存临时表的大小，超过则使用磁盘临时表

max_heap_table_size ​

  • 描述：MEMORY存储引擎表的最大大小
  • 默认值：
    • MySQL 5.6：16MB
    • MySQL 5.7：16MB
    • MySQL 8.0：16MB
  • 建议值：与tmp_table_size一致
  • 使用场景：控制MEMORY表的最大大小

安全配置参数 ​

密码策略 ​

validate_password_policy ​

  • 描述：密码验证策略
  • 默认值：
    • MySQL 5.6：MEDIUM
    • MySQL 5.7：MEDIUM
    • MySQL 8.0：MEDIUM
  • 可选值：
    • 0：LOW，只检查长度
    • 1：MEDIUM，检查长度、数字、大小写、特殊字符
    • 2：STRONG，检查长度、数字、大小写、特殊字符、字典文件
  • 建议值：MEDIUM 或 STRONG
  • 使用场景：强制用户使用强密码

validate_password_length ​

  • 描述：密码最小长度
  • 默认值：8
  • 建议值：12-16
  • 使用场景：设置密码的最小长度

访问控制 ​

skip_name_resolve ​

  • 描述：是否跳过主机名解析
  • 默认值：OFF
  • 建议值：ON
  • 使用场景：加快连接建立速度，防止DNS解析问题导致连接延迟
  • 注意事项：启用后，授权表中的host字段必须使用IP地址

bind_address ​

  • 描述：MySQL绑定的IP地址
  • 默认值：
    • MySQL 5.6：*（所有地址）
    • MySQL 5.7：*（所有地址）
    • MySQL 8.0：*（所有地址）
  • 建议值：具体的IP地址，如0.0.0.0（允许所有地址）或特定IP
  • 使用场景：限制MySQL监听的IP地址，提高安全性

max_connect_errors ​

  • 描述：允许的最大连接错误次数
  • 默认值：100
  • 建议值：1000
  • 使用场景：防止暴力破解攻击，超过次数后禁止该IP连接

日志配置参数 ​

慢查询日志 ​

slow_query_log ​

  • 描述：是否启用慢查询日志
  • 默认值：OFF
  • 建议值：ON
  • 使用场景：记录执行时间超过long_query_time的查询

slow_query_log_file ​

  • 描述：慢查询日志文件路径
  • 默认值：hostname-slow.log
  • 建议值：根据系统配置调整，如/var/log/mysql/slow.log

long_query_time ​

  • 描述：慢查询的阈值（秒）
  • 默认值：10
  • 建议值：0.5-2
  • 使用场景：定义慢查询的时间阈值

log_queries_not_using_indexes ​

  • 描述：是否记录未使用索引的查询
  • 默认值：OFF
  • 建议值：OFF（生产环境）
  • 使用场景：记录未使用索引的查询，便于优化
  • 注意事项：在生产环境可能产生大量日志

二进制日志 ​

log_bin ​

  • 描述：是否启用二进制日志
  • 默认值：
    • MySQL 5.6：OFF
    • MySQL 5.7：OFF
    • MySQL 8.0：ON
  • 建议值：ON
  • 使用场景：用于主从复制和时间点恢复

log_bin_basename ​

  • 描述：二进制日志文件的基本名称
  • 默认值：datadir/binlog
  • 建议值：根据系统配置调整

binlog_format ​

  • 描述：二进制日志格式
  • 默认值：
    • MySQL 5.6：STATEMENT
    • MySQL 5.7：ROW
    • MySQL 8.0：ROW
  • 可选值：
    • STATEMENT：记录SQL语句
    • ROW：记录行级变更
    • MIXED：混合模式
  • 建议值：ROW
  • 使用场景：控制二进制日志的格式，影响复制的安全性和性能
  • 注意事项：ROW格式更安全，但日志体积更大

expire_logs_days ​

  • 描述：二进制日志的过期时间（天）
  • 默认值：
    • MySQL 5.6：0（永不过期）
    • MySQL 5.7：0（永不过期）
    • MySQL 8.0：30
  • 建议值：7-30
  • 使用场景：自动清理过期的二进制日志，节省磁盘空间

错误日志 ​

log_error ​

  • 描述：错误日志文件路径
  • 默认值：
    • MySQL 5.6：hostname.err
    • MySQL 5.7：hostname.err
    • MySQL 8.0：hostname.err
  • 建议值：根据系统配置调整，如/var/log/mysql/error.log

log_error_verbosity ​

  • 描述：错误日志的详细程度
  • 默认值：3
  • 可选值：
    • 1：只记录错误信息
    • 2：记录错误和警告信息
    • 3：记录错误、警告和通知信息
  • 建议值：2-3
  • 使用场景：控制错误日志的详细程度

复制配置参数 ​

主库配置 ​

server_id ​

  • 描述：服务器唯一ID
  • 默认值：1
  • 建议值：每个服务器使用唯一的ID
  • 使用场景：用于主从复制，标识不同的服务器

gtid_mode ​

  • 描述：是否启用GTID复制
  • 默认值：
    • MySQL 5.6：OFF
    • MySQL 5.7：OFF
    • MySQL 8.0：ON
  • 建议值：ON
  • 使用场景：简化主从复制的配置和管理

enforce_gtid_consistency ​

  • 描述：是否强制GTID一致性
  • 默认值：
    • MySQL 5.6：OFF
    • MySQL 5.7：OFF
    • MySQL 8.0：ON
  • 建议值：ON（当gtid_mode=ON时）
  • 使用场景：确保事务符合GTID一致性要求

从库配置 ​

slave_parallel_type ​

  • 描述：从库并行复制的类型
  • 默认值：
    • MySQL 5.6：DATABASE
    • MySQL 5.7：DATABASE
    • MySQL 8.0：LOGICAL_CLOCK
  • 可选值：
    • DATABASE：按数据库并行
    • LOGICAL_CLOCK：按逻辑时钟并行
  • 建议值：LOGICAL_CLOCK
  • 使用场景：提高从库的复制性能

slave_parallel_workers ​

  • 描述：从库并行复制的工作线程数
  • 默认值：
    • MySQL 5.6：0
    • MySQL 5.7：0
    • MySQL 8.0：4
  • 建议值：根据CPU核心数调整，一般为CPU核心数的一半
  • 使用场景：控制从库并行复制的线程数

slave_preserve_commit_order ​

  • 描述：是否保持事务提交顺序
  • 默认值：OFF
  • 建议值：ON（当slave_parallel_workers>0时）
  • 使用场景：确保从库上的事务提交顺序与主库一致

MySQL 8.0新特性配置 ​

持久化配置 ​

persist_only ​

  • 描述：是否只持久化配置，不立即生效
  • 默认值：无
  • 使用场景：MySQL 8.0的SET PERSIST_ONLY命令，用于持久化配置但不立即生效

persist_directory ​

  • 描述：持久化配置文件的目录
  • 默认值：datadir
  • 使用场景：MySQL 8.0用于存储持久化配置的目录

资源管理 ​

resource_group_enabled ​

  • 描述：是否启用资源组
  • 默认值：OFF
  • 建议值：ON（如果需要资源管理）
  • 使用场景：MySQL 8.0用于管理CPU资源的分配

安全增强 ​

caching_sha2_password_private_key_path ​

  • 描述：caching_sha2_password插件的私钥路径
  • 默认值：无
  • 使用场景：MySQL 8.0用于caching_sha2_password认证插件的私钥

caching_sha2_password_public_key_path ​

  • 描述：caching_sha2_password插件的公钥路径
  • 默认值：无
  • 使用场景：MySQL 8.0用于caching_sha2_password认证插件的公钥

配置文件示例 ​

基础配置文件（my.cnf） ​

ini
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
# 基本配置
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
user = mysql

# 字符集配置
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

# 连接配置
max_connections = 1000
max_user_connections = 100
wait_timeout = 300
interactive_timeout = 300
skip_name_resolve = ON
bind_address = 0.0.0.0

# InnoDB配置
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_files_in_group = 4
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_thread_concurrency = 0

# 性能配置
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

# 日志配置
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = OFF
log_bin = ON
log_bin_basename = /var/lib/mysql/binlog
binlog_format = ROW
expire_logs_days = 7
log_error = /var/log/mysql/error.log
log_error_verbosity = 2

# 复制配置
server_id = 1
log_slave_updates = ON
gtid_mode = ON
enforce_gtid_consistency = ON

[mysqld_safe]
log-error = /var/log/mysql/error.log
pid-file = /var/run/mysqld/mysqld.pid

配置优化建议 ​

优化原则 ​

  1. 根据硬件资源调整：配置应根据服务器的CPU、内存、磁盘等资源进行调整
  2. 根据工作负载调整：OLTP和OLAP工作负载的配置需求不同
  3. 逐步调整：不要一次性修改过多配置，应逐步调整并观察效果
  4. 监控效果：修改配置后，应监控数据库性能，评估优化效果
  5. 备份配置：修改配置前，应备份原始配置文件，便于回滚

常见优化场景 ​

OLTP工作负载优化 ​

  • 增加innodb_buffer_pool_size，提高缓存命中率
  • 配置合适的innodb_log_file_size，提高写入性能
  • 启用GTID复制，简化复制管理
  • 开启慢查询日志，便于优化查询

OLAP工作负载优化 ​

  • 关闭innodb_adaptive_hash_index，减少CPU开销
  • 增加sort_buffer_size和tmp_table_size，提高排序和分组性能
  • 考虑使用列存储引擎，如InfiniDB或ClickHouse
  • 优化查询，使用分区表等技术

高并发场景优化 ​

  • 增加innodb_buffer_pool_instances，减少锁竞争
  • 配置合适的max_connections，避免连接数过高
  • 启用skip_name_resolve，加快连接建立速度
  • 考虑使用读写分离，分担主库压力

总结 ​

MySQL配置参数是影响数据库性能、安全性和可靠性的重要因素。本文档总结了MySQL在基本配置、连接配置、InnoDB配置、性能配置、安全配置、日志配置、复制配置等方面的常用参数，涵盖了不同MySQL版本的默认值和建议值。

在实际使用中，应根据具体的业务场景、硬件资源和MySQL版本选择合适的配置参数，并持续监控和调整，以获得最佳的性能和可靠性。同时，建议定期备份配置文件，便于在配置错误时进行回滚。