MySQL配置文件及参数详解

MySQL的配置文件需要根据版本及实际情况进行相应配置，本人使用的是Percona版本，主要是用到线程池等功能，所以选择Percona版本，配置文件内容如下，大部分参数信息我参考了相关资料做了说明，如有不当之处欢迎大家来指正。

1 .参数说明

 [mysqld]


#file config
pid-file=/data/mysql/mysql3307/tmp/mysqld.pid
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3307/data
socket=/data/mysql/mysql3307/tmp/mysql.sock
user=mysql
port=3306

#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
back_log = 500     

#back_log值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 待连接进程时.back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog，目前系统为1024。对于Linux系统推荐设置为大于512的整数
#######################
server-id=394406
skip-name-resolve=1  # 跳过域名解析
character_set_server=utf8
max_connections = 1000
max_connect_errors = 100 ##max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系
interactive_timeout=600 #服务器关闭交互式连接前等待活动的秒数,同时设置interactive_timeout和wait_timeout才会生效
wait_timeout=600  # 服务器关闭非交互连接之前等待活动的秒数长时间的执行批量的MYSQL语句。最常见的就是采集或者新旧数据转化

event_scheduler=1 #开启事件调度器,0 off 1 on
explicit_defaults_for_timestamp #显示指定默认值为timestamp类型的字段
log_timestamps=SYSTEM ##5.7.2新增参数log_timestamps 参数默认使用 UTC 时区，这样会使得日志中记录的时间比中国这边的慢了 8 个小时，导致查看日志不方便。修改为 SYSTEM 就能解决问题
sql_mode='' #sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题 
###############
#如果使用mysql，为了继续保留大家使用oracle的习惯，可以对mysql的sql_mode设置如下：
#在my.cnf添加如下配置
#[mysqld]
#sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
#ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES' 
################
default_storage_engine=innodb

slow_query_log=1;#通过使用--slow_query_log[={0|1}]选项来启用慢查询日志。所有执行时间超过long_query_time秒(缺省值为10s)的SQL语句都会被记录到慢查询日志
long_query_time = 600 #slow_query_log 这句是开启记录慢查询功能，slow_query_log=0关闭；slow_query_log=1开启（这个1可以不写）
long_query_time = 1  记录超过1秒的SQL执行语句
slow_query_log_file='/data/mysql/mysql3307/data/slow.log'
log_error_verbosity=1 #全局动态变量，默认3，范围：1～3。表示错误日志记录的信息，1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息。
#slow_launch_time=2 #slow_launch_time的设定跟慢查询日志的查询阀值设定不同，表示了thread create的一个阀值，如果thread create的时间超过了这个值，这变量slow_launch_time的值加1

myisam_repair_threads=2 # 如果该值大于1，在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)  
myisam_recover_options=FORCE #myisam_recover_options=force，那么即使此时key cache不存在了也会进行强制修复，此时做的就是对比数据文件和索引文件，然后删除数据文件中多余的行，因此这样可能会丢数据

#配置了参数myisam_recover_options=default，这个配置表示每次访问MyISAM表之前都会先检测表是否需要修复，如果需要则自动进行，这也就是前面看到信息last (automatic) repair failed。而修复失败是因为这个参数带来的修复行为默认是从key cache里面找需要修复的数据，而我当时是shutdown实例，rsync到新环境中起实例，此时已没有当时的现场(key cache环境)，加上default不会强制进行修复（强制修复表如果索引文件和数据文件数据不一致则自动进行删除或者增加行），（如果是myisam_recover_options=force，那么即使此时key cache不存在了也会进行强制修复，此时做的就是对比数据文件和索引文件，然后删除数据文件中多余的行，因此这样可能会丢数据）

