工作原理

类 UNIX 系统中,配置文件位置目录:

1
2
3
4
5
/etc/my.cnf
/etc/mysql/my.cnf
# 如果不知道当前的配置文件位置,可以执行以下指令
which mysqld # 查找 mysqld 位置
/usr/bin/mysqld --verbose --help | grep -A 1 'Default options'

最终,服务器会读取配置文件内容,去除空行和注释行,如果手动启动 mysql,会将命令行选项和配置文件内容一起处理。所以,对于长期使用的设置应该写入到全局配置文件,而不是命令行指定。

配置项建议 小写+下划线 的固定格式,即使下划线和中横线是等价的。

作用域

全局作用域:服务器级别。
会话作用域:每个数据库连接。

1
2
3
# 可以使用 show variables 查看
set autocommit = off; # 设置 session 变量,退出当前 session,该配置失效。
set global autocommit = on; # 设置全局变量,退出当前 session,该配置仍然有效。但是对于该配置修改前的连接请求无效。

SET 命令不支持后缀单位,如 1M ;但是支持表达式,如 1024 * 1024。生产环境中,应该 谨慎使用动态设置变量,因为这可能会指导数据库做大量工作。

对于一些连接级别的设置,不要轻易增加全局级别的值。有一些缓存会一次性分配指定大小的全部内存,而不管实际上是否需要这么大。更好的方法是,给这个连接指定连接级别的配置。

对于服务器的配置文件,最好添加版本管理,这样会很方便的返回之前的版本。

具体配置

通用

  • datadir 数据存放目录
  • socket
  • pid_file
    MySQL 启动,进程 id 写入的文件位置。
  • user
  • port
    端口号
  • default_storage_engine
    默认存储引擎,默认 InnoDB。

InnoDB

  • innodb_buffer_pool_size = [134217728/128M]
    InnoDB 缓冲池大小。InnoDB 严重依赖于缓冲池,通常设置的在服务器的最大内存 75% 以内越大越好。但是,缓冲池越大,在关闭或打开的时候,需要将赃页写入数据文件,越耗时。
  • innodb_buffer_pool_instances = [1]
    该参数为 innodb_buffer_pool_size 的辅助参数。当 ibps 设置较大时,开启多个内存缓冲池实例,可以提高并行的内存读写。
  • innodb_log_buffer_size = [16777216/16M]
    事务提交前,不会直接将改变的日志写出磁盘,而是先记入缓存。适当增加这个值,以节省磁盘 I/O 。
  • innodb_log_file_size = [50331648/48M]
    InnoDB 事物日志的大小,方便用户对 MySQL 的恢复。
  • innodb_flush_log_at_trx_commit
    存储引擎事务提交时刷新日志的方式。这个参数涉及到事务从缓存写入到磁盘的方式。0:定时每秒一次 buffer 写入 file , 并且 flush 到磁盘。1:每次事务提交时 buffer 写入 file , 并且 flush 到磁盘。2:每次提交事务 buffer 写入 file,MySQL 定时每秒一次 flush 磁盘。如果项目中,应该没有频率较快的事务提交,因此默认值 1 种方式是非常适合。
  • innodb_file_per_table
    在重建或修改表时,innodb 的表数据会从共享表空间移动到独立表空间(.ibd)。这样可能会损失少许的查询效率,增加文件数量,但是优势是明显的。可以避免产生巨大的表空间,表删除可以释放表空间,同时在备份的时候也更有效率。因此采用默认值就可以。

MyISAM

  • key_buffer_size = [8388608/8M]
    默认情况下,MyISAM 将所有索引都缓存在默认键缓存中。该配置指定索引缓冲的大小,它决定索引处理的速度,尤其是索引读的速度。可以通过检查状态值 key_read_request 和 key_reads 查看其设置是否合理。该配置只对 MyISAM 有效

Logging

  • log_error = [/var/log/mysqld.log]
    错误日志目录位置,可以设置为单独错误文件:/var/lib/mysql/mysql-error.log

