11. MySQL 参数

EnigmaGcl
137 阅读9分钟

MySQL 相关的参数说明，很杂，可以简单过一遍，记不住没关系，有印象就行，后续如果有相关问题，可以当做手册来查询使用

  1. admin_adress + admin_port

    不受 max_connections 限制，当连接满了，可以通过 admin_address + admin_port 来连接，解决连接满后连接不上的问题， 搭配 grant service_connection_admin on *.* to 'root'@'localhost' with grant option 使用，且 admin_address 设置为 127.0.0.1，只允许 localhost 来连接，更安全，HA 不建议使用

  2. autocommit

    autocommit=0，事务会自动开启，并且只有显示 commit，事务才会提交，不建议设置为 0，容易忘记，变成长事务，建议手动 begin;……commit; 来处理事务

  3. MySQL 参数 scope

    MySQL 的参数有三种类型：

    • global：参数修改后全局生效，影响所有新建立的会话，已存在会话不受影响
    • global,session：修改 GLOBAL 后，仅新会话继承新值，已存在的会话仍使用原值。修改 SESSION 后，仅当前会话生效
    • session：参数仅影响当前会话，其他会话不受影响

  4. auto_increment_offset（初始值） 和 auto_increment_increment（步长）

    主要用于双主复制（单元化），计算逻辑是用自增计数器的值作为 id，自增计数器的值计算逻辑是在当前自增计数器值基础上+步长，如果遇到手动指定 id 的场景，会计算自增计数器的值和手动指定的 id 值大小关系，如果手动指定的 id 大，那么自增计数器值更新为比手动指定的 id 更大的，满足 offset+increment*n 的值。

    MySQL 8.0 之前，自增计数器值是不会持久化的，每次启动后查一下最大的 id 来确定，有问题，比如插入后删除，重启后有些 id 其实已经用过，导致重复主键风险，8.0 及后续版本会将自增计数器值持久化，启动后直接读取即可

  5. back_log

    当 MySQL 瞬时并发连接数超过当前线程处理能力时，新的连接请求不会立即被拒绝，而是进入一个等待队列。back_log 定义了该队列的最大长度，其核心作用是：

    • 缓冲突发流量：避免因瞬间高并发导致连接被直接拒绝（如大量客户端同时尝试连接）。
    • 平滑处理压力：为 MySQL 分配时间逐步处理排队中的连接请求，而非瞬时过载。

  6. big_tables

    当设置为 ON 时，MySQL 强制所有内部临时表直接存储在磁盘上（使用磁盘临时表），而非优先尝试使用内存临时表。 MySQL 中会使用临时表的操作主要有以下几类：

    • group by
    • distinct
    • order by
    • union/union all
    • 子查询
    • 多表 join

    这个参数一般建议设置为 OFF，不过在后台分析时可能会用，可以 session 单独设置

  7. binlog purge

    • binlog_expire_logs_auto_purge：控制是否自动清理过期 Binlog 文件。仅当 binlog_expire_logs_auto_purge=ON 时，binlog_expire_logs_seconds 的设置才会生效。
    • binlog_expire_logs_seconds：定义二进制日志文件的保留时间（单位：秒），超过该时间的 Binlog 文件会被自动删除。

  8. binlog_rows_query_log_events

    该参数如果开启，即使 binlog 设置为row格式，也会记录原 sql 语句(原文)

  9. binlog_row_metadata

    控制是否在 Binlog 中记录与行变更相关的元数据，包括：

    • 表结构信息（列名、数据类型、字符集等）。
    • 主键列的定义。
    • 其他表元数据（如 SIGNED 属性、ENUM/SET 的字符串映射）。

    使用命令 mysqlbinlog --print-table-metadata=true -vv binlog.000004 查询，注意，必须要用参数 --print-table-metadata=true，具体内容如下：

    image.png

    这个参数如果开启，可以看到 binlog 需要额外记录的数据会多很多，对性能影响较高，但是如果后面磁盘速度进一步提升，这点数据量记录对性能影响不大的话，建议开启，这会对 DTS 产生质的影响，以前 DTS 需要记录表结构信息，是有状态的服务，如果有这个功能后，DTS 完全可以设计为无状态服务

  10. genery_log

    建议关闭，因为性能比较差，每次写需要申请锁，锁比较大，导致并发性能不佳，最大会影响 70%～80%，建议使用 slow_log，或者使用审计日志，审计日志对写文件加锁做了优化，性能非常高，就算记录的数据比 genery_log 还大也没问题

  11. gtid_next

    session 级变量，用于手动指定下一个事务的全局事务标识符（GTID）  的会话级参数，主要在基于 GTID 的复制（GTID-based Replication）中使用。取值如下：

    • AUTOMATIC（默认值）：由 MySQL 自动生成 GTID，通常用于正常复制流程。
    • ANONYMOUS：禁用 GTID，事务以匿名方式记录（仅适用于非 GTID 复制模式）。
    • 'specific_gtid' ：手动指定一个 GTID（如 3E11FA47-71CA-11E1-9E33-C80AA9429562:100），需确保该 GTID未被使用。

  12. innodb_autoextend_increment

    当 InnoDB 表空间（如系统表空间 ibdata1 或独立表空间 *.ibd）需要扩展时，该参数定义每次自动扩展的增量大小。

    • 系统表空间（ibdata） ：
      当 innodb_data_file_path 配置为自动扩展（如 ibdata1:12M:autoextend）时，每次扩展的增量由 innodb_autoextend_increment 决定。
    • 独立表空间（File-Per-Table） ：
      若启用 innodb_file_per_table=ON，每个表的 .ibd 文件在需要扩展时，也会按此参数指定的步长增长。

  13. transaction_isolation

    事务隔离级别，建议设置为 RC，并发性能能提升非常多

  14. last_insert_id

    • 仅返回**当前会话（连接）**中最后一次插入操作生成的自增ID，不同会话之间的操作互不影响。
    • 2715314 仅记录由自增机制生成的 ID，手动指定值时不会更新。
    • 若一次性插入多行数据（如 INSERT INTO ... VALUES (...), (...)），返回第一条记录的自增ID。

  15. 慢查询相关参数

    • slow_query_log：默认 off，建议开启
    • min_examined_row_limit：默认为 0，不生效，含义是查询扫描行数少于该值，不记录
    • log_slow_admin_statement：默认关闭，建议开启，含义是控制是否将管理类语句（如 OPTIMIZE TABLE、ANALYZE TABLE、ALTER TABLE 等）记录慢查询，即使它们的执行时间未超过 long_query_time 阈值。

  16. log_slave_updates

    默认是 ON，建议开启，作用是控制从库（Slave）是否将接收到的更新操作记录到自身二进制日志（Binlog）  的关键参数，主要用于多级复制（Chain Replication）或级联复制（Cascading Replication）场景。，在主从复制情况建议开启，防止主从切换需要备份从库的 binlog，要求从库 binlog 是完整的，纯读库建议关闭

  17. log_slow_replica_statements

    控制从库（Replica）上复制线程（SQL 线程）执行的语句是否记录到慢查询日志的参数，主要用于监控从库复制过程中的慢查询。binlog 为 row 格式时，该参数无效

  18. lower_case_table_names

    总是建议设置为 1，控制表名和数据库名大小写敏感性的关键参数

  19. max_allowed_packet

    控制 客户端与服务器之间传输的数据包最大大小 的关键参数，直接影响 SQL 语句、结果集和二进制日志的传输能力。

    最大值为 1GB，建议修改为 1GB，有多大设置为多大，因为如果设置过小，小于一个 event，此时传输会出问题，因为 event 是 binlog 中一个基本单位，比如记录传输前后行数据信息，如果该参数设置过小，小于一个 event 大小，就会报错，主库执行大事务时，若生成的 Binlog Event 超过 max_allowed_packet，会立即抛出错误：

    ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

  20. max_binlog_size

    default = max = 1GB，binlog 文件大小，超过就切文件，一般会比配置值大些，比如已经 900MB 了，此时来个 binlog 300MB，MySQL 会继续记录，完成后立刻切文件

    切文件时，会将 redo 强制刷盘，所以 binlog 文件越小，切文件导致刷盘越频繁，所以不能太小，如果太大，在崩溃恢复时，恢复时间会变长，不过影响不大

  21. max_connect_errors

    控制 允许来自同一主机的连续失败连接次数 的参数，用于防范暴力破解攻击或异常连接导致的资源耗尽，当某个主机（IP）在连接 MySQL 时连续失败次数超过 max_connect_errors 的限制，MySQL 会暂时阻止该主机的后续连接请求。建议设置为 1000

  22. max_heap_table_size

    默认 16M，与 tmp_table_size（默认 16M）一起作用，tmp_table_size 是线程独享，max_heap_table_size 则是线程共享， 也就是说如果多个线程临时表内存大于 16M 后，后续线程只能使用磁盘临时表了，尽管没有达到 tmp_table_size 限制，建议设置大些，比如 500M、1GB 等

  23. max_user_connections

    控制 单个用户账户同时可建立的连接数上限 的参数，用于防止特定用户过度占用连接资源。建议设置为 0

  24. open_files_limit

    MySQL 进程能打开的文件句柄数，建议设置为 65535

  25. slave_preserve_commit_order

    从库是否按照主库事务提交顺序提交，建议设置为 on

  26. wait_timeout

    连接超时时间，默认 8 小时

  27. innodb_buffer_pool_instance

    内存大于 1GB 才允许配置，小于配置不生效

  28. innodb_extend_and_initialize

    默认值 ON，是 InnoDB 存储引擎中控制 表空间文件扩展时是否初始化新分配空间 的参数，直接影响文件扩展的性能与数据安全性。当 InnoDB 表空间（如 ibdata1 或独立表空间）需要扩展时，若启用此参数，新分配的磁盘空间会被显式初始化为零，避免残留旧数据。

  29. innodb_io_capacity

    8.0 默认 200,8.4 默认 10000，用于控制 InnoDB 后台任务（如脏页刷新、合并插入缓冲等操作）的 I/O 吞吐量。它直接影响数据库的写入性能和磁盘 I/O 资源的使用效率。

avatar
文章
阅读
粉丝