admin 管理员组

文章数量: 888297


2024年2月23日发(作者:oops网络用语什么意思)

mysql之全局变量详解

mysql全局变量详解

目前通过自己常用和网络配合,整理出来的,希望能给你们提供便利,请供参考。

show global variables

auto_increment_offset

auto_increment_increment

自增长字段从那个数开始,他的取值范围是1...65535

表示自增长字段每次递增的量,其默认值是1,取值范围是1...65535.

在主主同步配置时,需要将两台服务器的auto_increment_increment增长量都配置为2,而要把auto_increment_offset分别配置为1和2,这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。

设定MySQL事务是否自动提交,1表示立即提交,0表示需要显式提交。作用范围为全局或会话,可用于配置文件中(但在5.5.8之前的版本中不可用于配置文件),属于动态变量。

1、mysql使用InnoDB的引擎,那么是自动开启事务的,也就是每一条sql都是一个事务(除了select)。

2、由于第一条的原因,所以我们需要autocommit为on,否则每个query都要写一个commit才能提交。

3、在mysql的配置中,默认缺省autocommit就是为on,这里要注意,不用非要去mysql配置文件中显示地配置一下。

设定MySQL服务器是否为存储例程的创建赋予其创建存储例程上的EXECUTE和ALTER ROUTINE权限,默认为1(赋予此两个权限给其创建者)。作用范围为全局。

当MySQL的主线程在短时间内收到大量连接请求时,其会花些时间检测已经有线程并为新请求启动新线程,back_log参数的值即为短时间内到达的请求中有多少可以被接受并等待主MySQL线程进行后续处理。作用范围为全局,可以用配置文件,非动态变量。

用于指定MySQL的安装目录,所有其它的常用相对路径都相对于此处的路径而言。作用范围为全局,可用于配置文件中,但属于非动态变量。

将所有临时表存储在磁盘,而非内存中,缺点是如果某个SQL执行时需要用到临时表,则性能会差很多.

为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存,用于提高记录bin-log的效率,作用于全局。默认值:32768(32K),范围:4096 ..

4294967295

大小选择:没有什么大事务,dml也不是很频繁的情况下可以设置小一点,建议是1048576(1M).如果事务大而且多,dml操作也频繁,则可以适当的调大一点(建议2097152—4194304,即2——4M)。

binlog_direct_non_transactional_updates

binlog_format

参数默认是关闭的。开启后,不管任何情况对非事务表的操作都将记录binlog。

mysql复制主要有三种方式:基于SQL语句的复制(statement-based

replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制1

autocommit

automatic_sp_privileges

back_log

basedir=PATH

big_tables

binlog_cache_size

mysql之全局变量详解

(mixed-based replication, MBR)。对应的,binlog的格式也有三种:STATEMENT,ROW,MIXED。

(1)STATEMENT模式(SBR)

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

last_insert_id(),以及user-defined functions(udf)等会出现问题)

(2)ROW模式(RBR)

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

(3)MIXED模式(MBR)

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

binlog_stmt_cache_size

bulk_insert_buffer_size

character_set_client

character_set_connection

Size of the cache to hold nontransactional statements for the binary log during

a transaction

为一次插入多条新记录的INSERT命令分配的缓存区长度(默认设置是8M)。

设置客户端的字符集(作用于Global,Session).

The character set for statements that arrive from the client.

作用于Global,Session

The character set used for literals that do not have a character set introducer

and for number-to-string conversion.

用于没有字符集导入符的文字和数字-字符串转换。

默认数据库使用的字符集。当默认数据库更改时,服务器则设置该变量。如果没有默认数据库,变量的值同character_set_server.

设置文件系统的字符集.

The file system character set.

用于向客户端返回查询结果的字符集.

服务器的默认字符集。

服务器用来保存识别符的字符集。该值一定是utf8。

字符集安装目录。

连接字符集的校对规则。

默认数据库使用的校对规则。当默认数据库改变时服务器则设置该变量。如果没有默认数据库,变量的值同collation_server。

服务器的默认校对规则。

事务结束类型:

(1)如果该值为0(默认),COMMIT和ROLLBACK不受影响。

(2)如果该值为1,COMMIT和ROLLBACK分别等同于COMMIT AND CHAIN和ROLLBACK AND CHAIN。(新事务用刚刚结束的事务相同的间隔等级立即启

动)。

(3)如果该值为2,COMMIT和ROLLBACK分别等同于COMM它RELEASE和2

character_set_database

character_set_filesystem

character_set_results

character_set_server

character_set_system

character_sets_dir

collation_connection

collation_database

collation_server

completion_type

mysql之全局变量详解

ROLLBACK RELEASE。(事务终止后,服务器断开)。

concurrent_insert

如果为ON(默认值),MySQL允许INSERT和SELECT语句在中间没有空数据块的MyISAM表中并行运行。你可以用--safe或--skip-new启动mysqld关闭该选项。该变量为整数,有3个值:

0 关

1 (默认)在没有空数据块的MyISAM表中启用并行插入

2 为所有MyISAM表启用并行插入。如果表有空记录或正被另一线程使用,新行将插入到表的最后。如果表未使用,MySQL将进行普通读锁定并将新行插入空记录。

mysqld服务器用Bad handshake响应前等待连接包的秒数。

MySQL数据目录。可以用--datadir选项设置该变量。

默认存储引擎.默认为InnoDB.

该选项只适用MyISAM表。它具有下述值可以影响CREATE TABLE语句使用的DELAY_KEY_WRITE表选项的处理。

OFF DELAY_KEY_WRITE被忽略。

ON MySQL在CREATE TABLE中用DELAY_KEY_WRITE选项。这是默认值。

ALL 用启用DELAY_KEY_WRITE选项创建表的相同方法对所有新打开表的进行处理。

如果启用了DELAY_KEY_WRITE,说明使用该项的表的键缓冲区在每次更新索引时不被清空,只有关闭表时才清空。遮掩盖可以大大加快键的写操作,但如

果你使用该特性,你应用--myisam-recover选项启动服务器,为所有MyISAM表添加自动检查(例如,--myisam-recover=BACKUP,FORCE).

插入delayed_insert_limit 延迟行后,INSERT DELAYED 处理器线程检查是否有挂起的SELECT语句。如果有,在继续插入延迟的行之前,允许它们先执行。

INSERT DELAYED处理器线程终止前应等待INSERT语句的时间。

这是各个表中处理INSERT DELAYED语句时队列中行的数量限制。如果队列满了,执行INSERT DELAYED语句的客户端应等待直到队列内再有空间。

该变量说明用/操作符执行除操作的结果可增加的精确度的位数。默认值是4。最小和最大值分别为0和30。下面的示例说明了增加默认值的结果。

该变量适用于NDB。默认值为0(OFF):如果你执行类似查询SELECT * FROM

t WHERE mycol = 42,其中mycol为没有索引的列,当满了的表扫描每个NDB节点时,执行该查询。每个节点使用WHERE条件将每一行发送给MySQL服务器。如果engine_condition_pushdown被设置为1(ON),该条件“pushed

down”给存储引擎并发送给NDB节点。每个节点都执行扫描,并只向MySQL服务器发送回匹配条件的行。

二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除。

如果用--flush选项启动mysqld该值为ON。

如果设为非零值,每隔flush_time秒则关闭所有表以释放硬盘资源并同步未清空的数据。我们建议只在Windows 9x或Me,或有最小资源的系统中使用该选项.

If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If

3

connect_timeout

datadir

default_storage_engine

delay_key_write

delayed_insert_limit

delayed_insert_timeout

delayed_queue_size

div_precision_increment

engine_condition_pushdown

expire_logs_days

flush

flush_time

foreign_key_checks

mysql之全局变量详解

set to 0, foreign key constraints are ignored, with a couple of exceptions. When

re-creating a table that was dropped, an error is returned if the table definition

does not conform to the foreign key constraints referencing the table. Likewise,

an ALTER TABLE operation returns an error if a foreign key definition is

incorrectly formed.

ft_boolean_syntax

使用IN BOOLEAN MODE执行的布尔全文搜索支持的操作符系列。参见12.7.1节,“布尔全文搜索”。

默认变量值为'+ -><()~*:""&|'。更改这些值的规则是:

o 操作符函数由其在字符串内的位置决定。

o 替换值必须是14个字符。

o 每个字符必须为ASCII码非文字数字字符。

o 第1个或第2个字符必须为空格。

o 除非语句在第11个字符和第12个字符处引用了操作符,否则不允许复制。这两个字符可以不相同,但这是唯一可能的两个。

o 位置10、13和14(默认设置为‘:’、‘&’和‘|’)保留用于将来扩展。

FULLTEXT索引中所包含的字的最大长度。

注释:更改该变量后必须重建FULLTEXT索引。应使用REPAIR TABLE tbl_name

QUICK。

FULLTEXT索引中所包含的字的最小长度。

注释:更改该变量后必须重建FULLTEXT索引。应使用REPAIR TABLE tbl_name

QUICK。

使用WITH QUERY EXPANSION进行全文搜索的最大匹配数。

用于读取全文搜索的停止字清单的文件。该文件中的所有字都会用到;注释不重要。默认情况下,使用内嵌式停止字清单(如myisam/ft_static.c文件中所定义)。将该变量设置为空字符串('')则禁用停止字过滤。

注释:更改该变量或停止字文件的内容后必须重建FULLTEXT索引。应使用REPAIR TABLE tbl_name QUICK。

Whether the general query log is enabled. The value can be 0 (or OFF) to

disable the log or 1 (or ON) to enable the log. The default value depends on

whether the --general log option is given. The destination for log output is

controlled by the log output.

是否启用了通用查询日志。值可以是0(或关闭)禁用日志或1(或在)启用日志。默认值取决于——通用日志选项。日志输出的目的地是由日志输出控制.

The name of the general query log file. The default value is host_, but

the initial value can be changed with the --general_log_file option.

一般查询日志文件的名称。默认值是主机名。日志,但初始值可以改变——通用日志文件选项。

允许的GROUP_CONCAT()函数结果的最大长度。

是否zlib压缩库适合该服务器。如果不适合,不能使用COMPRESS()和UNCOMPRESS()函数。

