MySQL5.6配置项及可能的优化

一、操作系统层面

1.1、关闭大页内存

大页内存是为了满足某些需要快速获取大量内存的程序，但对MySQL、Redis、MongoDB是不需要的：

echo "never" > /sys/kernel/mm/transparent_hugepage/enabled
echo "never" > /sys/kernel/mm/transparent_hugepage/defrag

将两条命令写入到/etc/rc.local永久生效。

1.2、禁用swap

临时关闭swap：

echo 0 > /proc/sys/vm/swappiness

永久禁用：在/etc/sysctl.conf添加vm.swappiness=0

1.3、IO调度策略

在CentOS6系统中，需要调整IO调度策略：

echo "deadline" > /sys/block/sda/queue/scheduler

在CentOS7系统中，默认值就是“deadline”。

1.4、文件系统

尽量使用ext4或xfs类型的文件系统，避免使用lvm。

二、参数说明

参数	说明
port=3306	MySQL进程使用的端口。
bind_address=0.0.0.0	MySQL绑定的地址。
user=mysql	指定MySQL使用的用户。
basedir=/usr/local/mysql	MySQL主目录。
datadir=/usr/local/mysql/data	MySQL数据目录。
socket=/usr/local/mysql/mysql.sock	MySQL套接字文件。
character_set_server=utf8	MySQL服务端默认字符集。
skip_name_resolve	跳过域名解析。
open_files_limit=65535	可以使用的最大文件描述符。
log_error=error.log	记录错误日志的文件。
max_connections=1000	MySQL的最大连接数。MySQL会为每个连接提供内存缓冲区，连接数越多，消耗的内存越多。在开启数据库时，可以先设置一个较大的值，之后通过’show status like “%connect%”'观察“max_used_connections”来调整。
back_log=500	MySQL能暂存的连接数。如果MySQL的连接数达到“max_connections”，新的请求会被放在堆栈中等待已有连接释放资源，而不是直接拒绝连接。
thread_cache_size=32	服务器线程缓存，表示可以重新利用的保存在缓存中的线程的数量。当一个客户端断开连接时，客户端的线程将被放到该缓存中，以响应下一个客户端连接，而不是直接销毁已经断开的连接。一般1G内存的服务器配置为4，2G内存服务器配置为8。
max_connect_errors=20	安全相关参数，连接失败的错误次数。如密码暴力破解等。当达到该次数，MySQL将禁止该主机的连接，直到MySQL重启，或通过“FLUSH HOSTS”命令清空主机信息。
wait_timeout=60	MySQL在关闭一个非交互的空闲连接前等待的秒数。如果该值过小，将导致一些持久连接不起作用。如果太大，在“SHOW PROCESSLIST”命令执行时，能看到很多“sleep”连接。一般希望该值尽可能的低。
interactive_timeout=600	MySQL在关闭一个交互连接前等待的秒数。比如在终端上进行MySQL管理，超过这个时间没有操作，将自动断开与MySQL的连接。
connect_timeout=60	连接MySQL的超时时间。
slow_query_log	记录慢查询日志。
long_query_time=1	超过这个时间的查询被认为是慢查询。
slow_query_log_file=slow.log	记录慢查询日志的文件。
log_queries_not_using_indexes	将没有使用索引的查询也当做是慢查询。
server_id=1	用于主从复制时的唯一主机ID。
log_bin=mysql-bin	开启记录二进制日志，及二进制日志文件的前缀，文件后缀如“000001”。
binlog_format=row	二进制日志记录格式。“statement”格式将完整记录SQL语句；“row”以事件形式记录数据变化，相对严谨。
max_binlog_size=256M	一个二进制日志文件的大小，文件大小超过这个值，将在新的文件中继续记录二进制日志。
binlog_cache_size=2M	在每个事务中用来存储二进制日志的缓存大小。如果事务不大，且DML不频繁，可以设置为1M。否则，可以调整为2-4M。
max_binlog_cache_size=8M	二进制日志能够使用的最大内存。
expire_logs_days=8	二进制日志文件过期时间，将会被自动清理。
sync_binlog=1	设置为0时，将由操作系统决定何时将二进制日志写入到磁盘；设置为1，每次事务提交时都会刷写二进制日志到磁盘。在主从复制模式中，可以大大减少由于binlog写入不及时导致的主从不同步问题。
gtid_mode=on	启用GTID主从复制模式。
enforce_gtid_consistency=true	强制GTID的一致性。
log_slave_updates=1	主从复制模式中，设置为1时，从库的数据更新也会记入二进制日志。多用于双主模式或MHA。
relay_log_purge=0	主从复制模式中，设置为0时，将不会自动清理中继日志relay log。
max_allowed_packet=16M	安全相关参数，MySQL服务器能接收的最大网络数据包。
key_buffer_size=64M	MyISAM表索引缓冲区大小，决定索引处理速度，尤其是索引读的速度。在InnoDB存储引擎中，临时表（多表联结，子查询，union都会产生临时表）的创建也依赖该参数。内存不够时，将在硬盘上创建临时表：ibdata1(MySQL5.6)；ibtemp1(MySQL)。通过‘show status like “key_read%”’命令：key_read_requests-索引读取请求次数；key_reads-在内存中没有找到，直接从硬盘中读取的次数。通过‘show status like “created_tmp%”’：Created_tmp_disk_tables-在硬盘上创建临时表；Created_tmp_tables-在内存中创建临时表。通常将Created_tmp_tables/(Created_tmp_disk_tables+Created_tmp_tables) 控制在5%-10%。
query_cache_size=0	查询缓存大小，将SELECT查询的结果保存在缓存中，从而加快下一次SELECT查询的速度。设置为0时，表示禁用查询缓存。
sort_buffer_size=128k	为需要进行排序的线程分配的缓冲区，ORDER BY、GROUP BY、DISTINCT、UNION等语句。
join_buffer_size=128k	JOIN缓冲区。
read_buffer_size=128k	读入缓冲区大小。对表的顺序扫描的请求将分配读入缓冲区。
read_rnd_buffer_size=128k	随机读（查询操作）缓冲区大小。以上四个参数会给每个连接分配单独的内存。
autocommit=1	开启事务的自动提交。
transaction_isolation=repeatable-read	InnoDB存储引擎中事务的隔离级别。read-uncommitted：允许事务查看其它事务未提交的更改，基本不用；read-committed：允许事务查看其它事务已提交的更改，但会导致“幻读”现象；repeatable-read：可重复读，确保每个事务的SELECT输出完全一致，防止“幻读”现象。通过“undo”中的“快照”实现读一致性，是默认的隔离级别；serializable：事务之间完全隔离。
innodb_buffer_pool_size=1G	缓冲数据和索引可以使用的内存。一般设置为物理内存的70%。
innodb_data_file_path=ibdata1:1024M:autoextend	设定共享表空间文件的名称、大小、自动扩容。共享表空间是MySQL5.5默认的数据存储文件，会将所有数据统一存放到该文件。在MySQL5.6以后，该文件仍然存在，存放系统数据、undo日志和磁盘临时表。
innodb_file_per_table=1	MySQL5.6以后，将数据存储方式改为“每个表使用单独的表空间存储”。为每个表都是用单独的“.ibd”（表数据文件）和“.frm”（表结构定义文件）。
innodb_log_buffer_size=16M	InnoDB存储引擎中，redo log写缓冲区的大小。redo log加载的是数据的修改记录。
innodb_log_file_size=64M	redo log日志文件大小。redo log写缓冲区中的数据最终都会写入到redo log名为ibdata1、ibdata2日志文件中。两个文件默认的大小都是固定48M，通过轮询覆盖写入。
innodb_log_files_in_group=2	redo log日志文件的个数。
innodb_flush_log_at_trx_commit=1	控制InnoDB存储引擎将redo log buffer中的数据写入日志文件并flush磁盘的时间点。设置为0时：每当事务提交，不做日志写入，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；设置为1时：每当事务提交，都会做redo日志写入、flush磁盘操作，确保了事务的ACID；设置为2时，每当事务提交，都会做redo日志写入，但每秒钟执行一次flush磁盘操作。
innodb_flush_method=O_DIRECT	设置为fsync，当数据页需要持久化时，首先将数据写入系统缓冲中，由系统决定何时写入磁盘。redo buffer持久化时，首先将数据写入系统缓冲，由系统决定何时写入磁盘。但是当innodb_flush_log_at_trx_commit=1时，redo buffer会在事务提交之后立即写入磁盘；设置为O_DIRECT：数据页需要持久化时，直接写入磁盘。redo buffer持久化时，首先将数据写入系统缓冲，由系统决定何时写入磁盘。但是当innodb_flush_log_at_trx_commit=1时，redo buffer会在事务提交之后立即写入磁盘。
innodb_thread_concurrency=0	用来设置InnoDB控制CPU的并发数量，0表示不限制。通常64核以下CPU，不需要做设置，默认为0即可。