cnf配置 mysql8 mysql8 配置文件

转载

陌陌香阁 2023-09-06 22:13:51

文章标签 cnf配置 mysql8 数据库 mysql dba MySQL 文章分类 MySQL 数据库

文章目录

1、local_infile
2、secure-file-priv
3、 log_timestamps
4、default-time_zone
5、wait_timeout
6、default_storage_engine
7、default_authentication_plugin
8、max_allowed_packet
9、max_connections
10、open_files_limit
11、log_bin_trust_function_creators
12、skip-name-resolve
13、lower_case_table_names
14、character-set-server
15、collation-server
16、init_connect
17、innodb_read_io_threads
18、innodb_io_capacity
19、innodb_buffer_pool_size
20、innodb_log_file_size
21、innodb_file_per_table
22、innodb_flush_log_at_trx_commit
23、key_buffer_size
24、server-id
25、slow_query_log
26、binlog_cache_size
27、binlog_format
28、binlog_stmt_cache_size
29、log_queries_not_using_indexes
30、sync_binlog
31、innodb_flush_log_at_trx_commit
32、tmp_table_size
33、max_heap_table_size
34、table_open_cache

1、local_infile

[mysqld]
local_infile = 1   ##禁用客户端的load data local infile命令    默认0开启

SHOW GLOBAL VARIABLES LIKE 'local_infile';

在数据库的使用过程中，经常需要进行批量的数据导入操作，MySQL数据库批量数据导入可以使用LOAD DATA命令，实现大批量数据的快速入库需求。

在使用LOAD DATA命令可能会遇到如下报错情况：

使用 LOAD DATA LOCAL INFILE 命令批量导入数据时，报错：
ERROR 1148 (42000): The used command is not allowed with this MySQL version.
使用 LOAD DATA INFILE 命令导入数据时，报错：
ERROR 1045 (28000): Access denied for user 'xxxx'@'%' (using password: YES)

原因分析

第一种报错是受参数设置的影响：
MySQL服务端参数：local_infile 用于控制MySQL Server是否允许使用LOAD DATA LOCAL INFILE命    令导入存放于客户端的数据文件。
MySQL客户端参数：--local-infile 用于控制MySQL Client是否允许使用LOAD DATA LOCAL INFILE命令导入存放于客户端的数据文件。

第二种报错是命令和账号权限不匹配导致的：
LOAD DATA LOCAL INFILE 命令可以导入客户端的数据文件，使用具有普通写权限的账号即可。
LOAD DATA INFILE 命令可以导入服务端的数据文件，需要使用具备FILE权限的账号（出于安全考虑不    建议放开FILE权限）。

解决方案

第一种报错解决方案：确保MySQL客户端登录时开启参数--local_infile，且确保MySQL服务端开启参数local_infile。
第二种报错解决方案：使用LOAD DATA命令时注意添加LOCAL关键字。

2、secure-file-priv

secure_file_priv = /usr/local/mysql/temp     ##此开关默认为NULL，即不允许导入导出

secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。

ure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制
如何查看secure-file-priv参数的值：

mysql> show global variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.09 sec)

3、 log_timestamps

log_timestamps=SYSTEM

这个参数主要是控制 error log、slow_log、genera log，等等记录日志的显示时间参数，但不会影响 general log 和 slow log 写到表 (mysql.general_log, mysql.slow_log) 中的显示时间。在查询行的时候，可以使用 CONVERT_TZ() 函数，或者设置会话级别的系统变量 time_zone 来转换成所需要的时区。

该参数全局有效，可以被设置的值有：UTC 和 SYSTEM，默认使用 UTC。它还支持动态设置，不过建议大家在配置文件中就写上，以免重启之后造成不必要的麻烦。

SHOW GLOBAL VARIABLES LIKE 'log_timestamps';

4、default-time_zone

default-time_zone = '+8:00'

这个参数设置默认的服务器时区。该参数用于设置全局 time_zone 系统变量。如果未给出该选项，默认时区为格林威治时间。通常该参数的值，要求与操作系统的时区设置值一致。错误的时区设置可能导致数据库的时间函数返回结果不正常。对于中国地区的客户，通常使用北京时间，因此该参数值设置为’+8:00’。

mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CEST   |
| time_zone        | SYSTEM |
+------------------+--------+

5、wait_timeout

wait_timeout = 28800

指的是mysql在关闭一个非交互的连接之前所要等待的秒数，其取值范围为1-2147483(Windows)，1-31536000(linux)，默认值28800。
服务器在关闭非交互式连接前，应用程序等待的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。

6、default_storage_engine

default_storage_engine = InnoDB

MySQL的默认存储引擎

