mysql5.7 常用参数

`max_connections`

命令行格式	`--max-connections=#`
系统变量	`max_connections`
范围	global
动态的	是的
类型	整数
默认值	`151`
最小值	`1`
最大值	`100000`

允许的最大同时客户端连接数。最大有效值是open_files_limit-810的有效值和为max_connections实际设置的值中的较小值。

`open_files_limit`

命令行格式	`--open-files-limit=#`
系统变量	`open_files_limit`
范围	global
动态的	不
类型	整数
默认值	`5000, with possible adjustment`
最小值	`0`
最大值	`platform dependent`

有效值计算公式

10 + max_connections + (table_open_cache * 2)
max_connections * 5
如果该限制为正但不是 Infinity，则为操作系统限制。
如果操作系统限制为 Infinity： open_files_limit如果在启动时指定，则为 value，否则为 5000。

在 Linux 上，的值open_files_limit不能超过ulimit -n。

`table_open_cache`

命令行格式	`--table-open-cache=#`
系统变量	`table_open_cache`
范围	global
动态的	是的
类型	整数
默认值	`2000`
最小值	`1`
最大值	`524288`

计算公式为： open_files_limit - 10 -max_connections / 2

`thread_cache_size`

命令行格式	`--thread-cache-size=#`
系统变量	`thread_cache_size`
范围	global
动态的	是的
类型	整数
默认值	`-1`（表示自动调整大小；不要分配此文字值）
最小值	`0`
最大值	`16384`

服务器应缓存多少线程以供重用。当客户端断开连接时，如果客户端的线程少于缓存中的线程，则将客户端的线程放入缓存中 thread_cache_size。如果可能，通过重用从缓存中获取的线程来满足对线程的请求，并且只有当缓存为空时才会创建新线程。如果您有很多新连接，可以增加此变量以提高性能。通常，如果您有良好的线程实现，这不会提供显着的性能改进。但是，如果您的服务器每秒看到数百个连接，您通常应该设置 thread_cache_size足够高，以至于大多数新连接都使用缓存线程。默认值基于以下公式，上限为 100： 8 + (max_connections / 100)

`back_log`

命令行格式	`--back-log=#`
系统变量	`back_log`
范围	global
动态的	不
类型	整数
默认值	`-1`（表示自动调整大小；不要分配此文字值）
最小值	`1`
最大值	`65535`

MySQL可以拥有的未完成连接请求数。当MySQL主线程在很短的时间内收到很多连接请求时，这一点就开始发挥作用。然后，主线程需要一些时间（尽管很少）来检查连接并启动一个新线程。back_log值指示在MySQL暂时停止响应新请求之前，在这短时间内可以叠加多少请求。只有当你期望在短时间内有大量的连接时，你才需要增加这个值。

back_log 不能设置为高于操作系统限制。

默认值基于以下公式，上限为 900：50 + (max_connections / 5)

`max_connect_errors`

命令行格式	`--max-connect-errors=#`
系统变量	`max_connect_errors`
范围	global
动态的	是的
类型	整数
默认值	`100`
最小值	`1`
最大值（64 位平台）	`18446744073709551615`
最大值（32 位平台）	`4294967295`

在出现max_connect_错误后，来自主机的连续连接请求在没有成功连接的情况下被中断，服务器会阻止该主机进行进一步的连接。如果在上一次连接中断后，在少于max_connect_errors的尝试次数内成功建立了与主机的连接，则主机的错误计数将清除为零。要解除阻止主机，请刷新主机缓存

`max_user_connections`

命令行格式	`--max-user-connections=#`
系统变量	`max_user_connections`
范围	global, session
动态的	是的
类型	整数
默认值	`0`
最小值	`0`
最大值	`4294967295`

任何给定 MySQL 用户帐户允许的最大同时连接数。值 0（默认值）表示 “没有限制。”

`wait_timeout`

命令行格式	`--wait-timeout=#`
系统变量	`wait_timeout`
范围	global, session
动态的	是的
类型	整数
默认值	`28800`
最小值	`1`
最大值 (Windows)	`2147483`
最大值（其他）	`31536000`

服务器在关闭非交互式连接之前等待其活动的秒数。

`connect_timeout`

命令行格式	`--connect-timeout=#`
系统变量	`connect_timeout`
范围	global
动态的	是的
类型	整数
默认值	`10`
最小值	`2`
最大值	`31536000`

mysqld 服务器在响应Bad handshake之前等待连接数据包的秒数。默认值为 10 秒。

