（DBA之路【八】）关于show variables那些参数的故事

基于我自己的版本：5.5.35-1ubuntu对http://blog.csdn.net/beiigang/article/details/39030695进行了修改。

1)low_priority_updates

在myisam表中此参数用于调整读锁和写锁的优先级。默认为0.

注：（以***释来自网上）

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
  在 my.cnf 的配置方法
  [mysqld]
  low-priority-updates

• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

2）lower_case_file_system/lower_case_table_name

MySQL中，一个库会对应一个文件夹，库里的表会则以文件的方式存放在文件夹内，所以，操作系统对大小写的敏感性决定了数据库和表的大小写敏感（MySQL有一个只读的系统变量lower_case_file_system，其值反映的正是当前文件系统是否区分大小写）

我的是默认lower_case_file_system=OFF,lower_case_table_name=0，表示文件系统和表名都是区分大小写的。

注：（以***释来自网上）

MYSQL在LINUX下数据库名、表名、列名、别名大小写规则如下： 
1.数据库名与表名是严格区分大小写的 
2.表的别名是严格区分大小写的 
3.列名与列的别名在所有的情况下均是忽略大小写的 
4.变量名也是严格区分大小写的 

3)max_allowed_packet  

value:16777216

最大接收包的大小（可以理解为指令集大小）

修改方法1） 方法1可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。

max_allowed_packet = 20M如果找不到my.cnf可以通过mysql --help | grep my.cnf去寻找my.cnf文件。

2） 方法2 进入mysql server在mysql 命令行中运行set global max_allowed_packet = 2*1024*1024*10然后关闭掉这此mysql server链接，再进入。show VARIABLES like ‘%max_allowed_packet%‘;查看下max_allowed_packet是否编辑成功

4）max_binlog_cache_size

max_binlog_cache_size 表示的是binlog 能够使用的最大cache 内存大小

当我们执行多语句事务的时候 所有session的使用的内存超过max_binlog_cache_size的值时

就会报错：“Multi-statement transaction required more than ‘max_binlog_cache_size‘ bytes ofstorage”

5）max_binlog_size

二进制日志最大大小

当停止或者重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新 的日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件，不定期处理的话长期以往硬盘扛不住。。

mysql提供了mysqlbinlog命令来查看日志文件，如mysqlbinlog xxx-bin.001 | more。在记 录每条变更日志的时候，日志文件都会把当前时间给记录下来，以便进行数据库恢复。

通过SET SQL_LOG_BIN的值来设定是否启用日志。

数据恢复：

如果遇到灾难事件，应该用最近一次制作的完整备份恢复数据库，然后使用备份之后的日志 文件把数据库恢复到最接近现在的可用状态。  使用日志进行恢复时需要依次进行，即最早生成的日志文件要最先恢复：

 mysqlbinlog xxx-bin.00001 | mysql –u root –p

mysqlbinlog xxx-bin.00002 | mysql –u root –p

日志：（以***释来自网上）

1、MySQL日志文件系统的组成
  a、错误日志：记录启动、运行或停止mysqld时出现的问题。
  b、通用日志：记录建立的客户端连接和执行的语句。
  c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。
  d、二进制日志：记录所有更改数据的语句。还用于复制。
  e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
  f、Innodb日志：innodb redo log

查看日志：mysqlbinlog [options] log_file ...

例如，要显示名字为binlog.000003的日志文件，按如下方式调用即可：mysqlbinlog binlog.000003。提供了mysqlbinlog命令来查看日志文件，如mysqlbinlog xxx-bin.001 | more。在记录每条变更日志的时候，日志文件都会把当前时间给记录下来，以便进行数据库恢复。

注意在进行数据恢复的时候要按文件顺序来恢复。

6）max_binlog_stmt_cache_size

max_binlog_stmt_cache_size 这个值load data导入超大的文件（10G以上）必须要加大。

7）max_connections

 实际MySQL服务器允许的最大连接数； 

