mysql参数调优

[client]
port=3306
socket=/tmp/mysql.sock
default-character-set=utf8

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

[mysqld]
port=3306
character-set-server=utf8
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/mydata/data
#如果你的mysql版本高于5.6.6，关于timestamp为null的时候，可能会报错
explicit_defaults_for_timestamp=true
lower_case_table_names=1
#如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500
back_log=103
max_connections=10000
max_connect_errors=100000

#缓存
table_open_cache=512
#文档上说，增加table_open_cache，会增加文件描述符，当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上，引用文档上的一句话：
If table_open_cache is set too high, MySQL may run out of file descriptors and refuse connections, fail to perform queries, and be very unreliable.
external-locking=FALSE
sort_buffer_size=2M
join_buffer_size=2M
#发现 thread_cache_size 原来设置好小默认的是0，这个默认值实质上也偏小，建议大的web项目，要调大这个值。
thread_cache_size=51
#MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化
query_cache_size=32M
#query_cache_limit=4M
transaction_isolation=REPEATABLE-READ
#tmp_table_size 控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定
#max_heap_table_size 用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。
tmp_table_size=96M
max_heap_table_size=96M
sync_binlog = 1 #表示每次事务提交

###***slowqueryparameters
long_query_time=2
slow_query_log = 1
slow_query_log_file=/mydata/data/slow.log

replication

log-bin=mysql-bin
server-id=111
expire_logs_days = 7 #日志过期时间，设置为0则永不过期
binlog_format = ROW #复制模式
max_binlog_size = 1000M #超过max_binlog_size
binlog_cache_size = 16M
#二进制日志缓冲大小,通过show status like ‘binlog_%’;查看调整写入磁盘的次数，写入磁盘为0最好
max_binlog_cache_size = 256M
relay_log_recovery = 1
#当slave从库宕机后，假如relay-log损坏了，
#导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，
#并且重新从master上获取日志，这样就保证了relay-log的完整性。
gtid_mode = on
enforce_gtid_consistency = 1
#关闭从库同步到主库
log_slave_updates   = 0
#relay.info明文存储不安全，把relay.info中的信息记录在table中相对安全。
#可以避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错。
master-info-repository=TABLE
relay-log-info-repository=TABLE

#***MyISAMparameters
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M

#  innodbstorageengineparameters
innodb_buffer_pool_size=4G #推荐内存的50%-75%
innodb_data_file_path=ibdata1:1G:autoextend
innodb_open_files = 500

限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300

innodb_write_io_threads = 16
innodb_read_io_threads = 16

innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4

注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从 1-64

innodb_thread_concurrency = 0

默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量

innodb_purge_threads = 1

InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中，清除操作是主线程的一部分，这意味着运行时它可能会堵塞其它的数据库操作。

从MySQL5.5.X版本开始，该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单

独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1

innodb_flush_log_at_trx_commit = 2

0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。

主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1

1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。

2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。

每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘

默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。

设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。

总结

设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能

innodb_log_buffer_size = 4M

此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间

innodb_log_file_size = 32M

此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3

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

innodb_max_dirty_pages_pct = 90

innodb主线程刷新缓存池中的数据，使脏数据比例小于90%

innodb_lock_wait_timeout = 120

InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒

[mysqldump]
quick
max_allowed_packet=32M

[mysqld_safe]
open-files-limit=10240
log-error=/mydata/data/error.log
#pid 亦即 Process ID
pid-file=/mydata/data/mysqld.pid