是否crypt()系统调用适合该服务器。如果不适合,不能使用CRYPT()函数。

如果mysqld支持ARCHIVE表则为YES,否则为NO。

YES if mysqld supports dynamic loading of plugins, NO if not. If the value is NO,

4

ft_max_word_len

ft_min_word_len

ft_query_expansion_limit

ft_stopword_file

general_log

general_log_file

group_concat_max_len

have_compress

have_crypt

have_csv

have_dynamic_loading

mysql之全局变量详解

you cannot use options such as --plugin-load to load plugins at server startup,

or the INSTALL PLUGIN statement to load plugins at runtime.

have_geometry

have_innodb

have_ndbcluster

have_openssl

have_partitioning

have_profiling

have_query_cache

have_rtree_keys

have_ssl

是否服务器支持空间数据类型。

如果mysqld支持InnoDB表则为YES。如果使用--skip-innodb则为DISABLED。

如果mysqld支持NDB CLUSTER表则为YES。如果使用了--skip-ndbcluster则为DISABLED。

如果mysqld支持客户端/服务器协议的SSL(加密)则为YES。

YES if mysqld supports partitioning.

YES if statement profiling capability is present, NO if not. If present, the

profiling system variable controls whether this capability is enabled or disabled.

如果mysqld支持查询缓存则为YES。

RTREE索引是否可用。(用于MyISAM表的空间索引)

YES if mysqld supports SSL connections, NO if not. DISABLED indicates that the

server was compiled with SSL support, but was not started with the appropriate

--ssl-xxx options. For more information.

是否启用符号链接支持。在Unix中需要用于支持DATA DIRECTORY和INDEX

DIRECTORY表选项。

The server sets this variable to the server host name at startup.

服务器为每个连接的客户端执行的字符串。字符串由一个或多个SQL语句组成。要想指定多个语句,用分号间隔开。例如,每个客户端开始时默认启用autocommit模式。没有全局服务器变量可以规定autocommit默认情况下应禁用,但可以用init_connect来获得相同的效果.

启动服务器时用--init-file选项指定的文件名。文件中包含服务器启动时要执行的SQL语句。每个语句必须在同一行中并且不能包括注释。

该变量类似init_connect,但是每次SQL线程启动时从服务器应执行该字符串。该字符串的格式与init_connect变量相同。

Specifies whether to dynamically adjust the rate of flushing dirty pages in the

InnoDB buffer pool based on the workload. Adjusting the flush rate dynamically

is intended to avoid bursts of I/O activity. This setting is enabled by default. See

Section 14.6.3.5, “Configuring InnoDB Buffer Pool Flushing” for more

nformation.

Whether the InnoDB adaptive hash index is enabled or disabled. It may be

desirable, depending on your workload, to dynamically enable or disable

adaptive hash indexing to improve query e the adaptive

hash index may not be useful for all workloads, conduct benchmarks with it

both enabled and disabled, using realistic workloads.

InnoDB用来存储数据目录信息&其它内部数据结构的内存池的大小。你应用程序里的表越多,你需要在这里分配越多的内存。如果InnoDB用光了这个池内的内存,InnoDB开始从操作系统分配内存,并且往MySQL错误日志写警告信息。默认值是1MB。

当自动扩展表空间被填满之时,为扩展而增加的尺寸(MB为单位)。默认值是8。这个选项可以在运行时作为全局系统变量而改变。

The lock mode to use for generating auto-increment values. The permissible

values are 0, 1,or 2, for “traditional”, “consecutive”, or “interleaved” lock

5

have_symlink

hostname

init_connect

init_file

init_slave

innodb_adaptive_flushing

innodb_adaptive_hash_index

innodb_additional_mem_pool_size

innodb_autoextend_increment

innodb_autoinc_lock_mode

mysql之全局变量详解

mode, respectively.

Divides the buffer pool into a user-specified number of separate regions, each

with its own LRU list and related data structures, to reduce contention during

concurrent memory read and write operations. This option only takes effect

when you set innodb_buffer_pool_size to a value of 1GB or more. The total size

you specify is divided among all the buffer pools. For best efficiency, specify a

combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so

that each buffer pool instance is at least 1 gigabyte.

InnoDB用来缓存它的数据和索引的内存缓冲区的大小。你把这个值设得越高,访问表中数据需要得磁盘I/O越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的80%。尽管如此,还是不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。

Whether InnoDB performs change buffering, an optimization that delays write

operations to secondary indexes so that the I/O operations can be performed

sequentially. The permitted values are described in the following table.

innodb_buffer_pool_instances

innodb_buffer_pool_size

innodb_change_buffering

InnoDB在所有对磁盘的页面读取上使用校验和验证以确保额外容错防止硬件损坏或数据文件。尽管如此,在一些少见的情况下(比如运行标准检查innodb_checksums

之时)这个额外的安全特征是不必要的。在这些情况下,这个选项(默认是允许的)可以用--skip-innodb-checksums来关闭。

The number of threads that can commit at the same time. A value of 0 (the

innodb_commit_concurrency

default) permits any number of transactions to commit simultaneously.

Determines the number of threads that can enter InnoDB concurrently. A

thread is placed in a queue when it tries to enter InnoDB if the number of

threads has already reached the concurrency a thread is permitted

to enter InnoDB, it is given a number of “free tickets” equal to the value of

innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely

until it has used up its tickets. After that point, the thread again becomes

subject to the concurrency check (and possible queuing) the next time it tries

to enter InnoDB. The default value is 5000 as of MySQL 5.6.6, 500 before that.

到单独数据文件和它们尺寸的路径。通过把innodb_data_home_dir连接到这里指定的每个路径,到每个数据文件的完整目录路径可被获得。文件大小通过给尺寸值尾加M或G以MB或者GB(1024MB)为单位被指定。文件尺寸的和至少是10MB。在一些操作系统上,文件必须小于2GB。如果你没有指定innodb_data_file_path,开始的默认行为是创建一个单独的大小10MB名为ibdata1的自扩展数据文件。在那些支持大文件的操作系统上,你可以设置文件大小超过4GB。你也可以使用原始磁盘分区作为数据文件.

目录路径对所有InnoDB数据文件的共同部分。如果你不设置这个值,默认是MySQL数据目录。你也可以指定这个值为一个空字符串,在这种情况下,你可以在innodb_data_file_path中使用绝对文件路径。

默认地,InnoDB存储所有数据两次,第一次存储到doublewrite缓冲,然后存储到确实的数据文件。这个选项可以被用来禁止这个功能。类似于innodb_checksums,这个选项默认是允许的;因为标准检查或在对顶级性能的需要超过对数据完整性或可能故障的关注之时,这个选项用--skip-innodb-doublewrite来关闭。

6

innodb_concurrency_tickets

innodb_data_file_path

innodb_data_home_dir

innodb_doublewrite

mysql之全局变量详解

innodb_fast_shutdown

如果你把这个参数设置为0,InnoDB在关闭之前做一个完全净化和一个插入缓冲合并。这些操作要花几分钟时间,设置在极端情况下要几个小时。如果你设置这个参数为1,InnoDB在关闭之时跳过这些操作。默认值为1。如果你设置这个值为2 (在Netware无此值), InnoDB将刷新它的日志然后冷关机,仿佛MySQL崩溃一样。已提交的事务不会被丢失,但在下一次启动之时会做一个崩溃恢复。

The file format to use for new InnoDB tables. Currently, Antelope and

Barracuda are supported. This applies only for tables that have their own

tablespace, so for it to have an effect,innodb_file_per_table must be enabled.

The Barracuda file format is required for certain InnoDB features such as table

aware that ALTER TABLE operations that recreate InnoDB tables

(ALGORITHM=COPY) will use the current innodb_file_format setting (the

conditions outlined above still apply).

This variable can be set to 1 or 0 at server startup to enable or disable whether

InnoDB checks the file format tag in the system tablespace (for example,

Antelope or Barracuda). If the tag is checked and is higher than that supported

by the current version of InnoDB, an error occurs and InnoDB does not start. If

the tag is not higher, InnoDB sets the value of innodb_file_format_max to the

file format tag.

At server startup, InnoDB sets the value of this variable to the file format tag in

the system tablespace (for example, Antelope or Barracuda). If the server

creates or opens a table with a “higher” file format, it sets the value of

innodb_file_format_max to that format.

这个选项致使InnoDB用自己的.ibd文件为存储数据和索引创建每一个新表,而不是在共享表空间中创建.

当innodb_flush_log_at_trx_commit被设置为0,日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何操作。当这个值为1(默认值)之时,在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新。当设置为2之时,在每个提交,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新。尽管如此,在对日志文件的刷新在值为2的情况也每秒发生一次。我们必须注意到,因为进程安排问题,每秒一次的刷新不是100%保证每秒都发生。你可以通过设置这个值不为1来获得较好的性能,但随之你会在一次崩溃中损失二分之一价值的事务。如果你设置这个值为0,那么任何mysqld进程的崩溃会删除崩溃前最后一秒的事务,如果你设置这个值为2,那么只有操作系统崩溃或掉电才会删除最后一秒的事务。尽管如此,InnoDB的崩溃恢复不受影响,而且因为这样崩溃恢复开始作用而不考虑这个值。注意,许多操作系统和一些磁盘硬件会欺骗刷新到磁盘操作。尽管刷新没有进行,你可以告诉mysqld刷新已经进行。即使设置这个值为1,事务的持久程度不被保证,且在最坏情况下掉电甚至会破坏InnoDB数据库。在SCSI磁盘控制器中,或在磁盘自身中,使用有后备电池的磁盘缓存会加速文件刷新并且使得操作更安全。你也可以试着使用Unix命令hdparm来在硬件缓存中禁止磁盘写缓存,或使用其它一些对硬件提供商专用的命令。这个选项的默认值是1。

7

innodb_file_format

innodb_file_format_check

innodb_file_format_max

innodb_file_per_table

innodb_flush_log_at_trx_commit

mysql之全局变量详解

innodb_flush_method

