专栏首页洁癖是一只狗Mysql宕机临时处理方案

Mysql宕机临时处理方案

在日常开发中,难免会遇到业务高峰期,到时mysql不可用,但是这个时候领导肯定要求的最低限度,就是让业务跑起来,今天我们就说说有哪些方案可以临时解决这种问题

短连接

正常的短连接就是连接数据库后,执行少量的sql,下次在使用的时候,再次连接,但是这种情况,当遇到业务高峰期的时候,就有可能导致mysql不可用,我们在之前的文章中知道,连接是一个很复杂的过程,成本很高,不但要进行权限的验证,还要获取这个连接数据的读写权限。

在业务量不大的时候,这些成本还比较小,但是,短连接存在一定的风险,当数据处理的慢的时候,超过max_connections参数,就会拒绝新来的链接,出现提示Too many connections,这种提示对于用户就是数据库不可用,当然如果是这样的话,我们是不是就可以直接修改参数max_connections,使其值变大,但是我们还要考虑,连接数多的话,也会消耗大量的资源,导致cpu居高不下,最终连接无法获取资源,不能执行sql,因此我们是不是还有其他方案呢

第一种,处理那些占用的连接,但是不工作的链接

max_connections参数,不是看谁在running,仅仅如果站着连接就是计数,对于那些不需要保持连接的线程,我们就可以杀掉,kill connections,这个行为和设置等待时间wait_timeout是一样的,线程空闲多长时候后,就会断开连接,

但是我们需要注意,在show processlist的结果里面,我主动踢掉sleep是有损的.举个例子如下图

上面图中,如果我们断开sessionA,但是此时还有提交,只能按照数据库的事物进行回滚了,但是如果我们断开sessionB,就没有说明影响,所以我们断开的优先级,就应该断开像sessionB这样的连接在事务外空闲连接的。

问题又来了,如何判断线程是在事物外连接空闲的呢,可以用下面语句

show processlist

发现id=4,5都是在sleep中,我们在查看事务的状态,你可以查 information_schema 库的 innodb_trx 表。

看到 trx_mysql_thread_id=4,就是上面id=4线程在事务中.

最后,我们在断开连接的时候,应该优先断开事物之外空闲的线程,如果还不够,可以断开事物内空闲太久的线程

这个时候,我们还要注意一点,就是我们在服务端进行kill connections+id 的时候,客户端是不知道的,如果客户端在拿着断开的连接操作sql,就会报错ERROR 2013 (HY000): Lost connection to MySQL server during query,这种对于客户端就是不可用的,所以你是DBA的大佬,麻烦下次断开连接的时候,通知一下开发,拜托。

第二种,减少连接的消耗

如果我们的业务就是要在短时间内筹备大量的链接,这个行为就有可能把数据搞挂,此时我们是可以跳过权限的验证阶段的,

要跳过权限验证直接使用下面参数,重启数据库就可以了

–skip-grant-tables

但是这种启动风险很高,特别是在外网可以访问的情况,所以不建议使用这种方式,

而在mysql8.0版本,当我们使用上面参数重启数据的时候,默认打开skip-networking参数,限制只能本地连接.

除了短连接可能带来的性能,往往还有其他情况导致性能,经典的两类

  1. 新出现慢查询,导致性能问题
  2. QPS突增导致性能问题

慢查询性能问题

数据导致性能问题,一般有三类

  1. 索引设计错误
  2. sql没有写好
  3. 数据库索引选择错误

我们按照上面三类情况,分别给出解决方案

索引设计错误

我们在mysql5.6版本之后,可以使用online DDL建立索引,对于数据库已经被搞挂了的情况,我们直接使用 alter table 语句建立索引

但是最好的一种情况就是我们有一主一备,主A,备B按照下面方式进行建立索引

  1. 在备库B上,执行set sql_log_bin=off,不进行binlog,然后在备库上alter tbale 建立索引
  2. 切换主备库
  3. 主B,备A,在备库A,上执行set sql_log_bin=off,然后在备库A执行alter table建立索引