如果客户端经常遇到以下错误，增加connect_timeout值可能会有所帮助。 Lost connection to MySQL server at 'XXX', system error: errno

`innodb_open_files`

命令行格式	`--innodb-open-files=#`
系统变量	`innodb_open_files`
范围	global
动态的	不
类型	整数
默认值	`-1`（表示自动调整大小；不要分配此文字值）
最小值	`10`
最大值	`2147483647`

指定InnoDB一次可以打开的最大文件数。最小值为10。如果innodb_file_per_table被禁用，默认值为300；否则，默认值为300或table_open_cache设置，以较高者为准。

`query_cache_limit`

命令行格式	`--query-cache-limit=#`
已弃用	5.7.20
系统变量	`query_cache_limit`
范围	global
动态的	是的
类型	整数
默认值	`1048576`
最小值	`0`
最大值（64 位平台）	`18446744073709551615`
最大值（32 位平台）	`4294967295`

不要缓存大于此字节数的结果。默认值为 1MB。

`query_cache_size`

命令行格式	`--query-cache-size=#`
已弃用	5.7.20
系统变量	`query_cache_size`
范围	全球的
动态的	是的
类型	整数
默认值	`1048576`
最小值	`0`
最大值（64 位平台）	`18446744073709551615`
最大值（32 位平台）	`4294967295`

分配给缓存查询结果的内存量。默认情况下，查询缓存处于禁用状态。这是使用默认值1M实现的，查询缓存类型的默认值为0。（如果将大小设置为0，为了显著减少开销，还应使用query_cache_type=0启动服务器。

允许值为1024的倍数；其他值向下舍入到最接近的倍数。对于query_cache_size的非零值，即使query_cache_type=0，也会分配许多字节的内存。有关更多信息，请参阅第8.10.3.3节“查询缓存配置”。

查询缓存需要大约40KB的最小大小来分配其结构。（具体大小取决于系统架构。）如果将query_cache_size的值设置得太小，则会出现警告.

`sort_buffer_size`

命令行格式	`--sort-buffer-size=#`
系统变量	`sort_buffer_size`
范围	global, session
动态的	是的
类型	整数
默认值	`262144`
最小值	`32768`
最大值 (Windows)	`4294967295`
最大值（其他，64 位平台）	`18446744073709551615`
最大值（其他，32 位平台）	`4294967295`

每个必须执行排序的会话都会分配一个这样大小的缓冲区。sort_buffer_size不特定于任何存储引擎，通常用于优化。sort_buffer_size值至少必须足够大，以容纳排序缓冲区中的15个元组。此外，增加“max_sort_length”的值可能需要增加“ sort_buffer_size”的值。

如果您 Sort_merge_passes在输出中看到每秒很多SHOW GLOBAL STATUS，您可以考虑增加该 sort_buffer_size值以加快ORDER BY或GROUP BY无法通过查询优化或改进的索引来改进的操作。

优化器试图计算出需要多少空间，但可以分配更多空间，直到达到极限。在全局范围内将其设置为大于所需值会减慢大多数排序查询的速度。最好将其作为会话设置来增加，并且仅适用于需要更大大小的会话。在Linux上，有256KB和2MB的阈值，其中较大的值可能会显著减慢内存分配，因此您应该考虑保持低于这些值中的一个。尝试为你的工作量找到最佳价值。

排序缓冲区大小的最大允许设置为4GB−1。64位平台允许使用较大的值（64位窗口除外，对于64位窗口，较大的值被截断为4GB）−1）带有警告）。

`read_only`

命令行格式	`--read-only[={OFF\|ON}]`
系统变量	`read_only`
范围	global
动态的	是的
类型	布尔值
默认值	`OFF`

如果read_only启用系统变量，则服务器不允许客户端更新，除非来自具有 SUPER特权的用户。默认情况下禁用此变量。

服务器还支持 super_read_only系统变量（默认禁用），它具有以下效果：

如果super_read_only启用，服务器将禁止客户端更新，即使是拥有SUPER 权限的用户也是如此。
设置super_read_only 为ON隐式 read_only强制 ON.
设置read_only为 OFF隐式 super_read_only强制 OFF.

即使read_only启用，服务器也允许这些操作：

