MySQL 配置
本文最后更新于：2024年3月18日 凌晨
MySQL 配置
JDBC连接参数
| 参数名称 |
参数说明 |
缺省值 |
最低版本要求 |
| user |
数据库用户名（用于连接数据库） |
|
所有版本 |
| password |
用户密码（用于连接数据库） |
|
所有版本 |
| useUnicode |
是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk，本参数值必须设置为true |
false |
1.1g |
| characterEncoding |
当useUnicode设置为true时，指定字符编码，比如可设置为gb2312或gbk |
false |
1.1g |
| autoReconnect |
当数据库连接异常中断时，是否自动重新连接？ |
false |
1.1 |
| autoReconnectForPools |
是否使用针对数据库连接池的重连策略 |
false |
3.1.3 |
| failOverReadOnly |
自动重连成功后，连接是否设置为只读？ |
true |
3.0.12 |
| maxReconnects |
autoReconnect设置为true时，重试连接的次数 |
3 |
1.1 |
| initialTimeout |
autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒 |
2 |
1.1 |
| connectTimeout |
和数据库服务器建立Socket连接时的超时，单位：毫秒，0表示永不超时，适用于JDK 1.4及更高版本 |
0 |
3.0.1 |
| socketTimeout |
Socket操作（读写）超时，单位：毫秒，0表示永不超时 |
0 |
3.0.1 |
查看参数变量
1
2
| SHOW GLOBAL VARIABLES;
SHOW VARIABLES LIKE '%connect%';
|
设置参数变量
1
| SET GLOBAL variable = '';
|
1
| SET SESSION variable = '';
|
- 设置配置文件参数（重启MySQL后永久生效）:
/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf
1
2
3
| [mysqld]
bind-address = 0.0.0.0 # 表示允许任何主机登陆MySQL
port=3306 # 表示MySQL运行端口为3306
|
连接参数变量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| | max_connect_errors | 999999999 | ##允许单用户连接错误最大值，超过后在不刷新状态的情况下，禁止该用户新连接
| max_connections | 6000 | ##实例最大连接数限制
| max_user_connections | 0 | ##单用户连接最大限制，默认0表示无限制，遵守总连接数小于等于max_connections
| connect_timeout | 10 | ##用户连接超时限制，超过10秒，如果依旧无法连接到mysql，则终止连接
| delayed_insert_timeout | 300 | ##延迟插入超时时间，300秒
| innodb_flush_log_at_timeout | 1 | ##刷新redo log buffer超时时间，1秒
| innodb_lock_wait_timeout | 120 | ##事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败，120秒
| innodb_rollback_on_timeout | ON |
| interactive_timeout | 28800 | ##mysql客户端交互连接超时时间，默认8小时，用于控制sleep超时
| lock_wait_timeout | 31536000 | ##主要针对DDL产生的metadata locks超时时间
| net_read_timeout | 60 | ##网络读取数据超时时间，60秒
| net_write_timeout | 60 | ##为网络写入数据超时间60秒
| rpl_stop_slave_timeout | 31536000 | ##停止从库服务超时时间
| slave_net_timeout | 60 | ##slave网络超时时间
| thread_pool_idle_timeout | 60 |
| wait_timeout | 28800 | ##jdbc/odbc连接超时时间，默认8小时，用于控制sleep超时
| Connection_errors_max_connections | 1906160 | ##用户最大错误连接数
| Connections | 87341259 | ##历史用户连接累积值
| Max_used_connections | 10242 | ##历史最大连接用户数，processlist看到的连接数
| Threads_connected | 298 | ##当前用户连接数，当前processlist数
| Threads_running | 2 | ##当前用户连接处于running状态的数目，非sleep
| Aborted_clients | 85050948 | ##非法终止客户端连接的状态统计，例如通过kill终止连接
| Aborted_connects | 1905528 | ##非法连接操作状态统计，例如用户密码错误
|
查询缓存参数变量
- Qcache_free_memory:Query Cache中目前剩余的内存大小，通过这个参数可以较为准确的观察到当前系统的Query Cache是否充足，是否需要增加还是过剩了。
- Qcache_lowmem_prunes：多少条query语句因为内存不足而从Query Cache中清除掉。
- 通过Qcache_lowmem_prunes和Qcache_free_memory相互结合，能更清楚的了解到系统中的Query Cache是否真的足够，是否非常频繁的出现因为内存不足而存在Query被清除的现象，这个数字要放在一个较长的时间段看才有意义，如果这个数字在不断增长，表示可能存在较严重的碎片化现象，或者内存很少了。
- Qcache_total_blocks：当前Query Cache中block的数量。
- Qcache_free_blocks：缓存中相邻内存块的个数，如果该值显示较大，说明Query Cache中的内存碎片较多了。
- Qcache_hits：表示有多少次命中缓存，我们可以通过这个值来验证我们查询的缓存效果，数字越大效果越理想。
- Qcache_inserts：表示多少次未命中而插入，就是说新来的SQL请求在缓存中没有找到，不得不执行查询处理，执行查询处理之后将结果insert到缓存中，这样的情况次数越大，表示查询缓存应用的数量越少，效果也就越不理想。
- Qcache_queries_in_cache：当前的Query Cache 中Query数量，即当前的缓存中使用到的缓存查询数量。
- Qcache_not_cached：未进入查询缓存的select数量。
- 查询缓存碎片率 :
Qcache_free_blocks /Qcache_total_blocks * 100%，经验值判断，如果查询碎片率超过20%，可以使用flush query cache来整理缓存碎片。
- query_cache_size：查询缓存大小，QC的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值，这个值将会被四舍五入到最接近或等于1024的倍数。
- query_cache_limit：超出此大小的查询将不被缓存，限定了单个查询缓存的最大内存数量。
- query_cache_type：缓存类型，决定缓存什么样的查询：
- 0:OFF，相当于禁用（5.7默认禁用了查询缓存，需要在
my.cnf中进行设置）
- 1:ON，缓存所有结果，除非你的select语句显示使用了SQL_NO_CACHE禁用了查询缓存。
- 2:DEAND，只缓存select语句中通过SQL_CACHE指定需要缓存的查询。
- query_cache_min_res_unit：缓存块的最小大小，最小单位是4KB，设置的大点对大数据查询有好处，但是小数据多的话容易产生内存碎片造成资源浪费。
- 单个查询占用缓存的平均值:
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
- 查询缓存利用率:
(query_cache_size - Qcache_free_memory)/ query_cache_size * 100%
- 查询缓存利用率在25%以下说明
query_cache_size设置的过大了，可以适当减小。
- 查询缓存利用率在80%以上，而且
Qcache_lowmem_prunes > 50说明query_cache_size设置的有点小，要不就是碎片太多。
- 查询缓存命中率:
Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%
- sort_buffer_size：每个需要排序的线程分配的一个缓存区域，通过设置该参数可以加速 order by 或者group by的操作。
sort_buffer_size是一个connection级别的参数，在每个connection第一次需要使用这个buffer的时候，一次性分配指定数量大小的内存。sort_buffer_size不是越大越好，由于是connection级别的参数，过大的设置在遇到高并发场景下可能会很快耗尽系统资源，例如1000个请求的并发的消耗大小为：`1000 * sort_buffer_size(2MB) = 2G
innoDB 参数变量
- innodb_buffer_pool_size：这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。
- 该参数分配内存的原则：这个参数默认分配只有8M，可以说是非常小的一个值，如果是一个专用DB服务器，那么他可以占到内存的70%-80%，这个参数不能动态更改，所以分配需多考虑，分配过大，会使Swap占用过多，致使Mysql的查询特慢，如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。
- innodb_file_per_table：作用：使每个Innodb的表，有自已独立的表空间，如删除文件后可以回收那部分空间，默认是关闭的，建议打开（innodb_file_per_table=1)
- innodb_log_buffer_size：作用：事务在内存中的缓冲，也就是日志缓冲区的大小，默认设置即可，具有大量事务的可以考虑设置为16M，另外如果你需要处理大理的TEXT，或是BLOB字段，可以考虑增加这个参数的值。
- innodb_flush_logs_at_trx_commit：控制事务的提交方式，也就是控制log的刷新到磁盘的方式，这个参数只有3个值（0,1,2)
- 0:log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作。
- 1:（默认为1）在每次事务提交的时候将logbuffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步。
- 2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步，此外，每秒会有一次文件系统到磁盘同步操作。
严格审核输入的值
1
| SET SQL_MODE = 'STRICT_TRANS_TABLES';
|
- 对于错误数据的约束：默认MySQL允许非法的或不正确的值插入或更新，或者将其转为合法的值。
- 比如对not null 的字段插入null值，会将其转换为0，再进行插入，可以设置 sql_mode 的值来严格审核输入的值。