MySQL生产case的处理

1、大量sleep状态线程，逼近最大连接数
一般应用重启或者应用加机器，数据库会出现大量sleep连接
可先kill部分线程
联系相关开发调整jdbc初始化连接参数
2、大量running状态线程
• 如果是慢查询SQL导致，MySQL服务器负载(io、cpu)应该很高
可先kill部分线程，避免超过最大连接数，缓解服务器压力，联系相关开发
同时进行SQL优化，加索引、改写SQL，紧急情况下应通知相关人员重启应用
生成kill线程SQL的SQL:
select CONCAT(‘kill ‘,id,’;’) from information_schema.PROCESSLIST
where command not in (‘Sleep’)
and user not in (‘repl’);
• 如果是锁等待导致，MySQL服务器基本无压力
可先kill部分线程，避免超过最大连接数
尽快找到未提交的事务线程，并kill它
查询当前持有锁线程及此线程最后一条SQL、锁等待的线程：
SELECT b.PROCESSLIST_ID,a.trx_id,a.trx_state,a.trx_started,c.SQL_TEXT,e.trx_mysql_thread_id
FROM
information_schema.innodb_trx a,
performance_schema.threads b,
performance_schema.events_statements_current c,
information_schema.INNODB_LOCK_WAITS d,
information_schema.innodb_trx e
where a.trx_mysql_thread_id=b.PROCESSLIST_ID
and b.THREAD_ID=c.THREAD_ID
and a.trx_id=d.blocking_trx_id
and e.trx_id=d.requesting_trx_id
3、从库复制sql_thread报错，应确首先确保数据一致性。
对于可控的错误，如从库因特殊原因，DBA曾经手工创建或删除了某个表、用户等对象，现在主库也创建或删除了同名的对象，此时复制对象冲突或不存在报错，可酌情删除或创建对象，也可跳过此事务。
其它情况建议重建从库。

4、数据库宕机，当前生产数据库日志刷盘策略配置为双1，可保证宕机数据不丢
• 从库宕机
若从库无业务访问，从库服务器开启后检查复制状态，系统日志，数据库日志，如无问题，从库可继续投入使用，否则应尽快搭建新从库
若从库有业务访问，应尽快将原从库虚IP绑定至主库，或告知应用运维修改JDBC数据源，并尽快搭建新从库

• 主库宕机
宕机后系统启动+数据库启动时间可能比较漫长，为保证系统可用性，应尽快将原主库请求切换至从库，改虚IP或改JDBC数据源。MySQL 故障处理Q&A
5、mysql DNS反解：skip-name-resolve
错误日志有类似警告：
120119 16:26:04 [Warning] IP address ‘192.168.1.10’ could not be resolved: Name or service not known
120119 16:26:04 [Warning] IP address ‘192.168.1.14’ could not be resolved: Name or service not known
120119 16:26:04 [Warning] IP address ‘192.168.1.17’ could not be resolved: Name or service not known

通过show processlist发现大量类似如下的连接：
|592|unauthenticated user|192.168.1.10:35320|NULL|Connect| |login|NULL|
|593|unauthenticated user|192.168.1.14:35321|NULL|Connect| |login|NULL|
|594|unauthenticated user|192.168.1.17:35322|NULL|Connect| |login|NULL|

skip-name-resolve 参数的作用：不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间。
修改配置文件添加并需要重启：
[mysqld]
skip-name-resolve

6、问题错误日志：Error: Can’t create a new thread (errno 12)
数据库服务器问题，数据库操作无法创建新线程。一般是有以下3个方面的原因：
1）max_connections过大
2）wait_timeout过大
3）ulimit 太小

7、从库延时过大
MySQL slave延迟过大，这时可以show slave status查看状态。
1）如果从库的Exec_Master_Log_Pos在往前推进，说明可能是主库的并发插入太高，导致从库单线程跟不上。这需要查看主库和从库的io等指标来确认。
2）如果从库的Exec_Master_Log_Pos一直静止不动，这很可能是存在大事务。在日志为row模式的时候，slave端show processlist只能看到ddl的操作，dml的操作是看不到的。
这时候就需要结合informa_schema.innodb_trx来查看里面的事务状态了，里面可以看到正在运行的事务的一些指标，大事务一般需要整改。
如果是碰到那种没有主键的表的更新，那会非常恐怖，需要kill 进程，增加索引才能继续下去，不然一般是跑不出来的。

