MySQL 中 my.cnf 常用配置

1. 常用配置

[mysqld]

port = 3306 #默认
pid-file =  /data/mysql/mysql.pid
basedir =  /usr/local/mysql/ #程序安装目录
symbolic-link = 0  #多客户访问同一数据库，该选项默认开启
tmpdir =  /usr/local/mysql/tmp/ #此目录被 MySQL用来保存临时文件
open_files_limit = 65535 #打开时，和max_connections对比，取大数
datadir = /var/lib/mysql  #数据库目录
log-error = /var/lib/mysql/error.log
slow_query_log=on   #开启慢查询日志相关
long_query_time=2   #默认10秒
slow_query_log_file = /var/log/mysql/slow_query.log  #慢查询日志路径
log-queries-not-using-indexes = 1   #记录没有使用索引的sql

socket=/var/lib/mysql/mysql.sock  #该条配置需在[client]段同时配置
default_storage_engine=InnoDB
innodb_file_per_table = on     #InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间
innodb_buffer_pool_size=4G     #生产中要改，建议为操作系统内存的70%-80%，需重启服务生效
skip_name_resolve = on         #忽略主机名解析，提高访问速度（注意配置文件中使用主机名将不能解析）
lower_case_table_names = 1     #忽略表单大小写
character-set-server=utf8mb4   #设定默认字符为utf8mb4

使用innodb注意事项
- 所有InnoDB数据表都创建一个和业务无关的自增数字型作为主键，对保证性能很有帮助
- 杜绝使用text/blob，确实需要使用的，尽可能拆分出去成一个独立的表
- 时间戳建议使用 TIMESTAMP 类型存储
- IPV4 地址建议用 INT UNSIGNED 类型存储
- 性别等非是即非的逻辑，建议采用 TINYINT 存储，而不是 CHAR(1)；例可以使用0,1,2来表示，未知,男,女，优点搜索快，缺点显示/存储都要转换
- 存储较长文本内容时，建议采用JSON/BSON格式存储；

查询缓存相关

query_cache_type=1  #0表示禁用缓存，1表示会缓存所有的结果，2表示只缓存SQL_CACHE缓存
query_cache_limit = 2M
query_cache_size = 64M 或32M 或128M

系统资源相关

back_log = 500  #如果系统在一个短时间内有很多连接，则需要增大该参数的值小于512
max_connections = 1000 #默认100,生产则需要增大该参数值,最大连接数16384

二进制日志相关

server_id=1                    
log_bin=mysql-bin
#log-bin-index=master-bin.index
expire_logs_days = 7
#binlog_format = row    #默认为mix，新版中设为这两项可提高安全性
#binlog_row_image = minimal
max_binlog_size = 100m   #默认是1G
binlog_cache_size = 4m
#binlog-do-db = DBNAME #指定mysql的binlog日志只记录哪个库
max_binlog_cache_size = 512m  #生产4g
#skip-slave-start

2. 不常用配置

mysqld

[mysqld]

slave-load-tmpdir =  /usr/local/mysql/tmp/   
#当 slave 执行 load data infile 时用skip-external-locking
#不使用系统锁定，要使用 myisamchk,必须关闭服务器 ,避免 MySQL的外部锁定，减少出错几率增强稳定性。

skip-networking 
#开启该选项可以彻底关闭 MySQL 的 TCP/IP 连接方式，
#如果 WEB 服务器是以远程连接的方式访问 MySQL 数据库服务器则不要开启该选项！否则将无法正常连接！
#如果所有的进程都是在同一台服务器连接到本地的 mysqld, 这样设置将是增强安全的方法。

sysdate-is-now = 1 
#把SYSDATE 函数编程为 NOW的别名

default-time-zone = system
#服务器时区，或者'+08:00'

default_table_type = InnoDB
#默认表类型

default-storage-engine = InnoDB
#默认存储引擎

系统资源相关

max_connect_errors = 10000  
#如果某个用户发起的连接 error 超过该数值，则该用户的下次连接将被阻塞，直到管理员执行 flush hosts命令或者服务重启， 
#防止非法的密码以及其他在链接时的错误会增加此值