8）max_connect_errors 

当客户端连接服务端超时(超过connect_timeout), 服务端就会给这个客户端记录一次error，当出错的次数达到max_connect_errors的时候，这个客户端就会被锁定。除非执行FLUSH HOSTS命令，建议：能设多大就设多大

9）max_delayed_thread 其实与max_insert_delayed_thread数目同

设定最大启动线程来处理INSERT DELAYED语句也就是可以启动的最大处理insert delayed的数量 

10）max_error_count

运行出现的最大错误总数

11）max_tmp_tables：

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

默认:mysql> show variables like "tmpdir";

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

12）max_heap_table_size

这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#,但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。这个变量和tmp_table_size一起限制了内部内存表的大小。

13）max_insert_delayed_thread

Innodb提供有insert buffer以批量操作减少IO(最新版本扩充为change buffer)，MyISAM也有类似功能，称作insert delayed，即延迟提交；延迟插入的记录保存在内存中，如果此时断电则会丢失，当新插入行不立即被查询时可用此功能提升IO；

设定最大启动线程来处理INSERT DELAYED语句也就是可以启动的最大处理insert delayed的数量 

14）max_join_size 

允许读取的连接的数量

15）max_length_for_sort_data

mysql有两种文件排序算法（双路排序和单路排序），如果需要排序的列的总大小加上order by列的大小超过了 max_length_for_sort_data定义的字节，mysql就会使用双路排序。

可以通过改变 max_length_for_sort_data变量的值来影响mysql选择的算法。因为单路排序为将要排序的每一行创建了固定的缓冲区，varchar列的最大长度是 max_length_for_sort_data规定的值，而不是排序数据的实际大小。

双路排序：（以***释来自网上）

读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

双路排序的开销可能会非常巨大，因为他会读取表两次，第二次读取会引发大量的随机IO,对于myisam涞说，这个代价尤其昂贵，myisam表利用系统调用去提取每行的数据。

单路排序：
读取查询需要的所有列，按照order by 列对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

16）max_long_data_size

最大输入数据的长度

17）max_prepare_stmt_count

最大预处理命令数目，该当用到REPAIRTABLE, ALTER TABLE, LOAD DATA INFILE的时候，相应的索引会被重建，然后要用到临时文件，此

batched_key_access

Controls use of BKA join algorithm

block_nested_loop

Controls use of BNL join algorithm

engine_condition_pushdown

Controls engine condition pushdown

index_condition_pushdown

Controls index condition pushdown

index_merge

Controls all Index Merge optimizations

index_merge_intersection

Controls the Index Merge Intersection Access optimization

index_merge_sort_union

Controls the Index Merge Sort-Union Access optimization

index_merge_union

Controls the Index Merge Union Access optimization

mrr

Controls the Multi-Range Read strategy

mrr_cost_based

Controls use of cost-based MRR if mrr=on

semijoin

Controls all semi-join strategies

firstmatch

Controls the semi-join FirstMatch strategy

loosescan

Controls the semi-join LooseScan strategy (not to be confused with LooseScan for GROUP BY)

materialization

Controls materialization (including semi-join materialization)

51）performance_schema    默认OFF

主要用于收集数据库服务器性能参数

performance_schema提供以下功能：
1.提供进程等待的详细信息，包括锁、互斥变量、文件信息；
2.保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
3.对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）

Performance的开启很简单，在my.cnf中[mysqld]加入performanc_schema，检查性能数据库是否启动的命令:

SHOW VARIABLES LIKE ‘performance_schema’;

若是返回的 值为ON，则说明性能数据库正常开启状态。

它还包括了许多具体的设置参数

performance_schema_XXX_size

51）pid_file                              

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

52） plugin_dir                            

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

如果插件目录对服务器可写，则有可能给用户通过“SELECT ... INTO DUMPFILE”在目录中写可执行代码。可以通过设置插件目录只读来阻止或者给目录设置--secure-file-priv select可保持安全 。

