MySQL（八）优化之参数调整配置详解（理解后根据业务场景调整参数）_wait_timeout 7200 是多少

MySQL优化之参数调整配置详解（理解后根据业务场景调整参数）

max_connections：

Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

查看MySQL最大连接数：

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1
2
3
4
5
6

查看当前连接数：

mysql> show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 102   |
+----------------------+-------+
1
2
3
4
5
6

修改最大连接数：

#临时修改为最大连接数为1024个
mysql> set global max_connections=1024;

#永久修改为最大连接数为1024个
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
max_connections=1024
1
2
3
4
5
6

如果max_used_connections跟max_connections相同,那么就是max_connections设置过低或者超过服务器的负载上限了，低于10%则设置过大.

back_log：

mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。

back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它

查看最大堆栈连接数：

mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 80    |
+---------------+-------+
1
2
3
4
5
6

查看MySQL当前连接进程：（设定back_log值的判断依据）

mysql> show full processlist;
| Id  | User | Host      | db   | Command | Time | State        | Info                                               |
+-----+------+-----------+------+---------+------+--------------+----------------------------------------------------+
|   5 | root | localhost | olda | Query   |    0 | starting     | show full processlist                              |
| 208 | root | localhost | NULL | Sleep   | 1838 |              | NULL                                               |
| 209 | root | localhost | olda | Query   |    6 | Sending data | select * from olda.student where name='olda_74256' |
| 210 | root | localhost | olda | Query   |    6 | Sending data | select * from olda.student where name='olda_74256' |
| 211 | root | localhost | olda | Query   |    0 | Sending data | select * from olda.student where name='olda_74256' |
| 212 | root | localhost | olda | Query   |    0 | Sending data | select * from olda.student where name='olda_74256' |
...............
...............
...............(省略记录其他多个进程)
1
2
3
4
5
6
7
8
9
10
11
12

发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值

修改最大堆栈连接数：

#永久修改最大堆栈连接数（不能临时修改堆栈数）
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
back_log=1024
1
2
3

wait_timeout和interactive_timeout：

interactive_timeout：

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

wait_timeout：

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

如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
1

设置建议：

如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低

修改端口连接方式举例：

#这是测试修改参数，（生产中一定不要这么干,根据生产环境开发需要的长连接时间修改）
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
wait_timeout=7200
interactive_timeout=7200
1
2
3
4

长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。

一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。

key_buffer_size：

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

• 此参数与myisam表的索引有关
• 临时表的创建有关（多表链接、子查询中、union）

在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃

临时表有两种创建方式：

内存中------->key_buffer_size
磁盘上------->ibdata1(5.6)

存放ibtmp1 (5.7），已经更新为独立表空间了
1
2
3
4

查看key_buffer_size默认值：

#单位为 "字节"
mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1
2
3
4
5
6
7

查看Myisam索引请求情况：

#一共有 30 个索引读取请求，有 5 个请求在内存中没有找到直接从硬盘中读取索引，（控制在 5% 以内）
mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 30    |    一共发生了多少次读取索引
| Key_reads         | 5     |    从"磁盘"读取索引次数
+-------------------+-------+
1
2
3
4
5
6
7
8

设置依据：

通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。

注意：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。

使用检查状态值created_tmp_disk_tables得知创建临时表信息：

mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |    在"磁盘"种创建临时表情况
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 6     |    在"内存"中创建临时表情况
+-------------------------+-------+
1
2
3
4
5
6
7
8

计算磁盘或内存创建临时表比例：

Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)      ==     内存临时表的占比

Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) ==     磁盘临时表的占比（10%以下）
1
2
3

修改key_buffer_size值：

#当前为测试值，具体配置大小根据生产环境压力测试而定
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
key_buffer_size=16M
1
2
3

根据MySQL在 磁盘 上创建临时表的百分比来设置索引缓存区大小

query_cache_size：

查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。

• 查询完结果之后，会对SQL语句进行hash运算，得出hash值,我们把他称之为SQL_ID
• 会将存储引擎返回的结果+SQL_ID存储到缓存中。

使用方式：

1、一条SQL执行时，进行hash运算，得出SQLID，去找query cache
2、如果cache中有，则直接返回数据行，如果没有，就走原有的SQL执行流程
1
2

一个SQL语句查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。

注意：两个SQL语句，只要想差哪怕是一个字符（列如大小写不一样；多一个空格等）,那么这两个sql将使用不同的一个cache。

查看默认配置：

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |     超过此大小的查询将不缓存：（单位：字节）
| query_cache_min_res_unit     | 4096    |     缓存块的最小大小：（默认是4KB，设置值大对大数据查询有好处）
| query_cache_size             | 1048576 |     默认缓存大小 1M：（单位：字节）
| query_cache_type             | OFF     |     缓存类型
| query_cache_wlock_invalidate | OFF     |     默认是关闭SQL查询缓存的
+------------------------------+---------+

query_cache_type：
    如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。
    如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
    如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

修改缓存大小判断依据：

mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |    缓存中相邻内存块的个数：（如果该值显示较大，则说明Query Cache 中的内存碎片较多了）
| Qcache_free_memory      | 1031832 |    空闲的缓存空间大小 1MB：（单位：字节）
| Qcache_hits             | 0       |    SQL查询语句命中缓存次数：（数字越大，缓存效果越理想。）
| Qcache_inserts          | 0       |    表示多少次未命中然后插入数据到缓存：（这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想）
| Qcache_lowmem_prunes    | 0       |    多少条SQl查询缓存因为内存不足而被清除出QueryCache：（如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。）
| Qcache_not_cached       | 125157  |    不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
| Qcache_queries_in_cache | 0       |    当前Query Cache 中cache 的Query 数量
| Qcache_total_blocks     | 1       |    当前Query Cache 中的block 数量
+-------------------------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13

修改缓存大小及开启缓存：（根据生产环境需求开启）

#参数仅代表当前测试环境，（根据生成环境配置大小）
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
1
2
3
4
5

注意：如果出现hits命中比例过低，其实就可以关闭查询缓存了。使用redis专门缓存数据库

max_connect_errors：

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

查看默认最大连接失败数：

mysql> show variables like '%max_connect_errors%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1
2
3
4
5
6

查看当前错误连接次数：

mysql> show status like '%errors_max_conn%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_max_connections | 0     |
+-----------------------------------+-------+
1
2
3
4
5
6

修改连接失败最大数：

#参数仅代表当前测试环境，（根据生成环境配置大小）
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
max_connect_errors=2000
1
2
3

刷新host连接错误记录：

#清除所有连接错误的主机IP信息
flush hosts;
1
2

sort_buffer_size：

每个需要进行排序的线程分配该大小的一个缓冲区。

ORDER BY
GROUP BY
distinct
union
1
2
3
4

查看默认排序缓存大小：

mysql> show variables like 'sort_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |     默认为256KB
+------------------+--------+
1
2
3
4
5
6

配置依据：

• Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置 + 高并发可能会耗尽系统内存资源。每个用户连接后都会分配一个sort_buffer空间。
• 列如：有500个连接将会消耗500 乘以 sort_buffer_size（2M）=1G内存

修改排序缓存参数大小：

#一般不会设置特别大，否则对服务器内存压力过大
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
sort_buffer_size=1M
1
2
3

结合索引，减少排序

max_allowed_packet：

mysql根据配置文件会限制，server接受的数据包大小。

查看默认最大更新数据大小：

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |       默认最大更新数据大小为 "4M"
+--------------------+---------+
1
2
3
4
5
6

配置依据：

• 有时候大量的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，建议设置参数的最大值是1GB，注意：必须设置1024的倍数

修改大事务限制参数：

#根据生产业务需求修改
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
max_allowed_packet=32M
1
2
3

join_buffer_size：

用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

select a.name,b.name from a join b on a.id=b.id where xxxx
1

尽量在SQL与方面进行优化，效果较为明显。

优化的方法：在on条件列加索引，至少应当是有MUL索引

查看join独享缓存大小：

mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |    默认大小256KB
+------------------+--------+
1
2
3
4
5
6

修改join独立缓存大小：

#不代表生产参数，和key_buffer_size配合使用
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
join_buffer_size=2M
1
2
3

thread_cache_size：

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能。

配置依据：

官方建议设置规则：1GB 内存配置为8个缓存，2GB配置为16个缓存，3GB配置为32个缓存，4GB或更高内存，可配置更大。

• 通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
• 服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

查看试图连接MySQL（不管成功或失败）的连接数：

mysql> show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 102   |
| Threads_created   | 102   |
| Threads_running   | 81    |
+-------------------+-------+
1
2
3
4
5
6
7
8
9

参数解释：

Threads_cached    : 代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected : 代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created   : 代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
Threads_running   : 代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
1
2
3
4

查看默认线程缓存数：

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 18    |
+-------------------+-------+
1
2
3
4
5
6

修改线程缓存数：

#根据用户量级及服务器性能调配
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
thread_cache_size=200
1
2
3

Threads_created ：一般在架构设计阶段，会设置一个测试值，做压力测试。

如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）

如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。

innodb_buffer_pool_size：

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。

配置依据：

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

查看默认innodb缓存空间大小：

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |     128MB ：默认单位：字节
+-------------------------+-----------+
1
2
3
4
5
6

修改innodb缓存空间大小：

#根据服务器内存性能调配，类似于索引数据及数据等缓存最大伐值
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
innodb_buffer_pool_size=4096M
1
2
3

查看详细innodb缓存信息：
show engine innodb status\G

innodb_flush_log_at_trx_commit：

主要控制了innodb将log buffer中的数据写入redo日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

• 0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
• 1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；
• 2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。

查看默认刷新策略：

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1
2
3
4
5
6

修改刷新redo日志策略：