mysql> show variables like '%storage_engine%';
 
+----------------------------+--------+
 
| Variable_name              | Value  |
 
+----------------------------+--------+
 
| default_storage_engine     | InnoDB |
 
| default_tmp_storage_engine | InnoDB |
 
| storage_engine             | InnoDB |
 
+----------------------------+--------+
 
3 rows in set (0.00 sec)

1：default_storage_engine 表示永久表（permanent tables）的默认存储引擎。

 2：default_tmp_storage_engine 表示临时表的默认存储引擎。

 storage_engine这个系统变量不推荐使用，它已经被系统变量default_storage_engine替代了

7、default_authentication_plugin

default_authentication_plugin=mysql_native_password

Mysql在之前的版本的关于password的加密方法都是使用的 mysql_native_password，不过到MySQL8.0的时候换成了caching_sha2_password

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)

mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
---------------------

8、max_allowed_packet

max_allowed_packet = 512M

服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB 字段一起工作时相当必要)，每个连接独立的大小.大小动态增加。设置最大包,限制server接受的数据包大小，避免超长SQL的执行有问题默认值为16M，当MySQL客户端或mysqld
服务器收到大于 max_allowed_packet 字节的信息包时，将发出“信息包过大”错误，并关闭连接。对于某些客户端，如果通信信息包过大，在执行查询期间，可能会遇到“丢失与 MySQL 服务器的连接”错误。默认值 16M。

show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 4194304    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.04 sec)

9、max_connections

max_connections = 65536

指定MySQL允许的最大连接进程数。如果在访问数据库时经常出现"Too Many Connections"的错误提示，则需要增大该参数值。

mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 58    |
| Threads_connected | 57    |   ###这个数值指的是打开的连接数
| Threads_created   | 3676  |
| Threads_running   | 4     |   ###这个数值指的是激活的连接数，这个数值一般远低于connected数值
+-------------------+-------+
 
Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数
 
这是是查询数据库当前设置的最大连接数
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

10、open_files_limit

open_files_limit = 65536

MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections5和ulimit-n的值，哪个大用哪个，当open_file_limit被配置的时候，比较open_files_limit和max_connections5的值，哪个大用哪个。

open_files_limit 是mysql中的一个全局变量且不可动态修改。它控制着mysqld进程能使用的最大文件描述(FD)符数量。需要注意的是这个变量的值并不一定是你设定的值，mysqld会在系统允许的情况下尽量获取更多的FD数量。

open_files_limit这个参数应该在my.cnf中设定，因为mysqld_safe脚本读到这个变量会尝试执行ulimit -n 改变针对当前环境的FD limits. 然后在把这个变量传给mysqld,因此需要用root启动mysqld_safe，否则可能会无法修改成功（非root用户所使用的值不能超过hard limit）. 如果只是在[mysqld]中指定这个变量，可能会受限于系统对默认的设置而无法生效。

11、log_bin_trust_function_creators

log_bin_trust_function_creators=1

当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。如果变量设置为1，MySQL不会对创建存储函数实施这些限制。此变量也适用于触发器的创建。

12、skip-name-resolve

skip-name-resolve

禁止 MySQL 对外部连接进行 DNS 解析，使用这一选项可以消除 MySQL 进行 DNS 解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用 IP 地址方式，否则 MySQL 将无法正常处理连接请求！

13、lower_case_table_names

lower_case_table_names=1

mysql设置大小写是否敏感的一个参数。

lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=2 表名存储为给定的大小写但是比较的时候是小写的
unix,linux下lower_case_table_names默认值为 0 .Windows下默认值是 1 .Mac OS X下默认值是 2

14、character-set-server

character-set-server = utf8mb4

server 级别字符集，服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义。

15、collation-server

collation-server = utf8mb4_general_ci

设置字段编码

查看当前库的编码信息：

show global variables like ‘%coll%’

16、init_connect

init_connect='SET NAMES utf8mb4'

表示初始化连接都设置为utf8mb4字符集

17、innodb_read_io_threads

innodb_read_io_threads = 16

innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4

18、innodb_io_capacity

innodb_io_capacity = 2000

数据库落盘脏页个数 ,配置压力和磁盘的性能相关，如果过大，IO能力不足，则出现卡顿。
innodb_io_capacity默认是200，单位是页，该参数的设置大小取决于硬盘的IOPS，即每秒的输入输出量(或读写次数)。
可以动态调整参数:set global innodb_io_capacity=2000;

查看 innodb_io_capacity值: show variables like '%innodb_io_cap%';

19、innodb_buffer_pool_size

innodb_buffer_pool_size = 2G

该参数定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。
MySQL最大缓冲区可用内存，看机器内存情况给定