类型：目录名

默认是：mysql安装目录/lib/pligin

53） port                          

Mysql监听tcp/ip端口号

默认3306

54） preload_buffer_size           

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

作用域：全局、会话

55） profiling                              默认 OFF

设置showprofile 命令是否可用。

当profiling=0/OFF 则show profile不可用

当profiling=1/ON 则show profile可用

ps: show profile是显示查询一些信息。

56）profiling_history_size                  默认15

设置showprofile 命令显示多少条查询的。

默认是15，最大值是100

若此变量的值是0则profiling=0/OFF

57）protocol_version                        默认10

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

58）proxy_user   默认空

代理用户，

设置：
mysql> SET GLOBAL sql_mode=‘strict_trans_tables‘;


111） sql_notes                               默认ON
若是1/on,warning_count数会增加且服务器会记录警告内容，否者不会。
mysqldump输出内容到这个变量设置为0，使重载转储文件不会产生警告事件不影响重载操作的完整性。

112）sql_quote_show_create                  默认 ON
若值为1，则会给SHOW CREATE TABLE和SHOW CREATE DATABASE添加服务器引号表示符，
否则引号被禁用。

113） sql_safe_updates                       默认 OFF
值若是ON，则mysql则退出在where和limit字句中没关键字的update或delete语句。这有利于捕抓到键使用不当的update或delete语句。
默认值是OFF

114）sql_select_limit                
这是设置select查询语句返回数据最大行数。一个新的连接的默认值是服务器允许每个表的最大行数。如果你已经改变了极限，默认值可以通过指定一个DEFAULT值恢复。

115）sql_slave_skip_counter               
在主从库维护中，有时候需要跳过某个无法执行的命令，需要在slave处于stop状态下，执行 set global sql_slave_skip_counter=N以跳过命令。常用的且不易用错的是N=1的情况
set global sql_slave_skip_counter=N的意思，即为在start slave时，当N=1时，会连续跳过若干个event，直到当前所在的事务结束，  当然如果N>1，则每跳过一个event都要N--.
   小结：
   1、set global sql_slave_skip_counter=N中的N是指跳过N个event
   2、最好记的是N被设置为1时，效果跳过下一个事务。
   3、跳过第N个event后，位置若刚好落在一个事务内部，则会跳过这整个事务
   4、一个insert/update/delete不一定只对应一个event，由引擎和日志格式决定


116） sql_warnings                           默认OFF
该变量操作单行插入数据出现warning错误时是否产生一个warning字符串信息。
默认值是OFF，设置为ON的话就产生信息。

117） ssl_ca                              
一个受信任的SSL的CA列表中的文件路径。

118）ssl_capath                            
包含受信任的SSL PEM格式的CA证书目录的路径。

119）ssl_cert                               
为建立一个安全的连接使用的SSL证书文件的名称。

120） ssl_cipher                         
允许使用SSL加密的密码列表。

121）ssl_key                             
为建立一个安全的连接而使用的SSL密钥文件的名称。

122）storage_engine                          默认InnoDB
默认的存储引擎。

123）sync_binlog                             默认0
当值>0,则每一个sync_binlog写二进制日志后mysql都同步该日志到磁盘中（fddatasync()）.


124）sync_frm                                默认ON
若值为true，任何非临时表在创建其.frm文件时会同步到磁盘。这会速度变慢但数据奔溃时安全点。
默认是：true

125）sync_master_info   
默认为0，如果大于0，从机同步master.info文件到磁盘后，每sync_master_info事件都会导致备库额外的fsync()操作

126）sync_relay_log
默认值0，如果大于0时，服务器中继日志同步到磁盘后，每一个sync_relay_log写入中继日志

127）sync_relay_log_info
默认值0，如果大于0，从机同步继电器log.info文件到磁盘后，每sync_relay_log_info交易会导致备库额外的fsync()操作

（125-127建议关闭）

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