8、事后追查根源sql
有时候会碰到cpu某段时间飙升，或者某段时间应用响应缓慢，当时没有查看，事后再查看数据库当时的状态和导致故障发生的原因。mysql没有oracle的awr，只能如下处理：
1）查看当时的主机负载，还有mysql的qps指标，查看是否是应用请求数过多导致的。
2）查看当时的慢sql，一般根据Rows_examined来进行排名，找出排名最高的几个sql，如果占比很明显，说明就是这几个sql导致资源耗尽的。
pt-query-digest slow.log --order-by=Rows_examined:sum --since ‘2017-04-17 09:30:00’ --until ‘2017-04-17 10:00:00’ > slow_report.log
3）提前建立周全的状态数据收集机制。比如说每10s收集一次processlist的信息，每10分钟收集一次performance_schema.events_statements_summary_by_digest的信息，这样对事后追查非常有效。

9、死锁分析解决
MySQL的innodb虽然也是行锁，但是还会有间隙锁的存在，所以MySQL死锁发生的概率会比Oracle大的多。每次发生死锁，一般如下分析：
1）MySQL发生死锁后，也会根据权重自动kill掉一个session，同时会把死锁的相关信息记录在 show engine innodb status里面的内容中。但是只会记录最后一次的信息，之前的都会被冲掉。
在分析死锁的时候，可以尝试打开innodb_print_all_deadlocks这个参数，这样会记录所有的死锁信息到error.log中，这样对分析死锁会有帮助。
2）根据死锁发生的内容，结合当时保存的 processlist信息(如果有保存的话)，分析发生的原因，一般原因是事务过大、锁粒度过大、sql执行计划有问题、应用逻辑有问题等。找出原因后，跟开发一起整改。
3）把数据库的事务隔离级别从REPEATABLE-READ改成READ-COMMITTED，这样可以大大降低死锁发生的概率。

10、slave跳过复制报错
有时候需要手动跳过slave的报错，分"非gtid模式"和"gtid模式"两种情况。
1）非gtid模式
只需通过设定 SQL_SLAVE_SKIP_COUNTER 的值，即可忽略一些复制事件。例如：
#需要先关闭SLAVE服务
mysql> STOP SLAVE;
#忽略N个事件（event），通常一个SQL是一个事件
mysql> SET global SQL_SLAVE_SKIP_COUNTER=N;
#再次启动SLAVE服务
mysql> START SLAVE;
2）gtid模式
mysql> show slave status\G
*************************** 1. row ***************************
………………………………………………………
Master_Server_Id: 201
Master_UUID: c8e4f482-91c5-11e5-8415-080027844a21
………………………………….
Retrieved_Gtid_Set: c8e4f482-91c5-11e5-8415-080027844a21:90672-90677
Executed_Gtid_Set: c8e4f482-91c5-11e5-8415-080027844a21:1-39917:90659-90663:90671-90676,
d15845eb-91c5-11e5-8416-080027844a21:1-128
Auto_Position: 0
1 row in set (0.00 sec)

从上面可以看到，slave端已经执行完了90676这个事务，但是在执行90677这个事务时出现了错误。

mysql> set gtid_next=“c8e4f482-91c5-11e5-8415-080027844a21:90677”;
Query OK, 0 rows affected (0.00 sec)
–设置gtid_next为报错的这个gtid号。

mysql> begin;commit;
Query OK, 0 rows affected (0.02 sec)
–通过begin;commit;执行一个空事务。

mysql> set gtid_next=“AUTOMATIC”;
Query OK, 0 rows affected (0.00 sec)
–然后把gtid_next设置回原来的值 AUTOMATIC。

mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
–启动slave。

mysql> show slave status\G
…………………
Retrieved_Gtid_Set: c8e4f482-91c5-11e5-8415-080027844a21:90672-90677
Executed_Gtid_Set: c8e4f482-91c5-11e5-8415-080027844a21:1-39917:90659-90663:90671-90677,
d15845eb-91c5-11e5-8416-080027844a21:1-128
–可以看到Executed_Gtid_Set已经成功执行到了90677(其实是绕过了这个事务)。
11、MySQL 5.7定位io占用高的数据库线程
MySQL数据库连接采用的线程方式，不同于Oracle的进程方式，一般很难直观的定位io占用最高的数据库连接。在5.7则可以用如下方式进行定位：
先用iotop -u mysql确定占用io最高的数据库线程的TID号。
然后在5.7里，查询performance_schema.threads
select name,type,thread_id,thread_os_id,processlist_id
from performance_schema.threads
where thread_os_id = $TID;
这样就确定了占用io最高的数据库线程。