Mysql lost Connections

背景介绍

下午六点多，收到短信报警。接口的响应时间超长，响应状态为不响应。线上查看接口日志，发现无法链接数据库。
报错如下：
Could not create connection to database server. Attempted reconnect 3 times . Giveing up

• 初次判断 数据库连接过多，断开连接池
  
  由此可见是 数据库链接打满，拒绝链接导致。
• 但是为什么会链接暴涨呢
  复现链接暴涨，使用show full processlists 查看链接来自哪个服务，最终确定来自定时器任务
  猜测1、既然链接不上，是不是mysql的链接数设置，查看mysq最大链接数 max_connections 100000的链接数 足够用， 说明不是连接数限制
  猜测2、 是否是查询超时，一直在进行查询， 查看mysql的超时时间 超时时间也已经足够，另一个超时设置就是客户端自己本身 jdbc的链接超时时间， 这一点也被排除。
  到这里又陷入了
• 忽略了错误日志
  当场景浮现的时候，忘记去查看mysql的日志，不看不知道 一看就吓一跳。报错如下：

2019-06-23T13:35:34.890827Z 0 [ERROR] InnoDB: Space id and page no stored in the page, read in are [page id: space=940, page number=1670], should be [page id: space=940, page number=1658]
2019-06-23 21:35:34 0x2b366929e700  InnoDB: Assertion failure in thread 47512692582144 in file btr0pcur.cc line 454
InnoDB: Failing assertion: btr_page_get_prev(next_page, mtr) == btr_pcur_get_block(cursor)->page.id.page_no()
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
13:35:34 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=1048576
max_used_connections=22
max_threads=10000
thread_count=22
connection_count=21
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 15509743 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2b36fc0008c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 2b366929dea8 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf18655]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x4a4)[0x79bdf4]
/lib64/libpthread.so.0(+0xf370)[0x2b35244f4370]
/lib64/libc.so.6(gsignal+0x37)[0x2b352579c1d7]
/lib64/libc.so.6(abort+0x148)[0x2b352579d8c8]
/usr/local/mysql/bin/mysqld[0x10a5725]
/usr/local/mysql/bin/mysqld[0x10d3667]
/usr/local/mysql/bin/mysqld[0x102d412]
/usr/local/mysql/bin/mysqld(_Z15row_search_mvccPh15page_cur_mode_tP14row_prebuilt_tmm+0xc99)[0x1032a39]
/usr/local/mysql/bin/mysqld(_Z24row_scan_index_for_mysqlP14row_prebuilt_tPK12dict_index_tbPm+0x850)[0x1011210]
/usr/local/mysql/bin/mysqld(_ZN11ha_innobase7recordsEPy+0x1eb)[0xf47deb]
/usr/local/mysql/bin/mysqld(_Z13opt_sum_queryP3THDP10TABLE_LISTR4ListI4ItemEPS4_+0x91e)[0xe4bc6e]
/usr/local/mysql/bin/mysqld(_ZN4JOIN8optimizeEv+0x99f)[0xce9e3f]
/usr/local/mysql/bin/mysqld(_ZN13st_select_lex8optimizeEP3THD+0x672)[0xd2e3c2]
/usr/local/mysql/bin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x21f)[0xd2e65f]
/usr/local/mysql/bin/mysqld[0xcf0073]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x3712)[0xcf3a52]
/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3ad)[0xcf551d]
/usr/local/mysql/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x116d)[0xcf66ed]
/usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0x194)[0xcf71e4]
/usr/local/mysql/bin/mysqld(handle_connection+0x29c)[0xdc1bec]
/usr/local/mysql/bin/mysqld(pfs_spawn_thread+0x174)[0x11cddf4]
/lib64/libpthread.so.0(+0x7dc5)[0x2b35244ecdc5]
/lib64/libc.so.6(clone+0x6d)[0x2b352585e76d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (2b36fc0157d0): is an invalid pointer
Connection ID (thread ID): 37
Status: NOT_KILLED

这里问题已经很明显了。Space id and page no stored in the page, read in are .上网查询一番，是mysql的innodb 存储 是分块存储的，其中一块数据损坏

mysqlcheck工具检查看具体哪个表的文件损坏：
mysqlcheck -A -u root -p

得出结论是一个用户积分表数据损坏。
验证问题 
>>> select count(1) from tbl_jifen
>lost connections

• 到这里问题已经很明了了。数据恢复即可
  这里有两种办法：
  方法1、使用innodb_force_recovery参数来恢复，innodb_force_recovery参数有6个选项具体如下：

1 (SRV_FORCE_IGNORE_CORRUPT)
 
Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
 
2 (SRV_FORCE_NO_BACKGROUND)
 
Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.
 
3 (SRV_FORCE_NO_TRX_UNDO)
 
Does not run transaction rollbacks after crash recovery.
 
4 (SRV_FORCE_NO_IBUF_MERGE)
 
Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics.
This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes. Sets InnoDB to read-only.
 
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
 
Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.
This value can permanently corrupt data files. Sets InnoDB to read-only.
 
6 (SRV_FORCE_NO_LOG_REDO)
 
Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files.
Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures. Sets InnoDB to read-only.

innodb_force_recovery=1 最小的损失去恢复数据, 增加配置 重启服务，dump出来数据

方法2、既然是某一块数据有问题，我们避过有问题的数据进行，其他数据的恢复，确保大部分数据ok.有问题的数据 我们反向从redis集群中读取插入
1、利用数据表id字段查找（利用二分查找快速锁定）
2、排除有问题数据，select其他数据入新表即可

总结

当时遇到这个问题，还是很高兴的。 又有新的东西可以挑战一下。学到新的东西

缺失：没有第一时间去查看mysql的日志，锁定问题所在。
再次说明 遇到问题 看日志 看日志 看日志 ！！！