##################
#memory config
##################
table_open_cache = 10000 #MYSQL默认的table_open_cache为64，这个数值是偏小的，如果max_connections较大，则容易引起性能问题。
表现：数据库查询效率慢，show processlist 发现比较多的查询正在opening table。
max_allowed_packet = 16M #用来控制其通信缓冲区的最大长度，解决执行一个SQL，但SQL语句过大或者语句中含有BLOB或者longblob字段。比如，图片数据的处理
max_heap_table_size = 64M #这个变量定义了用户可以创建的内存表的大小，这个值用来计算内存表的最大行数值
tmp_table_size = 1073741824    #copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等需要用到结果集时，参数中设置的临时表的大小小于结果集的大小时，就会将该表放在磁盘上，这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另外一个参数max_heap_table_size比tmp_table_size小时，则系统会把max_heap_table_size的值作为最大的内存临时表的上限，大于这个时，改写硬盘
sort_buffer_size = 50M #你可以考虑增加sort_buffer_size 来加速ORDER BY 或者GROUP BY 操作,不能通过查询或者索引优化的在任何情况下, 设置它大于需要的全局会减慢很多的查询。最后是作为一个会话设置来增加,只有对需要大量的内存的会话, 在Linux上,有阀值为256KB 和2MB ,大的值可能显著的减慢内存分配
join_buffer_size = 50M #如果应用中，很少出现join语句，则可以不用太在乎join_buffer_size参数的设置大小。如果join语句不是很少的话，个人建议可以适当增大join_buffer_size到1MB左右，如果内存充足可以设置为2MB或更多
thread_cache_size = 8 #根据物理内存设置规则如下1G  —> 8, 2G  —> 16 ,3G  —> 32 ,>3G  —> 64

# thread_concurrency = 8
ft_min_word_len = 2 #中文分词，ft_min_word_len设置为2，调用'repair table your_table quick',修复索引。相比方案2，此方案较节省空间。对于主要使用中文的系统而言，此方案更佳
memlock  #服务器是否锁定在内存中
default-storage-engine = MyISAM
thread_stack = 192K    #每个连接线程被创建时,MySQL给它分配的内存大小.当MySQL创建一个新的连接线程时,需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。
transaction_isolation = REPEATABLE-READ ##事务隔离级别，全局默认是REPEATABLE-READ,其实MySQL本来默认也是这个级别，不过binlog格式为row时 基本上生产环境事务隔离级别可以设置为READ-COMMITED,其中默认隔离级别为RR的原因后续有机会再行说明

key_buffer_size = 1024M    #这个参数是用来设置索引块(index blocks)缓存的大小,它被所有线程共享,严格说是它决定了数据库索引处理的速度,尤其是索引读的速度。那我们怎么才能知道key_buffer_size的设置是否合理呢，一般可以检查状态值Key_read_requests和Key_reads，比例key_reads / key_read_requests应该尽可能的低，比如1:100，1:1000 ，1:10000。其值可以用以i下命令查得：mysql> show status like 'key_read%';
read_buffer_size = 2M    # MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能
read_rnd_buffer_size = 16M #MySql的随机读（查询操作）缓冲区大小.当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
bulk_insert_buffer_size = 4294967296 #批量插入数据缓存大小，可以有效提高插入效率，默认为8M
myisam_sort_buffer_size = 128M  #MyISAM表发生变化时重新排序所需的缓冲
myisam_max_sort_file_size = 10G    # MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)
myisam_repair_threads = 1    # 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们. 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择
#myisam_recover

############
#innodb config
############
innodb_buffer_pool_size = 40G    #这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，
  #然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了
innodb_data_file_path = ibdata1:10M:autoextend    #数据文件配置,共享表空间地址及初始化大小，自动扩展属性
innodb_read_io_threads=8
innodb_write_io_threads=8        # innodb_read_io_threads innodb_write_io_threads 多核cpu可以通过这两个参数更有效的利用cpu性能 
innodb_thread_concurrency = 16   # 调节  并发线程数的限制值
innodb_flush_log_at_trx_commit = 2  
## innodb_flush_log_at_trx_commit ##
# 0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
#1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
#2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作
innodb_log_buffer_size = 8M   # InnoDB的写操作，将数据写入到内存中的日志缓存中，由于InnoDB在事务提交前，并不将改变的日志写入到磁盘中，因此在大事务中，可以减轻磁盘I/O的压力。通常情况下，如果不是写入大量的超大二进制数据（a lot of huge blobs），4MB-8MB已经足够了
innodb_max_dirty_pages_pct = 75 # 关于innodb_max_dirty_pages_pct值的争议，如果值过大，内存也很大或者服务器压力很大，那么效率很降低，如果设置的值过小，那么硬盘的压力会增加，建议是在75-80.并且innodb plugin引进了innodb_adaptive_flushng(自适应的刷新)，该值影响每秒刷新脏页的数量
innodb_flush_method = O_DIRECT  
##innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、刷写模式，对于这个参数，文档上是这样描述的：
#有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT
#默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer
#为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件
#为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log 