这个选项只在Unix系统上有效。如果这个选项被设置为fdatasync (默认值),InnoDB使用fsync()来刷新数据和日志文件。如果被设置为O_DSYNC,InnoDB使用O_SYNC来打开并刷新日志文件,但使用fsync()来刷新数据文件。如果O_DIRECT被指定了(在一些GNU/Linux版本商可用),InnoDB使用O_DIRECT来打开数据文件,并使用fsync()来刷新数据和日志文件。注意,InnoDB使用fsync()来替代fdatasync(),并且它默认不使用O_DSYNC,因为这个值在许多Unix变种上已经发生问题。

Lets InnoDB load tables at startup that are marked as corrupted. Use only

during troubleshooting, to recover data that is otherwise inaccessible. When

troubleshooting is complete, turn this setting back off and restart the server.

警告:这个选项仅在一个紧急情况下被定义,当时你想要从损坏的数据库转储表。可能的值为从1到6。作为一个安全措施,当这个选项值大于零之时,InnoDB阻止用户修改数据。

The innodb_io_capacity parameter sets an upper limit on the I/O activity

performed by the InnoDB background tasks, such as flushing pages from the

buffer pool and merging data from the change default value is 200.

For busy systems capable of higher I/O rates, you can set a higher value at

server startup, to help the server handle the background maintenance work

associated with a high rate of row innodb_io_capacity limit is a

total limit for all buffer pool instances. When dirty pages are flushed, the

innodb_io_capacity limit is divided equally among buffer pool

systems with individual 5400 RPM or 7200 RPM drives, you might lower the

value to the former

default of parameter should be set to approximately the number of I/O

operations that the system can performper second. Ideally, keep this setting as

low as practical, but not so low that these background activities InnoDB System

Variables 2071 fall behind. If the value is too high, data is removed from the

buffer pool and insert buffer too quickly to provide significant benefit from the

value represents an estimated proportion of the I/O operations per

second (IOPS) available to older-generation disk drives that could perform

about 100 IOPS. The current default of 200 reflects that modern storage

devices are capable of much higher I/O general, you can increase the

value as a function of the number of drives used for InnoDB I/O,particularly fast

drives capable of high numbers of IOPS. For example, systems that use multiple

disks or

solid-state disks for InnoDB are likely to benefit from the ability to control this

parameter.

Enable this option to allow index key prefixes longer than 767 bytes (up to 3072

bytes) for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

(Creating such tables also requires the option values

innodb_file_format=barracuda and innodb_file_per_table=true.)

InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒。为在一个复制建立中最大可能的持久程8

innodb_force_load_corrupted

innodb_force_recovery

innodb_io_capacity

innodb_large_prefix

innodb_lock_wait_timeout

mysql之全局变量详解

度和连贯性,你应该在主服务器上的文件里使用innodb_flush_log_at_trx_commit=1和sync-binlog=1。

这个选项在InnoDB搜索和索引扫描中关闭下一键锁定。这个选项的默认值是假(false)。正常地,InnoDB使用一个被称为next-key locking的算法。当搜索或扫描一个表索引之时,InnoDB以这样一种

方式实行行级锁定,它对任何遇到的索引记录设置共享的或独占的锁定。因此,行级锁定实际是索引记录锁定。InnoDB对索引记录设置的锁定也影响被锁定索引记录之前的“gap”。如果一个用户对某一索引内的记录R又共享的或独占的锁定,另一个用户不能立即在R之前以索引的顺序插入一个新的索引记录。这个选项导致InnoDB不在搜索或索引扫描中使用下一键锁定。下一键锁定仍然被用来确保外键强制及重复键核查.

InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小。明智的值是从1MB到8MB。默认的是1MB。一个大的日志缓冲允许大型事务运行而不需要在事务提交之前往磁盘写日志。因此,如果你有大型事务,使日志缓冲区更大以节约磁盘I/O。

在日志组里每个日志文件的大小。在32位计算机上日志文件的合并大小必须少于4GB。默认是5MB。明智的值从1MB到N分之一缓冲池大小,其中N是组里日志文件的数目。值越大,在缓冲池越少需要检查点刷新行为,以节约磁盘I/O。但更大的日志文件也意味这在崩溃时恢复得更慢。

在日志组里日志文件的数目。InnoDB以循环方式写进文件。默认是2(推荐)。

innodb_locks_unsafe_for_binlog

innodb_log_buffer_size

innodb_log_file_size

innodb_log_files_in_group

到InnoDB日志文件的目录路径。它必须有和innodb_log_arch_dir一样的值。innodb_log_group_home_dir

如果你不指定任何InnoDB日志参数,默认的是在MySQL数据目录里创建两个5MB大小名为ib_logfile0和ib_logfile1的文件。

innodb_max_dirty_pages_pct

这是一个范围从0到100的整数。默认是90。InnoDB中的主线程试着从缓冲池写页面,使得脏页(没有被写的页面)的百分比不超过这个值。如果你有SUPER权限,这个百分比可以在服务器运行时按下面来改变:SET

GLOBAL innodb_max_dirty_pages_pct = value;

这个选项控制在净化操作被滞后之时,如何延迟INSERT, UPDATE和DELETE操作。这个参数的默认值是零,意为无延迟。这个选项可以在运行时作为全局系统变量而被改变。InnoDB事务系统维持一个事务列表,该列表有被UPDATE或DELETE操作标志为删除的索引记录。让这个列表的长度为purge_lag。当purge_lag超过innodb_max_purge_lag之时,每个INSERT,

UPDATE和DELETE操作延迟((purge_lag/innodb_max_purge_lag)*10)-5毫秒。在净化批处理的开始,延迟每隔10秒计算。如果因为一个旧的可以看到行被净化的一致的读查看,删除操作不被延迟。对有问题的工作量,典型设置可能是1百万,假设我们的事务很小,只有100字节大小,我们就可以允许在我们的表之中有100MB未净化的行。

我们为数据库保持的日志组内同样拷贝的数量。当前这个值应该被设为1。

Specifies the approximate percentage of the InnoDB buffer pool used for the

old block sublist. The range of values is 5 to 95. The default value is 37 (that is,

3/8 of the pool).

Non-zero values protect against the buffer pool being filled up by data that is

9

innodb_max_purge_lag

innodb_mirrored_log_groups

innodb_old_blocks_pct

innodb_old_blocks_time

mysql之全局变量详解

referenced only for a brief period, such as during a full table scan. Increasing

this value offers more protection against full tablescans interfering with data

cached in the buffer ies how long in milliseconds (ms) a block

inserted into the old sublist must stay there after its first access before it can be

moved to the new sublist. If the value is 0, a block inserted into the old sublist

moves immediately to the new sublist the first time it is accessed, no matter

how soon after insertion the access occurs. If the value is greater than 0, blocks

remain in the old sublist until an access occurs at least that many ms after the

first access. For example, a value of 1000 causes blocks to stay in the old sublist

for 1 second after the first access before they become eligible to move to the

new default value is 1000 as of MySQL 5.6.6, 0 before that.

innodb_open_files

在InnoDB中,这个选项仅与你使用多表空间时有关。它指定InnoDB一次可以保持打开的.ibd文件的最大数目。最小值是10。默认值300。

When this option is enabled, information about all deadlocks in InnoDB user

transactions is recorded in the mysqld error log. Otherwise, you see

information about only the last deadlock, using the SHOW ENGINE INNODB

STATUS command. An occasional InnoDB deadlock is not necessarily an

issue,because InnoDB detects the condition immediately, and rolls back one of

the transactions might use this option to troubleshoot why

deadlocks are happening if an application does not have appropriate

error-handling logic to detect the rollback and retry its operation. A large

number of deadlocks might indicate the need to restructure transactions that

issue DML or SELECT ... FOR UPDATE statements for multiple tables, so that

each transaction accesses the tables in the same order,thus avoiding the

deadlock condition.

The granularity of changes, expressed in units of redo log records, that trigger a

purge operation,flushing the changed buffer pool blocks to disk. This option is

intended for tuning performance in combination with the setting

innodb_purge_threads=n, and typical users do not need to modify it.

The number of background threads devoted to the InnoDB purge operation.

The new default and minimum value of 1 in MySQL 5.6.5 signifies that the

purge operation is always performed by background threads, never as part of

the master thread. Non-zero values runs the purge operation in one or more

background threads, which can reduce internal contention within InnoDB,

improving scalability. Increasing the value to greater than 1 creates that many

separate purge threads, which can improve efficiency on systems where DML

operations are performed on multiple tables. The maximum is 32.

Enables the random read-ahead technique for optimizing InnoDB I/O. Random

read-ahead functionality was removed from the InnoDB Plugin (version 1.0.4)

and was therefore not included in MySQL 5.5.0 when InnoDB Plugin became

the “built-in” version of InnoDB. Random read-ahead was reintroduced in

MySQL 5.1.59 and 5.5.16 and higher along with the

innodb_random_read_ahead configuration option, which is disabled by default.

Controls the sensitivity of linear read-ahead that InnoDB uses to prefetch pages

10

innodb_print_all_deadlocks

innodb_purge_batch_size

innodb_purge_threads

innodb_random_read_ahead

innodb_read_ahead_thresho

mysql之全局变量详解

ld into the buffer InnoDB reads at least innodb_read_ahead_threshold

pages sequentially from an extent (64 pages), it initiates an asynchronous read

for the entire following extent. The permissible range of values is 0 to 64. As of

MySQL 5.6.1, a value of 0 disables read-ahead. Prior to 5.6.1, a value of 0 would

trigger a read-ahead upon reading the boundary page of a 64 page extent. For

the default of 56, InnoDB must read at least 56 pages sequentially from an

extent to initiate an asynchronous read for the following extent.

The number of I/O threads for read operations in InnoDB. The default value is

4. Its counterpart for write threads is innodb_write_io_threads. See Section

14.6.7, “Configuring the Number of Background InnoDB I/O Threads” for

more information.

The replication thread delay (in

innodb_thread_concurrency is reached.

ms) on a slave server if

innodb_read_io_threads

innodb_replication_delay

innodb_rollback_on_timeout

In MySQL 5.6, InnoDB rolls back only the last statement on a transaction

timeout by default. If innodb_rollback_on_timeout is specified, a transaction

timeout causes InnoDB to abort and roll back the entire transaction.

Defines how many of the rollback segments in the system tablespace are used

for InnoDB setting, while still valid, is replaced by

innodb_undo_logs.

