【MySQL运维】MySQL配置文件示例与说明

一、作用域相关配置

• [mysql]：mysql客户端作用域，该作用域下的配置仅对mysql这个命令客户端生效
• [mysqldump]：mysqldump客户端作用域
• [mysqladmin]：mysqladmin客户端作用域
• [client]：其他客户端的作用域。该作用域下的配置对mysqladmin、myqsldump等客户端命令都生效。由于不是所有客户端都有相同的配置，可能会因为多余的配置导致启动报错
• [mysqld]：服务端选项作用域，大部分配置都在该作用域下
• [mysqld-5.7]：对某一版本的服务端生效

二、客户端相关配置

• prompt：自定义提示符，方便查看当前所操作数据库，如prompt= "[\\u@linuxe][\\d]>"
• tee：将操作记录写到日志中作为审计，如tee= "/data/dblog/tee.log"  #
• no-auto-rehash：禁止自动读取元数据，虽然无法再进行命令补齐，但是可以降低负载。如果在使用mysql命令连接服务卡在提示符就是元数据被锁导致，用mysql -A选项也可以启用该功能
• default-character-set：客户端字符集设置，建议和服务端一致，如default-character-set=utf8mb4

三、服务端相关配置

1、数据库基本信息

• user：指定数据库服务的用户，如user=mysql
• basedir：MySQL安装路径，如basedir=/usr/local/mysql
• datadir：MySQL数据文件存放路径，如datadir=/data/mysql/data
• port：MySQL实例端口，如port=3306
• socket：MySQL sock文件路径，如socket=/tmp/mysql.sock 
• character-set-server：字符集设置，如character-set-server=utf8mb4
• skip-character-set-client-handshake：强制客户端字符集和服务端一致
• default-storage-engine：默认存储引擎，如default-storage-engine=INNODB
• skip-name-resolve：不把客户端的IP反向解析成域名，直接用IP来做权限判断即可
• server_id：主从复制会用到，每个节点ID不能相同，可以避免双主架构带来的循环复制问题
• tmpdir：临时表路径，如tmpdir=/data/mysqltmp/mysql3306
• max_connect_errors：允许客户端连接失败的次数，超过该次数就会被服务拒绝连接
• back_log：如果客户端连接数上限后，允许多少个客户端进入一个队列排队
• interactive_timeout=3600：交互模式下会话超时时间，单位为秒
• wait_timeout=3600：应用会话连接超时时间，单位为秒，默认为8小时。应用程序在连接成功后如果没有后续操作，则连接处于Sleep空闲状态
• max_allowed_packet：MySQL与客户端建立连接后能传输的数据包限制，如果一个包含大批量数据更新的SQL超过了该参数的限制就会失败。最大可配置为1G。
• time_zone：显示指定时区，默认为SYSTEM，即系统的操作时区
• performance_schema：显式启用performance_schema数据库，该选项在5.7开始默认是启用的
• lower_case_tables_name：建议设置为1，表示不对表名进行大小写区分
   

2、数据库内存信息（尽可能的为数据库分配更多的内存，减少与磁盘的IO）

• max_connections：客户端最大连接数，建议参考show status like '%Threads_running%'的值。该值不要调得太大，实际内存计算是max_connections*(sort_buffer_size+read_buffer_size+read_rnd_buffer_size+join_buffer_size+binlog_cache_size+thread_stack)每个客户端要单独申请内存空间
• sort_buffer_size：排序时用到的内存空间，默认256K。调大后可以提升order by语句的效率。经过Percona测试发现盲目调大会起到反效果，通常Sort_merge_passes/(Sort_scan+Sort_ range)>2说明sort_buffer_size设置偏小，需要将其调大

show global status like '%sort%' 

• read_buffer_size：顺序读缓冲区大小，默认128K
• read_rnd_buffer_size：随机读缓冲区大小，默认256K
• join_buffer_size：默认256K
• binlog_cache_size：当事务产生的binlog超过该参数值就会使用临时文件来存放binlog cache，默认32K，适当调大可以加快binlog写入效率
• thread_stack：每个线程连接时申请的内存空间，默认256K
• key_buffer_size：默认64M
• tmp_table_size：内存临时表的最大值，默认64M。临时表常常用于存放group by所需要的统计数据，方便后面对这些数据进行排序。如果内存临时表超过该参数限额，则开始生成基于磁盘的临时表，影响性能。通过show status like '%tmp%'后观察tmp_disk_tables的数量，如果数值增长很快说明tmp_table_size设置得太小了。调大该值可以提高GROUP BY语句和联合查询的速度。需注意该参数还受到max_heap_table_size的影响，如max_heap_table_size设置得比tmp_table_size小，那max_heap_table_size的值才是最大的内存临时表的上限

 