如果服务器是副本，则由复制线程执行的更新。read_only在复制设置中，在副本服务器上启用以确保副本仅接受来自源服务器而不是来自客户端的更新可能很有用。
使用ANALYZE TABLEor OPTIMIZE TABLE语句。只读模式的目的是防止更改表结构或内容。分析和优化不属于此类更改。这意味着，例如，可以使用mysqlcheck --all-databases --analyze对只读副本执行一致性检查。
对TEMPORARY表的操作。
插入日志表（mysql.general_log和 mysql.slow_log）；请参阅第 5.4.1 节，“选择常规查询日志和慢查询日志输出目标”。
从 MySQL 5.7.16 开始，更新 Performance Schema 表，例如UPDATEor TRUNCATE TABLE操作。

复制源服务器上的更改read_only不会复制到副本服务器。可以在副本上设置该值，而与源上的设置无关。

以下条件适用于启用尝试 read_only（包括因启用而导致的隐式尝试 super_read_only）：

LOCK TABLES如果您有任何显式锁（使用获取）或有待处理的事务，则尝试失败并发生错误。
当其他客户端有任何正在进行的语句、活动LOCK TABLES WRITE或正在进行的提交时，尝试阻塞，直到锁被释放并且语句和事务结束。虽然启用的尝试处于read_only挂起状态，但其他客户端对表锁或开始事务的请求也会阻塞，直到 read_only设置完成。
如果存在持有元数据锁的活动事务，则尝试阻塞，直到这些事务结束。
read_only可以在您持有全局读锁（通过获取 FLUSH TABLES WITH READ LOCK）时启用，因为这不涉及表锁。

`read_rnd_buffer_size`

命令行格式	`--read-rnd-buffer-size=#`
系统变量	`read_rnd_buffer_size`
范围	global, session
动态的	是的
类型	整数
默认值	`262144`
最小值	`1`
最大值	`2147483647`

此变量用于从MyISAM 表中读取，并且对于任何存储引擎，用于多范围读取优化。

`net_buffer_length`

命令行格式	`--net-buffer-length=#`
系统变量	`net_buffer_length`
范围	global, session
动态的	是的
类型	整数
默认值	`16384`
最小值	`1024`
最大值	`1048576`
块大小	`1024`

每个客户端线程都与一个连接缓冲区和结果缓冲区相关联。两者都以给出的大小开始， net_buffer_length但根据需要动态放大到 max_allowed_packet字节。结果缓冲区缩小到 net_buffer_length每个 SQL 语句之后。

此变量通常不应更改，但如果您的内存很少，您可以将其设置为客户端发送的语句的预期长度。如果语句超过此长度，则连接缓冲区会自动扩大。net_buffer_length可设置的最大值为 1MB。

此变量的会话值是只读的。

`thread_stack`

命令行格式	`--thread-stack=#`
系统变量	`thread_stack`
范围	global
动态的	不
类型	整数
默认值（64 位平台）	`262144`
默认值（32 位平台）	`196608`
最小值	`131072`
最大值（64 位平台）	`18446744073709547520`
最大值（32 位平台）	`4294967295`
块大小	`1024`

每个线程的堆栈大小。默认值对于正常操作来说足够大。如果线程堆栈大小太小，则会限制服务器可以处理的 SQL 语句的复杂性、存储过程的递归深度以及其他消耗内存的操作。

`skip_name_resolve`

命令行格式	`--skip-name-resolve[={OFF\|ON}]`
系统变量	`skip_name_resolve`
范围	global
动态的	不
类型	布尔值
默认值	`OFF`

检查客户端连接时是否解析主机名。如果这个变量是OFF， mysqld在检查客户端连接时解析主机名。如果是ON， mysqld只使用 IP 地址；在这种情况下，Host授权表中的所有列值都必须是 IP 地址。

`sync_binlog`

Command-Line Format	`--sync-binlog=#`
System Variable	`sync_binlog`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`1`
Minimum Value	`0`
Maximum Value	`4294967295`

控制 MySQL 服务器将二进制日志同步到磁盘的频率。

sync_binlog=0：禁用MySQL服务器将二进制日志同步到磁盘。相反，MySQL 服务器依靠操作系统不时将二进制日志刷新到磁盘，就像它对任何其他文件所做的那样。此设置提供最佳性能，但在电源故障或操作系统崩溃的情况下，服务器可能已提交尚未同步到二进制日志的事务。
sync_binlog=1：在提交事务之前启用二进制日志到磁盘的同步。这是最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响。在电源故障或操作系统崩溃的情况下，二进制日志中丢失的事务仅处于准备状态。这允许自动恢复例程回滚事务，从而保证二进制日志中不会丢失任何事务。
sync_binlog=N，其中N是 0 或 1 以外的值：二进制日志在 N收集二进制日志提交组后同步到磁盘。在电源故障或操作系统崩溃的情况下，服务器可能已提交尚未刷新到二进制日志的事务。由于磁盘写入次数增加，此设置可能会对性能产生负面影响。较高的值可提高性能，但会增加数据丢失的风险。

