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
版权声明:本文标题:mysql全局变量详解 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.freenas.com.cn/jishu/1708679612h529254.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论