3、数据库日志信息

· 错误日志

• log_error：错误日志路径，如/data/mysql/logs/error.log

· 慢日志

• slow_query_log：慢日志开关，on代表打开
• slow_query_log_file：慢日志路径，如/data/mysql/logs/slow.log，不指定的话默认在数据库文件目录下，名为hostname-slow.log
• long_query_time：慢日志标准，达到这个标准的才会被记录慢日志，单位为秒，设为0代表记录所有查询
• log_queries_not_using_indexes=1：将没有使用索引的语句都记录到慢日志中，即便没有达到阈值
• log_throttle_queries_not_using_indexes=60：和上面选项配合使用，如果没有使用索引的语句执行频繁，那每分钟最多只记录60次
• log-slow-admin-statements：记录由ALTER TABLE等语句引发的慢查询

· 二进制日志

• log_bin：binlog日志路径，如/data/mysql/logs/master-bin
• log_bin_index：binlog索引文件路径，如/data/mysql/logs/master-bin.index
• binlog_format：binlog日志格式，推荐row
• binlog_rows_query_log_events：将所执行的原始sql语句记录到日志中，更便于分析
• expire_logs_days：binlog过期天数
• max_binlog_size：单个binlog文件大小
• binlog_cache_size：binlog缓存配置，和性能优化相关
• log_timestamps：默认为UTC时间，这样查看日志会存在时区问题，建议修改为SYSTEM，和系统时间一致

· 双1参数

• innodb_flush_log_at_trx_commit：redolog刷盘策略，建议为1。0是每秒刷新一次redo buffer到磁盘，当服务崩溃可能丢失1秒数据；1是每次事务提交时都立即刷新到磁盘；2是每次事务提交都立即刷新到os cache中，随后刷新到磁盘，机器断电才会丢失1秒数据
• sync_binlog：binlog刷盘策略，建议为1。当值为0代表事务提交时仅将Binlog信息写入OS Cache，但是由系统自己控制刷盘时机，如果系统宕机则丢失缓存中的所有数据；当值为1时代表每个事务提交时同步把Binlog刷新到磁盘，当数据库或操作系统宕机恢复后，由于Binlog中缺少的任何事务都是处于准备阶段，所以MySQL会回滚这些事务，保证无数据丢失，数据库安全性最高，但是性能损耗也最大；当值为N时代表每N次事务提交才会刷盘，如果操作系统在这个时候宕机，数据库可能会丢失N个事务

· 主从复制

• log_slave_updates：从库也记录binlog，可以实现级联复制以及起到binlog备份作用，如果从库只配置了log_bin而没有开启该选项，则binlog为空。不承载高可用的从库可以关闭节约IO性能
• relay_log_info_repository：设置为table，将中继日志状态存放在mysql.slave_relay_log_info表中

slave_parallel_workers：该值为0代表单线程复制，大于1时表示启用多线程复制。该选项值可动态设置，但重启复制线程才会生效。通常和CPU核数一致，可以提升从库复制效率，一定程度上解决从库延迟问题。并行复制开启后通过processlist看到状态为system lock的system user用户就是并行复制的线程

 

4、InnoDB信息

· InnoDB性能

