MySQL-DB参数、内存、I/O、安全等相关参数设置

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

服务器参数介绍

MySQL获取信息配置领

1） 命令行

mysqld_safe --datadir=/xxx/xxxx

2） 配置文件

持久化的配置都要写到配置文件中.

MySQL读取的配置文件可以通过如下命令来查看

[root@artisan mysql]# mysqld --help --verbose |grep -A  1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
[root@artisan mysql]# 

首先从/etc/my.cnf 读取，逐步往后找，后面的覆盖前面的。

MySQL配置参数的作用域

内存配置相关参数

• 确定可以使用的内存的上限 ，不要超过服务器的内存
• 32位的操作系统，能使用的不足4G，这个也需要注意
• 确定MySQL每个连接使用的内存 sort_buffer_size： 举个例子，如果配置的过大，比如100M， 这个时候如果有10个连接过来，那就要分配1G的内存。。。 join_buffer_size read_buffer_size read_rnd_buffer_size 这4个参数都是给每个线程分配的
• 如何为缓存池分配内存 Innodb_buffer_pool_size : 确保分配足够多的内存 key_buffer_size

需根据实际情况调整

I/O相关配置参数

这部分参数决定了MySQL如何同步缓冲池中的数据到缓存。

Innodb I/O 相关的配置

• Innodb_log_file_size 单个事务文件的大
• Innodb_log_files_in_group 事务日志文件的个数
• Innodb_log_buffer_size 缓冲区的大小 事务日志中大小 Innodb_log_file_size * Innodb_Log_files_in_group ， 循环使用。 先写到缓冲区，Innodb_log_buffer_size 指定缓冲区的大小，不用设置太大（32M-128M就够了），默认1秒刷一次，然后再写到日志。
• Innodb_flush_log_at_trx_commit : 刷日志的频率 0： 每秒进行一次log写入cache,并flush log到磁盘 1[默认]：在每次事务提交执行log写入cache，并flush log到磁盘 2[建议]：在每次事务提交执行log写入cache，每秒执行一次flush log到磁盘
• Innodb_flush_method = O_DIRECT (建议值)
• Innodb_file_per_table = 1 (启用表空间)
• Innodb_doublewrite = 1 (启用双写缓存)

MyISAM I/O 相关的配置

MySQL的系统表，使用了MyISAM存储引擎，所以也是需要了解的。

• delay_key_write OFF: 每次写操作刷新键缓冲中的脏块到磁盘 ON: 只对在键表时指定了delay_key_write选项的表时使用延迟刷新 ALL:对所有MyISAM表都使用延迟键写入

安全相关配置参数

• expire_logs_days ： 指定自动清理binlog的天数，如果开启了binlog，最好配置这个参数，防止磁盘被占满
• max_allowed_packet ： 控制MySQL可以接手的包的大小 ， 主从节点最好一致，否则容易同步失败
• skip_name_resolve： 禁用DNS查找
• sysdate_is_now ：确保sysdate()返回确定性日志
• read_only: 禁止非super权限的用户写权限， 从节点建议开启。
• skip_slave_start : 禁用slave自动恢复。 从节点建议开启
• sql_mode :设置MySQL所使用的SQL模式 可选项： strict_trans_tables、 no_engine_subtitution、 no_zero_date 、no_zero_in_date、 only_full_group_by

其它常用配置参数

• sync_binlog ：控制MySQL如何向磁盘刷新binlog , 默认为0 。 建议设置为1
• tmp_table_size 和 max_heap_table_size 控制内存临时表的大小 ，建议这两个值设置一样大，超过最大内存后，将转化为磁盘存储
• max_connections : 控制允许的最大连接数，默认100， 建议2000 ，根据应用调整。

小结

说了这么多参数配置， 可不要指望修改一两个参数，性能就能有质的提升。

首先应该聚焦于 数据结构 和 SQL的优化，实在没有优化的空间了，再考虑这些。

优化的顺序

1. 数据库结构设计和SQL语句，最容易看到成果
2. 数据库存储引擎的选择和参数配置
3. 系统优化
4. 硬件升级

由上到下优化