mysql配置详解明.docx
《mysql配置详解明.docx》由会员分享，可在线阅读，更多相关《mysql配置详解明.docx（80页珍藏版）》请在冰豆网上搜索。
mysql配置详解明
mysql配置项调查报告
Mysqlserver包含了很多系统变量（systemvariables），用来显示mysql是如何配置的。
每一个系统变量都有一个默认值（defaultvalue）.
系统变量可以在mysql启动时，使用命令行参数来设置（set）,也可以用指定的文件（例如配置文件）；大部分的系统变量，都可以在server运行中更改，利用set语句；这样，你就不用停止/重启服务来实现对服务的更改了。
你可以使用很多不同的方法来查看系统变量的name和value。
●查看server会用到的变量value，包括那些默认compiled-in的，和从文件读到的。
可以使用这个命令：
mysqld--verbose--help
●查看server会用到的变量value，只包括默认compiled-in的，不包括从文件得到的。
可以使用这个命令：
mysqld--no-defaults--verbose--help
●查看正在运行的程序，使用的变量，使用SHOWVARIABLES命令
普通配置项
auto_increment_increment
auto_increment_offset
这两个配置项，经常在一起使用，所以一起说明。
命令行：
--auto_increment_increment[=#]
--auto_increment_offset[=#]
auto_increment_increment和auto_increment_offset用来控制AUTO_INCREATEMENTcolumns的操作。
这两个配置项都有当前会话值和全局值。
每一个变量的取值范围都是[1,65535]。
如果设置为0，则会被自动置为1；如果设置为大于65535或者小于0，则会自动被置为65535。
如果试图去设置负数，则会报错，并且值没有变化。
auto_increment_offset：
AUTO_INCREATEMENTcolumns的起始值；
auto_increment_increment：
AUTO_INCREATEMENTcolumns自增长的步长；
应用场景：
mysql>SET@@auto_increment_offset=5;
QueryOK,0rowsaffected（0.00sec）
mysql>SHOWVARIABLESLIKE'auto_inc%';
+--------------------------+-------+
|Variable_name|Value|
+--------------------------+-------+
|auto_increment_increment|10|
|auto_increment_offset|5|
+--------------------------+-------+
2rowsinset（0.00sec）
mysql>CREATETABLEautoinc2
->（colINTNOTNULLAUTO_INCREMENTPRIMARYKEY）;
QueryOK,0rowsaffected（0.06sec）
mysql>INSERTINTOautoinc2VALUES（NULL）,（NULL）,（NULL）,（NULL）;
QueryOK,4rowsaffected（0.00sec）
Records:
4Duplicates:
0Warnings:
0
mysql>SELECTcolFROMautoinc2;
+-----+
|col|
+-----+
|5|
|15|
|25|
|35|
+-----+
4rowsinset（0.02sec）
注意点：
1.计算公式：
auto_increment_offset+N×auto_increment_increment
whereNisapositiveintegervalueintheseries[1,2,3,...].（第一列的值是auto_increment_offset本身）
2.当auto_increment_offset的值比auto_increment_increment大时，auto_increment_offset的值会被忽略；
3.当auto_increment列已经有内容时，修改这两个配置项。
此时插入列中内容，选择从比列中现有的最大的数开始。
例如：
auto_increment_increment=10
auto_increment_offset=1
+-----+
|col|
+-----+
|1|
|11|
|21|
|31|
+-----+
修改后：
auto_increment_offset=4，这时再插入5列：
generatetheseries5+N*10=[5,15,25,35,45,...]
比列中现有最大的是35，所以，插入后的结果为：
+-----+
|col|
+-----+
|1|
|11|
|21|
|31|
|35|
|45|
|55|
|65|
+-----+
autocommit
命令行
VariableNameautocommit
VariableScopeSession
DynamicVariableYes
PermittedValuesTypeBoolean
设置为1，对表的所有操作都会自动生效。
设置为0，必须使用COMMIT来接受一个事务，使用ROLLBACK来取消。
当autocommit由0变为1时，mysql会对所有正在open的事务autocommit生效。
默认情况下，client连接时的值是autocommit=1。
如果要修改该默认值，可以设置init_connect系统变量。
autocommit的测试
对于innodb表：
首先创建两个innodb表：
mysql>createtabletab_kx（aintauto_increment,primarykey（a））;
QueryOK,0rowsaffected（0.16sec）
mysql>createtabletab_kx2（aintauto_increment,primarykey（a））;
QueryOK,0rowsaffected（0.17sec）
在session1，设置autocommit为OFF：
mysql>setautocommit=0;
QueryOK,0rowsaffected（0.00sec）
mysql>showvariableslike'%autocommit%';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|autocommit|OFF|
+---------------+-------+
1rowinset（0.00sec）
在session2设置autocommit为ON：
mysql>showvariableslike'%autocommit%';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|autocommit|ON|
+---------------+-------+
1rowinset（0.00sec）
在session1，对tab_kx插入三行数据但不提交：
mysql>insertintotab_kxvalues（''）;
QueryOK,1rowaffected,1warning（0.00sec）
mysql>insertintotab_kxvalues（''）;
QueryOK,1rowaffected,1warning（0.00sec）
mysql>insertintotab_kxvalues（''）;
QueryOK,1rowaffected,1warning（0.00sec）
mysql>select*fromtab_kx;
+---+
|a|
+---+
|1|
|2|
|3|
+---+
3rowsinset（0.00sec）
mysql>select*fromtab_kx2;
Emptyset（0.00sec）
此时查看session2是否可以看到这三行数据：
mysql>select*fromtab_kx;
Emptyset（0.00sec）
【说明】，session2看不到其他session没有提交的DML；
此时在session2插入四行数据到表tab_kx2（由于是autocommit的，所以不需要提交）：
mysql>insertintotab_kx2values（）;
QueryOK,1rowaffected（0.07sec）
mysql>insertintotab_kx2values（）;
QueryOK,1rowaffected（0.04sec）
mysql>insertintotab_kx2values（）;
QueryOK,1rowaffected（0.04sec）
mysql>insertintotab_kx2values（）;
QueryOK,1rowaffected（0.02sec）
mysql>select*fromtab_kx2;
+---+
|a|
+---+
|1|
|2|
|3|
|4|
+---+
4rowsinset（0.00sec）
【注意】但此时session1仍然看不到tab_kx2的数据，
mysql>select*fromtab_kx2;
Emptyset（0.00sec）
当手动执行commit时，才可以看到这些数据，同时session2也可以看到tab_kx表的数据。
mysql>commit;
QueryOK,0rowsaffected（0.03sec）
mysql>select*fromtab_kx2;
+---+
|a|
+---+
|1|
|2|
|3|
|4|
+---+
4rowsinset（0.00sec）
【结论】对于autocommit为OFF的session，其事务开始时间点之后发生的DML操作对其都是不可见的，只有当事务结束时才可见。
（其他session的DDL即时可见）
另外，如果其session执行了一个DDL，会隐式提交之前的事务，DML的修改其他session可见了。
但有意思的是，当我一个session中对tab_kx表drop的时候，会话被阻塞了（如果隐式提交了，应该不会发生这种情况），在另个session中执行读，也被阻塞了
此时我在原来的session（autocommit为OFF）执行显示提交commit：
其他session都被“激活”了，而且是按执行顺序激活的。
myisam表：
创建两个myisam表，
mysql>createtabletab_kx（aintauto_increment,primarykey（a））engine=myisam;
QueryOK,0rowsaffected（0.05sec）
mysql>createtabletab_kx2（aintauto_increment,primarykey（a））engine=myisam;
QueryOK,0rowsaffected（0.05sec）
在session1，（autocommit为OFF）插入三行数据，
mysql>insertintotab_kxvalues（）;
QueryOK,1rowaffected（0.00sec）
mysql>insertintotab_kxvalues（）;
QueryOK,1rowaffected（0.00sec）
mysql>insertintotab_kxvalues（）;
QueryOK,1rowaffected（0.00sec）
mysql>select*fromtab_kx;
+---+
|a|
+---+
|1|
|2|
|3|
+---+
3rowsinset（0.00sec）
在其他session中查询tab_kx，
mysql>select*fromtab_kx;
+---+
|a|
+---+
|1|
|2|
|3|
+---+
3rowsinset（0.00sec）
可以看到结果，这与innodb的表不同；不需要显示提交，DML修改就可以对外可见了。
【结论】myisam是非事务的存储引擎，commit是对事物有效的，所以没啥作用。
【注意】但当我对tab_kx做删除时，会话被阻塞，尽管没有事务性，但autocommit=OFF仍然在起作用，表上仍然持有锁，
在session1执行显示提交commit，立刻解锁，阻塞的会话被激活。
automatic_sp_privileges
VersionIntroduced
5.0.3
VariableName
automatic_sp_privileges
VariableScope
Global
DynamicVariable
Yes
PermittedValues
Type
boolean
Default
TRUE
默认值为1，server自动授予storedroutine（存储过程）的创建者EXECUTEandALTERROUTINE权限。
（droproutine需要ALTERROUTINE权限。
）当routine被drop时，server也会自动取消相应的权限。
当automatic_sp_privileges为0，server就不会自动增删这些权限。
（storedroutines:
其中包括storedprocedure和function）
关于创建routine的账户，createroutine的账户和使用CREATE的账户必须是同一个账户。
与调用DEFINER来定义routine的账户可以不同。
back_log
命令行
Command-LineFormat--back_log=#
Option-FileFormatback_log
OptionSetsVariableYes,back_log
VariableNameback_log
VariableScopeGlobal
DynamicVariableNo
PermittedValuesTypenumeric
Default50Range1-65535
应用场景
当Mysql的主线程在很短的时间内，接受到大量的连接请求时，该配置项的作用才会显示出来。
主线程检查这个连接并开启一个新的线程需要一些时间（尽管这个时间非常短）。
back_log配置项表明了：
在mysql暂停下来不处理任何请求的段时间内，有多少了请求可以被缓存下来。
当你期望短时间内有大量连接的时候，你可以将这个值配置大一些。
同时这个值也是incomingTCP/IPconnections的监听队列。
你的操作系统对这个队列也有他的限制。
Unixlisten（）systemcall的帮助信息会更详尽。
该值的配置不能操作操作的限制。
basedir
Command-LineFormat
--basedir=path
-b
Option-FileFormat
basedir
OptionSetsVariable
Yes,basedir
VariableName
basedir
VariableScope
Global
DynamicVariable
No
PermittedValues
Type
filename
Mysql安装的基本路径，所有相对路径的解析都是基于此参数。
mysql>SHOWVARIABLESlike'%base%';
+------------------------+-----------------------------------+
|Variable_name|Value|
+------------------------+-----------------------------------+
|basedir|/home/work/gm/ddbs/agent1/mysql1/|
+------------------------+-----------------------------------+
可以在配置文件中指定，也可以在启动时以参数形式传入。
big_tables
如果设置为1，所有的temporarytables就会被强制的存储在硬盘上，而不是内存中。
尽管这会很慢，但是当进行select操作，需要很多的临时表空间时，以下error将不会再发生：
Thetabletbl_nameisfull。
新connection的默认值是0（使用内存中的临时表）。
一般情况下，不用修改该变量，因为内存中的表会自动的转到硬盘上。
注意点
该变量一般被叫做sql_big_tables。
通常情况下，你永远不必设置该变量，因为in-memory表会被自动转成disk-based表。
binlog_cache_size
Command-LineFormat
--binlog_cache_size=#
Option-FileFormat
binlog_cache_size
OptionSetsVariable
Yes,binlog_cache_size
VariableName
binlog_cache_size
VariableScope
Global
DynamicVariable
Yes
PermittedValues
PlatformBitSize
32
Type
numeric
Default
32768
Range
4096-4294967295
PermittedValues
PlatformBitSize
64
Type
numeric
Default
32768
Range
4096-184********709547520
cacheforbinarylog的大小。
该binarylog用来存储事务中的sql语句。
如果该server支持事务存储引擎并且log_bin=ON，那么每一个client都会被分配binarylogcache。
如果你经常操作大数据量，多语句的事务，那么应该提高该参数的配置来获得性能。
Binlog_cache_use和Binlog_cache_disk_use对你会有参考意义。
bulk_insert_buffer_size
Command-LineFormat
--bulk_insert_buffer_size=#
Option-FileFormat
bulk_insert_buffer_size
OptionSetsVariable
Yes,bulk_insert_buffer_size
VariableName
bulk_insert_buffer_size
VariableScope
Both
DynamicVariable
Yes
PermittedValues
PlatformBitSize
32
Type
numeric
Default
8388608
Range
0-4294967295
PermittedValues
PlatformBitSize
64
Type
numeric
Default
8388608
Range
0-184********709547520
MyISAM类型数据表使用特殊的类树结结构的缓存。
使用整块方式（bulk）来加速INSERT...SELECT,INSERT...VALUES（...）,（...）,...,和LOADDATAINFILE添加非空tables。
该参数限制每个线程使用枢性结构缓存的大小。
设置为0，表示禁用该优化。
默认值是8MB.
注意点
只能用于向非空数据表中执行插入操作。
character_set_client
VariableName
character_set_client
VariableScope
Both
DynamicVariable
Yes
PermittedValues
Type
string
来自client的语句的characterset。
当client连接server时，使用该session变量指定的characterset（许多client支持--default-character-set来指定）。
mysqld启动时可以用--skip-character-set-client-handshake来忽略clientcharacterset的配置。
其他信息
mysql>SHOWVARIABLESlike'%character_set%';
+--------------------------+--------------------------------------------------------+
|Variable_name|Value|
+--------------------------+--------------------------------------------------------+
|character_set_client|gbk|
|character_set_connection|gbk|
|character_set_database|gbk|
|character_set_fil
升级会员 