connect-timeout = 10 
#连接超时之前的最大秒数,在 Linux 平台上，该超时也用作等待服务器首次回应的时间

wait-timeout = 28800 #等待关闭连接的时间

interactive-timeout = 28800 
#关闭连接之前，允许 interactive_timeout（取代了wait_timeout）秒的不活动时间。
#客户端的会话 wait_timeout 变量被设为会话interactive_timeout 变量的值。
#如果前端程序采用短连接，建议缩短这2个值, 如果前端程序采用长连接，可直接注释掉这两个选项，
#默认配置(8小时)  

slave-net-timeout = 600 
#从服务器也能够处理网络连接中断。但是，只有从服务器超过slave_net_timeout 秒没有从主服务器收到数据才通知网络中断

net_read_timeout = 30      #从服务器读取信息的超时

net_write_timeout = 60     #从服务器写入信息的超时

net_retry_count = 10       #如果某个通信端口的读操作中断了，在放弃前重试多次。

net_buffer_length = 16384  
#包消息缓冲区初始化为 net_buffer_length 字节，但需要时可以增长到 max_allowed_packet 字节

max_allowed_packet = 64M
#服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小
#当与大的BLOB 字段一起工作时相当必要， 每个连接独立的大小.大小动态增加。
#设置最大包,限制server接受的数据包大小，避免超长SQL的执行有问题 默认值为16M，
#当MySQL客户端或mysqld服务器收到大于 max_allowed_packet 字节的信息包时，将发出“信息包过大”错误，并关闭连接。
#对于某些客户端，如果通信信息包过大，在执行查询期间，可能会遇到“丢失与 MySQL 服务器的连接”错误。默认值 16M。

table_cache = 512 
# 所有线程所打开表的数量. 增加此值就增加了mysqld所需要的文件描述符的数量这样你需要确认在
# [mysqld_safe]中 “open-files-limit” 变量设置打开文件数量允许至少4096

thread_stack = 192K 
# 线程使用的堆大小. 此容量的内存在每次连接时被预留.
# MySQL本身常不会需要超过 64K 的内存如果你使用你自己的需要大量堆的 UDF 函数
# 或者你的操作系统对于某些操作需要更多的堆,你也许需要将其设置的更高一点.默认设置足以满足大多数应用

thread_cache_size = 20 
#在 cache 中保留多少线程用于重用.当一个客户端断开连接后,
#如果 cache 中的线程还少于 thread_cache_size,则客户端线程被放入 cache 中.
#这可以在你需要大量新连接的时候极大的减少线程创建的开销(一般来说如果你有好的线程模型的话,这不会有明显的性能提升.)

thread_concurrency = 8  
#允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.该参数取值为服务器逻辑CPU数量×2

query_cache_min_res_unit = 2K 
#查询缓存分配的最小块大小.默认是 4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，
#就容易造成内存碎片和浪费查询缓存碎片率=Qcache_free_blocks/Qcache_total_blocks*100%
#如果查询缓存碎片率超过 20%，可以用 FLUSH QUERY CACHE 整理缓存碎片，
#或者试试减小query_cache_min_res_unit，
#如果你的查询都是小数据量的话。查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size *100%
#查询缓存利用率在25%以下的话说明 query_cache_size 设置的过大，可适当减小;
#查询缓存利用率在 80%以上而且 Qcache_lowmem_prunes > 50 的话
#说明 query_cache_size 可能有点小，要不就是碎片太多。
#查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

tmp_table_size = 512M 
#临时表的最大大小，如果超过该值，则结果放到磁盘中,此限制是针对单个表的,而不是总和

max_heap_table_size = 512M 
#独立的内存表所允许的最大容量.此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源

INNODB 相关选项

skip-innodb 
#如果你的MySQL服务包含 InnoDB 支持但是并不打算使用的话,
#使用此选项会节省内存以及磁盘空间,并且加速某些部分

innodb_status_file = 1 
#启用InnoDB的status file，便于管理员查看以及监控等 show engine innodb status\G

innodb_open_files = 2048 
#限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300