[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
innodb_flush_log_at_trx_commit=1
1
2

innodb_thread_concurrency：

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

查看默认innodb线程并发数量：

mysql> show variables like 'innodb_thread_concurrency';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0     |
+---------------------------+-------+
1
2
3
4
5
6

配置依据：

根据服务器CPU核心数量配置，还有当前系统所有CPU核心使用情况，均不均匀

修改innodb线程并发数：

#当前配置为测试环境，根据生产环境服务器CPU核心数量修改
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
innodb_thread_concurrency=4
1
2
3

innodb_log_buffer_size：

此参数确定redo日志文件所用的内存缓存区大小，以M为单位。redo日志内存缓存区更大能提高性能，对于较大的事务，可以增大缓存大小。

设定依据：

• 1、大事务： 存储过程调用 CALL
• 2、多事务

查看默认redo日志缓存大小：

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |    默认16MB：默认单位：字节
+------------------------+----------+
1
2
3
4
5
6

修改redo日志缓存大小：

#当前配置为测试环境，根据生产环境业务的事务大小配置
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
innodb_log_buffer_size=32M
1
2
3

innodb_log_file_size：

设置 ib_logfile0与ib_logfile1文件

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

查看默认redo磁盘日志大小：

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |   默认48MB：默认单位：字节
+----------------------+----------+
1
2
3
4
5
6

修改redo磁盘日志文件大小：

#生产中一般最高也就设置256MB
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
innodb_log_file_size=100M
1
2
3

innodb_log_files_in_group：

redo磁盘日志写到多个文件。推荐设置为3，也就是有ib_logfile2文件

注意：都是与innodb_log_file_size参数配置一起使用

查看默认文件数：

mysql> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
1
2
3
4
5
6

修改redo磁盘日志文件数：

[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
innodb_log_files_in_group=3
1
2

read_buffer_size：

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

查看默认读入缓冲区大小：

mysql> show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |   默认128KB，默认单位：字节
+------------------+--------+
1
2
3
4
5
6

修改读入缓冲区大小：

#当前为测试环境测试参数
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
read_buffer_size=2M
1
2
3

注意：一般生产环境设置为“1MB”就可以，并发高就设置“2MB”，最高不要超过“4MB”

read_rnd_buffer_size：

MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

查看随机查询缓冲区大小：

mysql> show variables like 'read_rnd_buffer_size';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |   默认256KB，默认单位：字节
+----------------------+--------+
1
2
3
4
5
6

修改随机查询缓冲区大小：

[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
read_rnd_buffer_size=2M
1
2

注意：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

顺序IO：索引扫描，顺序IO

bulk_insert_buffer_size：

批量插入数据缓存大小，可以有效提高插入效率。

查看插入数据缓存大小：

mysql> show variables like 'bulk_insert_buffer_size';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |   默认8MB，默认单位：字节
+-------------------------+---------+
1
2
3
4
5
6

修改插入数据缓存大小：

#根据生产业务插入数据的需求进行修改缓存大小
[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
bulk_insert_buffer_size=8M
1
2
3

binlog_cache_size：

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

查看默认binlog内存缓存大小：

mysql> mysql> show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |    默认32KB，默认单位：字节
+-------------------+-------+
1
2
3
4
5
6

修改binlog内存缓存大小：

[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
binlog_cache_size=2M
1
2

max_binlog_cache_size：

表示的是binlog 能够使用的最大cache 内存大小。

查看默认binlog最大内存缓存空间大小：

mysql> show variables like 'max_binlog_cache_size';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |    默认16383PB，默认单位：字节
+-----------------------+----------------------+
1
2
3
4
5
6

修改binlog最大使用内存空间大小：

[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
max_binlog_cache_size=8M
1
2

max_binlog_size：

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

查看每个最大binlog日志文件大小：

mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |    默认1GB，默认单位：字节
+-----------------+------------+
1
2
3
4
5
6

修改每个最大binlog日志文件大小：

[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
max_binlog_size=512M
1
2

expire_logs_days：

定义了mysql清除过期日志的时间。二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。

查看默认删除binlog时间：

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |   默认不自动删除binlog日志文件
+------------------+-------+
1
2
3
4
5
6

修改binlog日志删除时间：

[root@mysql ~]# vim /usr/local/mysql/conf/my.cnf
expire_logs_days=7
1
2

MySQL安全参数：

双1标准(基于安全的控制)：
binlog刷新参数默认是0，意味着mysql并不刷新，由操作系统自己决定什么时候刷新缓存到持久化设置

sync_binlog=1                       什么时候刷写binlog日志到磁盘，每次事务commit
innodb_flush_log_at_trx_commit=1    什么时间刷写redo日志到磁盘当，每次事务commit或每秒钟
innodb_flush_log_at_timeout=1       参数决定最多丢失多少秒的数据，默认是1秒
1
2
3

存储引擎安全：

Innodb_flush_method=(O_DIRECT, fdatasync)

fdatasync：

（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
    但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
1
2
3

Innodb_flush_method=O_DIRECT

（1）在数据页需要持久化时，直接写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
    但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
1
2
3

最安全模式：

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
1
2

最高性能模式：

innodb_flush_log_at_trx_commit=0
innodb_flush_method=fdatasync
1
2