mysql配置文件my.cnf的事例并附解释

#更改磁盘调度算法

echo 'deadline' >/sys/block/xvdb/queue/scheduler

#关掉NUMA特性

#numactl --interleave=all

下面是my.cnf

[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld_safe]
#google优化过的内存分配模块,这个模块可以让MySQL在高并发下内存占用更加稳定.系统不自带,需要自行安装,建议使用.没有安装模块设置这个参数会报错.
#malloc-lib=tcmalloc
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
log-error        = /data/mysql/data/mysql.err
#表名的大小写敏感选项,默认为0,即开启大小写敏感,1为大小写不敏感
#lower_case_table_names = 1
#改变mysql的时区,默认是SYSTEM,即与系统同步,show variables like '%time_zone%'查看,按实际情况更改
default-time-zone = '+8:00'
#改变日志显示时间,默认是UTC,和中国时区相差了8小时,看着很奇怪,改成系统SYSTEM就可以了
log_timestamps = SYSTEM
#默认存储引擎,5.5之后默认就都是innodb
default-storage-engine=INNODB
#默认表的存储引擎,5.5之后默认就都是innodb,不设置就跟随default-storage-engine配置
#default_table_type = InnoDB
#MySQL程序的路径,代码调用路径
basedir=/usr/local/mysql
#全局数据文件及结构的存放位置；表数据(包括innodb引擎),索引,日志(除非单独设置)等文件都会存放在这里
datadir=/data/mysql/data
#innodb引擎的共享表空间数据文件根目录,如果不设置,默认使用datadir参数的目录
#innodb_data_home_dir = /data/mysql/data
#全局默认字符集类型,按需求设定,utf8,utf8mb4,gb2312等
character-set-server = utf8mb4
#全局默认字符校对规则,utf8默认校对规则是utf8_general_ci(不区分大小写),utf8_bin是区分大小写的,按需求设置.
#collation_server = utf8_bin
#当连接处于输入密码阶段,超过connect_timeout的连接请求将会被拒绝.默认是10秒,一般不用设置,也算是不长不短了,最好不要设置太长,连接挂起也是要耗费一定资源
#connect_timeout=5
#关闭一个非交互的连接(纯sleep状态)之前所要等待的秒数,其取值范围为1-31536000(linux),默认值28800.太长不利于高效利用资源,但某些耗费时间的sql和一些php/python/ruby等的脚本调用则不能设置太短时间,不然会提前中断.如果有连接池则最好默认或设置更大一些,而且告诉开发,连接池的超时时间不应该超过这个时间,不然会有奇怪的事情
wait_timeout=86400
#关闭一个交互的连接(sleep和其他状态)之前所要等待的秒数,默认值为28800,调小一些有利于资源回收,如果有需求用到长连接可以调大,因为超时就会强制关闭sql,如果有连接池则最好默认或设置更大一些,而且告诉开发,连接池的超时时间不应该超过这个时间,不然会有奇怪的事情
interactive_timeout = 86400
#在连接繁忙阶段(非sleep),对TCP/IP链接有效,只针对在Activity状态下的线程,客户端发送、接收、或者处理数据包的读超时时间,默认是30,一般不用设置,除非你网络状态很差
#net_read_timeout=300
#在连接繁忙阶段(非sleep),对TCP/IP链接有效,只针对在Activity状态下的线程,客户端发送、接收、或者处理数据包的写超时时间,默认是60,一般不用设置,除非你网络状态很差
#net_write_timeout=300
#禁用域名DNS查找,不能在mysql的授权表中使用主机名或域名,只能使用IP或localhost
#skip_name_resolve = 1
#跳过外部锁定,防止文件目录不可用
skip-external-locking
#使用InnoDB引擎独立表空间,每个表独立表空间,不使用共享表空间ibdata,但是表的元数据、undo log、插入缓冲等等还是保存在共享表空间里,新版本默认为开
innodb_file_per_table = 1
#在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中,默认值为50.并发高需要调高这个值,但是太高会占内存,不能超过系统设定值.
back_log = 103
#安全参数,与性能无关,阻止设定值次数尝试登陆失败后的客户端登陆,以防止暴力破解密码,内网安全要求低的可以适当增加.可以用flush hosts命令刷新这个登录失败的主机记录.
max_connect_errors = 100000
#控制innodb自增长锁模式参数,全局变量,只读变量,整型值,有0,1,2三个值(分别代表traditional(“传统”), consecutive(“连续”)和interleaved(“交叉”)),默认为1,表示自增ID是连续的,一般也不用修改.
#innodb_autoinc_lock_mode = 2
#线程处理模式,no-threads(单线程处理),one-thread-per-connection(每个请求对应一个线程),dynamically-loaded/pool-of-threads(线程池模式,官方mysql和Percona参数名字不一样)官方版本需要购买服务开启
#thread_handling = pool-of-threads
#线程池中连接超时的时间,默认500ms,调低可以提高连接池的利用
#thread_pool_stall_limit = 200
#索引缓冲区的大小,只对MyISAM表起作用,但是系统表和临时磁盘表都还是MyISAM表,要想加快这些的运行速度,这个值还是不能太低
key_buffer_size = 32M
#批量插入缓存大小,只对MyISAM表起作用,适用于在一次性插入100-1000+条记录时,提高效率.默认值是8M
#bulk_insert_buffer_size = 64M
#网络数据包容量限制,较大的插入/更新/导出/导入会受此参数限制,超过限制则导致sql执行失败.默认4M(太小),最大限制为1GB(太大),现在网络设备带宽都比较大,建议最少设置到32M以上,千兆以上网络建议64M/128M比较好.该值应为1024的倍数;否则四舍五入取最接近的倍数,mha等集群架构最好设定成64M以上.
max_allowed_packet = 128M
#全局最大打开文件数,不可以超过系统设定的最大文件数,不然无效
open_files_limit = 65535
#innodb引擎限制一次打开表空间文件.ibd的文件描述符数量,只在使用独立表空间时才有用,范围10~4294967295,不可以超过全局最大文件数.新版默认为-1,表示根据实际打开数量自动设置(自动计算规则：如果innodb_file_per_table没有打开,则默认值为300,如果打开了,则以innodb_open_files与table_open_cache中的较大值为准),仅用于限制innodb表空间.ibd文件的描述符数量,不影响table cache的数量,增大这个变量需要注意同时修改server层的变量open_files_limit=num(只读变量),这个参数为系统级别的文件句柄限制参数,由于每个session操作数据库表时都要占用文件描述符,数据库连接本身也要占用文件描述符,因此如果手动指定innodb_open_files参数时,还需要注意并发连接数量open_files_limit的大小设置
innodb_open_files = 65530
#允许存到缓存里的表的句柄数量,增大可改善频繁打开和关闭表的操作,新版本默认是2000.参考值为max_connections*表的个数,如果在show processlist里经常发现opening table那么可能就是这两个变量设置小了,也受open_files_limit的设置和innodb_open_files变量影响.
table_open_cache = 65530
#与table_open_cache作用类似,但是是缓存表定义文件.frm的数量.默认值是-1,表示自动计算,计算公式(400 + (table_open_cache / 2)),如果打开的表实例的数量超过这个参数设置,则LRU机制标记表实例并最终从数据字典缓存中删除,有助于解决大量内存被用于缓存极少使用的表实例的情况.
#table_definition_cache = 4096
#5.6.6版本新增,每一个线程可以使用缓存表的句柄的并发数(分区数),不能超过table_open_cache,默认是1,最大值为64,一般够用,除非库的并发实在太多且频繁打开表.
table_open_cache_instances = 16
#排序缓存,会话级别内存缓存,一般OLTP超过512K性能增加不明显,建议值为56K-1M,如果是数据仓库(OLAP)设置8M以上能进一步提升效果.如果通过show global status看到Sort_merge_passes的值很大,可以考虑通过适当调整这个参数的值来增大排序缓存区,但最好不要全局设置,只对当前session设置较大的值就可以了
sort_buffer_size = 512K
#TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行,主要影响导入导出
net_buffer_length = 8K
#以顺序读扫描的方式扫描表数据的时候使用缓冲区的大小,会话级别内存缓存,默认为128K,最大为2G,只对MyISAM表起作用,要注意系统临时表也是myisam
read_buffer_size = 1M
#会话级别内存缓存,以随机读扫描的方式扫描表数据的时候使用缓冲区的大小,默认为256K,最大为2G,原本只对MyISAM表起作用,但是新版本也会用做新特性mrr的buffer,
read_rnd_buffer_size = 16M
#MyISAM表发生变化时重新排序所需的缓冲,临时磁盘表都是MyISAM表,要加快临时表排序,这个值不能低,特别是数据仓库(OLAP)
myisam_sort_buffer_size = 128M
#MySQL重建索引时所允许的最大临时文件的最大大小
myisam_max_sort_file_size = 10G
#自动检查和修复没有适当关闭的 MyISAM 表
myisam_repair_threads = 1
#MyISAM表在打开的时候被自动检查,如果表被不恰当地关闭,就修复表.
#myisam_recover
#启用binlog,并指定存储位置,默认存到数据目录,主库一定要开,从库可适当关闭
log-bin=/data/mysql/data/mysql-bin
#默认是1,开启记录sql语句到binlog中,改成0则是禁止sql语句记入二进制日志文件binlog中,当然也就不会更新到备库中
#sql_log_bin = 0
#binlog类型格式,建议用mixed或row,但现在大多数情况会出现mixed格式转换成row格式的事情(特别是RC隔离级别),而STATEMENT只在个别情况使用了.
binlog_format=mixed
#binlog校验规则,一般不用设置.当binlog event发送回来最后获取event内容的时候,会增加4个额外字节做校验用.在5.6.5以后的版本默认是CRC32,低版本都是NONE.在使用MGR集群时需要设置为NONE
#binlog_checksum=NONE
#数据库ID号,主要用于主从复制相关,尽量设置成全网唯一值
server-id = 18650
#共享表空间容量大小,建议至少1G以上,5.7之后才实现真正大小缩放(以前只会变大)
innodb_data_file_path = ibdata1:1G:autoextend
#硬盘上单个redolog文件的容量大小,建议1-2G或以上,默认值为48M,最小值为1M,5.6.3版本开始支持最大512G.设置越大则数据库I/O越少,但是故障恢复的代价就越大,时间越长.
innodb_log_file_size = 1024M
#redolog内存参数,8-32M即可,设置越大则数据库I/O越少
innodb_log_buffer_size = 16M
#设置有几组redolog,设置多一些也可以减少I/O压力,特别是大事务的情况,最大值为100,最小值且也是默认值是2.
innodb_log_files_in_group = 3
#redolog存放目录,建议不设置,ib_logfile默认存在数据文件目录下,设置它主要是分散磁盘压力
#innodb_log_group_home_dir = /tmp
#控制innodb数据文件及redo log的打开、刷写模式,有三个值：fdatasync(默认且速度最快),O_DSYNC(san存储和读较多推荐使用),O_DIRECT(禁用了系统缓存,速度慢但最安全,raid卡+wb或原子写FIO上效果好),现在大多数 新环境中,都用上了固态硬盘和raid卡,所以建议设置成O_DIRECT,避免使用固态硬盘而造成的双缓存现象,减轻存储数据的代价
innodb_flush_method = O_DIRECT
#开启独立的purge线程并设定有多少个,有助于提高DML多表操作的效率.以及减少innodb内部的资源争用.新版默认值为4,最大值为32,
#innodb_purge_threads = 8
#表示一次完成多少个undolog page,但这个值的副作用是会影响到undolog的释放,因为总是在128轮purge后释放undolog page.默认300,可以设置1-5000,设置少一些会加快undo回收.
#innodb_purge_batch_size = 1
#控制innodb内部写线程数量参数,全局变量,只读变量,新版默认值为4,最小值为1,最大值为64,受操作系统的aio-max-nr设置限制
#innodb_write_io_threads = 8
#控制innodb内部读线程数量参数,全局变量,只读变量,新版默认值为4,最小值为1,最大值为64
#innodb_read_io_threads = 8
#事务隔离级别,总共四种:READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE,默认是RR,一般而言,用RC也不会有大问题,毕竟有问题的幻读也都是已经提交的数据,反而减轻锁粒度.
transaction_isolation = READ-COMMITTED
#默认为0,即关闭,意为禁止使用非安全锁,也即启用间隙锁功能.类似于将MySQL的事务隔离级别设定为READ-COMMITTED,但是这个参数必须重启mysql程序来加载配置才生效,一般也不用设置,除非有特殊需求.不过也有如下特性:(1)对UPDATE或DELETE语句来说,InnoDB仅锁定需要更新或删除的行,对不能够被WHERE条件匹配的行施加的锁会在条件检查后予以释放.这可以有效地降低死锁出现的概率；(2)执行UPDATE语句时,如果某行已经被其它语句锁定,InnoDB会启动一个“半一致性(semi-consistent)”读操作从MySQL最近一次提交版本中获得此行,并以之判定其是否能够并当前UPDATE的WHERE条件所匹配.如果能够匹配,MySQL会再次对其进行锁定,而如果仍有其它锁存在,则需要先等待它们退出.
#innodb_locks_unsafe_for_binlog = 1
#事务提交后通过fsync()写到硬盘redo文件ib_logfile的频率,0是存在内存中到一定量再写入(一般是1秒),1是立刻写入(速度最慢但最安全,最多只丢一个事务),2是存到操作系统缓存由操作系统决定写入时间(5.6.6之前是每隔1s刷盘,之后的版本是通过参数innodb_flush_log_at_timeout设置,默认也是1s.,但避免了mysql进程挂了就完全丢数据的问题),由于直接影响了磁盘IO,所以对数据库性能影响较大.
innodb_flush_log_at_trx_commit = 1
#5.6.6引入,参数innodb_flush_log_at_trx_commit=1时,此超时参数不起作用,当innodb_flush_log_at_trx_commit=0/2时才起作用.表示每隔几秒一次的频率刷新redo log(在5.6.6之前是固定每秒一次刷新一次,5.6.6之后刷新频率可以通过这个参数设置.)默认值是1S.如果你觉得磁盘压力太大,那可以适当调大,但请注意数据安全性就降低了.
#innodb_flush_log_at_timeout = 5
#刷新binlog的频率,0是由操作系统决定刷新时间,1每次事务都刷新一次(最慢但最安全,最多只丢一个事件),N(除0和1的其他数量,非负数)每N个事务刷新一次,如果不支持group commit那么就表示n个event(所有事件,包含一般事务和alter/create等的隐式事务)刷新一次,如果是支持group commit,那么就表示攒够n个event后binlog group才刷新一次,即支持group commit的性能更好,减少IO次数,安全性也比0提高了一些,但是和1的选项相比,安全性还是略差,会丢n-1个事务,mysql5.6后基本完美支持了group commit.
sync_binlog = 1
#行格式定义,有Antelope类型(支持冗余,紧凑行格式)可以使用redundant和compact两种行格式,Barracuda类型(支持数据压缩,动态行格式,改善blob,text字段存储布局)可以使用旧的redundant和compact,还可以用compressed和dynamic四种行格式,要想支持压缩类型,必须先定义Barracuda.尽量不要用compressed行格式,因为innodb_buffer中的pages无法压缩,只能压缩保存到磁盘中的page,表定义中需要添加ROW_FORMAT=xxx行格式配合使用.默认值为antelope,建议使用默认的antelope文件格式和compact的行格式,适用于绝大多数场景,需要用到压缩和不需要经常修改和备份的表则选择Barracuda更好一些.
#innodb_file_format = Barracuda
#允许索引使用动态压缩,但是表的row_format必须是compressed或者dynamic.可以使索引列长度大于767bytes,但是总长度不能大于3072 bytes.
#innodb_large_prefix = 1
#开启与关闭刷新邻接页功能,机械磁盘建议设置为1即启用,对于SSD则建议设置为0即关闭,还可以设置为2,表示同样刷新邻接页,但不要求在同一个区中的脏页是连续的.
#innodb_flush_neighbors = 0
#实例级别内存参数,innodb缓存总量(包含一些其他缓存),默认值为128M,设置越大,innodb性能越高,一般单实例建议50%-70%,多实例建议20%左右,5.6版本前是只读变量,只能重启实例生效,5.7.x后可动态修改.
innodb_buffer_pool_size = 830M
#缓存池实例并发数量,减少内部对缓存池数据结构的争用(缓存池的访问在某些阶段是互斥的)而提高并发性能,只在innodb_buffer_pool_size大于1G时生效,默认值在小于1G时为1,大于1G时为8,要保证innodb_buffer_pool_size/innodb_buffer_pool_instances大于128M,不然可能会有反效果,建议大于512M以上.在5.7最大值是64.
#innodb_buffer_pool_instances = 16
#设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小.表越多需要分配越多的内存.如果超出设定值,会在错误日志写警告信息.默认值是 1MB.
#innodb_additional_mem_pool_size = 2M
#默认为关闭OFF.如果开启该参数,启动MySQL服务时,MySQL将本地热数据加载到InnoDB缓冲池中,按需求开启
#innodb_buffer_pool_load_at_startup = 1
#默认为关闭OFF.在关闭时把热数据dump到本地磁盘,和上面参数配合,按需求开启.
#innodb_buffer_pool_dump_at_shutdown = 1
#当脏页占了多少百分比后就开始刷新脏页,写得越多,应该设置越低(减少占用缓存),根据LSN来参考log sequence number,Log flushed up to的差距来设定,相差越远就应该设的越低,默认值为75表示75%,最小值为0,最大值为99
#innodb_max_dirty_pages_pct = 50
#innodb_buffer_pool中old_block_sublist(旧数据链)最大占比,默认是8分3,也就是37%,取值范围是5~95,如果读多写少可适当增加,反之减少.
#innodb_old_blocks_pct = 50
#将缓存池刷新到磁盘的脏页数量,默认值为200,根据硬盘性能判定,机械硬盘1千左右,SSD硬盘4-5千左右,PCIE-SSD可以1-2万,但不建议超过2万
#innodb_io_capacity = 1000
#最大刷新到磁盘的脏页数量,根据上面参数设定,但一定要比他多
#innodb_io_capacity_max = 2000
#控制Innodb双写缓冲的开关(双写缓冲是对连续磁盘空间的顺序写,减少随机I/O),默认开启,0是开启,1是关闭,如果存储设备支持原子写,可以关闭,写入速度增加明显,其他情况建议开启
#innodb_doublewrite = 1
#限制并发增删改查的操作数量,当并发达到这个限制,额外的线程将被放置到队列中,等待并发连接释放,会睡眠数微秒,可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间,期间不会占用cpu和io资源.默认为0(无限制),最大值为1000.大多数情况下不需要设置,但当并发太高,服务器支撑不起负载的时候,可以设置64~128,建议为CPU核心数X磁盘数量X2,实际上，在线程进入锁等待以后，并发线程的计数也会减一，也就是说等行锁（也包括间隙锁）的线程是不算在 128 里面的。
#innodb_thread_concurrency = 128
#以前叫insert buffer,现在叫change buffer,因为现在设置的值有：inserts、deletes、purges、changes(inserts和deletes)、all(默认)、none.只能用在非唯一的索引上,嵌套在innodb_buffer_pool里面,一般不需要动
#innodb_change_buffering = all
#设置innodb_change_buffering占用innodb_buffer_pool的大小,默认是25%,最高只能是50%,5.6后支持,数据修改比较多的话,看需求设置
#innodb_change_buffer_max_size = 25
#在新版本mysql中,query_cache已失去意义,并且偶尔会造成query_cache锁,增加额外开销,建议直接关闭
query_cache_size=0
query_cache_type=0
#MDL元数据锁的超时时间,类似(不只是)数据结构ddl或dml等操作的锁的等待时间,默认值为31536000秒(一年),设置这个参数主要是避免一些大表的DDL或DML操作长期锁住表,如果表的数据量小默认就可以.
lock_wait_timeout = 600
#innodb事务请求行锁的超时时间限制参数,太多锁等待是很糟糕的,而innodb有自旋锁,等待时间太长也会耗费资源,单位是秒,最小可设置为1s,最大可设置1073741824秒(34年),默认是50s.
innodb_lock_wait_timeout = 300
#默认值为OFF,如果事务因为加锁超时,会回滚上一条语句执行的操作.如果设置ON,则整个事务都会回滚.注重事务一致性的需要开启
innodb_rollback_on_timeout = 1
#当innodb线程获取mutex资源失败后,自旋锁尝试的次数,默认是30.因为自旋锁是占用cpu和内存资源的,所以适当降低可以降低负载,但是也会提高查询失败的次数.提高该值则相反,最好测试后再考虑修改.
#innodb_sync_spin_loops = 30
#定义InnoDB自旋操作的空闲循环转数,默认为6转.只要不为0,对性能变化影响不大,一般是配合innodb_sync_spin_loops来使用.
#innodb_spin_wait_delay = 6
#用来配置从服务器的更新是否写入二进制日志,如果有层级从库就必须开(注意server-id),没有的话建议关闭,能有效提高性能.在GTID复制模式下,所有数据库都要开启log_slave_updates参数,是因为为了方便快速同步数据,如果涉及到切换,那么可以快速地把二进制日志进行同步(因为所有服务器都有记录完整的binlog)
log_slave_updates = 1
#当slave从库宕机后,假如relay-log损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,并且重新从master上获取日志,这样就保证了relay-log的完整性.默认情况下该功能是关闭的,建议开启,可以有效保证数据一致性.
relay_log_recovery = 1
#设置数据库为只读状态,普通用户不可以写入/更改数据(管理员还是可以写),默认关闭(即0),一般从库最好设置,避免不必要的误操作,主库就当然不要动他
#read_only=1
#5.7引入的新参数,以前的read_only不能限制super用户(例如root),而这个参数可以.
#super_read_only=1
#5.7引入的新参数,开启后之后,执行的SQL只能是只读
#tx_read_only=1
#从库relay log执行完后的自动清除开关,默认开启(即1),某些情况适用关闭(即0),只作用于从库
#relay_log_purge=0
#临时表容量缓存,会话级别内存缓存,超过之后临时表会转到磁盘上,磁盘上的临时表引擎默认是myisam,内存的临时表引擎是memory,均不可读写.由于复杂查询往往都有用到临时表并且这个参数是会话级别的,如果设置太大可能容易造成OOM,如果有大的查询,可以对当前session设置较大的值就可以了,如果通过show global status看到Created_tmp_disk_tables的值很大,可以套用这个公式:Created_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables)*100% > 10%,确认是否需要增加.
tmp_table_size=64M
#临时表行数缓存,会话级别内存缓存,和tmp_table_size一起限制临时表大小,可以创建的内存表(memory table)的最大行数,一般默认16M就足够.如果有大的查询,可以对当前session设置较大的值就可以了,如果数据量很大,最好调大.
#max_heap_table_size=96M
#如果临时表实在太大,磁盘空间压力增大,则需要考虑设置临时表存储位置,默认是/tmp
#tmpdir=/tmp
#数据库允许的最大连接数限制,建议设置成最高可负载连接数的80%,不然可能卡死,默认是151.如果status变量Connection_errors_max_connections不为零并一直在增长,就说明被这个参数限制而连接失败的用户很多,应该考虑增大这个参数的值,另外这个参数也受操作系统的open-files-limit设置限制,而mysql的最大连接数其实是：max_connections+1
max_connections=1024
#每个独立数据库用户的最大连接数,默认是0,表示没限制,设置这个可以防止最大连接数max_connections满了,导致管理用户(root等)都连不上的情况
max_user_connections=256
#可以重新利用保存在缓存中线程的数量,当线程断开后不会立刻关闭,会保存到缓存中等待被再利用,默认值为-1,表示自动计算:8+(max_connections/100).并发低此参数效果不明显,并发高调大效果明显,当公式Threads_created/Connections状态变量比值越接近1,说明线程的命中率越低,就应该考虑增加这个参数的值
#thread_cache_size = 51
#会话级别内存缓存,联结查询缓存,默认值为256K,最小值为128字节,join比较多就调大,也是要注意oom,如果有大的查询,可以对当前session设置较大的值就可以了.
join_buffer_size = 128M
#开启慢查询参数,0关闭,1开启,默认关闭,可动态修改.
#slow_query_log = 1
#设置慢查询存储路径
slow_query_log_file = /tmp/slow_querys.log
#慢查询记录时间,5.5之后可以设置到少于1秒,建议设置到0.1秒
long_query_time = 0.1
#用来设置是否记录没有使用索引的查询到慢查询记录,默认关闭,看需求开启,会产生很多日志,可动态修改.
#log_queries_not_using_indexes = 1
#管理指令也会被记录到慢查询.比如OPTIMEZE TABLE, ALTER TABLE,默认关闭,看需求开启,会产生很多日志,可动态修改.
#log_slow_admin_statements = 1
#是否把从库执行慢的语句也计入从库的慢查询日志中,看需求开启,会产生很多日志,可动态修改.
#log_slow_slave_statements  = 1
#binlog会话级别内存缓存,事务没有提交的时候,产生的日志记录到Cache中；等到事务需要提交的时候,则把日志持久化到磁盘.
binlog_cache_size = 4M
#最大binlog日志的缓存区大小,所有会话的binlog_cache_size总数不能超过这个值,默认就是最大值,在32位的系统中是4G,64位的是16P,设置它是防止mysql内存溢出
#max_binlog_cache_size = 4G
#binlog二进制日志写入给定值,默认值是1GB,最大不能设置大于1G,根据实际产生情况设置,文件太大不利于打开,但是如果你正使用大的事务,二进制日志还是会超过限制
max_binlog_size = 500M
#限制binlog空间大小,默认为0(即无限制),如果你正使用大的事务,二进制日志还是会超过max_binlog_size限制,而最大值则受这个参数限制,可以防止磁盘被撑爆.
#relay_log_space_limit=16G
#binlog保存天数,过期自动删除,默认值为0(代表不会自动清理binlog)
expire_logs_days=7
#当binlog_format=row时控制是否记录用户原生SQL的参数到binlog file中,默认关闭
#binlog_rows_query_log_events = 1
#打开、关闭普通查询日志功能,默认关闭,如果设置为1或者ON,则数据库所有的SQL都会被记录下来,一般会增长很快,也会浪费数据库5%-10%的性能.
#general_log = 1
#设置查询日志路径和文件名,如果不设置,则默认在datadir目录下命名为hostname.log,和上面参数结合使用.
#general_log_file = /tmp/general_querys.log
#指定查询日志general_log和慢查询日志slow_log存到数据库的表中,分别是mysql.general_log和mysql.slow_log,默认是FILE,即文件中,按实际情况设置.
#log_output=TABLE
#5.6新功能,从ibdata1分离undo文件,开启并指定有多少个undo log文件,默认为0,即不开启该功能.mysql8.0默认开启并设置成2
innodb_undo_tablespaces = 4
#指定存放undo的目录,默认是当前数据目录(datadir参数指定的目录),出于减轻磁盘压力的思维下,可以考虑独立出去
#innodb_undo_directory = /tmp/
#指定undo回滚段大小,至少大于等于35,默认128,一般情况下也够了,mysql8.0取消了这个参数
#innodb_undo_logs = 256
#开启在线回收(收缩)undo log日志文件,支持动态设置.
innodb_undo_log_truncate =1
#当undo超过这个阀值(默认是1G),会触发truncate回收(收缩)动作,truncate后空间缩小到10M,视乎你硬盘容量而定.
#innodb_max_undo_log_size = 2G
#控制回收(收缩)undo log的频率.undo log空间在它的回滚段没有得到释放之前不会收缩,想要增加释放回滚区间的频率,就得降低这个参数值,默认128.
#innodb_purge_rseg_truncate_frequency = 64
#mysql5.6后参数,开启后,会将所有deadlock记录在error_log中,作为dba,你应该需要知道有多少死锁信息
innodb_print_all_deadlocks = 1
#在5.6.2之前,slave记录的master信息以及slave应用binlog的信息存放在文件中,即master.info与relay-log.info.在5.6.2版本之后,允许记录到table中,多源复制,这个必须是table
master_info_repository = TABLE
#同上信息,多源复制,这个必须是table
relay_log_info_repository = TABLE
#GTID模式,5.6新功能,新的复制方式,需要就打开,建议binlog改成row
gtid_mode = on
#强制GTID的一致性,一般和上面参数一起使用,但是开启后只允许能够保障事务安全,并且能够被日志记录的SQL语句被执行,像create table ... select 和 create temporarytable语句,以及同时更新事务表和非事务表的SQL语句或事务都不允许执行.
enforce_gtid_consistency = 1
#关闭numa功能,5.6.27新参数,默认为0,即开启,我们要设成1,即关闭这个功能.numa是linux系统为了合理分配多核环境下内存空间而存在的功能,但是对于大内存应用反而造成了性能瓶颈,所以对于数据库类别应用来说,应当要关闭.
#innodb_numa_interleave = 1
#用于从flush队列中取事务的超时时间,这主要是防止并发事务过高,导致某些事务的RT上升,单位微秒,默认是0.
#binlog_max_flush_queue_time = 0
#默认为开启on(即1),事务将以二进制日志的顺序相同的顺序进行写入,当设置为关闭off(即0)时,事务可能以和binlog不相同的顺序被提交,高并发下会有一些性能的提升
#binlog_order_commits = 1
#innodb存储引擎恢复级别,可选0-6,默认是0,当数据库或表不能正常使用时才可能需要用到
#innodb_force_recovery = 0
#5.7.15新功能,关闭死锁监测,正常情况下死锁监测只占少部分资源,所以默认开启也不需要理会.在高并发情况下则相反,会造成额外的开销,关闭他就能提高并发,但是出现死锁就不会回滚,会一直锁等待,所以关闭之后就需要调小innodb_lock_wait_timeout的值来减少锁等待时间,避免锁等待太长时间,代码层面也需要做好这个报错的返回规则
#innodb_deadlock_detect=off
#percona特有功能,其他版本暂时没有,限制所有事务的空闲时间,例如大量JDBC的长连接,特别有用,设置秒数即启用,默认不设置即没限制.
#innodb_kill_idle_transaction = 30
#数据导入/导出到文件的限制(load data和SELECT...into outfile),属于安全限制的一种,新版本默认是null,即禁止.可以输入文件夹名字,表示限制导入/导出文件夹,旧版本默认值为空,即完全不限制出导入/导出
#secure_file_priv = /tmp
#影响mysql选择排序的算法,适当调大有助于优化sql执行效率,mysql有两种文件排序算法(双路排序和单路排序),如果需要排序的列的总大小加上orderby列的大小超过了这个参数定义的字节,mysql就会使用双路排序,当包含text、blob列时,也会使用双路排序,双路排序的开销可能会非常巨大,因为他会读取表两次,第二次读取会引发大量的随机IO,对于myisam来说这个代价尤其昂贵,myisam表利用系统调用去提取每行的数据.单路排序效率更快一些,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是它会使用更多的内存空间,因为它把排序需要的所有列都一次性导出来保存在内存中
#max_length_for_sort_data = 4096
#设置从库SQL线程并行重放events(事务)的worker线程数量,默认值为0,最大值为1024.在5.6.x版本中设置这个参数大于0时,则SQL线程充当worker线程的协调者,在多个worker线程之间分发基于库级别的events,也就是库级别并行复制,5.7.x版本的并行复制可以设置基于组提交事务的并行复制,更加好用.
#slave_parallel_workers = 8
#5.7新参数,指定并行复制方式,但为了兼容5.6只支持库模式,默认的并行复制方式是基于库级别,即默认值是：DATABASE,我们要设置成5.7支持的基于组提交事务的并行复制方式,则需要设置成：LOGICAL_CLOCK,速度更快.
#slave_parallel_type = LOGICAL_CLOCK
#默认是0即关闭,当slave_parallel_type设置为LOGICAL_CLOCK的时候使用,1为开启,控制Slave上的binlog提交顺序和Master上的binlog的提交顺序一样,保证GTID的顺序.因为在slave上应用事务的顺序是无序的,和relay log中记录的事务顺序不一样,这样数据一致性是无法保证的.5.7.19前即使设成1也有可能有问题,所以需要使用这个参数的话请更新到5.7.19.
#slave_preserve_commit_order = 1
#binlog提交后等待延迟多少时间再同步到磁盘,单位是微秒,默认0,不延迟.设置延迟可以让多个事务在用一时刻提交,提高binlog组提交的并发数和效率,从而提高slave的吞吐量.建议默认
#binlog_group_commit_sync_delay = 100
#在等待上面参数超时之前,如果有足够多的事务,则停止等待直接提交.单位是事务数,默认0.建议默认
#binlog_group_commit_sync_no_delay_count = 100
#5.6.13之后引入的参数,控制stop slave 的执行时间,假设重放大事务的时候,突然执行stop slave,会执行很久,甚至可能产生死锁或阻塞,严重影响性能.默认值是31536000秒=1年.
#rpl_stop_slave_timeout=300
#判断主库是否挂掉的超时设置,在设定时间内依然没有获取到Master的回应就认为Master已经挂掉了,后续根据超时重连参数设置进行重连主库的操作.默认值：3600S
#slave_net_timeout=300
#自动处理同步复制错误,默认是STRICT严格模式,IDEMPOTENT是幂等模式,它跳过了duplicate-key(1062)和no-key-found(1032)等错误,该模式只有在ROW的binlog模式下生效,在STATEMENT的binlog模式下无效,和slave_skip_errors的作用是一样的,但是slave_skip_errors不支持动态修改,必须重启mysql才能生效,因此建议使用slave_exec_mode.正常情况下,应该是从库遇到错误就停止复制,然后人工去处理数据一致性问题,第一时间去想怎么满足这个复制,而不是去跳过这个事务.
#slave_exec_mode=IDEMPOTENT
#半同步主库开关,使用前需要先确认有没有加载半同步插件
#rpl_semi_sync_master_enabled = 1
#当确认半同步开启之后,控制主库使用半同步复制机制把binlog发送到slave之后,等待从库ACK接收确认包的时间,超过这个时间会自动转为异步,直到从库重新上线
#rpl_semi_sync_master_timeout = 3000
#5.7.3新加的半同步参数,至少有N个slave接收到日志,然后返回ack,这个半同步事务才能提交,默认是1.当这个值设置到和从库数量相等的话,则效果会等同于全同步复制.
#rpl_semi_sync_master_wait_for_slave_count = 2
#5.7新参数,控制半同步模式下 主库在返回给会话事务成功之前提交事务的方式.旧模式是AFTER_COMMIT,新模式是AFTER_SYNC,默认值：AFTER_SYNC .master 将每个事务写入binlog ,传递到slave,并且刷新到磁盘.master等待slave 反馈接收到事务并刷新到磁盘.一旦接到slave反馈,master在主库提交事务并且返回结果给会话. 在AFTER_SYNC模式下,所有的客户端在同一时刻查看已经提交的数据.假如发生主库crash,所有在主库上已经提交的事务已经同步到slave并记录到relay log.此时切换到从库,可以保障最小的数据损失.
#rpl_semi_sync_master_wait_point = AFTER_SYNC
#默认为on,即当从库降到0个的时候,主库依然等待从库发送ack确认包.改为off之后,即从库降到0个后,主库不再等待从库,直接转为异步.
#rpl_semi_sync_master_wait_no_slave=OFF
#半同步从库开关,使用前需要先确认有没有加载半同步插件
#rpl_semi_sync_slave_enabled = 1
#5.7.5新参数,定义磁盘临时表的引擎类型,默认是InnoDB.之前的版本中只能使用MyISAM(就算是新版的5.6也是)
#internal_tmp_disk_storage_engine = InnoDB
#5.6.3新参数,控制CREATE TEMPORARY TABLE 创建的临时表的引擎类型,在以前默认是MEMORY,
#default_tmp_storage_engine = InnoDB
#控制主从复制的同步进程是否随mysql程序启动而启动,默认是0(即随mysql进程一起启动),1为关闭.关闭之后从库重启后会出现双no状态,对于集群有一定意义.
#skip_slave_start=1
#只记录参数指定的数据库的binlog,通常用于主从复制环境的主库设置,有一定隐患,通常不会这样用
#binlog_do_db=work,test
#忽略参数指定的数据库的binlog,通常用于主从复制环境的主库设置,有一定隐患,通常不会这样用
#binlog_ignore_db=mysql,test
#从库同步选择参数,只执行某个库或某个表的同步语句,其他库和表都不处理,不能单行多选,有多一个库的需求就要多写一行配置
#replicate_wild_do_table=test.%
#从库同步选择参数,忽略掉某个库或某个表的同步语句,其他库和表的都会执行,不能单行多选,有多一个库的需求就要多写一行配置
#replicate_wild_ignore_table=mysql.%
#5.7.8被添加,单位为ms,动态参数,默认为0.控制查询的最长时间,默认是0,即无限制,但暂时也只能控制select,看你对数据库性能要求多高而定,设置较短时间等同于禁用复杂查询.
#max_execution_time = 300000
#在多线程复制时,在队列中Pending的事件所占用的最大内存,默认为16M,如果内存富余,或者延迟较大时,可以适当调大;这个值要比主库的max_allowed_packet大
#slave_pending_jobs_size_max = 128M
#5.6.6新参数,默认为0即关闭,1为开启,旧的时间格式timestamp有非标准行为,未声明为null的话,会被分配为notnull属性,而向此列插入null时将自动转换为时间戳,或者是"0000-00-00 00:00:00"这样的数据,这个参数的作用就是关闭这种非标准行为,让他可以插入null,当彻底放弃timestamp后,这个参数也会被放弃,建议多用datatime
#explicit_defaults_for_timestamp=1
#限制了同一时间在mysqld上所有session中prepared 语句的上限.它的取值范围为“0 - 1048576”,默认为16382.通常这个值够用,因为没有那么多这种需求,但是超出这个值的prepare语句就会报Can't create more than max_prepared_stmt_count statements (current value: 16382)错误.改大一些就好
#max_prepared_stmt_count=100000
#MySQL5.6新参数,可以设置Innodb数据页为8K,4K,32K,64K来适应更多的场景,一般默认16K即可.这个参数在一开始初始化时就要加入my.cnf里,如果已经创建了表,再修改,启动MySQL会报错.一般情况下我们会建议一页最好存储两行数据,但是有些情况数据很大或很小,就会浪费或者是溢出数据页,造成行迁移,更改这个参数可以适当避免.
#innodb_page_size = 8k
#认证加密方式,一般不用设置,默认是:mysql_native_password,在5.7.2之后可以设置sha256_password来使得更加安全,在mysql8.0之后默认是:caching_sha2_password,会让旧版本的mysql连不上.修改之后只对新授权用户生效.
#default_authentication_plugin=mysql_native_password
#开启performance_schema性能统计库功能,5.7之后默认开启,5.6之前默认关闭,注意：该参数为只读参数,需要在实例启动之前设置才生效.如果开启失败就要去错误日志进行排查.
#performance_schema = ON

[mysqldump]
quick
#同上,sql太长用mysqldump受此参数影响
max_allowed_packet = 128M
[mysql]
no-auto-rehash
prompt="\u@\h \R:\m:\s [\d]> "
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout = 28800