蛋疼的几个Mysql默认参数

amwdnuff

蛋疼的几个Mysql默认参数




今天发个自己整理的几个Mysql默认参数，默认的让人很蛋疼。持久更新……

1.max_connect_errors
默认为10，表示1个主机10次连接失败，将再也不能连接mysql服务器了，要等mysql admin执行flush hosts才可以进行连接。


A主机连接mysql服务器出现10次或更多连接错误，就会出现屏蔽掉A发起的任何连接，但是在mysqld重新启动或者执行用'mysqladmin flush-hosts'  或者 flush hosts才可以重新连接

2.connect_timeout

当网络有时候比较拥塞的时候，可能会高于这个数。
5.1.23 以前默认是5秒，以后默认是10秒，如果超过这个时间，服务器会记录： Lost connection to MySQL server at 'XXX', system error: errno.

3.skip-name-resolve
默认情况下mysql需要开启线程去请求客户端所用的域名，其中涉及到两个函数gethostbyaddr_r() and gethostbyname_r().通常这样会很慢，我们可以关闭mysqld不去反解dns。
如果关闭掉，grant授权这些都应该使用ip来定义。
{
The thread takes the IP address and resolves it to a host name (using gethostbyaddr()). It then takes that host name and resolves it back to the IP address (using gethostbyname()) and compares to ensure it is the original IP address.

If the operating system supports the thread-safe gethostbyaddr_r() and gethostbyname_r() calls, the thread uses them to perform host name resolution.

If the operating system does not support the thread-safe calls, the thread locks a mutex and calls gethostbyaddr() and gethostbyname() instead. In this case, no other thread can resolve host names that are not in the host name cache until the first thread unlocks the mutex.

You can disable DNS host name lookups by starting mysqld with the --skip-name-resolve option. However, in this case, you can use only IP numbers in the MySQL grant tables.

If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookups with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default value: 128) and recompiling mysqld.
}

4.interactive_timeout
默认是28800，默认太大了，可以改个稍微小的参数比如10-120

5.max_connecti**
最大的mysqld提供连接数，默认是150，一般正式生产服务器都不止这个数。可以根据实际情况进行修改

6.back_log
默认值50，较小参考值100-200(内存要消耗点)

7.slave_net_timeout
MySQL主从复制的时候， 当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据。
默认是3600秒，这个值太大可以改成30

8.wait_timeout
这个就不用说了，前面有篇《mysql的长连接》专门说了
默认28800,推荐：10