The maximum delay between polls for a spin lock. The low-level

implementation of this mechanism varies depending on the combination of

hardware and operating system, so the delay does not correspond to a fixed

time interval. The default value is 6.

How the server treats NULL values when collecting statistics about the

distribution of index values for InnoDB tables. This variable has three possible

values, nulls_equal, nulls_unequal, and nulls_ignored. For nulls_equal, all NULL

index values are considered equal and form a single value group that has a size

equal to the number of NULL values. For nulls_unequal, NULL values are

considered unequal, and each NULL forms a distinct value group of size 1. For

nulls_ignored, NULL values are ignored.

When this variable is enabled, InnoDB updates statistics when metadata

statements such as SHOW TABLE STATUS or SHOW INDEX are run, or when

accessing the INFORMATION_ or

INFORMATION_TICS tables. (These updates are similar to what

happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics

during these operations. Leaving this setting disabled can improve access speed

for schemas that have a large number of tables or indexes. It can also improve

the stability of execution plans for queries that involve InnoDB tables.

To change the setting, issue the statement SET GLOBAL

innodb_stats_on_metadata=mode,where mode is either ON or OFF (or 1 or 0).

Changing this setting requires the SUPER privilege and immediately affects the

operation of all connections.

When innodb_strict_mode is ON, InnoDB returns errors rather than warnings

11

innodb_rollback_segments

innodb_spin_wait_delay

innodb_stats_method

innodb_stats_on_metadata

innodb_stats_sample_pages Deprecated, use innodb_stats_transient_sample_pages instead.

innodb_strict_mode

mysql之全局变量详解

for certain default value is OFF.

Strict mode helps guard against ignored typos and syntax errors in SQL, or other

unintended consequences of various combinations of operational modes and

SQL statements. When innodb_strict_mode is ON, InnoDB raises error

conditions in certain cases, rather than issuing a warning and processing the

specified statement (perhaps with unintended behavior). This is analogous to

sql_mode in MySQL, which controls what SQL syntax MySQL accepts, and

determines whether it silently ignores errors, or validates input syntax and data

values.

The innodb_strict_mode setting affects the handling of syntax errors for

CREATE TABLE, ALTER TABLE and CREATE INDEX statements.

innodb_strict_mode also enables a record size check, so that an INSERT or

UPDATE never fails due to the record being too large for the selected page

recommends enabling innodb_strict_mode when using

ROW_FORMAT and

KEY_BLOCK_SIZE clauses on CREATE TABLE, ALTER TABLE, and CREATE INDEX

innodb_strict_mode is OFF, InnoDB ignores conflicting

clauses and creates the table or index, with only a warning in the message log.

The resulting table might have different behavior than you intended, such as

having no compression when you tried to create a compressed table. When

innodb_strict_mode is ON, such problems generate an immediate error and the

table or index is not created, avoiding a troubleshooting session later.

You can turn innodb_strict_mode ON or OFF on the command line when you

start mysqld, or in the configuration file or . You can also enable or

disable innodb_strict_mode at runtime with the statement SET

[GLOBAL|SESSION] innodb_strict_mode=mode, where mode is either ON or

OFF. Changing the GLOBAL setting requires the SUPER privilege and affects the

operation of all clients that subsequently connect. Any client can change the

SESSION setting for innodb_strict_mode, and the setting affects only that client.

当被设置为ON或者1(默认地),这个变量允许InnoDB支持在XA事务中的双向提交。允许innodb_support_xa导致一个额外的对事务准备的磁盘刷新。如果你对使用XA并不关心,你可以通过设置这个选项为OFF或0来禁止这个变量,以减少磁盘刷新的次数并获得更好的InnoDB性能。

The number of times a thread waits for an InnoDB mutex to be freed before the

thread is default value is 30.

InnoDB重视LOCK TABLES,直到所有其它线程已经释放他们所有对表的锁定,MySQL才从LOCK TABLE ..WRITE返回。默认值是1,这意为LOCK TABLES让InnoDB内部锁定一个表。在使用AUTOCOMMIT=1的应用

里,InnoDB的内部表锁定会导致死锁。你可以在文件(Windows上是文件)里设置innodb_table_locks=0 来消除这个问题。

innodb_support_xa

innodb_sync_spin_loops

innodb_table_locks

InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制。如果有性能问题,并且SHOW INNODB STATUS显示许多线程在等innodb_thread_concurrency

待信号,可以让线程“thrashing”,并且设置这个参数更小或更大。如果你的计算机有多个处理器和磁盘,你可以试着这个值更大以更好地利用计算 12

mysql之全局变量详解

机的资源。一个推荐的值是系统上处理器和磁盘的个数之和。值为500或比500大会禁止调用并发检查。默认值是20,并且如果设置大于或等于20,并发检查将被禁止。

How long InnoDB threads sleep before joining the InnoDB queue, in

microseconds. The default value is 10000. A value of 0 disables sleep. In MySQL

5.6.3 and higher, you can set the configuration option

innodb_adaptive_max_sleep_delay to the highest value you would allow for

and InnoDB automatically adjusts

innodb_thread_sleep_delay innodb_thread_sleep_delay,

innodb_thread_sleep_delay up or down depending on the current

thread-scheduling activity. This dynamic adjustment helps the thread

scheduling mechanism to work smoothly during times when the system is

lightly loaded and when it is operating near full capacity.

Specifies whether to use the Linux asynchronous I/O subsystem. This variable

applies to Linux systems only, and cannot be changed while the server is

running. Normally, you do not need to touch this option,because it is enabled

by of MySQL 5.5, the asynchronous I/O capability that InnoDB has on

Windows systems is available on Linux systems. (Other Unix-like systems

continue to use synchronous I/O calls.) This feature improves the scalability of

heavily I/O-bound systems, which typically show many pending reads/writes in

the output of the command SHOW ENGINE INNODB STATUSG.

Running with a large number of InnoDB I/O threads, and especially running

multiple such instances on the same server machine, can exceed capacity limits

on Linux systems. In this case, you may receive the following error:

EAGAIN: The specified maxevents exceeds the user's limit of available events.

You can typically address this error by writing a higher limit to

/proc/sys/fs/r, if a problem with the asynchronous I/O

subsystem in the OS prevents InnoDB

from starting, you can start the server with innodb_use_native_aio=0 disabled

(use innodb_use_native_aio=0 in the option file). This option may also be

turned off automatically

during startup if InnoDB detects a potential problem such as a combination of

tmpdir location, tmpfs filesystem, and Linux kernel that does not support AIO

on tmpfs.

Whether InnoDB uses the operating system memory allocator (ON) or its own

(OFF). The default value is ON.

The InnoDB version number. Starting in 5.6.11, the separate numbering for

InnoDB is discontinued and this value is the same as for the version variable.

The number of I/O threads for write operations in InnoDB. The default value is

4. Its counterpart for read threads is innodb_read_io_threads.

服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。又见wait_timeout。

用于完全联接的缓冲区的大小(当不使用索引的时候使用联接操作)。一般情况获得快速联接的最好方法是添加索引。当增加索引时不可能通过增加13

innodb_use_native_aio

innodb_use_sys_malloc

innodb_version

innodb_write_io_threads

interactive_timeout

join_buffer_size

mysql之全局变量详解

keep_files_on_create

join_buffer_size值来获得快速完全联接。将为两个表之间的每个完全联接分配联接缓冲区。对于多个表之间不使用索引的复杂联接,需要多联接缓冲区。

If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is

created in the database directory. By default, if MyISAM finds an existing .MYD

file in this case, it overwrites it. The same applies to .MYI files for tables created

with no INDEX DIRECTORY option. To suppress this behavior, set the

keep_files_on_create variable to ON (1), in which case MyISAM will not

overwrite existing files and returns an error instead. The default value is OFF

(0). If a MyISAM table is created with a DATA DIRECTORY or INDEX DIRECTORY

option and an existing .MYD or .MYI file is found, MyISAM always returns an

error. It will not overwrite a file in the specified directory.

MyISAM表的索引块分配了缓冲区,由所有线程共享。key_buffer_size是索引块缓冲区的大小。键值缓冲区即为键值缓存。key_buffer_size的最大允许设定值为4GB。有效最大值可以更小,取决于可用物理RAM和操作系统或硬件平台强加的每个进程的RAM限制。增加该值,达到你可以提供的更好的索引处理(所有读和多个写操作)。通常为主要运行MySQL的机器内存的25%。但是,如果你将该值设得过大(例如,大于总内存的50%),系统将转换为页并变得极慢。MySQL依赖操作系统来执行数据读取时的文件系统缓存,因此你必须为文件系统缓存留一些空间。

同时写多行时要想速度更快,应使用LOCK TABLES。

你可以通过执行SHOW STATUS语句并检查Key_read_requests、Key_reads、Key_write_requests和Key_writes状态变量来检查键值缓冲区的性能。

Key_reads/Key_read_requests比例一般应小于0.01。如果你使用更新和删除,Key_writes/Key_write_requests比例通常接近1,但如果你更新时会同时影响到多行或如果你正使用DELAY_KEY_WRITE表选项,可能小得多。用key_buffer_size结合Key_blocks_unused状态变量和缓冲区块大小,可以确定使用的键值缓冲区的比例。从key_cache_block_size服务器变量可以获得缓冲区块大小。使用的缓冲区的比例为:1 - ((Key_blocks_unused *

key_cache_block_size) / key_buffer_size)该值为约数,因为键值缓冲区的部分空间被分配用作内部管理结构。

可以创建多个MyISAM键值缓存。4GB限制可以适合每个缓存,而不是一个组。

该值控制将缓冲区从键值缓存热子链(sub-chain)降级到温子链(sub-chain)。如果值更低,则降级更快。最小值为100。默认值是300。

键值缓存内块的字节大小。默认值是1024。

键值缓存缓冲区链热子链和温子链的划分点。该值为缓冲区链用于温子链的百分比。允许的值的范围为1到100。默认值是100。

mysqld编译时是否使用了大文件支持选项。

If large page support is enabled, this shows the size of memory pages. Large

memory pages are supported only on Linux; on other platforms, the value of

this variable is always 0.

说明是否启用了大页面支持。

The locale to use for error messages. The default is en_US. The server converts

14

key_buffer_size

key_cache_age_threshold

key_cache_block_size

key_cache_division_limit

large_files_support

large_page_size

large_pages

lc_messages

mysql之全局变量详解

the argument to a language name and combines it with the value of

lc_messages_dir to produce the location for the error message file.

lc_messages_dir

The directory where error messages are located. The server uses the value

together with the value of lc_messages to produce the location for the error

message file.

This variable specifies the locale that controls the language used to display day

and month names and abbreviations. This variable affects the output from the

DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. Locale names are

POSIX-style values such as 'ja_JP' or 'pt_BR'.The default value is 'en_US'

regardless of your system's locale setting.

服务器的许可类型。

是否LOCAL支持LOAD DATA INFILE语句。

This variable specifies the timeout in seconds for attempts to acquire metadata

locks. The permissible values range from 1 to 31536000 (1 year). The default is

31536000.

This timeout applies to all statements that use metadata locks. These include

DML and DDL operations on tables, views, stored procedures, and stored

functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and

HANDLER timeout does not apply to implicit accesses to system

tables in the mysql database, such as grant tables modified by GRANT or

REVOKE statements or table logging statements. The timeout does apply to

system tables accessed directly, such as with SELECT or UPDATE.

The timeout value applies separately for each metadata lock attempt. A given

statement can require more than one lock, so it is possible for the statement to

block for longer than the lock_wait_timeout value before reporting a timeout

error. When lock timeout occurs, ER_LOCK_WAIT_TIMEOUT is

_wait_timeout does not apply to delayed inserts, which always

execute with a timeout of 1 is done to avoid unnecessary timeouts

because a session that issues a delayed insert receives no notification of

delayed insert timeouts.

是否用–memlock将mysqld锁在内存中。

是否启用将所有查询记录到常规查询日志中.

是否启用二进制日志。

若启用了二进制记录,则该变量适用。它控制是否可以信任保存的程序的作者不会创建向二进制日志写入不安全事件的程序。如果设置为0(默认情况),不允许用户创建或修改保存的程序,除非他们不仅拥有CREATE

ROUTINE或ALTER ROUTINE权限还拥有SUPER权限。设置为0还强制限制,程序必须用DETERMINISTIC 特征或用READS SQL DATA或NO SQL特征声明。如果变量设置为1,MySQL不对保存程序的创建强加限制。

错误日志的位置。

The destination for general query log and slow query log output. The value can

be a comma-separated list of one or more of the words TABLE (log to tables),

FILE (log to files), or NONE (do not log to tables or files). The default value is

FILE. NONE, if present, takes precedence over any other specifiers. If the value

15

lc_time_names

license

local_infile

lock_wait_timeout

locked_in_memory

log

log_bin

log_bin_trust_function_creators

log_error

log_output

mysql之全局变量详解

is NONE log entries are not written even if the logs are enabled. If the logs are

not enabled, no logging occurs even if the value of log_output is not NONE.

log_queries_not_using_indexes

log_slave_updates

log_slow_queries

log_warnings

Whether queries that do not use indexes are logged to the slow query log.

是否从服务器从主服务器收到的更新应记入从服务器自己的二进制日志。要想生效,必须启用从服务器的二进制记录.

是否记录慢查询。用long_query_time变量的值来确定“慢查询”.

是否产生其它警告消息。默认情况下启用。放弃的连接不记入错误日志,除非值大于1。

如果查询时间超过该值,则增加Slow_queries状态变量。如果你正使用--log-slow-queries选项,则查询记入慢查询日志文件。用实际时间测量该值,而不是CPU时间,因此低于轻负载系统阈值的查询可能超过重负载系统的阈值。

如果设置为1,所有INSERT、UPDATE、DELETE和LOCK TABLE WRITE语句将等待直到受影响的表没有挂起的SELECT或LOCK TABLE READ。该变量以前叫做sql_low_priority_updates。

该变量说明是否数据目录所在的文件系统对文件名的大小写敏感。ON说明对文件名的大小写不敏感,OFF表示敏感。

如果设置为1,表名用小写保存到硬盘上,并且表名比较时不对大小写敏感。如果设置为2,按照指定的保存表名,但按照小写来比较。该选项还适合数据库名

和表的别名。

如果你正使用InnoDB表,你应在所有平台上将该变量设置为1,强制将名字转换为小写。如果运行MySQL的系统对文件名的大小写不敏感(例如Windows或Mac OS X),你不应将该变量设置为0。如果启动时没有设置该变量,并且数据目录所在文件系统对文件名的大小写不敏感,MySQL自动将lower_case_table_names设置为2。

包或任何生成的/中间字符串的最大大小。

包消息缓冲区初始化为net_buffer_length字节,但需要时可以增长到max_allowed_packet字节。该值默认很小,以捕获大的(可能是错误的)数据包。如果你使用大的BLOB 列或长字符串,你必须增加该值。应同你想要使用的最大的BLOB一样大。max_allowed_packet的协议限制为1GB。

如果多语句事务需要更大的内存,你会得到错误Multi-statement transaction

required more than 'max_binlog_cache_size' bytes of storage。

如果二进制日志写入的内容超出给定值,日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。

请注意如果你正使用事务:事务以一个块写入二进制日志,因此不不能被几个二进制日志拆分。因此,如果你有大的事务,二进制日志可能会大于max_binlog_size。如果max_relay_log_size为0, max_binlog_size的值也适用于中继日志。

If nontransactional statements within a transaction require more than this

many bytes of memory, the server generates an error. The minimum value is

4096. The maximum and default values are 4GB on 32-bit platforms and 16EB

(exabytes) on 64-bit platforms.

16

long_query_time

low_priority_updates

lower_case_file_system

lower_case_table_names

max_allowed_packet

max_binlog_cache_size

max_binlog_size

max_binlog_stmt_cache_size

mysql之全局变量详解

max_connect_errors

max_connections

如果中断的与主机的连接超过该数目,该主机则阻塞后面的连接。你可以用FLUSH HOSTS语句解锁锁定的主机。

允许的并行客户端连接数目。增大该值则增加mysqld 需要的文件描述符的数量。

不要启动大于该数目的线程来处理INSERT DELAYED语句。如果所有INSERT

DELAYED线程已经在使用,你想在新表中插入数据,行插入时好像未指定DELAYED属性。如果你将该值设置为0,MySQL不会创建线程来处理DELAYED行;其结果是完全禁用了DELAYED。

保存由SHOW ERRORS或SHOW WARNINGS显示的错误、警告和注解的最大数目。

该变量设置MEMORY (HEAP)表可以增长到的最大空间大小。该变量用来计算MEMORY表的MAX_ROWS值。在已有的MEMORY表上设置该变量没有效果,除非用CREATE TABLE或TRUNCATE TABLE等语句重新创建表。

该变量为max_delayed_threads的同义词。

不允许可能需要检查多于max_join_size行(为单个表语句)或行组合(为多个表语句)或可能执行大于max_join_size次硬盘查询的SELECT语句。通过设置该值,你可以捕获键使用不正确并可能花很长时间的SELECT语句。如果用户想要执行没有WHERE子句的花较长时间或返回数百万行的联接,则设置它。将该变量设置为DEFAULT之外的值,将SQL_BIG_SELECTS的值重设为0。如果你重新设置SQL_BIG_SELECTS值,max_join_size变量被忽略。如果查询结果位于查询缓存中,则不检查结果大小,因为前面已经计算了结果,不会要求服务器将它发送给客户端。该变量以前叫做sql_max_join_size。

确定使用的filesort算法的索引值大小的限值.

The max_long_data_size system variable. Use max_allowed_packet instead.

This variable limits the total number of prepared statements in the server. (The

sum of the number of prepared statements across all sessions.) It can be used

in environments where there is the potential for denial-of-service attacks based

on running the server out of memory by preparing huge numbers of

statements. If the value is set lower than the current number of prepared

statements, existing statements are not affected and can be used, but no new

statements can be prepared until the current number drops below the limit.

The default value is 16,382. The permissible range of values is from 0 to 1

g the value to 0 disables prepared statements.

如果复制从服务器写入中继日志时超出给定值,则滚动中继日志。通过该变量你可以对中继日志和二进制日志设置不同的限制。但是,将该变量设置为0,MySQL可以对二进制日志和中继日志使用max_binlog_size。max_relay_log_size必须设置在4096字节和1GB(包括)之间,或为0。默认值是0。

限制根据键值寻找行时的最大搜索数。MySQL优化器假定当用扫描键在表内搜索匹配的行时,不需要超过该数量的键值搜索,而不管键的实际基数是什么。将该值设置为较低的值(100?),你可以强制MySQL选择键值而不选择表扫描。

当排序BLOB或TEXT值时使用的字节数。只使用每个值的前max_sort_length17

max_delayed_threads

max_error_count

max_heap_table_size

max_insert_delayed_threads

max_join_size

max_length_for_sort_data

max_long_data_size

max_prepared_stmt_count

max_relay_log_size

max_seeks_for_key

max_sort_length

mysql之全局变量详解

字节;其它的被忽略。

The number of times that any given stored procedure may be called recursively.

The default value for this option is 0, which completely disables recursion in

stored procedures. The maximum value is 255. Stored procedure recursion

increases the demand on thread stack space. If you increase the value of

max_sp_recursion_depth, it may be necessary to increase thread stack size by

increasing the value of thread_stack at server startup.

客户端可以同时打开的临时表的最大数。(但该选项还未生效)。

任何给定的MySQL账户允许的最大同时连接数。0值表示“没有限制”。该变量具有全局范围和(只读)会话范围。会话变量的的值与全局变量的值相同,除非当前账户具有非零MAX_USER_CONNECTIONS资源限制。在这种情况下,会话值反应了账户限制。

max_sp_recursion_depth

max_tmp_tables

max_user_connections

max_write_lock_count

超过写锁定限制后,允许部分读锁定。

The size of the metadata locks cache. The server uses this cache to avoid

metadata_locks_cache_size creation and destruction of synchronization objects. This is particularly helpful

on systems where such operations are expensive,such as Windows XP.

min_examined_row_limit

multi_range_count

myisam_data_pointer_size

Queries that examine fewer than this number of rows are not logged to the

slow query log.

This variable has no effect. It is deprecated as of MySQL 5.6.7 and will be

removed in a future MySQL release.

默认指针大小,单位是字节,当未指定MAX_ROWS选项时,CREATE TABLE使用该变量创建MyISAM表。该变量不能小于2或大于7。默认值是6.

重建MyISAM索引(在REPAIR TABLE、ALTER TABLE或LOAD DATA INFILE过程myisam_max_sort_file_size

中)时,允许MySQL使用的临时文件的最大空间大小。如果文件的大小超过该值,则使用键值缓存创建索引,要慢得多。该值的单位为字节。

The maximum amount of memory to use for memory mapping compressed

MyISAM files. If many compressed MyISAM tables are used, the value can be

myisam_mmap_size

decreased to reduce the likelihood of memoryswapping problems.

myisam_recover_options

myisam_repair_threads

myisam_sort_buffer_size

--myisam-recover选项的值。

如果该值大于1,在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)。默认值是1。注释:多线程维护仍然是alpha 编码。

