本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • 【MySQL运维】my.cnf配置文件常用选项与优化配置

【MySQL运维】my.cnf配置文件常用选项与优化配置

发布:TangLu2020-9-25 15:15分类: MySQL 标签: mysql 数据库

my.cnf线上配置示例

[client]
port = 3307
socket = /data/mysql3307/mysql.sock

[mysql]
no-auto-rehash
default-character-set = utf8mb4

[mysqld]
user    = mysql
port    = 3307
basedir = /usr/local/mysql
datadir = /data/mysql3307
socket  = /data/mysql3307/mysql.sock
tmpdir  = /data/dbtmp/mysql3307
character_set_server = utf8mb4
skip-character-set-client-handshake = 1
skip-name-resolve
default-storage-engine = InnoDB
default-time-zone = "+8:00"
server-id = IP后两段
back_log            = 1024
connect_timeout     = 20
interactive_timeout = 28800
wait_timeout        = 28800
lock_wait_timeout   = 3600
max_execution_time  = 1800000
transaction_isolation = READ-COMMITTED
max_allowed_packet  = 512M
skip-external-locking
open_files_limit    = 65535
max_connections     = 1000
max_connect_errors  = 1000
sort_buffer_size    = 2M
read_buffer_size    = 2M
read_rnd_buffer_size = 2M
join_buffer_size     = 2M
binlog_cache_size    = 2M
key_buffer_size      = 64M
tmp_table_size       = 64M
max_heap_table_size  = 64M
table_open_cache     = 8192
table_definition_cache 	= 8192
group_concat_max_len 	= 10240
#skip-grant-tables
#init_connect ='set names utf8mb4'
#explicit_defaults_for_timestamp = 0
#sql_mode = "NO_ENGINE_SUBSTITUTION"
#optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off"

### Error log 
log_error = /data/dblog/mysql3307/error.log

### Binlog
master_info_repository 	  	= TABLE
log_bin = /data/dblog/mysql3307/binlog/mysql-bin
expire_logs_days 	    	= 10
binlog_format   	    	= row 
binlog_rows_query_log_events    = on
max_binlog_size 	 	= 1024M

### Relaylog
relay_log_info_repository 	= TABLE
relay-log = /data/dblog/mysql3307/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
# read_only = 1
# super_read_only = 1

### Slow log
slow_query_log 			= 1
slow_query_log_file = /data/dblog/mysql3307/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
log_timestamps = system

### 双1
innodb_flush_log_at_trx_commit 	= 1
sync_binlog 	   		= 1

### Innodb
innodb_data_home_dir = /data/mysql3307
innodb_data_file_path 		= ibdata1:1024M:autoextend
innodb_buffer_pool_size = (内存*0.6)G
innodb_buffer_pool_instances 	= 16  
innodb_flush_method 	 	= O_DIRECT
innodb_file_per_table 		= 1
innodb_log_file_size 		= 4G
innodb_log_files_in_group 	= 3
innodb_lock_wait_timeout 	= 10
innodb_buffer_pool_load_at_startup	= 1
innodb_buffer_pool_dump_at_shutdown	= 1
innodb_log_buffer_size = 32M
innodb_read_io_threads 		= 16
innodb_write_io_threads 	= 16
innodb_purge_threads	 	= 8
innodb_undo_logs 	 	= 128
innodb_undo_tablespaces 	= 0
innodb_io_capacity 	 	= 4000
innodb_io_capacity_max 	 	= 10000
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_undo_log_truncate = 1

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

一、作用域相关配置

[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

basedirMySQL安装路径,如basedir=/usr/local/mysql
datadirMySQL数据文件存放路径,如datadir=/data/mysql/data
port:MySQL实例端口,如port=3306
socketMySQL 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超过了该参数的限制就会失败


2、数据库内存信息

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:默认2M
read_buffer_size:默认0.128M
read_rnd_buffer_size默认0.256M
join_buffer_size默认0.128M
binlog_cache_size:默认0

thread_stack:每个线程连接时申请的内存空间,默认0.2M
key_buffer_size:默认64M
tmp_table_size:内存临时表的最大值,默认64M。临时表超过该值会生成基于硬盘的临时表。调大该值可以提高一些复杂的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等语句引发的慢查询


· binlog
log_binbinlog日志路径,如/data/mysql/logs/master-bin

log_bin_index:binlog索引文件路径,如/data/mysql/logs/master-bin.index
binlog_formatbinlog日志格式,推荐row

binlog_rows_query_log_events:将所执行的原始sql语句记录到日志中,更便于分析

expire_logs_daysbinlog过期天数
max_binlog_size单个binlog文件大小
binlog_cache_sizebinlog缓存配置,和性能优化相关

log_timestamps:默认为UTC时间,这样查看日志会存在时区问题,建议修改为SYSTEM,和系统时间一致


4、InnoDB信息

sync_binlog:双1参数。事务落盘策略,建议为1,代表每次有事务提交都刷新日志到磁盘中

innodb_buffer_pool_sizeMySQL最重要的性能参数,通过该项来设置数据和索引的缓存区大小,提升效率。建议设置为系统最大内存的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_log_at_trx_commit:双1参数。redolog落盘策略,建议为1。0是每秒刷新一次redo buffer到磁盘,当服务崩溃可能丢失1秒数据;1是每次事务提交时都立即刷新到磁盘;2是每次事务提交都立即刷新到os cache中,随后刷新到磁盘,机器断电才会丢失1秒数据
innodb_flush_method=O_DIRECT默认为fsync,代表刷数据到磁盘的时候会先申请系统级别的缓存,这样性能虽然会更好但是会申请额外的内存。由于MySQL除了innodb_buffer_pool_size占用内存,每个连接还会单独申请内存空间,再加上fsync申请的内存,很容易导致OOM。改为O_DIRECT会跨过系统缓存直接写到磁盘。

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

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

innodb_log_file_sizeredo log日志大小,默认是48M。日志命名为ib_logfile0~ib_logfileN。如果设置太小会导致数据库经常flush日志到磁盘影响性能,配置越大写操作效率越高,但出现崩溃等意外时恢复时间就更长,小业务200M就够用,中型业务2G左右

innodb_log_buffer_sizeredo log缓存区域大小

innodb_log_files_in_group:redo log有多少个,默认为2

innodb_io_capacity=2000:脏页刷新速度,单位为页。磁盘速度越快就设置越大,默认是200,建议和磁盘IOPS一样
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_flush_neighbors:默认为1,在刷脏页时会把邻近脏页一起刷到磁盘,对于机械硬盘能够减少磁盘寻道的开销,显著提升性能,对于SSD提升很小

温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《【MySQL运维】my.cnf配置文件常用选项与优化配置
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-631.html  [生成短网址]

已有 2/1745 人参与

评论:

silent3035 2020-11-24 14:19
请问 ,咱们站点的广告在哪里, 我给你点点.
TangLu 2020-11-24 15:23
@silent3035:感谢 在网站右侧标签栏下方有2个广告区

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~