129）table_definition_cache               
可以存储在定义缓存的表定义的数目，如果使用大量的表，你可以创建一个大表的定义缓存，加快开放表，他表定义缓存占用较少的空间，并且不使用文件描述符，不像正常的表缓存。最低和默认值均为400。

130） table_open_cache                     
所有线程一共能打开的表的数量，增加该值增加mysqld要求的文件描述符的数量。
可以通过检查Opened_tables状态变量来检查是否要增加该值。
如果Opened_tables状态变量很大则不需要用flush tables，而是增加该变量的值。

131）thread_cache_size                       
该变量设置多少个线程服务器重用缓存。当一个客户端断开连接的时候，该客户端的线程总数还没超过该值数时，则还是保存在缓冲区内。
如果有很多新的连接，则可以通过增加该值来提交性能。
通常情况下，这并不能提供一个显着的性能改进，除非你有一个很好的线程执行。
但是，如果服务器每秒有成千新连接的话，则需要确保该值足够大以至于新的连接的线程可以保存在缓存区内。

132）thread_handling                     
服务器用什么线程句柄来控制连接线程。
当值是no-theads,服务器使用单独的线程
当值是one-thread-per-connection，服务器用一个线程控制每一个客户连接。
当值是no-threads,在linux下debug是很有用的。

133）thread_concurrency    
 默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量。这个参数在linux下面是有效的。默认是10，我改成0并被系统强制变成1之后，造成了经常阻塞的问题。目前我设置的是16，等于CPU线程数。其实10已经够用了，不建议修改此参数。

134）thread_stack                       
每个线程的栈的大小。crash-me测试检测到的限制，很多都是依赖于这个值。
如果线程的堆栈大小是太小了，它限制了复杂的服务器可以处理的SQL语句，存储过程的递归深度，和其他消耗内存的行动。

135） time_format                             |%H:%i:%s
该变量被弃用

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

137）timed_mutexes                          |OFF
这个参数用来控制是否对Innodb引擎的mutex wait进行 计时统计，以方便进行性能诊断
timed_mutexes控制的mutex wait时间统计，因为只在debug模式下进行编译，而且5.6以后使用performance schema的等待事件进行替代，所以参数做了删除处理。

138） timestamp                           
TIMESTAMP值可以从1970的某时的开始一直到2037年，精度为一秒，其值作为数字显示。

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

140） tmpdir                                
临时目录的路径

141） transaction_alloc_block_size     
将保存到二进制日志中的事务因查询而分配的内存块的大小

142）transaction_prealloc_size             
为transaction_alloc_blocks分配的固定缓冲区的大小（字节），在两次查询之间不会释放。使该值足够大，将所有查询固定到一个事务中，可以避免多次malloc()调用。

143） tx_isolation                         
默认事务隔离级别。默认值为REPEATABLE-READ
该变量可以被直接设置（全局），也可以通过set transaction=值 语句来设置。
值可以是：
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

144）unique_checks                           
该变量控制是否在InnoDB表的辅助索引的唯一性进行检查。
当值是0/OFF，则服务器假设输入的数据不存在重复键，储存引擎跳过运行。（这个是你要储存的数据不用唯一性所以设为OFF）
默认值是ON

145）updatable_views_with_limit          
该变量控制如果更新包含LIMIT子句，是否可以在当前表中使用不包含主关键字的视图进行更新。(通常用GUI工具生成这类更新)。更新指UPDATE或DELETE语句。这儿主关键字指PRIMARY KEY，或一个UNIQUE索引，其中任何列不可以包含NULL。
该变量有两个值：
值是1或YES：只发出警告(没有错误消息)。这是 默认值。
值是0或NO：禁止更新。
默认值是YES

146）version                             
服务器的版本号

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

148）version_compile_machine              
该Mysql程序是用什么工具编译出来的

149）version_compile_os                
该Mysql程序是编译时的操作系统

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

151） warning_count                         
上一个查询语句出现的errors, warnings, notes错误的数目。