当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序MyISAM索引分配的缓冲区。

当为MyISAM表搜集关于索引值分发的统计信息时服务器如何处理NULL值。该变量有两个可能的值,nulls_equal和nulls_unequal。对于nulls_equal,认为所有NULL索引值时相等的,并形成一个数值组,其空间大小等于NULL值的数。对于nulls_unequal,NULL值认为是不相等的,每个NULL形成一个数值组,大小为1。

Use memory mapping for reading and writing MyISAM tables.

在查询之间将通信缓冲区重设为该值。一般情况不应改变,但如果内存很小,可以将它设置为期望的客户端发送的SQL语句的长度。如果语句超出该长度,缓冲区自动扩大,直到max_allowed_packet字节。

中断读前等待连接的其它数据的秒数。当服务器从客户端读数时,net_read_timeout指控制何时中断的超时值。当服务器向客户端写时,18

myisam_stats_method

myisam_use_mmap

net_buffer_length

net_read_timeout

mysql之全局变量详解

net_write_timeout指控制何时中断的超时值。又见slave_net_timeout。

net_retry_count

net_write_timeout

new

如果某个通信端口的读操作中断了,在放弃前重试多次。在FreeBSD中该值应设得很高,因为内部中断将发送至所有线程。

中断写之前等待块写入连接的秒数。又见net_read_timeout。

