MySQL配置参数

目录

一.模板        

二.注释

1.基本

2.日志相关

3.双一标准（sync_binlog 和 innodb_flush_log_at_trx_commit）

4.事务

5.GTID与主从复制

6.表空间

7.优化

---

一.模板（数值仅供参考）        

一.基本
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
server_id=1
port=3306
socket=/tmp/mysql.sock
secure-file-priv=/tmp
default_storage_engine=innodb
#innodb_read_only=0
read_only=0
super_read_only=0

二.日志相关
log_error=/data/mysql/mysql.err
log_timestamps=system
innodb_print_all_deadlocks = 1

log_bin=/data/mysql/mysql-bin
#log_bin_basename
binlog_format=row
binlog_cache_size = 2M
max_binlog_cache_size = 8M
max_binlog_size=1073741824 
expire_logs_days=15 
sync_binlog=1

slow_query_log=1 
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes=1

general_log=1
general_log_file=/data/mysql/general.log

#事务（REDO）日志
innodb_log_buffer_size=128M
innodb_log_file_size=48M
innodb_log_files_in_group = 3

三.双一标准（sync_binlog 和 innodb_flush_log_at_trx_commit）
#最高安全模式
##sync_binlog=1
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT

#最高性能
##innodb_flush_log_at_trx_commit=0
##Innodb_flush_method=fsync

四.事务
autocommit=0
transaction_isolation=REPEATABLE READ

五.GTID与主从复制
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

#主主复制
auto_increment_increment=2
auto_increment_offset=1

relay_log_purge =0
slave-skip-errors = 1032,1062,1007

#从库多线程MTS
slave-parallel-type=LOGICAL_CLOCK	
slave-parallel-workers=16			
master_info_repository=TABLE		
relay_log_info_repository=TABLE
relay_log_recovery=ON

六.表空间
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64
innodb_file_per_table= 1

七.优化
skip-name-resolve
##innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_size=2048M
key_buffer_size=64M
max_connections=1024
#max_used_connections
max_allowed_packet=32M
thread_cache_size=32
query_cache_size=128M
query_cache_type=1
query_cache_limit =50M           
query_cache_min_res_unit=4096

wait_timeout=60
interactive_timeout=1200

back_log=1024
max_connect_errors=2000
sort_buffer_size=1M
join_buffer_size=2M
innodb_thread_concurrency=8
read_buffer_size =1M
read_rnd_buffer_size =1M
bulk_insert_buffer_size =8M

二.注释

1.基本

user=mysql
mysql启动时使用的用户

basedir=/usr/local/mysql
安装路径

datadir=/data/mysql
数据路径

server_id=1
标识数据库不同实例

port=3306
端口号

socket=/tmp/mysql.sock
本地socket连接的文件路径及文件名

secure-file-priv=/tmp
导入导出（select into outfile）权限路径

default_storage_engine=innodb
默认存储引擎

#innodb_read_only=0

read_only=0
只读模式开关，限定的是普通用户

super_read_only=0
只读模式开关，限定的是普通用户

2.日志相关

log_error=/data/mysql/mysql.err
错误日志的路径及文件名，默认开启

log_timestamps=system
控制错误日志、慢查询日志等日志中的显示时间。但它不会影响查询日志和慢日志写到表(mysql.general_log, mysql.slow_log) 中的显示时间

innodb_print_all_deadlocks = 1
死锁监控信息记录到错误日志的开关，1表示写进错误日志


log_bin=/data/mysql/mysql-bin
binlog日志（开关）路径及文件名

#log_bin_basename


binlog_format=row
binlog日志的格式

binlog_cache_size = 2M
为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M

max_binlog_cache_size = 8M
表示的是binlog 能够使用的最大cache 内存大小

max_binlog_size=1073741824 
max_binlog_size= 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。

expire_logs_days=15 
binlog日志文件的保留时间

sync_binlog=1
"双一标准，每次事务提交都立即刷写binlog到磁盘。
sync_binlog=0，默认，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
sync_binlog>0，表示每次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。
sync_binlog=1是最安全的，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置


slow_query_log=1 
慢查询日志开关

slow_query_log_file=/data/mysql/slow.log
慢查询日志路径及文件名

long_query_time=0.1
设置慢查询的时间

log_queries_not_using_indexes=1
没走索引的语句也记录到慢查询日志


general_log=1
常规（查询）日志开关，默认不开启，一般也不开启

general_log_file=/data/mysql/general.log
常规日志路径及文件名



#事务（REDO）日志
innodb_log_buffer_size=128M
此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

innodb_log_file_size=48M
"设置 ib_logfile0  ib_logfile1 
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能."