InnoDB缓存池缓存索引、行的数据、自适应哈希索引、插入缓存（Insert Buffer）、锁还有其他的内部数据结构。（所以，如果数据库的数据量不大，并且没有快速增长，就不必为缓存池分配过多的内存，当为缓存池配置的内存比需要缓存的表和索引大很多也没什么必要，会造成资源浪费。）

a.很大的缓存池可能会有很多脏页，导致InnoDB在关闭的时候会消耗很长的时间，因为要把脏页写回到数据文件里

b.一个大的缓存池重启服务器可能也要比较长的时间用来预热缓冲池，当然Percona的Server有快速预热的功能可以节省很多时间。

20、innodb_log_file_size

innodb_log_file_size = 128M

事物日志大小.在日志组中每个日志文件的大小，你应该设置日志文件总合大小到你缓冲池大小的5%~100%，来避免在日志文件覆写上不必要的缓冲池刷新行为.不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间.

MySQL的InnoDB 存储引擎使用一个指定大小的Redo log空间（一个环形的数据结构），Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group（默认为2）参数来调节。将这俩参数相乘即可得到总的可用Redo log 空间。尽管技术上并不关心你是通过innodb_log_file_size还是innodb_log_files_in_group来调整Redo log空间，不过多数情况下还是通过innodb_log_file_size 来调节。

为InnoDB引擎设置合适的Redo log空间对于写敏感的工作负载来说是非常重要的，然而，这项工作是要做出权衡的。配置的Redo空间越大，InnoDB就能更好的优化写操作；然而，增大Redo空间也意味着更长的恢复时间当出现崩溃或掉电等意外时。

关于恢复时间，并不好预测对于一个指定的 innodb_log_file_size 值出现崩溃是需要多长的恢复时间–他取决于硬件能力、MySQL版本以及工作负载等因素。然而，一般情况下我们可以按照每1GB的Redo log的恢复时间大约在5分钟左右来估算。如果恢复时间对于你的使用环境来说很重要，我建议你做一些模拟测试，在正常工作负载下（预热完毕后）模拟系统崩溃，来评估更准确的恢复时间。

虽然恢复时间可以作为一个限制innodb_log_file_size的参考因素，也还有一些别的方式可以观察该参数设置是否“合理”。

21、innodb_file_per_table

innodb_file_per_table = 1

InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间

Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间.
共享表空间以及独立表空间都是针对数据的存储方式而言的。

共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。默认的文件名为:ibdata1 初始化为10M。

独立表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。

22、innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 0

如果设置为 1 ,InnoDB 会在每次提交后刷新(fsync)事务日志到磁盘上,这提供了完整的 ACID 行为.如果你愿意对事务安全折衷, 并且你正在运行一个小的食物, 你可以设置此值到 0 或者 2 来减少由事务日志引起的磁盘 I/O

23、key_buffer_size

key_buffer_size = 512M

指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。如果是以InnoDB引擎为主的DB，专用于MyISAM引擎的 key_buffer_size 可以设置较小，8MB 已足够如果是以MyISAM引擎为主，可设置较大，但不能超过4G. 在这里，强烈建议不使用MyISAM引擎，默认都是用InnoDB引擎.注意：该参数值设置的过大反而会是服务器整体效率降低！

24、server-id

server-id = 1
server-id  = 100M
binlog_expire_logs_seconds = 604800

server-id用于标识数据库实例，防止在链式主从、多主多从拓扑中导致SQL语句的无限循环：
binlog 二进制文件大小

25、slow_query_log

slow_query_log = 1
slow_query_log_file = /usr/local/mysql/log/mysql_slow_query.log
long_query_time = 5

开启慢日志记录
慢日志记录保留目录
设置慢查询时间为5秒;

set long_query_time=1; #设置慢查询时间为1 秒;

set global slow_query_log=on; #开启慢查询日志;

show global status like 'slow_queries';   #显示慢查询次数

show variables like 'long_query_time' ;   #显示慢查询时间;

show variables like 'slow_query_log' ;  #显示慢查询日志是否开启

26、binlog_cache_size

binlog_cache_size=1048576

事物级别的参数，用于指定存储整个事物生成的binlog event的内存大小，对于大事物来讲很可能超过这个参数的设置，则需要开启binlog 临时文件用于存储。

27、binlog_format

binlog_format='ROW'

mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

28、binlog_stmt_cache_size

binlog_stmt_cache_size=1048576