在MySQL 4.0中使用该变量来打开4.1中的一些行为,并用于向后兼容性。在MySQL 5.1中,它的值一直是OFF.

old is a compatibility variable. It is disabled by default, but can be enabled at

startup to revert the server to behaviors present in older versions.

When old is enabled, it changes the default scope of index hints to that used

prior to MySQL is, index hints with no FOR clause apply only to how

indexes are used for row retrieval and not to resolution of ORDER BY or GROUP

BY care about enabling this in a replication setup. With

statement-based binary logging, having different modes for the master and

slaves might lead to replication errors.

When this variable is enabled, the server does not use the optimized method of

processing an ALTER TABLE operation. It reverts to using a temporary table,

copying over the data, and then renaming the temporary table to the original,

as used by MySQL 5.0 and earlier. For more information on the operation of

ALTER TABLE.

是否服务器应为MySQL用户账户使用pre-4.1-style密码。

操作系统允许mysqld打开的文件的数量。这是系统允许的实际值,可能与你在启动选项中赋给mysqld的值不同。若在系统中MySQL不能更改打开的文件的数量,则该值为0。

在查询优化从优化器搜索空间裁减低希望局部计划中使用的控制方法。0值禁用该方法,以便优化器进行穷举搜索。值为1使优化器根据中间方案中得出的行数来裁减方案。

查询优化器进行的搜索的最大深度。如果值大于查询中的关系数则查询方案比较佳,但生成查询执行方案需要的时间更长。值大于查询中的关系数则返回的执行方案更快,但方案远没有优化。如果设置为0, 系统自动选择合理的值。如果设置为查询中使用的表的最大数加2,优化器转换为MySQL

5.0.0(和以前的版本)中使用的算法并搜索。

The optimizer_switch system variable enables control over optimizer behavior.

The value of this variable is a set of flags, each of which has a value of on or off

to indicate whether the corresponding optimizer behavior is enabled or

disabled. This variable has global and session values and can be changed at

runtime. The global default can be set at server startup.

The value of this variable is ON or OFF to indicate whether the Performance

Schema is enabled. By default, the value is ON by default as of MySQL 5.6.6 and

OFF before that. At server startup, you can specify this variable with no value or

a value of ON or 1 to enable it, or with a value of OFF or 0 to disable it.

The number of rows in the events_waits_history_long table.

The number of rows per thread in the events_waits_history table.

old

old_alter_table

old_passwords

open_files_limit

optimizer_prune_level

optimizer_search_depth

optimizer_switch

performance_schema

performance_schema_events_waits_history_long_size

performance_schema_events_waits_history_size

19

mysql之全局变量详解

performance_schema_max_cond_classes

performance_schema_max_cond_instances

performance_schema_max_file_classes

The maximum number of condition instruments.

The maximum number of instrumented condition objects.

The maximum number of file instruments.

The maximum number of opened file objects.

The value of performance_schema_max_file_handles should be greater than

the value of open_files_limit: open_files_limit affects the maximum number of

open file handles the

server can support and performance_schema_max_file_handles affects how

many of these file handles can be instrumented.

The maximum number of instrumented file objects.

The maximum number of mutex instruments.

The maximum number of instrumented mutex objects.

The maximum number of rwlock instruments.

The maximum number of instrumented rwlock objects.

The maximum number of instrumented rwlock maximum number

of opened table objects.

The maximum number of instrumented table objects.

The maximum number of thread instruments.

The maximum number of instrumented thread objects. The value controls the

size of the threads table. If this maximum is exceeded such that a thread cannot

be instrumented, the Performance Schema increments the

Performance_schema_thread_instances_lost status variable. The

max_connections system variable affects how many threads are run in the

server.

performance_schema_max_thread_instances affects how many of these

running threads can be instrumented. The default value of

performance_schema_max_thread_instances is autosized

based on the value of max_connections.

进程ID (PID)文件的路径名。可以用--pid-file选项设置该变量。

插件目录的路径。在MySQL 5.1.2中加入了该变量。

服务器帧听TCP/IP连接所用端口。可以用--port选项设置该变量。

重载索引时分配的缓冲区大小。

If set to 0 or OFF (the default), statement profiling is disabled. If set to 1 or ON,

statement profiling is enabled and the SHOW PROFILE and SHOW PROFILES

20

performance_schema_max_file_handles

performance_schema_max_file_instances

performance_schema_max_mutex_classes

performance_schema_max_mutex_instances

performance_schema_max_rwlock_classes

performance_schema_max_rwlock_instances

performance_schema_max_table_handles

performance_schema_max_table_instances

performance_schema_max_thread_classes

performance_schema_max_thread_instances

pid_file

plugin_dir

port

preload_buffer_size

profiling

mysql之全局变量详解

statements provide access to profiling variable is deprecated in

MySQL 5.6.8 and will be removed in a future MySQL release.

The number of statements for which to maintain profiling information if

profiling is enabled. The default value is 15. The maximum value is 100. Setting

the value to 0 effectively disables profiling. This variable is deprecated in

MySQL 5.6.8 and will be removed in a future MySQL release.

MySQL服务器使用的客户端/服务器协议的版本。

为查询分析和执行过程中创建的对象分配的内存块大小。如果内存分段过程中遇到问题,将该变量增加一位会有帮助。

不要缓存大于该值的结果。默认值是1048576(1MB)。

查询缓存分配的最小块的大小(字节)。默认值是4096(4KB)。

为缓存查询结果分配的内存的数量。默认值是0,即禁用查询缓存。请注意即使query_cache_type设置为0也将分配此数量的内存.

设置查询缓存类型。设置GLOBAL值可以设置后面的所有客户端连接的类型。客户端可以设置SESSION值以影响他们自己对查询缓存的使用。下面的表显示了可能的值:

0或OFF:不要缓存或查询结果。请注意这样不会取消分配的查询缓存区。要想取消,你应将query_cache_size设置为0。

1或ON:缓存除了以SELECT SQL_NO_CACHE开头的所有查询结果。

2或DEMAND:只缓存以SELECT SQL_NO_CACHE开头的查询结果。

该变量默认设为ON。

一般情况,当客户端对MyISAM表进行WRITE锁定时,如果查询结果位于查询缓存中,则其它客户端未被锁定,可以对该表进行查询。将该变量设置为1,则可以对表进行WRITE锁定,使查询缓存内所有对该表进行的查询变得非法。这样当锁定生效时,可以强制其它试图访问表的客户端来等待。

用于查询分析和执行的固定缓冲区的大小。在查询之间该缓冲区不释放。如果你执行复杂查询,分配更大的query_prealloc_size值可以帮助提高性能,因为它可以降低查询过程中服务器分配内存的需求。

范围优化时分配的块的大小.

每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描,可能需要增加该值,默认值为131072。

当变量对复制从服务器设置为ON时,从服务器不允许更新,除非通过从服务器的线程或用户拥有SUPER权限。可以确保从服务器不接受客户端的更新命令。

当排序后按排序后的顺序读取行时,则通过该缓冲区读取行,避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是,这是为每个客户端分配的缓冲区,因此你不应将全局变量设置为较大的值。相反,只为需要运行大查询的客户端更改会话变量。

The name of the relay log file.

The name of the relay log index file. The default name is

host_ in the data directory, where host_name is the name

of the slave server.

The name of the file in which the slave records information about the relay

21

profiling_history_size

protocol_version

query_alloc_block_size

query_cache_limit

query_cache_min_res_unit

query_cache_size

query_cache_type

query_cache_wlock_invalidate

query_prealloc_size

range_alloc_block_size

read_buffer_size

read_only

read_rnd_buffer_size

relay_log

relay_log_index

relay_log_info_file

mysql之全局变量详解

logs. The default name is in the data directory.

relay_log_purge

当不再需要中继日志时禁用或启用自动清空中继日志。默认值是1(启用)。

Enables automatic relay log recovery immediately following server startup. The