innodb_log_files_in_group = 3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

3.双一标准（sync_binlog 和 innodb_flush_log_at_trx_commit）

#最高安全模式
##sync_binlog=1

innodb_flush_log_at_trx_commit=1
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个
1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush  到操作系统的文件系统缓存  fsync到物理磁盘；
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次；
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。

Innodb_flush_method=O_DIRECT
控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存，
O_DIRECT  :数据缓冲区写磁盘,不走OS buffer；
fsync :日志和数据缓冲区写磁盘,都走OS buffer；
O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer


#最高性能
##sync_binlog=1
##innodb_flush_log_at_trx_commit=0
##Innodb_flush_method=fsync

4.事务

autocommit=0
关闭事务自动提交

transaction_isolation=REPEATABLE READ
事务隔离级别

5.GTID与主从复制

gtid-mode=on
启用gtid类型，否则就是普通的复制架构

enforce-gtid-consistency=true
强制GTID的一致性

log-slave-updates=1
slave更新是否记入日志


auto_increment_increment=2
自增步长，主主复制中，一般有n台主MySQL就填n

auto_increment_offset=1
自增偏移量，主主复制中，一般第m台主MySQL就填m。


relay_log_purge =0
当 relay_log_purge=1时，旧relay logs会在SQL线程执行完毕后被自动删除；
当 relay_log_purge=0时，旧的 relay log 则会被保留。虽然这并不会影响从库复制本身，但还是会有坑：由于崩溃或停止 MySQL 时，SQL 线程可能没有执行完全部的 relay log，最后一个 relay log 中的一部分数据会被重新下载到新的文件中。也就是说，这部分数据重复了两次。如果 SQL 跟得很紧，则可能在 IO 线程写入 relay log ，但还没有将同步到磁盘时，就已经读取执行了。这时，就会造成新的文件和旧的文件中少了一段数据。

slave-skip-errors = 1032,1062,1007
从库重做SQL出错时设置跳过的错误操作编号
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突"


#从库多线程MTS
slave-parallel-type=LOGICAL_CLOCK	
本地时钟，支持基于事务级别的sql并发回放

slave-parallel-workers=16	
并发回放线程个数，取cpu核心个数的一半
	
master_info_repository=TABLE	
master_info以表的方式存储信息

relay_log_info_repository=TABLE
relay_log_info以表的方式存储信息

relay_log_recovery=ON
开启relay_log的恢复功能

6.表空间

innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
共享表空间文件设置，8.0前undo log存储在共享表空间

innodb_autoextend_increment=64
共享表空间自增大小设置，初始化前可写入cnf

innodb_file_per_table= 1
开启独立表空间模式

7.优化

*****
skip-name-resolve
禁止 MySql Server 对外部连接进行 DNS 解析，使用这一选项可以消除 MySql 进行 DNS 解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求

##innodb_flush_log_at_trx_commit=1

innodb_max_dirty_pages_pct=75
最大脏页的百分数，当内存中脏页所占百分比超过这个值，INNODB就会进行写操作以把页中的已更新数据写入到磁盘文件中，默认值为75

innodb_buffer_pool_size=2048M
"缓冲区池，一般建议最多是物理内存的75-80%，InnoDB使用该参数指定大小的内存来缓冲数据和索引。
对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。"

key_buffer_size=64M
指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度

max_connections=1024
最大连接数

#max_used_connections
合理范围0.9max<=$<max

max_allowed_packet=32M
最大的数据包大小，有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数

thread_cache_size=32
服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。

query_cache_size=128M
查询缓存大小 (注：QC存储的最小单位是1024byte

query_cache_type=1
"缓存类型，如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。
如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。"

query_cache_limit =50M 
超过此大小的查询将不缓存
          
query_cache_min_res_unit=4096
缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费


****
wait_timeout=60
指的是mysql在关闭一个非交互的连接之前所要等待的秒数

interactive_timeout=1200
指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。


***
back_log=1024
"mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它…...依据 show full processlist发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值"

max_connect_errors=2000
负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系

sort_buffer_size=1M
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 、GROUP BY、distinct、union

join_buffer_size=2M
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。尽量在SQL与方面进行优化，效果较为明显。优化的方法：在join查询的on条件列加索引，至少应当是有MUL索引

innodb_thread_concurrency=8
此参数用来设置innodb线程的并发数量，默认值为0表示不限制。

read_buffer_size =1M
MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

read_rnd_buffer_size =1M
MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

bulk_insert_buffer_size =8M
批量插入数据缓存大小，可以有效提高插入效率，默认为8M