Mysql常用DBA管理整理

Mysql常用DBA管理整理

时间：2010-8-16

一、MySQL的超时解析

1.1 connect_timeout

获取连接的阶段起作用。如果超过了指定的时间内还没有拿到连接的话就中断连接.

1.2 interactive_timeout

这个与wait_timeout表示在连接空闲阶段起作用

1.3 net_read_timeout和net_write_timeout

表示在连接繁忙阶段起作用

即使没有网络问题，也不能允许客户端一直占用连接。对于保持sleep状态超过了wait_timeout（或interactive_timeout，取决于CLIENT_INTERACTIVE标志）的客户端，MySQL会主动断开连接。

即使连接没有处于sleep状态，即客户端忙于计算或者存储数据，MySQL也选择了有条件的等待。在数据包的分发过程中，客户端可能来不及响应 （发送、接收、或者处理数据包太慢）。为了保证连接不被浪费在无尽的等待中，MySQL也会选择有条件（net_read_timeout和 net_write_timeout）地主动断开连接。

 

结论：有三种类型的连接分别为

1. 请求服务器的连接有一个超时时间

2. 当客户端为空闲的时候MYSQL会认为到底你要空闲多久时间就有了一个wait_timeout

3. 当客户端太繁忙了那也有一个超时时间的即为net_read_timeout

二、mysqldump意久终止原因及解决方法

1、Lost connection to MySQL server at ‘reading initial communication packet’：
这个主要是因为DNS不稳定导致的。如果做了网络隔离，MySQL处于一个相对安全的网络环境，那么开启skip-name-resolve选项将会最大程度避免这个问题。

2、Lost connection to MySQL server at ‘reading authorization packet’：
从 MySQL获取一个可用的连接是多次握手的结果。在多次握手的过程中，网络波动会导致握手失败。增加connect_timeout可以解决这个问题；然 而增加connect_timeout并不能防止网络故障的发生，反而会引起MySQL线程占用。最好的解决办法是让mysqldump重新发起连接请 求。

3、Lost connection to MySQL server during query：
这个问题具备随机性，而淘宝MySQL的应用场景决定了我们无法多次备份数据以便重现问题。
然而我们注意到这个问题一般会在两种情况下会发生。一种是mysqldump **** | gzip ****；另外一种是mysqldump **** > /nfs-file
注 意，不管是gzip还是nfs都有一种特点，那就是它们影响了mysqldump的速度。从这个角度思考，是不是mysqldump从MySQL接受数据 包的速度不够快导致Lost connection to MySQL server during query错误呢？（这个问题我也经常遇到不清楚是什么原因导致的）

mysqldump就会报Lost connection to MySQL server during query错误。经过多次测试，确定这个错误是由于net_write_timeout设置过短引起。

 

结论：引起这个错误Lost connection to MySQL server during query 的一个主要原因是处理能力过慢导致服务器主动关闭连接。解决方案是配置net_write_timeout 更长一些!

 

三、如何保护MYSQL被入侵

3.1 删除授权表中的通配符

不要对用户使用%这样的通配符。容易出现越权

3.2 要求使用安全密码

使用复杂的密码

3.3 检查配置文件许可

3.4 加密客户与服务器之间数据传送

MySQL（及其它）客户与服务器构架的一个重要问题就是通过网络传送数据时的安全问题。

3.5 禁止远程访问

如果用户不需要远程访问服务器，你可以迫使所有MySQL连接通过UNIX插槽文件来完成，从而大大减少网络受攻击的风险。这一过程可通过跳过网络选项启动服务器来完成。这样可以阻止TCP/IP网络连接到MySQL上，保证没有用户可以远程连接系统。

3.6 积极监控MYSQL访问记录

MySQL带有许多不同的日志文件，它们记录客户连接，查询和服务器错误。其中，最重要的是一般查询日志，它用时间标签记录每名客户的连接和中断时间，并 记录客户执行的每个查询。如果你怀疑发生了不寻常的行为，如网络入侵，那么监控这个日志以了解行为的来源是个好方法。

 

四、MYSQL慢查询记录不准确的问题

修改配置文件将log-queries-not-using-indexes=0。

说明：这个参数的意思是指记录所有查询不使用索引的语句

 

五、mysql中table is read only解决

解决办法：mysqladmin –p flush-tables

 

六、MYSQL突然变慢了怎么办？

6.1 第一步：检查系统的状态

通过OS的一些工具检查系统的状态像CPU、内存、交换、磁盘的利用率、除此之外再观察一下哪些进程比较占用CPU、内存使用有ps aux 的命令.

6.1.1 使用sar检查IO

6.1.2 使用vmstat检查CPU资源

6.1.3 观察磁盘IO问题。做raid10提高性能，关闭swap分区提高内存使用率

6.1.4 网络问题是否能够telnet通。看看防火墙的设置是否正常。MYSQL是否开启了skip-networking 的选项

1. 检查系统环境是否存在瓶颈

6.2 第二步检查mysql参数

2.1.1 max_connect_errors

max_connect_errors默认值为10，如果受信帐号错误连接次数达到10则自动堵塞，需要flush hosts来解除。如果你得到象这样的一个错误：

Host 'hostname' is blocked because of many connection errors.

Unblock with 'mysqladmin flush-hosts'

这意味着，mysqld已经得到了大量(max_connect_errors)的主机'hostname'的在中途被中断了的连接请求。在max_connect_errors次失败请求后，mysqld认定出错了(象来字一个黑客的攻击)，并且阻止该站点进一步的连接，直到某人执行命令mysqladmin flush-hosts。

内网连接的话，建议设置在10000以上，已避免堵塞，并定期flush hosts。

 

 

2.1.2 connect_timeout

指定MySQL服务等待应答一个连接报文的最大秒数，超出该时间，MySQL向客户端返回 bad handshake。默认值是5秒，在内网高并发环境中建议设置到10-15秒，以便避免bad hand shake。建议同时关注thread_cache_size并设置thread_cache_size为非0值，大小具体调整。

 

2.1.3 skip-name-resolve

skip-name-resolve能大大加快用户获得连接的速度，特别是在网络情况较差的情况下。MySQL在收到连接请求的时候，会根据请求包中获得的ip来反向追查请求者的主机名。然后再根据返回的主机名又一次去获取ip。如果两次获得的ip相同，那么连接就成功建立了。在DNS不稳定或者局域网内主机过多的情况下，一次成功的连接将会耗费很多不必要的时间。假如MySQL服务器的ip地址是广域网的，最好不要设置skip-name-resolve。

 

2.1.4 slave-net-timeout=seconds

  参数含义：当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据。默认值是3600秒，如果需要保证同步性，如此NC的参数请极力控制在10秒以下。

2.1.5 master-connect-retry

参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试。默认是60秒，请按照合理的情况去设置参数。

 

第二步：检查配置参数是否合理

 

6.3 第三步检查mysql的相关状态值

6.3.1 关注连接数

mysql> show global status like 'Max_used_connections';

是否达到了最大连接数了。

3.1.2 show full processlist

1.显示所有进程

如果正在运行的语句过多，运行时间过长就说明MYSQL的执行效率可能存在问题。必要的时候可以将相应慢的进程给kill掉！

 

3.2 关注下系统的锁情况

show status like '%lock%';

3.3 关注下慢查询日志

如果CPU资源负载过高的情况下可以考虑将慢查询给关闭掉！

 

第三步：检查MYSQL当前的运行状态(一些状态参数、进程、锁)

定期对表做优化！