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

生猛干货

带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

---

官方文档

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配置参数的作用域

• 全局参数

  set global 参数名=参数值;
  set @@global.参数名 := 参数值;  --- 两种方式都可以 
  

  需要在MySQL客户端中执行

• 会话参数

  	set [session]  参数名=参数值;
  	set @@session.参数名 := 参数值;  --- 两种方式都可以 
  

  操作下看看

  [root@artisan ~]# mysql -u root -p
  Enter password: 
  .....
  
  mysql> # 这两个参数要一起设置，否则会取大的那个为准。
  mysql> show variables where variable_name = 'wait_timeout' or variable_name = 'interactive_timeout';
  +---------------------+-------+
  | Variable_name       | Value |
  +---------------------+-------+
  | interactive_timeout | 28800 |
  | wait_timeout        | 28800 |
  +---------------------+-------+
  2 rows in set (0.00 sec)
  
  mysql> set global wait_timeout=3600; set interactive_timeout=3600;
  Query OK, 0 rows affected (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> 
  
  
  

  设置了global 参数，有些已经登录的会话，要退出重新登录才生效。

---

内存配置相关参数

• 确定可以使用的内存的上限 ，不要超过服务器的内存

• 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. 硬件升级

由上到下优化

---

搞定MySQL