一、作用域相关配置
- [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