innodb_lock_wait_timeout = 120 #  innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒，最小可设置为1s(此时需要考虑应用端的频繁异常处理会消耗性能，不能设置过小)，最大可设置1073741824秒以上
innodb_buffer_pool_instances=8  #当 innodb_buffer_pool_size 设置的 大于 1GB 以后  那么此参数设置就尤为重要了,   MySQL 5.6.6开始 此参数默认为 8,  主要目的是为了解决 互斥锁, 每个缓冲池管理其自己的空闲列表,提高查询并发性, 对于互斥锁 可以自行补脑吧,如果innodb_buffer_pool_size大于1.3GB，则innodb_buffer_pool_instances的默认值为innodb_buffer_pool_size/ 128MB   即大致为 10 左右.每个实例 具有独立的缓存区块
innodb_page_cleaners=8  # 为了提升扩展性和刷脏效率，在5.7.4版本里引入了多个page cleaner线程。从而达到并行刷脏的效果。 在该版本中，Page cleaner并未和buffer pool绑定，其模型为一个协调线程 + 多个工作线程，协调线程本身也是工作线程。因此如果innodb_page_cleaners设置为8，那么就是一个协调线程，加7个工作线程
#innodb_force_recovery=1

################
#log file config
################
expire_logs_days=30
innodb_log_files_in_group=5
innodb_log_file_size=500m
binlog_format=row
log_bin=on
log_bin=/data/mysql/mysql3307/logs/mysql-bin
log_bin_index=/data/mysql/mysql3307/logs/mysql-bin.index

################
#replication config
################
#slave_parallel_type=LOGICAL_CLOCK
slave-parallel-workers=0   # MySQL 5.6版本也支持所谓的并行复制，但是其并行只是基于schema的，也就是基于库的。如果用户的MySQL数据库实例中存在多个schema，对于从机复制的速度的确可以有比较大的帮助
master_info_repository=table # 从机把主的信息存在主信息仓库里。主信息库可以是文件也可以上表，具体由—master-info-repository参数值决定。—master-info-repository＝file时 会生成master.info 和 relay-log.info2个文件，如果—master-info-repository＝table，信息就会存在mysql.master_slave_info表中。不管是设置的哪种值，都不要移动或者编辑相关的文件和表。想要更改配置通过再次执行change master to …语句，变更会自动保存到相关的文件和表。这个配置对应的表或者文件里的内容会覆盖某些命令行或者my.cnf中的配置
relay_log_info_repository=table # 建议将其修改为TABLE，因为1.relay.info明文存储不安全，把relay.info中的信息记录在table中相对安全。2.可以避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错
relay_log_recovery=on # 当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启
relay_log_purge=on # 有时候，我们希望将 MySQL 的 relay log 多保留一段时间，比如用于高可用切换后的数据补齐，于是就会设置 relay_log_purge=0，禁止 SQL 线程在执行完一个 relay log 后自动将其删除，但会有风险。 首先，为了让从库是 crash safe 的，必须设置 relay_log_recovery=1，这个选项的作用是，在 MySQL 崩溃或人工重启后，由于 IO 线程无法保证记录的从主库读取的 binlog 位置的正确性，因此，就不管 master_info 中记录的位置，而是根据  relay_log_info 中记录的已执行的 binlog 位置从主库下载，并让 SQL 线程也从这个位置开始执行。MySQL 启动时，相当于执行了 flush logs ，会新开一个 relay log 文件，新的 relay log 会记录在新的文件中。如果默认情况 relay_log_purge=1 时，SQL 线程就会自动将之前的 relay log 全部删除。而当 relay_log_purge=0 时，旧的 relay log 则会被保留。虽然这并不会影响从库复制本身，但还是会有地雷： 由于崩溃或停止 MySQL 时，SQL 线程可能没有执行完全部的 relay log，最后一个 relay log 中的一部分数据会被重新下载到新的文件中。也就是说，这部分数据重复了两次。 如果 SQL 跟得很紧，则可能在 IO 线程写入 relay log ，但还没有将同步到磁盘时，就已经读取执行了。这时，就会造成新的文件和旧的文件中少了一段数据。如果我们读取 relay log 来获取数据，必须注意这一点，否则就会造成数据不一致。而保留 relay log 的目的也在于此。因此，在处理 relay log 时必须格外小心，通过其中 binlog 头信息来确保正确性。
#####
slave_preserve_commit_order=1 # 对于多线程slaves，来保障事务在slave上执行的顺序与relay log中的顺序严格一致，只有当slave_parallel_workers开启时有效，此时log_bin,log_slave_updates必须开启，而且slave_parallel_type值必须为“LOGICAL_CLOCK”（默认值为DATABASE）。即当多线程开启时，且根据relay log中事务的逻辑顺序执行statements，是否需要严格保持顺序，默认值为0表示并发执行忽略顺序