recovery process creates a new relay log file, initializes the SQL thread position

to this new relay log, and initializes the I/O thread to the SQL thread position.

Reading of the relay log from the master then continues. In MySQL 5.6.5 and

earlier, it was possible to change this global variable dynamically; beginning

with MySQL 5.6.6, it is read-only. (Bug #13840948) Regardless of the MySQL

Server version, its value can be changed by starting the slave with the

--relay-log-recovery option, which should be used following a unexpected halt

on the replication slave to ensure that no possibly corrupted relay logs are

processed.

When relay_log_recovery is enabled and the slave has stopped due to errors

encountered while running in multi-threaded mode, you cannot execute

CHANGE MASTER TO if there are any gaps in the log. Beginning with MySQL

5.6.6, you should use START SLAVE UNTIL SQL_AFTER_MTS_GAPS to ensure

that all gaps are processed before switching back to single-threaded mode or

executing a CHANGE MASTER TO statement.

The maximum amount of space to use for all relay logs.

The value of the --report-host option.

The account password of the slave to be reported to the master during slave

registration. This value appears in the output of SHOW SLAVE HOSTS on the

master server if the --show-slave-auth-info option is given.

Although the name of this option might imply otherwise, --report-password is

not connected to the MySQL user privilege system and so is not necessarily (or

even likely to be) the same as the password for the MySQL replication user

account.

The TCP/IP port number for connecting to the slave, to be reported to the

master during slave registration. Set this only if the slave is listening on a

nondefault port or if you have a special tunnel from the master or other clients

to the slave. If you are not sure, do not use this to MySQL 5.6.5,

the default value for this option was 3306. In MySQL 5.6.5 and later, the value

shown is the port number actually used by the slave (Bug #13333431). This

change also affects the default value displayed by SHOW SLAVE HOSTS.

The account user name of the slave to be reported to the master during slave

registration. This value appears in the output of SHOW SLAVE HOSTS on the

master server if the --show-slave-auth-info option is given.

Although the name of this option might imply otherwise, --report-user is not

connected to the MySQL user privilege system and so is not necessarily (or

even likely to be) the same as the name of the MySQL replication user account.

如果用--secure-auth选项启动了MySQL服务器,它将阻塞有旧格式(4.1之前)密码的所有账户所发起的连接。在这种情况下,该变量的值为ON,否则为OFF。如果你想要防止使用旧格式的密码(致使网络通信不安全),你应启用该选项。如果启用该选项并且授权表为pre-4.1格式,服务器启动失败并22

relay_log_recovery

relay_log_space_limit

report_host

report_password

report_port

report_user

secure_auth

mysql之全局变量详解

secure_file_priv

server_id

skip_external_locking

且会出现错误。参见A.2.3节,“客户端不支持鉴定协议”。当用于客户端选项时,如果服务器需要该客户端账户的旧格式的密码,则客户端拒绝连接该服务器。

This option limits the effect of the LOAD DATA and SELECT ... INTO OUTFILE

statements and the LOAD_FILE() function to work only with files in the specified

directory.

--server-id选项的值。用于主复制服务器和从复制服务器。

如果mysqld使用外部锁定,该值为OFF。

This variable is set from the value of the --skip-name-resolve option. If it is OFF,

mysqld resolves host names when checking client connections. If it is ON,

mysqld uses only IP numbers; in this case,all Host column values in the grant

tables must be IP addresses or localhost. See Section 8.12.6.2,“DNS Lookup

Optimization and the Host Cache”.

如果服务器只允许本地(非TCP/IP)连接,该值为ON。在Unix中,本地连接使用Unix套接字文件。在Windows中,本地连接使用命名管道或共享内存。在NetWare中,只支持TCP/IP连接,因此不要将该变量设置为ON。

防止不具有SHOW DATABASES权限的人们使用SHOW DATABASES语句。如果你担心用户能够看见属于其它用户的数据库,这样设置可以提高安全性。其效果取决于SHOW DATABASES权限:如果变量值为ON,只允许具有SHOW

DATABASES权限的人们使用SHOW DATABASES 语句,并且该语句将显示所有数据库名。如果值为OFF,允许所有用户执行SHOW DATABASES,但只显示用户具有SHOW DATABASES或其它权限的数据库的名称。

Controls how a slave thread resolves conflicts and errors during replication.

IDEMPOTENT mode causes suppression of duplicate-key and no-key-found

errors. This mode should be employed in multi-master replication, circular

replication, and some other special replication scenarios. STRICT mode is the

default, and is suitable for most other mode is needed for

multi-master replication, circular replication, and some other special replication

scenarios for MySQL Cluster Replication.

从服务器为复制LOAD DATA INFILE语句创建临时文件的目录名。

In MySQL 5.6.6 and later, this variable sets the maximum packet size for the

slave SQL and I/O threads,so that large updates using row-based replication do

not cause replication to fail because an update exceeded max_allowed_packet.

This global variable always has a value that is a positive integer multiple of

1024; if you set it to some value that is not, the value is rounded down to the

next highest multiple of 1024 for it is stored or used;setting

slave_max_allowed_packet to 0 causes 1024 to be used. (A truncation warning

is issued inall such cases.) The default and maximum value is 1073741824 (1

GB); the minimum is 1024.

放弃读操作前等待主/从连接的更多数据的等待秒数。

从服务器应跳过(忽视)的复制错误。

如果由于ofInnoDB死锁或超过InnoDB的innodb_lock_wait_timeout或NDBCLUSTER的TransactionDeadlockDetectionTimeout或23

skip_name_resolve

skip_networking

skip_show_database

slave_compressed_protocol

如果主、从服务器均支持,确定是否使用从/主压缩协议。

slave_exec_mode

slave_load_tmpdir

slave_max_allowed_packet

slave_net_timeout

slave_skip_errors

slave_transaction_retries

mysql之全局变量详解

slave_type_conversions

TransactionInactiveTimeout,复制从服务器SQL线程未能

执行事务,在提示错误并停止前它自动重复slave_transaction_retries次。默认值是10。

Controls the type conversion mode in effect on the slave when using row-based

replication. In MySQL 5.6.13 and later, its value is a comma-delimited set of

zero or more elements from the list: ALL_LOSSY,ALL_NON_LOSSY, ALL_SIGNED,

ALL_UNSIGNED. Set this variable to an empty string to disallow type

conversions between the master and the slave. Changes require a restart of the

slave to take _SIGNED and ALL_UNSIGNED were added in MySQL

5.6.13 (Bug#15831300). For additional information on type conversion modes

applicable to attribute promotion and demotion in row-based replication, see

Row-based replication: attribute promotion and demotion.

如果创建线程的时间超过该秒数,服务器增加Slow_launch_threads状态变量。

Specify the initial slow query log state. With no argument or an argument of 1,

the --slow-query-log option enables the log. If omitted or given with an

argument of 0, the option disables the log.

The name of the slow query log file. The default value is host_,

but the initial value can be changed with the --slow_query_log_file option

Unix平台:用于本地客户端连接的套接字文件。默认为/var/lib/mysql/。Windows:用于本地客户端连接的命名管道名。默认为mysql。

每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP

BY操作。

如果设置为1(默认情况),您可以通过使用以下结构查找包含一个AUTO_INCREMENT列的表的最后插入的行:

WHERE auto_increment_column IS NULL

此性质被有些ODBC程序,比如Access使用

如果设定为0,则MySQL会放弃有可能会花很长时间来执行的SELECT语句(也就是,对于这些语句,优化程序估算被检查的行的数目超过了max_join_size的值)。当一个不妥当的WHERE语句被发布后,本语句有用。一个新连接的默认值为1,这可以允许所有的SELECT语句。

如果设置为1,所有的临时表被存储在磁盘中,而不是存储在储存期中。这样会稍微慢些,但是对于需要一个大型临时表的SELECT操作,不会发生The

table tbl_name is full错误。对于一个新连接,默认值为0(使用存储器内部临时表)。通常,您不必设置此变量,因为根据需要,存储器内部表会被自动转换为以磁盘为基础的表。

SQL_BUFFER_RESULT会迫使来自SELECT语句的结果被放入临时表中。这可以帮助MySQL早点解除表锁定。当需要花较长时间把结果发送给客户端时,这是有好处的。

如果设置为0,则客户端的二进制日志中不会记录日志。客户端必须拥有SUPER权限来设置此选项。

如果设置为1,则此客户端的总查询日志中不会记录日志。客户端必须拥有SUPER权限来设置此选项。

24

slow_launch_time

slow_query_log

slow_query_log_file

socket

sort_buffer_size

sql_auto_is_null

sql_big_selects

sql_big_tables

sql_buffer_result

sql_log_bin

sql_log_off

mysql之全局变量详解

sql_low_priority_updates

如果设置为1,所有INSERT、UPDATE、DELETE和LOCK TABLE WRITE语句将等待直到受影响的表没有挂起的SELECT或LOCK TABLE READ。

不允许可能需要检查多于max_join_size行(为单个表语句)或行组合(为多个表语句)或可能执行大于max_join_size次硬盘查询的SELECT语句。通过设置该值,你可以捕获键使用不正确并可能花很长时间的SELECT语句。如果用户想要执行没有WHERE子句的花较长时间或返回数百万行的联接,则设置它。将该变量设置为DEFAULT之外的值,将SQL_BIG_SELECTS的值重设为0。如果你重新设置SQL_BIG_SELECTS值,max_join_size变量被忽略。如果查询结果位于查询缓存中,则不检查结果大小,因为前面已经计算了结果,不会要求服务器将它发送给客户端。

当前的服务器SQL模式,可以动态设置.

当设置为1时(默认情况),“注意”一级的警报被记录下来。当设置为0时,“注意”警告被压制。Mysqldump包含输出,用于把此变量设置为0,这样,对于不会影响重新载入操作整体性的事件,重新载入转储文件时不会产生警告。

如果设置为1,则SHOW CREATE TABLE会对表和列的名称加引号。如果设置为0,则加引号操作被禁用。默认情况下,本选项被启用,因此对于含有需要加引号的名称的表,复制操作起作用.

如果设置为1,则MySQL会放弃在WHERE子句或LIMIT子句中不使用关键字的UPDATE或DELETE语句。这样,当关键字使用不正确时,也有可能理解UPDATE或DELETE语句。这样就可以更改或删除大量的行。

从SELECT语句返回的记录的最大数目。对于一个新连接,默认值是“unlimited”。如果您更改了限值,可以使用SQL_SELECT_LIMIT DEFAULT值恢复默认值。

从服务器应跳过的来自主服务器的事件数。

本变量用于控制当出现警告时,单行INSERT语句是否产生一个信息字符串。默认值为0。把值设置为1,来产生一个信息字符串。

The path to a file with a list of trusted SSL CAs.

The path to a directory that contains trusted SSL CA certificates in PEM format.

The name of the SSL certificate file to use for establishing a secure connection.

A list of permissible ciphers to use for SSL encryption.

The name of the SSL key file to use for establishing a secure connection.

该变量是table_typeis的同义词.

Sets a soft upper limit for the number of cached stored routines per

connection. The value of this variable is specified in terms of the number of

stored routines held in each of the two caches maintained by the MySQL Server

for, respectively, stored procedures and stored functions. Whenever a stored

routine is executed this cache size is checked before the first or top-level

statement in the routine is parsed; if the number of routines of the same type

(stored procedures or stored functions according to which is being executed)

exceeds the limit specified by this variable, the corresponding cache is flushed

and memory previously allocated for cached objects is freed. This allows the

cache to

be flushed safely, even when there are dependencies between stored routines.

25

sql_max_join_size

sql_mode

sql_notes

sql_quote_show_create

sql_safe_updates

sql_select_limit

sql_slave_skip_counter

sql_warnings

ssl_ca

ssl_capath

ssl_cert

ssl_cipher

ssl_key

storage_engine

stored_program_cache

mysql之全局变量详解

sync_binlog

如果为正,当每个sync_binlog'th写入该二进制日志后,MySQL服务器将它的二进制日志同步到硬盘上(fdatasync())。请注意如果在autocommit模式,每执行一个语句向二进制日志写入一次,否则每个事务写入一次。默认值是0,不与硬盘同步。值为1是最安全的选择,因为崩溃时,你最多丢掉二进制日志中的一个语句/事务;但是,这是最慢的选择(除非硬盘有电池备份缓存,从而使同步工作较快)。

如果该变量设为1,当创建非临时表时它的.frm文件被同步到硬盘上(fdatasync());这样较慢但出现崩溃时较安全。默认值为1。

The effects of this variable on a replication slave depend on whether the slave's

master_info_repository is set to FILE or TABLE, as explained in the following

paragraphs.

master_info_repository = FILE. If the value of sync_master_info is greater than

0, the slave synchronizes its file to disk (using fdatasync()) after

every sync_master_info events. If it is 0, the MySQL server performs no

synchronization of the file to disk;instead, the server relies on the

operating system to flush its contents periodically as with any other file.

master_info_repository = TABLE. If the value of sync_master_info is greater

than 0, the slave updates its master info repository table after every

sync_master_info events. If it is 0, the table is never default value

for sync_master_info is 10000 as of MySQL 5.6.6, 0 before that.

If the value of this variable is greater than 0, the MySQL server synchronizes its

relay log to disk (using fdatasync()) after every sync_relay_log events are

written to the relay g sync_relay_log to 0 causes no synchronization to

be done to disk; in this case, the server relies on the operating system to flush

the relay log's contents from time to time as for any other file.

Prior to MySQL 5.6.6, 0 was the default for this variable. In MySQL 5.6. and

later, the default is 10000.A value of 1 is the safest choice because in the event

of a crash you lose at most one event from the relay log. However, it is also the

slowest choice (unless the disk has a battery-backed cache, which makes

synchronization very fast).

The effects of this variable on the slave depend on the server's

relay_log_info_repository setting (FILE or TABLE), and if this is TABLE,

additionally on whether the storage engine used by the relay log info table is

transactional (such as InnoDB) or not (MyISAM).

服务器系统时区。当服务器开始执行时,它继承机器默认时区设置值,可以由运行服务器的账户或在启动脚本中进行修改。该值用来设置system_time_zone。典型情况用TZ环境变量来指定时区。还可以用mysqld_safe脚本的--timez选项来指定。

The number of table definitions (from .frm files) that can be stored in the

definition cache. If you use a large number of tables, you can create a large

table definition cache to speed up opening of table definition cache

takes less space and does not use file descriptors, unlike the normal table

cache. The minimum value is 400. The default value is based on the following

formula, capped to a limit of 2000:

26

sync_frm

sync_master_info

sync_relay_log

sync_relay_log_info

system_time_zone

table_definition_cache

mysql之全局变量详解

400 + (table_open_cache / 2)

Before MySQL 5.6.8, the default is 400.

For InnoDB, table_definition_cache acts as a soft limit for the number of open

table instances in the InnoDB data dictionary cache. If the number of open

table instances exceeds the table_definition_cache setting, the LRU mechanism

begins to mark table instances for eviction and eventually removes them from

the data dictionary cache. The limit helps address situations in which significant

amounts of memory would be used to cache rarely used table instances until

the next server restart. The number of table instances with cached metadata

could be higher than the limit defined by table_definition_cache, because

InnoDB system table instances and parent and child table instances with foreign

key relationships are not placed on the LRU list and are not subject to eviction

from memory.

Additionally, table_definition_cache defines a soft limit for the number of

InnoDB file-pertable tablespaces that can be open at one time, which is also

controlled by innodb_open_files. If both table_definition_cache and

innodb_open_files are set, the highest setting is used. If neither variable is set,

table_definition_cache, which has a higher default value, is used. If the number

of open tablespace file handles exceeds the limit defined by

table_definition_cache or innodb_open_files, the LRU mechanism searches the

tablespace file LRU list for files that are fully flushed and are not currently being

extended. This process is performed each time a new tablespace is opened. If

there are no “inactive” tablespaces, no tablespace files are closed.

The number of open tables for all threads. Increasing this value increases the

number of file descriptors that mysqld requires. You can check whether you

need to increase the table cache by checking the Opened_tables status

variable. See Section 5.1.6, “Server Status Variables”. If the value of

Opened_tables is large and you do not use FLUSH TABLES often (which just

forces all tables to be closed and reopened), then you should increase the value

of the table_open_cache variable.

服务器应缓存多少线程以便重新使用。当客户端断开连接时,如果线程少于thread_cache_size,则客户端的线程被放入缓存。当请求线程时如果允许可以从缓存中重新利用线程,并且只有当缓存空了时才会创建新线程。如果新连接很多,可以增加该变量以提高性能。(一般情况,如果线程执行得很好,性能提高不明显)。

在Solaris中,mysqld用该值调用thr_setconcurrency()。该函数使应用程序向线程系统提供需要同时运行的期望的线程数目的提示。

The thread-handling model used by the server for connection threads. The

permissible values are no-threads (the server uses a single thread to handle

one connection) and one-thread-perconnection (the server uses one thread to

handle each client connection). no-threads is useful for debugging under Linux;

see Section 24.5, “Debugging and Porting MySQL”.If the thread pool plugin is

enabled, the server sets the thread_handling value to dynamicallyloaded.

thread_stack

table_open_cache

thread_cache_size

thread_concurrency

thread_handling

每个线程的堆栈大小。用crash-me测试检测出的许多限制取决于该值。默27

mysql之全局变量详解

认值足够大,可以满足普通操作。

time_format

time_zone

该变量为使用。

当前的时区。初使值是'SYSTEM'(使用system_time_zone的值),但可以用--default-time-zone选项在服务器启动时显式指定。

如果内存内的临时表超过该值,MySQL自动将它转换为硬盘上的MyISAM表。如果你执行许多高级GROUP BY查询并且有大量内存,则可以增加tmp_table_size的值。

保存临时文件和临时表的目录。该变量可以设置为几个路径,按round-robin模式使用。在Unix中应该用冒号(‘:’)间隔开路径,在Windows、NetWare和OS/2中用分号(‘;’)。用来将负荷分散到几个物理硬盘上。如果MySQL服务器为复制从服务器,你不应将tmpdir设置为指向基于内存的文件系统上的目录或当服务器主机重启时声明的目录。复制从服务器需要部分临时文件来在机器重启后仍可用,以便它可以复制临时表或执行LOAD DATA

INFILE操作。如果服务器重启时临时文件夹中的文件丢失了,则复制失败。但是,如果你使用MySQL4.0.0或更新版本,你可以使用slave_load_tmpdir变量设置从服务器的临时目录。在这种情况下,从

服务器不再使用常规tmpdir,说明你可以将tmpdir设置到一个非固定位置。

为保存将保存到二进制日志中的事务的查询而分配的内存块的大小(字节)。

tmp_table_size

tmpdir

transaction_alloc_block_size

为transaction_alloc_blocks分配的固定缓冲区的大小(字节),在两次查询transaction_prealloc_size

之间不会释放。使该值足够大,将所有查询固定到一个事务中,可以避免多次malloc()调用。

tx_isolation

默认事务隔离级别。默认值为REPEATABLE-READ。

如果设置为1(默认情况),则会对InnoDB表中的二级索引执行唯一性检查。如果设置为0,则对于被插入到InnoDB的插入缓冲器中的索引登录项,不执行唯一性检查。如果您可以肯定您的数据不违反唯一性要求,则您可以把此值设定为0,以加快向InnoDB导入大型表的速度。

该变量控制如果更新包含LIMIT子句,是否可以在当前表中使用不包含主关键字的视图进行更新。(通常用GUI工具生成这类更新)。更新指UPDATE或DELETE语句。这儿主关键字指PRIMARY KEY,或一个UNIQUE索引,其中任何列不可以包含NULL。该变量有两个值:

o 1或YES:只发出警告(没有错误消息)。这是默认值。

o 0或NO:禁止更新。

服务器版本号。

configure脚本有一个--with-comment选项,当构建MySQL时可以进行注释。该变量包含注释值。

MySQL构建的机器或架构的类型。

MySQL构建的操作系统的类型。

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

在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。又见interactive_timeout。

unique_checks

updatable_views_with_limit

version

version_comment

version_compile_machine

version_compile_os

wait_timeout

28


本文标签: 日志 服务器 使用