为了在与事务一起使用的复制设置中获得最大可能的持久性和一致性InnoDB，请使用以下设置：

警告

许多操作系统和一些磁盘硬件会欺骗刷新到磁盘的操作。他们可能会告诉 mysqld刷新已经发生，即使它没有发生。在这种情况下，即使使用推荐的设置也无法保证事务的持久性，在最坏的情况下，断电可能会损坏InnoDB数据。在 SCSI 磁盘控制器或磁盘本身中使用电池供电的磁盘缓存可以加快文件刷新速度，并使操作更安全。您还可以尝试禁用硬件缓存中的磁盘写入缓存。

`innodb_flush_log_at_timeout`

Command-Line Format	`--innodb-flush-log-at-timeout=#`
System Variable	`innodb_flush_log_at_timeout`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`1`
Minimum Value	`1`
Maximum Value	`2700`
Unit	seconds

每N秒写入并刷新日志。 innodb_flush_log_at_timeout 允许增加刷新之间的超时时间，以减少刷新并避免影响二进制日志组提交的性能。默认设置为 innodb_flush_log_at_timeout 每秒一次。

`innodb_flush_log_at_trx_commit`

Command-Line Format	`--innodb-flush-log-at-trx-commit=#`
System Variable	`innodb_flush_log_at_trx_commit`
Scope	Global
Dynamic	Yes
Type	Enumeration
Default Value	`1`
Valid Values	`0` `1` `2`

控制提交操作的严格 ACID合规性与重新安排和批量完成提交相关 I/O 操作时可能实现的更高性能之间的平衡。您可以通过更改默认值来获得更好的性能，但是您可能会在崩溃中丢失事务。

完全符合 ACID 需要默认设置 1。日志在每次提交事务时写入并刷新到磁盘。
设置为 0 时，日志每秒写入并刷新到磁盘一次。尚未刷新日志的事务可能会在崩溃中丢失。
设置为 2 时，日志在每次事务提交后写入并每秒刷新到磁盘一次。尚未刷新日志的事务可能会在崩溃中丢失。
对于设置 0 和 2，不能 100% 保证每秒刷新一次。由于 DDL 更改和其他InnoDB 导致日志独立于 innodb_flush_log_at_trx_commit 设置刷新的内部活动，刷新可能更频繁地发生，有时由于调度问题而不太频繁。如果每秒刷新一次日志，则在崩溃中可能会丢失最多一秒的事务。如果日志刷新频率高于或低于每秒一次，则可能丢失的事务量会相应变化。
日志刷新频率由控制 innodb_flush_log_at_timeout，它允许您将日志刷新频率设置为 N秒（其中 N是1 ... 2700，默认值为 1）。但是，任何意外的mysqld进程退出都可以擦除长达N数秒的事务。
DDL 更改和其他内部InnoDB 活动独立于设置刷新日志 innodb_flush_log_at_trx_commit 。
InnoDB 无论设置如何，崩溃恢复 innodb_flush_log_at_trx_commit都有效。事务要么被完全应用，要么被完全擦除。

为了在与事务一起使用的复制设置中保持持久性和一致性InnoDB：

如果启用了二进制日志记录，请设置 sync_binlog=1.
总是设置 innodb_flush_log_at_trx_commit=1。

警告

许多操作系统和一些磁盘硬件会欺骗刷新到磁盘的操作。他们可能会告诉 mysqld刷新已经发生，即使它没有发生。在这种情况下，即使使用推荐的设置也无法保证事务的持久性，在最坏的情况下，断电可能会损坏 InnoDB数据。在 SCSI 磁盘控制器或磁盘本身中使用电池供电的磁盘缓存可以加快文件刷新速度，并使操作更安全。您还可以尝试禁用硬件缓存中的磁盘写入缓存。

`expire_logs_days`

Command-Line Format	`--expire-logs-days=#`
System Variable	`expire_logs_days`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`0`
Minimum Value	`0`
Maximum Value	`99`
Unit	days

参考文档

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html

posted @ 2022-03-03 11:38 小吉猫阅读(131) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

参考文档

公告