slave_skip_errors=1032,1062 ## 如有需要再添加，指跳过从库部分错误，1032代表当delete数据是从库不存在的报错信息 1062为重复

[mysqld_safe]
log-error =/data/mysql/mysql3307/logs/mysqld.log
pid-file =/data/mysql/mysql3307/tmp/mysqld.pid

[client]
socket = /data/mysql/mysql3307/tmp/mysql.sock

[mysql]
prompt =\\u@\\d \\r:\\m:\\s>

2.  参考配置

生产环境的配置文件可以根据上面的参数说明自行配置，下面奉上姜老师推荐的配置以供参考，部分参数需要根据自己环境的情况调整

[client]user=davidpassword=88888888
[mysqld]
########basic settings########
server-id = 11port = 3306user = mysqlbind_address = 10.166.224.32   #根据实际情况修改# autocommit = 0   #5.6.X安装时，需要注释掉，安装完成后再打开character_set_server=utf8mb4skip_name_resolve = 1max_connections = 800max_connect_errors = 1000datadir = /data/mysql_data      #根据实际情况修改,建议和程序分离存放transaction_isolation = READ-COMMITTEDexplicit_defaults_for_timestamp = 1join_buffer_size = 134217728tmp_table_size = 67108864tmpdir = /tmpmax_allowed_packet = 16777216sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"interactive_timeout = 1800wait_timeout = 1800read_buffer_size = 16777216read_rnd_buffer_size = 33554432sort_buffer_size = 33554432
########log settings########
log_error = error.logslow_query_log = 1slow_query_log_file = slow.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 90long_query_time = 2min_examined_row_limit = 100
########replication settings########
master_info_repository = TABLErelay_log_info_repository = TABLElog_bin = bin.logsync_binlog = 1gtid_mode = onenforce_gtid_consistency = 1log_slave_updatesbinlog_format = rowrelay_log = relay.logrelay_log_recovery = 1binlog_gtid_simple_recovery = 1slave_skip_errors = ddl_exist_errors
########innodb settings########
innodb_page_size = 8192innodb_buffer_pool_size = 6G    #根据实际情况修改innodb_buffer_pool_instances = 8innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_lru_scan_depth = 2000innodb_lock_wait_timeout = 5innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_log_group_home_dir = /redolog/  #根据实际情况修改innodb_undo_directory = /undolog/      #根据实际情况修改innodb_undo_logs = 128innodb_undo_tablespaces = 3innodb_flush_neighbors = 1innodb_log_file_size = 4G               #根据实际情况修改innodb_log_buffer_size = 16777216innodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 67108864
########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin      #根据实际情况修改plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_master_timeout = 5000
[mysqld-5.7]innodb_buffer_pool_dump_pct = 40innodb_page_cleaners = 4innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb_purge_rseg_truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_write_set_extraction=MURMUR32show_compatibility_56=on