Other

  • tmp_table_size = [16777216/16M]

  • max_heap_table_size = [16777216/16M]
    这两个设置用于控制 Memory 引擎的内存临时表能使用的最大内存。如果隐身内存临时表(in-memory)的大小超过了这两个值,就会转换为磁盘 MyISAM 表(on-disk),这会导致严重的性能瓶颈。但不能设置过大,防止内存溢出。建议两个设置的大小相同。

    临时表 可以通过 CREATE TEMPORARY TABLE 创建。
    隐式临时表 MySQL 某些 SQL 在执行时会在内存(磁盘)中创建临时表,以优化 SQL 的执行。以下情况会生产隐式内存临时表:

    1. 使用了不同的 ORDER BY 和 GROUP BY 条件
    2. 同时使用了DISTINCT和 ORDER BY

    下面这些情况不会创建内存临时表,而使用磁盘临时表。

    1. 数据表中含有BLOB类型或TEXT类型字段列;
    2. 在GROUP BY或DISTINCT任何条件中含有超过512字节的列;
    3. 如果使用了UNION或UNION ALL,而且SELECT列中含有任何超过512字节的列
    4. 如果in-memory临时表变得太大,超过tmp_table_size或max_heap_table_size数值时;

    优化 Blob 和 Text 的场景
    服务器不能在内存临时表中存储 Blob 值。所以查询涉及 Blob 值,又需要使用临时表,不管 Blob 有多小,都会立即在磁盘上创建临> 时表,效率很低。有两种方法可以对此进行优化:

    1. 使用 substring() 函数把值转换为 varchar
    2. 让临时表更快一些。
  • query_cache_size = [1048576/1M]
    MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存,会立刻返回结果,跳过了解析,优化和执行阶段。可以通过 show status like '%Qcache%'; 查看查询缓存的命中情况。

  • query_cache_type = [OFF]
    查询缓存默认关闭,设置为 ON 打开。

  • max-connections = [151]
    服务器最大连接数,通常设置为现有连接数的 2 倍,以便应对短暂的连接高峰。可以通过观察 Max_used_connections 状态变量随着时间的变化来检测连接状态。

  • thread_cache_size = [9]
    观察 Threads_connected 状态变量并且找出它的最大最小值,设置超过最大值 2 倍。也可以查看 thread_cache_size 状态变量,如果这个变量一直增长,则可能要调大 thread_cache_size。对于大多数项目,这个默认值是偏小的。

  • open_files_limit = [5000]
    MySQL 可以打开文件描述符的最大数量。太小 MySQL 会报不能打开文件的错误,过大则应注意避免内存溢出的问题。

  • expire_logs_days = [0/不删除]
    如果设置了二进制日志,应该打开这个选项,可以让服务器在指定的天数之后清理旧的二进制日志。如果不启用,二进制日志会占用大量的磁盘空间。

  • max_allowed_packet = [4194304/4M]
    运行服务器能发送最大包的大小,默认 4M,如果设置得太小,复制时可能会出问题,表现为备库不能接受主库发送过来的复制数据。建议增加到 16M 或更大。

  • max_connect_errors = [100]
    短暂时间内不断尝试连接服务器,客户端会被列入黑名单,直到再次刷新主机缓存。这个默认值太小,可以增加这个值。

  • skip_name_resolve = [OFF]
    是否允许登录的时候使用 DNS 查找。建议设置为 ON,禁用 DNS 查找,改用 IP 地址访问。

复制相关

  • read_only = [OFF]
    这个设置禁止没有特权的用户对数据做变更。这非常使用于主从时对备库的设置,规范备库把主库主库作为数据的唯一来源。从库建议设置为 ON,但是也要提防从库提升后导致数据不能写入的问题。

  • skip_slave_start
    这个设置会阻止 MySQL 试图自动启动复制,这在服务器崩溃或某些情况下,启动复制是不安全的,所以要禁止,等到服务器排查完毕,手动复制。

  • slave_net_timeout = [60/分钟]
    备库连接主库失败,重新连接的等待时间。默认 60 分钟太长,建议改成 1 分钟。

  • sync_binlog = [1]

  • sync_master_info= [1]

  • sync_relay_log = [10000]

  • sync_relay_log_info= [10000]
    这几个配置表示每隔多少事件将复制状态同步到磁盘,这些配置使得主从复制崩溃的时候,更容易回复。但是频繁的磁盘操作 fsync() 来了严重的性能问题,会导致复制延迟。建议如果开启,适当增加间隔数。