上面的方案虽然很古老,但是在紧急处理上是很有效的

sql语句没有写好

没有写好sql,大多数就是没有使用索引,但是我们可以使用query_rewrite功能,把输入sql语句改成另外一种模式,比如下面语句

select * from t where id + 1 = 10000

可以使用下面语句改写成另外一种语句

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) 
values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

上面call query_rewrite.flush_rewrite_rules() 这个存储过程,是让插入的新规则生效,也就是查询重写,如下验证是否生效

数据库选错索引,也是经常遇到的,当然我们可以上面重写规则,添加 force index,

我们发现索引设计错误,sql语句没有写好,其实是可以避免的,我们在上西安之前,可以使用下面方式使其提前发现

  1. 上线前,打开慢查询日志,设置long_query_time=0,使所有sql都会写入日志
  2. 全面回归测试一下
  3. 观察慢查询日志的扫描行Rows_examined,是否和预期的一样

按照我多年的经验,几乎很少公司做这个事情,做了也很难坚持下来。

QPS突增

这种问题有可能是新的功能引起的,这个时候,我们就要下掉这个业务,我们有下面结果方案可以试试

  1. 如果我们有白名单,可以直接加白名单,使其数据库回复正常
  2. 如果是单独数据库中的用户引起的,可以使用管理员账号,删除现有的用户,断开现有的连接,使数据库恢复正常
  3. 如果和主要业务部署在一起,我们就可以用重写功能,让其改成selelct 1返回

方案也是有风险的

  1. 如果别的功能也有使用这个sql的模板,可能会误伤
  2. 往往业务不是一句sql,就能完成的,改成select 1返回会导致后面的逻辑失效

我们看到上面三大方案,方案三优先级应该放到最后,优先使用方案一二,这些方案使用的前提是你们的数据库有较好的规范,如白名单,业务账号分离等等。

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-11-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql索引解密(上)

    索引是数据库概念最重要的概念之一,也是我们经常要使用的优化手段,索引的出现其实就是为了提高数据查询的效率,就像书的目录一样

    小土豆Yuki
  • Mysql基础篇--面试锁机制

    数据库锁就是一种保证数据一致性而使各种共享资源在被并发访问,并发访问人有序所设计的一种规则。

    小土豆Yuki
  • Mysql事务隔离级别

    在所有事物中可以看到事物没有提交的结果,实际应用中是很少的,他的性能也不比其他隔离级别好很多,读到未提交的结果导致脏读

    小土豆Yuki
  • mysql和eclipse连接jdbc驱动配置

    下载jdbc地址http://dev.mysql.com/downloads/connector/j/下载文件为mysql-connector-java-gpl...

    王小雷
  • 如何应对区块链黑客?维基链CTO来支招

    近期,Fomo3D 第二轮最终大奖开出。安比(SECBIT)实验室分析发现,此轮游戏获奖技巧与第一轮如出一辙,均为黑客发起“阻塞交易”攻击,让自己成为最终得奖者...

    维基链WICC
  • Django REST framework+Vue 打造生鲜超市(十) 十一、pycharm远程代码调试

    十一、pycharm远程代码调试  第三方登录和支付,都需要有服务器才行(回调url),我们可以用pycharm去远程调试服务器代码 服务器环境搭建 以全新阿里...

    zhang_derek
  • 「大数据系列」Ignite:基于内存分布式数据库和缓存和处理平台

    首席架构师智库
  • 这些老照片如何用算法修复?

    原标题 | AI-Based Photo RestorationAI-Based Photo RestorationAI-Based Photo Restora...

    AI研习社
  • 十一、pycharm远程代码调试

     第三方登录和支付,都需要有服务器才行(回调url),我们可以用pycharm去远程调试服务器代码

    Freshman
  • 这些老照片如何用算法修复?

    嗨!各位,我是一位来自Mail.ru Group计算机视觉团队的研发工程师,在这篇文章当中,我将讲述我们如何为老军人的照片创造一个基于AI技术的照片修复项目(h...

    AI科技评论

扫码关注云+社区

领取腾讯云代金券