【MySQL运维】MySQL 5.7配置文件详细优化示例

tanglu 5548 2020-09-25

一、作用域相关配置

[mysqld]:服务端选项作用域,大部分配置都在该作用域下

[mysql]:mysql客户端作用域,该作用域下的配置仅对mysql这个命令客户端生效

[client]:其他客户端的作用域。该作用域下的配置对mysqladmin、myqsldump等客户端命令都生效。由于不是所有客户端都有相同的配置,可能会因为多余的配置导致启动报错

[mysqldump]:mysqldump客户端作用域

[mysqladmin]:mysqladmin客户端作用域

[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

extra_port:额外的连接端口,通常用于连接数用光后给root账号处理使用   

extra_max_connections:额外的连接数,配合extra_port使用

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反解析成域名(比如127.0.0.1被解析成localhost),这样使用IP来做权限控制更方便
server_id:主从复制会用到,每个节点ID不能相同,可以避免双主架构带来的循环复制问题

tmpdir:临时表路径,如tmpdir=/data/mysqltmp/mysql3306

max_connect_errors:单个客户端连接失败的最大次数,密码错误或者网络问题产生的连接失败都会计数,超过该次数就会被服务永久拒绝连接,直到使用mysqladmin flush-hosts才会恢复

max_user_connections:每一个用户所能产生的最大连接数,默认为0不限制。设置该值后,可以避免某个程序连接池配置不合理导致大量连接被占用

back_log:如果客户端连接数达到上限后,允许多少个客户端进入一个队列排队

max_allowed_packet:MySQL与客户端建立连接后能传输的数据包限制,如果一个包含大批量数据更新的SQL超过了该参数的限制就会失败。最大可配置为1G

time_zone:显示指定时区,默认为SYSTEM,即系统的操作时区。对于国内业务建议配置为default-time-zone='+08:00',其他地区和开发确认取对应时区即可。明确时区相比设置时区为system的好处有2个,1是system多了一层系统调用,对性能有轻微损耗;2是操作系统的时区设置可能不归DBA管,可能会因为运维人员的疏忽导致配置不当,如果设置的时区不正确则会导致NOW()、CURTIME()这类系统函数以及timestamp类型字段存储的数据和理想值不一样

performance_schema:显式启用performance_schema数据库,该选项在5.7开始默认是启用的

lower_case_table_names:建议设置为1,表示不对表名进行大小写区分

init_connect:设置每个会话连接后自动执行的命令,比如SET NAMES或者SET AUTOCOMMIT


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

max_connections:客户端最大连接数,超过该连接后就会出现Too many connections的错误。如果没有配置extra_port参数,那么root账号还能连接一次。该配置建议参考show status like '%Threads_running%'的值进行调整,不能盲目调大,因为MySQL申请内存的计算方式是max_connections*(sort_buffer_size+read_buffer_size+read_rnd_buffer_size+join_buffer_size+binlog_cache_size+thread_stack),也就是说每个客户端连接后还要单独申请内存空间,如果程序配置存在不合理的现象很容易OOM

sort_buffer_size:排序语句生成的结果会临时存放到内存空间中,默认256K,建议4-8M。如果查询结果超过这个大小则会在硬盘生成临时表进行存放,调大后可以提升order by语句的效率。经过Percona测试发现盲目调大会起到反效果,通常Sort_merge_passes/(Sort_scan+Sort_ range)>2说明sort_buffer_size设置偏小

show global status like '%sort%'

read_buffer_size:顺序读缓冲区大小,默认128K,建议调整为4-8M
read_rnd_buffer_size:随机读缓冲区大小,默认256K,建议调整为4-8M
join_buffer_size:默认256K,建议调整为4-8M
binlog_cache_size:当事务产生的binlog超过该参数值就会使用临时文件来存放binlog cache,默认32K,适当调大可以加快binlog写入效率

thread_stack:每个线程连接时申请的内存空间,默认256K
key_buffer_size:默认64M
tmp_table_size:内存临时表的最大值,默认64M,临时表可以提升group by和联合查询的性能。如果内存临时表超过该参数限额,则开始生成基于磁盘的临时表(tmp_disk_tables),此时查询性能是有明显影响的。通过show status like '%tmp%' 查看tmp_disk_tables的值,如果增长很快说明tmp_table_size设置过小,可以适当调大。需注意该参数还受到max_heap_table_size的影响,如max_heap_table_size设置得比tmp_table_size小,那max_heap_table_size的值才是最大的内存临时表的上限

max_heap_table_size:用户可以创建的内存临时表大小


3、数据库超时配置

interactive_timeout:交互模式下会话超时时间,比如mysql客户端,单位为秒,建议600-1800秒之间
wait_timeout:应用会话连接超时时间,比如jdbc,单位为秒,默认为8小时。应用程序在连接成功后如果没有后续操作,则连接处于Sleep空闲状态,建议600-1800秒之间

rpl_stop_slave_timeout:停止从库时的超时时间,如果超时会报错,避免从库回滚大事务一直卡住。如果从库存在大事务建议先直接停止mysql,增加索引后再stop slave

innodb_lock_wait_timeout:InnoDB行锁等待超时时间,比如事务A对某行数据进行修改但未提交,此时事务B也修改该行数据时就会被锁,直到超时。该值默认50,建议5-20秒

lock_wait_timeout:元数据锁超时时间,默认是1年,设置30秒足够

max_execution_time=60000:控制查询语句执行的最长时间,单位是毫秒。避免单个语句意外执行太长时间,比如一些程序框架会使用begin\commit声明事务或者代码错误的把SELECT语句放入到事务中


4、数据库日志信息

· errorlog

log_error:错误日志路径,如/data/mysql/logs/error.log


· slowlog
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等语句引发的慢查询


· binlog
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,和系统时间一致


· relaylog

relay-log:relaylog日志路径与文件名,如/data/mysql/logs/relay-bin

relay-log-purge:是否自动清理已经应用完的relaylog,1为自动清理  

relay_log_recovery:建议为1,开启中继日志恢复模式,可以保证数据一致性。如果从库宕机导致当前中继日志损坏的话,就放弃该日志并重新从master上获取

max_relay_log_size:单个中继日志文件的大小,如果没有设置的话会按照max_binlog_size的大小进行滚动


· Redo Log

innodb_log_file_size(需重启数据库才能生效):redo log日志大小,默认是48M,SSD硬盘建议配置到4G。如果设置太小会导致数据库经常flush日志到磁盘影响性能,配置越大写操作的效率越高,但是崩溃恢复时间会稍长一点

innodb_log_buffer_size:redo log缓存区域大小,刷新缓存的几种情况:master thread每秒刷新buffer、buffer写满时刷新、事务提交时刷新、服务重启时刷新,建议32-64M

innodb_log_files_in_group:redo log数量,默认2,最多支持4个,redolog日志命名为ib_logfile0~ib_logfileN


5、双1参数

innodb_flush_log_at_trx_commitredolog刷盘策略,建议为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个事务


6、主从复制相关配置

log_slave_updates:开启该选项后从库也会记录binlog,否则从其它库同步的数据不会写入binlog(如果从库配置了binlog而没有开启该选项则binlog为空)。不承载高可用的从库可以关闭节约IO性能,如果需要级联复制的场景可以开启

slave_parallel_workers:该值为0代表单线程复制,大于1时表示启用多线程复制。为了提升从库复制效率通常和CPU核数一致,该变量动态设置后需要重启复制线程才会生效。并行复制开启后通过processlist看到状态为system lock的system user用户就是并行复制的线程

slave_parallel-type:并行复制方式,5.7之前是基于DATABASE级别的粒度进行并行复制,并行效率很差。而5.7开始采用LOGICAL_LOCK实现了GroupCommit并行回放,同一个Group内的事务可以并行执行。在binlog中通过last_committed的值来标记事务所属的组,如果两个事务last_committed值一致,这两个事务就属于同一个组

slave_preserve_commit_order:保证从库回放事务的顺序与主库提交的顺序完全一致。比如主库执行了A、B、C 3个事务,且last_commited相同,从库满足并行回放的条件。但是如果slave_preserve_commit_order没有开启的话,则顺序可能是乱的。该选项动态开启时需要先停止SQL线程,并且在8.0.19以前从库必须同时开启log_bin和log_slave_updates才能进行配置

binlog_group_commit_sync_delay:并行复制时延迟多少微妙后才会调用fsync

binlog_group_commit_sync_no_delay_count:多线程复制时累计多少次以后就调用fsync

transaction_write_set_extraction(该配置在5.7.22开始支持):指定多线程复制时的HASH算法,建议配置为XXHASH64

binlog_transaction_dependency_tracking(该配置在5.7.22开始支持):指定多线程复制时并行事务依赖检查的方法,默认为COMMIT_ORDER,表示只有事务是同一时间提交的前提下才能生成一组相同的last_committed形成并行回放。而使用WRITESET_SESSION后可以实现"只要事务不存在锁冲突,即便提交的时间不相同、会话不相同,也可以归到一个组中进行提交"的效果

relay_log_info_repository:建议设置为table,将从库中继日志执行点等信息写入mysql.slave_relay_log_info表中,否则默认为file,写入的是relay_log.info文件中

master_info_repository:建议设置为table,将从库与主库的连接状态和用户账号、密码等配置信息写入到mysql.slave_master_info表中,否则默认为file,写入的是master.info文件中


7、InnoDB信息

· InnoDB性能

innodb_buffer_pool_size:MySQL最重要的性能参数,该变量用于设置数据和索引的缓存区大小,使用缓存区种的数据就可以避免与磁盘产生大量交互,从而提升效率,该变量建议设置为系统最大内存的70%(注意该变量的值必须是innodb_buffer_pool_instance * innodb_buffer_pool_chunk_size(默认128M,不能动态调整)的倍数)。5.7开始支持动态配置,但是在线修改的话值只能为字节格式,配置文件可以使用M、G单位。通过SHOW GLOBAL STATUS LIKE '%innodb%'命令查看MySQL剩余buffer,如果Innodb_buffer_pool_pages_free值很小或为0说明buffer_pool已经用光了,而Innodb_buffer_pool_wait_free不为0,说明产生过对应次数的等待内存释放行为。这2种情况都需要增加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不限制。如果为了避免并发太多导致CPU跑满可以设置一个合理值。由于锁等待产生的并发并不会带来CPU开销,所以这些并发不会占用这个连接名额,避免锁等待而让数据库不可用

innodb_strict_mode:开启严格模式,可以避免非空字段插入null等情况

innodb_stats_on_metadata:建议为off关闭动态统计被触发

innodb_io_capacity:脏页正常刷新频率,单位为页,默认200即代表每次刷新200页(每个页默认16K)。如果磁盘速度快就可以调大,建议设置为磁盘IOPS的一半或更高,SSD的话通常可以达到1W+,可以通过fio来测试磁盘IO

innodb_io_capacity_max:脏页最大刷新频率,建议和磁盘IOPS一样

innodb_flush_neighbors:默认为1代表刷脏页时会把邻近脏页一起刷到磁盘。对于机械硬盘能够减少磁盘寻道的开销,提升性能;但是对于SSD提升很小,建议为0关闭
innodb_file_per_table:是否开启独立表空间,5.6开始默认打开,通常也建议打开。开启后每个表数据都会单独存放在一个以表命名后缀为.ibd文件中分散IO提升性能,也便于表的维护,否则所有表数据会全部存放在ibdata1文件中,即便删除了某个表,这个文件空间也不会释放,只是将表空间被标记为可复用

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文件到内存进行缓存,提升性能

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缓存了

innodb_adaptive_hash_index:自适应哈希索引,建议关闭,避免因为哈希索引带来的锁竞争


· 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


四、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
extra_port                               = 13310
extra_max_connections          = 10
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
#max_user_connections                            = 100
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
sql_mode="NO_ENGINE_SUBSTITUTION"
skip-character-set-client-handshake = 1
skip-name-resolve
skip-ssl
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
rpl_stop_slave_timeout                  = 180

### 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 ###
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 = /data/mysql3310/log/relaylog/relay-bin
relay_log_recovery = 1
relay-log-purge = 1

### Replication ###
master_info_repository        = TABLE
relay_log_info_repository = TABLE
skip_slave_start = 1
log_slave_updates = 1
slave_parallel-type        = LOGICAL_CLOCK
slave_parallel_workers        = 8
slave_preserve_commit_order = 1
slave_net_timeout = 10 
# read_only = 1
# super_read_only = 1
transaction_write_set_extraction     =XXHASH64
binlog_transaction_dependency_tracking = WRITESET_SESSION

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

### 半同步复制 ###
plugin_dir=/usr/local/mysql/lib/plugin                                                                                                                                                                          
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"                                                                                                                    
rpl_semi_sync_master_enabled=1                                                                                                                                                                                  
rpl_semi_sync_slave_enabled=1                                                                                                                                                                                  
rpl_semi_sync_master_wait_no_slave=OFF                                                                                                                                                                          
rpl_semi_sync_master_wait_for_slave_count=1                                                                                                                                                                    
rpl_semi_sync_master_wait_point=AFTER_SYNC                                                                                                                                                                      
rpl_semi_sync_master_timeout=3000    

#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    = 10000
innodb_io_capacity_max    = 20000
innodb_max_dirty_pages_pct   = 75
innodb_flush_neighbors    = 0
innodb_thread_concurrency                   = 0
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
innodb_adaptive_hash_index          = 0

### 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



版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!
上一篇:【MySQL运维】show语句的使用
下一篇:使用ulimit、limits.conf与/proc/$pid/limits对进程资源进行限制
相关文章

 发表评论

评论列表

2020-11-24 14:19:11

请问 ,咱们站点的广告在哪里, 我给你点点.

2020-11-24 15:23:07

@silent3035 @silent3035:感谢 在网站右侧标签栏下方有2个广告区

微信二维码