mysql中的主要的参数

最新推荐文章于 2023-10-08 17:41:32 发布

aoerqileng

最新推荐文章于 2023-10-08 17:41:32 发布

阅读量1.3k

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/aoerqileng/article/details/51172833

版权

MYSQL 专栏收录该内容

274 篇文章 2 订阅

订阅专栏

1查看mysql的参数的默认值设置

mysqld --verbose --help

Usage: mysqld [OPTIONS]
msqld 后面的选项读取的顺序文件如下
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql-5.6.21/my.cnf/my.cnf ~/.my.cnf

2 查看server当前使用的参数

远程查看：mysqladmin -h ip -P port variables|grep binlog

本机查看：show variables

query_cache_size:

该参数是缓存查询结果集分配的内存大小，默认情况下，查询缓存是关闭的，query_cache_type是0的情况下，query_cache_size也会有默认的1M大小,,zai 5.6.8之前，默认的query_cache_type=1,query_cache_size=0,为了如果不使用查询缓存，query_cache_type应该设置成0，query_cache_size=0。这个参数可以接受的值是1024的倍数，如果不是就会设置成最最接近倍数的那个最小值。查询缓存需要最小40KB的空间来分配数据结构，如果设置的太小了，会有警告产生。

query_cache_type:

有3个可选设置，0,1,2 。0就是不适用查询缓存或在查询缓存中取数据，如果要禁止分配内存，还需设置query_cache_size=0。1会缓存所有的查询结果，除了select sql_no_cache这样的sql。2 只缓存select sql_cache这样的sql结果集。

table_definition_cache:

能够存储在定义缓存中的表定义数量,如果你使用了很多的表，你可以创建一个大的table definition cache来加快打开表的速度，table definition cache不会占用太多的空间，也不会像普通表缓存使用文件描述符，最小的值是400，默认值是基于下面的公式计算，最大到2000.

400+（table_open_cache/2),在5.6.8之前，默认的事400

table_open_cache:

所有线程打开的表的数量，增加这个值会增加mysqld需要的文件描述符，你可以通过查看opened_tables的状态来决定是否需要增加这个值，如果opened_tables这个值很大，并且你也不经常使用flush tables（这个动作会强制所有的表关闭在打开），那么就需要增加table_open_cache这个变量。

table_open_cache_instances:

打开表缓存实例的个数，为了减缓会话之间的竞争，打开的表缓存可以被分配到table_open_cache/table_open_cache_instances大小的不同小内存中，一个会话为了dml语句只需锁定其中一个内存实例，ddl语句仍然需要锁定整个内存，但是ddl执行的不是很频繁，在一个16核或是更多核的系统上推荐设置成8或16。

sql_mode

:在5.6上这个参数的默认值是 NO_ENGINE_SUBSTITUTION ，可以被动态设定，之前是个空的字符串，该参数的具体取值含义待看.....

skip-name-resolve:

在检查客户端连接的时候不解析名称，只使用ip，如果使用了这个选项，在分配权限的时候host的列必须是ip或localhost,

skip-networking:对tcp/ip的连接不做监听，所有与mysqld的交互使用命名管道或是socket，这个参数对于只有本地客户端访问的情况有用。

skip-slave-start:

back_log:

max_connections:允许同时能连接的客户端的个数，默认情况下这个值是151，增加这个值就增加了mysqld打开的文件描述符，如果需要的描述符不可用，这个server减少max_connections的数量。

max_connect_errors:如果一个机器连接次数超过该参数的值都没有成功，那么server会阻止该机器的更多的连接，可以通过刷新host cache来解除阻塞，可以通过flush hosts语句或是执行mysqladmin flush-hosts命令，如果一哥机器连接失败了好多次，但是在达到上限之前成功了，那么失败的计数会清除到0，mysql5.6.6的默认值是100.

max_allowed_packet:最大的包大小或是生成的字符串的大小，如果使用了blob或是很大的字符串，一定要调大这个值，协议限制该值大小是1GB,如果你通过修改max_allowed_packet改变了消息缓存大小，也应该改变客户端的缓存大小，例如mysql和mysqldump的默认大小是16MB和24MB，可以在命令行上设置max_allowed_packet来改变客户端的大小。

binlog_cache_size:

binlog_stmt_cache_size:

max_heap_table_size:

这个值设置了用户创建内存表允许的最大大小。

tmp_table_size:

是内存临时表的最大大小，实际的大小取决于tmp_table_size和max_heap_table_size哪个更小，如果一个内存临时表超出了限制，mysql会自动的将它转换成磁盘上的myisam表，如果有很多的group by查询要增加这2个参数。

thread_cache_size:

这个值表示有多少的线程缓存以备重用，当客户端的数量小于thread_cache_size的数量时，客户端在断开后，客户端的线程会放到缓存中，如果有很多的客户端的连接，可以增大这个值来提高性能，如果server每秒有几百个连接，你就要设置这个参数很大，这样就可以缓存线程。

key_buffer_size:

MyISAM表的索引块被缓存并且被所有的线程共享，key_buffer_size是索引块的内存空间，这个值在32位平台上的最大值是4GB，64位平台上的值可以更大，

可以使用show status语句来检查key_read_requests,key_reads,key_write_requests和key_writes变量， key_reads/key_read_requests的比率正常情况下应该小于0.01，key_writes/key_write_request应该接近1,

The fraction of the key buffer in use can be determined using key_buffer_size in conjunction with the Key_blocks_unused status variable and the buffer block size, which is available from the key_cache_block_size system variable:

 
 
  
  
   
   
  
  
 
 
1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)

sort_buffer_size

:每个会话的排序要分配sort_buffer_size大小的内存用来排序，sort_buffer_size不针对任何的存储引擎，增大max_sort_length可能要求增大sort_buffer_sized的大小，如果在show global status的输出中看到了每秒很多的sort_merge_passes，你需要增加sort_buffer_size来加快order by 或group by的操作。

read_buffer_size:

每个线程对MyISAM表做一个顺序扫描会分配read_buffer_size大小的内存，如果有很多的顺序读，就可能需要增加这个值，默认是131072，这个值应该设置成4KB的倍数，这个参数也会被用于所有存储引擎的下列情况中：

1 批量插入到分区中

2嵌套查询

3For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.

read_only:当该参数启用的时候，除了有super权限的用户外，所有的客户端都不能更新，这个参数默认是禁用的。如果这个参数被启用，server会允许下面这些操作：

1在复制环境下，在slave机器上启用这个参数能确保slave机器接受只来自master server的更新，一般在从库上要设置这个参数。

2使用 analyze table或optimize table命令

3对临时表的操作

4插入日志表

join_buffer_size:

该参数代表了没有使用索引进行了全表扫描的连接及普通索引扫描和范围索引扫描。通常，获得快速连接的最好方式是添加索引，当添加索引的方式行不通时，可以增加join_buffer_size来获得快速的全连接。一个join buffer为2个表的全连接分配，对于没有索引的复杂的多表连接，多个join buffer可能会被分配。

read_rnd_buffer_size:

这个参数用于MyISAM表的读取及对于任意的存储引擎，多范围读的优化。

bulk_insert_buffer_size:

MyISAM使用了特别的树状的缓存来使insert...select,insert..values, 及load data infile批量插入更快，当向非空表中插入数据的时候。这个值限制了每个线程缓存树的大小。默认是8MB。

collation_server:

使用指定的名称来作为server的排序规则。中文设置

character_set_server = utf8
collation_server = utf8_general_ci

log_slave_updates:

该值是说明slave在master上接收的更新是否应该被记录在slave自己的二进制日志中，在串联的结构中slave上应该开启这个参数，否则串联不起来，默认值是false。

-----------------------------------------------------------------------下面是主从复制相关的参数----------------------------------------------------------

log-bin-trust-function-creators

binlog-cache-size

事务缓存在内存中的部分的大小，单位字节，如果事务很大，超出了这个大小，那么超出的部分会放到磁盘上，所以有很大事务的情况下，需要增加这个值得大小。
max-binlog-cache-size

这个选项用于限制二进制日志中每个事务的大小，如果事务的大小超过了这个限制，语句将会出错终止。
max-binlog-size

如果向binary log写日志导致当前日志文件大小超过了这个变量设置的值，server会轮换二进制日志。
sync-binlog

当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

sql_log_bin 语句只在主库上执行，不在从库执行，也就是主库上的变动不写二进制日志，这个参数谨慎使用。

skip-slave-start 该参数是控制从库的复制不随数据库的启动而启动，这样才能方便管理，否则会出现很多无法控制的异常，该参数添加到配置文件中即可。

[server]
port=3306
#basedir=/usr
datadir=/data/mysql_3306
#socket=/tmp/mysql3306.sock

skip-name-resolve
skip-slave-start

server_id=123456

auto_increment_increment,auto_increment_offset

这两个参数主要是为了master-to-master的复制使用的，可以用来控制auto_increment的列，这2个参数可以在全局及会话级别设置，合理的范围是1到65535，auto_increment_incremnet控制着连续列的间隔，auto_increment_offset控制着自动增长的列的起始值。

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE autoinc1
    -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
  Query OK, 0 rows affected (0.04 sec)

mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
+-----+
4 rows in set (0.00 sec)

mysql> SET @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE autoinc2
    -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM autoinc2;
+-----+
| col |
+-----+
|   5 |
|  15 |
|  25 |
|  35 |
+-----+
4 rows in set (0.02 sec)

如果这2个值有改变了，但是表中已经存在了一些记录，那么新插入的值，会在按下面的公式生成的序列中生成

auto_increment_offset + N × auto_increment_increment

新的值将会是大于表中现有最大值得序列中的最小值。

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
+-----+
4 rows in set (0.00 sec)

mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
|  35 |
|  45 |
|  55 |
|  65 |
+-----+
8 rows in set (0.00 sec)

按上面的公式生成的序列是 [5, 15, 25, 35, 45, ...]，原有记录最大值是21，那么下一个值就是35