• innodb_buffer_pool_size：MySQL最重要的性能参数，通过该项来设置数据和索引的缓存区大小，提升效率。为了避免缓存频繁的被使用完毕而经常与磁盘交互，可以合理的调大该值，建议设置为系统最大内存的70%，并且该值需要是innodb_buffer_pool_instance*innodb_buffer_pool_chunk_size（默认128M，不能动态调整）的倍数。innodb_buffer_pool_size的值支持动态配置，但是在线修改的话值只能为字节，通过配置文件修改则可以使用M、G单位。通过SHOW GLOBAL STATUS LIKE '%innodb%'命令查看MySQL剩余buffer，如果Innodb_buffer_pool_pages_free值很小或为0、Innodb_buffer_pool_wait_free>0，说明buffer pool已经使用殆尽，需要增加innodb_buffer_pool_size的值
• innodb_buffer_pool_instances：将buffer_pool_size平均划分多个区域，提升并发性能，建议和CPU核数一致
• innodb_flush_method=O_DIRECT：默认为fsync，代表刷数据到磁盘的时候会先申请系统级别的缓存，这样性能虽然会更好但是会申请额外的内存。由于MySQL除了innodb_buffer_pool_size占用内存，每个连接还会单独申请内存空间，再加上fsync申请的内存，很容易导致OOM。改为O_DIRECT会跨过系统缓存直接写到磁盘。
• innodb_thread_concurrency：并发查询数（非并发连接），默认为0不限制，建议设置为128，避免并发太多导致CPU跑满。由于锁等待产生的并发并不会带来CPU开销，所以这些并发不会占用这个连接名额，避免锁等待而让数据库不可用
• innodb_strict_mode：开启严格模式，可以避免非空字段插入null等情况
• innodb_stats_on_metadata：建议为off关闭动态统计被触发
• innodb_log_file_size：redo log日志大小，默认是48M。日志命名为ib_logfile0~ib_logfileN。如果设置太小会导致数据库经常flush日志到磁盘影响性能，配置越大写操作的效率越高，但是崩溃恢复时间就会稍微长一点，SSD硬盘建议4G起配
• innodb_log_buffer_size：redo log缓存区域大小，刷新缓存的几种情况：master thread每秒刷新buffer、buffer写满时刷新、事务提交时刷新、服务重启时刷新
• innodb_log_files_in_group：redo log数量，取值为1-4，默认2。
• innodb_io_capacity=2000：脏页刷新速度，单位为页。磁盘速度越快就设置越大，默认是200，建议和磁盘IOPS一样，可以通过fio来测试磁盘IO
• innodb_flush_neighbors：默认为1代表刷脏页时会把邻近脏页一起刷到磁盘。对于机械硬盘能够减少磁盘寻道的开销，提升性能；但是对于SSD提升很小，建议为0关闭
• innodb_file_per_table=ON：是否开启独立表空间，开启后每个表数据都会单独存放在一个以表命名后缀为.ibd文件中。5.6开始默认打开，通常也会打开。可以分散IO提升性能，也便于表的维护，否则所有表数据会全部存放在ibdata1文件中，即便删除了某个表，这个文件空间也不会释放，只是将表空间被标记为可复用。
• innodb_lock_wait_timeout：InnoDB行锁等待超时时间，比如事务A对某行数据进行修改但未提交，此时事务B也修改该行数据时就会被锁，直到超时。该值默认50，建议5-20秒
• lock_wait_timeout：元数据锁超时时间，默认是1年，设置30秒足够
• innodb_buffer_pool_dump_at_shutdown：ON代表每次停机会dump出buffer pool中的数据
• innodb_buffer_pool_filename：dump出的数据文件名，如ib_buffer_pool
• innodb_buffer_pool_load_at_startup：ON代表启动服务时加载dump文件到内存进行缓存，提升性能
• max_execution_time=60000：控制每个语句执行的最长时间，避免单个语句意外执行太长时间，比如一些框架会默认把SQL使用begin\commit框起来，或者代码错误把SELECT语句放入到事务中。该配置项单位是毫秒。
• table_open_cache：指定打开表的缓存区大小，也可以理解为允许缓存客户端打开多少张表。这个配置需要结合open_tables（当前打开的表数量）与opened_tables（服务启动以来总共打开的表数量）两个状态值来调整。如果open_tables的值接近table_open_cache代表缓存区已经使用完，如果此刻opened_tables又在不断增加，说明mysql还在不断打开表。这个使用缓存是没有起到作用的，所以需要调大缓存区的值，但是不能大于系统文件描述符。
• innodb_large_prefix：是否允许单列的索引长度超过767字节
• innodb_old_blocks_pct：MySQL为避免LRU算法把冷数据放到头部，所以将LRU链表拆分为热、冷数据两个部分，优先对冷数据进行淘汰。该参数控制了冷热数据比例，默认37代表冷数据占37%
• innodb_old_blocks_time：控制冷数据转为热数据的策略，默认1000毫秒，代表数据页被加载到缓存页N毫秒后如果再次被访问，就挪动到热数据区域表头部。这个思想也可以用在Redis上，可以每天统计出来哪些商品被访问的次数最多，然后通过定时作业把这些热门商品预加载到Redis里，第二天对热门商品的访问就会优先走Redis缓存了

· Undo Log

• innodb_undo_tablespaces：undolog独立表空间个数，默认为0表示不为undolog设置独立表空间，而是记录到ibdata文件中。如果设置为4则代表创建命名为undo001~undo004的undolog。该配置在mysql初始化后就无法进行修改
• innodb_max_undo_log_size：单个回滚日志最大占用空间大小，默认为10M
• innodb_undo_log_truncate：当undolog超过innodb_max_undo_log_size阀值时对undolog空间进行收缩到10M

· Redo Log

 

四、my.cnf配置文件示例

[client]
port = 3310
socket = /tmp/mysql3310.sock

[mysql]
no-auto-rehash
prompt = "\\u@\\d \\R:\\m> "
default-character-set = utf8mb4