Mysql二进制日志缓存参数：
binlog_cache_size     //事务缓存大小
binlog_cahce_use      //事务缓存使用次数
binblog_cache_disk_use  //事务缓存磁盘使用次数(内存缓存设置过小不够用时)
binlog_stmt_cache_size     //非事务语句缓存大小
binlog_stmt_cache_use    //非事务语句缓存使用次数
binlog_stmt_cache_disk_use  //非事务语句磁盘缓存使用次数

29、log_queries_not_using_indexes

log_queries_not_using_indexes=ON

控制未使用索引的查询是否写入慢日志。
如果在启用慢速查询日志的情况下启用此变量，则会记录预期检索所有行的查询

此选项不一定意味着不使用索引。例如，使用完整索引扫描的查询使用索引，但会被记录，因为索引不会限制行数

30、sync_binlog

sync_binlog=100

MySQL 的二进制日志（binary log）同步到磁盘的频率（刷新二进制日志到磁盘），默认是0，意味着mysql并不刷新，由操作系统自己决定什么时候刷新缓存到持久化设置，如果这个值比0大，它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作。

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。这个是性能最好的。

sync_binlog=1，当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

如果没有设置它为1，那么崩溃后可能导致二进制日志没有同步事务数据，有可能binlog中最后的语句丢失。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘 同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表，MySQL服务器 处理COMMIT语句，它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃，重启时，事务被InnoDB回滚，但仍 然存在binlog中。可以用--innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事务支持，该选项作废了），该选项可以提供更大程度的安全，使每个事务的 binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步，该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从binlog剪切回滚的 InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收 回滚的语句)。

注：
大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把 sync_binlog 配置成 1. 为了追求高并发，提升性能，可以设置为 100 或直接用 0。而和 innodb_flush_log_at_trx_commit 一样，对于支付服务这样的应用，还是比较推荐 sync_binlog = 1.

31、innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit=2

innodb_flush_log_at_trx_commit：是 InnoDB 引擎特有的，ib_logfile的刷新方式（ ib_logfile：记录的是redo log和undo log的信息）

取值:0/1/2
innodb_flush_log_at_trx_commit=0，表示每隔一秒把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区，也就是内存上，如果机器宕掉，可能丢失1秒的事务数据。

innodb_flush_log_at_trx_commit=1，表示在每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。

innodb_flush_log_at_trx_commit=2，表示在每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。

sync_binlog
sync_binlog：是MySQL 的二进制日志（binary log）同步到磁盘的频率。

取值：0-N
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。这个是性能最好的。

sync_binlog=1，当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

注：
大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把 sync_binlog 配置成 1. 为了追求高并发，提升性能，可以设置为 100 或直接用 0. 
而和 innodb_flush_log_at_trx_commit 一样，对于支付服务这样的应用，还是比较推荐 sync_binlog = 1.

32、tmp_table_size

tmp_table_size = 512M

临时表的内存缓存大小
( 临时表是指sql执行时生成临时数据表 )

33、max_heap_table_size

max_heap_table_size = 512M

规定了内部内存临时表的最大值，每个线程都要分配。(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认:

mysql> show variables like "tmpdir";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| tmpdir | /tmp/ |

+---------------+-------+

优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句，并且你有很多内存，增大tmp_table_size(和max_heap_table_size)的值。这个变量不适用与用户创建的内存表(memory table).

你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数(Created_tmp_disk_tables和Created_tmp_tables)，一般的比例关系是:

Created_tmp_disk_tables/Created_tmp_tables<5%

max_heap_table_size

这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#

,但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。

这个变量和tmp_table_size一起限制了内部内存表的大小。

34、table_open_cache

table_open_cache=30000

表文件描述符的缓存大小
( 当打开一个表后会把这个表的文件描述符缓存下来 )

查看 table_open_cache

show global variables like 'table_open_cache';

设置 table_open_cache
set global table_open_cache = 2048; （立即生效重启后失效）

MySQL 配置文件 my.cnf 中 mysqld 下添加 table_open_cache

[mysqld]
table_open_cache = 2048

table_open_cache 设置多少合适呢？
不是越大越好 table_open_cache过大占用大量文件描述符资源而不释放
用尽了系统文件描述符资源导致无法接入新的连接

如何判断 table_open_cache 大小是否够用？
可根据MySQL的两个状态值来分析

Opened_tables  : 打开的所有表数量
open_tables    : 打开后在缓存中的表数量

通过以上两个值来判断 table_open_cache 是否到达瓶颈
当缓存中的值open_tables 临近到了 table_open_cache 值的时候
说明表缓存池快要满了但 Opened_tables 还在一直有新的增长这说明你还有很多未被缓存的表
这时可以适当增加 table_open_cache 的大小

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：compose docker 编排和部署 docker compose 教程

下一篇：androidstudio打包apk v1签名 apk打包签名工具

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