本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • 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-name-resolve
default_time_zone = "+8:00"
server-id = IP后两段
max_connections = 5000
max_connect_errors = 5000
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
join_buffer_size = 16M
read_buffer_size = 16M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
key_buffer_size = 16M
tmp_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 & REPL
log_bin = /data/dblog/mysql3307/binlog/mysql-bin
relay-log = /data/dblog/mysql3307/relaylog/relay-bin
expire_logs_days 	  	= 10
binlog_format   	  	= row 
max_binlog_size 		= 1024M
binlog_cache_size 		= 4M
binlog_stmt_cache_size  = 1M
master_info_repository 	  	= TABLE
relay_log_info_repository 	= TABLE
relay_log_recovery = 1
relay-log-purge = 1
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

### 双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.8)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命令生效。

[client]:其他客户端作用域,该作用域下的配置对mysqladmin、myqsldump等客户端命令都生效。但并不是所有客户端都有相同的配置,如果配置多了会导致配置异常而无法启动服务。

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


、客户端相关配置

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

default-character-set:客户端字符集设置,建议和服务端一致,如character-set=utf8mb4


三、服务端相关配置

1、数据库基本信息

basedirMySQL安装路径,如/usr/local/mysql
datadirMySQL数据文件存放路径,如/data/mysql/data
port:MySQL实例端口,如3306
socketMySQL sock文件路径,如/tmp/mysql.sock 

character-set-server:字符集设置,如utf8mb4
default-storage-engine:默认存储引擎,如INNODB
skip-name-resolve:不把客户端的IP反向解析成域名,直接用IP来做权限判断即可
server_id:主从复制会用到,每个节点ID不能相同


2、数据库日志信息

· 错误日志

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
expire_logs_daysbinlog过期天数
max_binlog_size单个binlog文件大小
binlog_cache_sizebinlog缓存配置,和性能优化相关


3、数据库性能相关

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

innodb_flush_log_at_trx_commit:双1参数。redolog落盘策略,建议为1。0是每秒刷新一次redo buffer到磁盘,当服务崩溃可能丢失1秒数据;1是每次事务提交时都立即刷新到磁盘;2是每次事务提交都立即刷新到os cache中,随后刷新到磁盘,机器断电才会丢失1秒数据
innodb_buffer_pool_sizeMySQL最大的内存区域,也是最重要的性能参数。用于设置数据和索引缓存大小,没有在缓存中找到数据则会读取硬盘,降低效率。建议建议设置为系统最大内存的80%。支持动态配置,在线修改的话只只能设置为字节格式,通过配置文件修改则可以加M、G等单位
innodb_flush_method=O_DIRECT默认为fsync,代表刷数据到磁盘的时候会先申请系统级别的缓存。但是MySQL中除了innodb_buffer_pool_size会占用内存,每个连接还会单独申请内存空间,再加上fsync申请的内存,容易导致OOM。改为O_DIRECT会跨过系统缓存直接写到磁盘。
innodb_log_file_sizeredo log日志大小,文件会自动命名为ib_logfile0~ib_logfileN。如果设置太小会导致数据库经常flush日志到磁盘影响性能,配置越大写操作效率越高,但出现崩溃等意外时恢复时间就更长,小业务200M就够用,中型业务2G左右

innodb_log_files_in_group:redo log有多少个

innodb_io_capacity=2000:脏页刷新速度,单位为页。磁盘速度越快就设置越大,默认是200,建议和磁盘IOPS一样
innodb_file_per_table=ON是否开启独立表空间,开启后每个表数据都会单独存放在一个以表命名后缀为.ibd文件中。5.6开始默认打开,通常也会打开。可以分散IO提升性能,也便于表的维护,否则所有表数据会全部存放在ibdata1文件中,即便删除了某个表,这个文件空间也不会释放,只是将表空间被标记为可复用。
innodb_lock_wait_timeout:InnoDB行锁等待超时时间,默认50,建议5-20秒

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

interactive_timeout=3600交互模式下会话超时时间,单位为秒
wait_timeout=3600应用会话连接超时时间,单位为秒,默认为8小时。应用程序在连接成功后如果没有后续操作,则连接处于Sleep空闲状态

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还在不断打开表。这个使用缓存是没有起到作用的,所以需要调大缓存区的值,但是不能大于系统文件描述符。

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

已有 2/545 人参与

评论:

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

发表评论:

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

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