[mysqld]
### General ###
user						= mysql
port						= 3310
basedir						= /usr/local/mysql
datadir						= /data/mysql3310/data
socket						= /tmp/mysql3310.sock
tmpdir						= /data/mysql3310/tmp/
character_set_server		= utf8mb4
default-storage-engine		= InnoDB
default-time-zone			= "+8:00"
server-id					= 30241
transaction_isolation		= READ-COMMITTED
max_connect_errors			= 500
back_log					= 1024
open_files_limit			= 65535
max_allowed_packet			= 512M
init_connect				='set names utf8mb4'
group_concat_max_len		= 10240
table_open_cache			= 8192
table_definition_cache		= 8192
log_timestamps				= system
performance_schema			= ON
lower_case_table_names		= 1
explicit_defaults_for_timestamp = 0
skip-character-set-client-handshake = 1
skip-name-resolve
skip-external-locking
#skip-grant-tables

### Timeout ###
connect_timeout				= 20
interactive_timeout			= 3600
wait_timeout				= 3600
lock_wait_timeout			= 300
max_execution_time			= 60000
innodb_lock_wait_timeout	= 10

### Memory ###
innodb_buffer_pool_size		= 32G
innodb_log_buffer_size 		= 32M
tmp_table_size         		= 64M
max_heap_table_size    		= 64M
key_buffer_size        		= 64M
max_connections        		= 500
 read_buffer_size      		= 4M
 sort_buffer_size      		= 4M
 read_rnd_buffer_size  		= 4M
 join_buffer_size      		= 4M
 binlog_cache_size     		= 16M

### Error Log ###
log_error					= /data/mysql3310/log/error.log

### Slow log ###
slow_query_log					= 1
slow_query_log_file = /data/mysql3310/log/slowlog/mysql.slow
long_query_time					= 1
log_slow_admin_statements		= 1
#log_queries_not_using_indexes	= 1
#log_throttle_queries_not_using_indexes = 10
#log_slow_slave_statements		= 1
#min_examined_row_limit			= 100

### Binlog ###
master_info_repository			= TABLE
sync_master_info				= 1
log_bin							= /data/mysql3310/log/binlog/mysql-bin
binlog_format   	    		= row 
binlog_rows_query_log_events    = on
max_binlog_size					= 1024M
expire_logs_days 	    		= 10

### Relay Log ###
relay_log_info_repository 		= TABLE
sync_relay_log_info 	        = 1
sync_relay_log 					= 1
relay-log						= /data/mysql3310/log/relaylog/relay-bin
relay_log_recovery				= 1
relay-log-purge					= 1

### Slave ###
skip_slave_start				= 1
# log_slave_updates				= 1
# slave_parallel-type			= LOGICAL_CLOCK
# slave_parallel_workers		= 8
# slave_preserve_commit_order	= 1
# read_only						= 1
# super_read_only				= 1
# slave_net_timeout				= 10 

### Gtid ###
#gtid_mode = on
#enforce_gtid_consistency = 1
#binlog_gtid_simple_recovery = 1

### 半同步复制 ###
#plugin_dir	 = /usr/lib64/mysql/plugin/
#plugin_load	 = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled 	= 1
#loose_rpl_semi_sync_slave_enabled 	= 1
#loose_rpl_semi_sync_master_timeout 	= 5000

### 双1 ###
innodb_flush_log_at_trx_commit 	= 1
sync_binlog 			   		= 1

### Innodb ###
innodb_data_home_dir			= /data/mysql3310/data
innodb_data_file_path 			= ibdata1:1024M:autoextend
innodb_buffer_pool_instances 	= 16  
innodb_support_xa				= 1
innodb_flush_method 		 	= O_DIRECT
innodb_file_per_table 			= 1
innodb_log_file_size 			= 4G
innodb_log_files_in_group 		= 4
innodb_buffer_pool_load_at_startup	= 1
innodb_buffer_pool_dump_at_shutdown	= 1
innodb_read_io_threads 			= 16
innodb_write_io_threads 		= 16
innodb_purge_threads	 		= 8
innodb_io_capacity 	 			= 4000
innodb_io_capacity_max 	 		= 10000
innodb_max_dirty_pages_pct	 	= 75
innodb_flush_neighbors 	 		= 0
innodb_thread_concurrency       = 128
innodb_strict_mode 	 			= 1
innodb_stats_on_metadata        = 0
innodb_print_all_deadlocks 		= 1
innodb_autoinc_lock_mode		= 2
innodb_sort_buffer_size 		= 67108864
innodb_large_prefix				= 1
innodb_page_size 	  			= 16384
innodb_open_files				= 65535
innodb_rollback_on_timeout		= 1

### Undo Log ###
#innodb_max_undo_log_size		= 1G  
#innodb_undo_tablespaces			= 4  
#innodb_undo_logs 	 			= 128 
#innodb_undo_log_truncate		= 1   

[mysqldump]
quick
max_allowed_packet  = 512M

[mysqld_safe]
open-files-limit = 65535