innodb_additional_mem_pool_size = 100M 
#设置InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，
#所以当我们一个MySQL Instance中的数据库对象非常多的时候，
#是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

innodb_write_io_threads = 4
innodb_read_io_threads = 4
#innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
#注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，
#修改完后重启MySQL服务,允许值的范围从 1-64

innodb_data_home_dir =  /usr/local/mysql/var/ 
#设置此选项如果你希望 InnoDB 表空间文件被保存在其他分区.默认保存在 MySQL 的 datadir 中.

innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend
#InnoDB将数据保存在一个或者多个数据文件中成为表空间.如果你只有单个逻辑驱动保存你的数据,
#一个单个的自增文件就足够好了.其他情况下.
#每个设备一个文件一般都是个好的选择.你也可以配置 InnoDB 来使用裸盘分区

innodb_file_io_threads = 4 
#用来同步 IO 操作的 IO 线程的数量. 
#此值在 Unix 下被硬编码为 4,但是在 Windows 磁盘 I/O 可能在一个大数值下表现的更好.

innodb_thread_concurrency = 16
#在InnoDb 核心内的允许线程数量,InnoDB 试着在 InnoDB 内保持操作系统线程的数量少于或等于这个参数
#给出的限制,最优值依赖于应用程序,硬件以及操作系统的调度方式.过高的值可能导致线程的互斥颠簸.
#默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量

innodb_flush_log_at_trx_commit = 1 
#0 每秒将日志缓冲区写入log file，并同时flush到磁盘。跟事务提交无关。在机器crash并重启后，
#0 会丢失一秒的事务日志数据（并不一定是1s，也许会有延迟，跟操作系统调度有关）。
#1 每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。（crash不会丢失事务日志）
#2 每次事务提交将日志缓冲区写入log file，每秒flush一次到磁盘。（crash有可能丢失数据）

innodb_log_buffer_size = 8M 
#用来缓冲日志数据的缓冲区的大小.当此值快满时, InnoDB 将必须刷新数据到磁盘上.
#由于基本上每秒都会刷#新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)

innodb_log_file_size = 500M 
#事务日志大小.在日志组中每个日志文件的大小，
#你应该设置日志文件总合大小到你缓冲池大小的5%~100%，
#来避免在日志文件覆写上不必要的缓冲池刷新行为.不论如何,
#请注意一个大的日志文件大小会增加恢复进程所需要的时间.

innodb_log_files_in_group = 2 
#在日志组中的文件总数.通常来说 2~3 是比较好的.

innodb_log_group_home_dir =  /usr/local/mysql/var/
#InnoDB的日志文件所在位置. 默认是 MySQL 的 datadir.
#你可以将其指定到一个独立的硬盘上或者一个 RAID1 卷上来提高其性能

innodb_max_dirty_pages_pct = 90 #innodb 
#主线程刷新缓存池中的数据，使脏数据比例小于 90%,这是一个软限制,不被保证绝对执行.

innodb_lock_wait_timeout = 50 
#InnoDB 事务在被回滚之前可以等待一个锁定的超时秒数。
#InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。
#InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒

innodb_flush_method = O_DSYNC 
#InnoDB 用来刷新日志的方法.表空间总是使用双重写入刷新方法.
#默认值是 “fdatasync”, 另一个是 “O_DSYNC”.

innodb_force_recovery=1

#如果你发现InnoDB 表空间损坏, 设置此值为一个非零值可能帮助你导出你的表.
#从1 开始并且增加此值知道#你能够成功的导出表.

innodb_fast_shutdown 
#加速InnoDB的关闭. 这会阻止InnoDB在关闭时做全清除以及插入缓冲合并.
#但是取而代之的是InnoDB可能在下次启动时做这些操作.

其他相关

[mysqldump]
quick

[mysql]
auto-rehash                    #允许通过 TAB 键提示
default-character-set = utf8   #数据库字符集
connect-timeout = 3

[client]
default-character-set=utf8

[mysqld_safe]
open-files-limit = 8192
#增加每个进程的可打开文件数量.确认你已经将全系统限制设定的足够高!打开大量表需要将此值设大