Mysql误删数据解决方案及kill语句原理

mysql误删数据

• 使用delete语句误删数据行
• 使用drop table或者truncate table误删数据表
• 使用drop database语句误删数据库
• 使用rm误删mysql整个实例
  

对于误删行

• 使用flashback工具闪回，把数据恢复回来。原理是修改binlog的内容，拿回原库重放，需要确保binlog_format=row和binlog_row_imsge=Full
  
• 具体恢复时
  
  • 如果是insert，将binlog event类型是write_rows event改为delete_rows event。
  • 如果是delete则相反。
  • 如果是update，binlog里有数据修改前和修改后的值，对调这两行即可。
• 多个事物也是按照以上原则倒叙执行。
• 预防：把sql_safe_updates参数设置为on。这样一来，如果我们忘记在delete或者update语"text-align: center">

  注意：

  为"text-align: center">

  一个系统"text-align: center">

  session B是直接终止掉线程，"text-align: center">

  可以看到：

  sesssion C执"text-align: center">

  id=12这个线程的Commnad列显示的是Killed。也就是说，客户端虽然断开了连接，但实际上服务端上这条语句还在执行过程中。

  在这个例子里，12号线程的等待逻辑是这样的：每10毫秒判断一下是否可以进入InnoDB执
  行，如果不行，就调用nanosleep函数进入sleep状态。

  也就是说，虽然12号线程的状态已经被设置成了KILL_QUERY，但是在这个等待进入InnoDB的循环过程中，并没有去判断线程的状态，因此根本不会进入终止逻辑阶段。

  而当session E执行kill connection 命令时，是这么做的，

  • 把12号线程状态设置为KILL_CONNECTION；
  • 关掉12号线程的网络连接。因为有这个操作，所以你会看到，这时候session C收到了断开连接的提示。

  那为什么执行show processlist的时候，会看到Command列显示为killed呢？其实，这就是因为在执行show processlist的时候，有一个特别的逻辑：

  如果一个线程的状态是KILL_CONNECTION，就把Command列显示成Killed。

  所以其实，即使是客户端退出了，这个线程的状态仍然是在等待中。只有等到满足进入InnoDB的条件后，session C的查询语句继续执行，然后才有可能判断到线程状态已经变成了KILL_QUERY或者KILL_CONNECTION，再进入终止逻辑阶段。

  kill无效的第一类情况，即：线程没有执行到判断线程状态的逻辑。可能也会由于IO压力过大，读写IO的函数一直无法返回，导致不能及时判断线程的状态。

  • 第二类情况，终止逻辑耗时较长
  • 超大事物执行期间被kill，回滚操作耗时很长。
  • 大会滚操作，比如查询过程中生成了很大的临时文件，删除临时文件需要等待IO资源，导致耗时较长。
  • DDL执行到最后阶段，如果被kill，需要删除中间过程的临时文件，也需要IO资源。

  ctrl+C，mysql实际上也是启动了一个连接进程发送了kill query命令。

  关于客户端连接慢的误解

  如果库里面的表很多，连接就会很慢。比如有一个库有上万个表，使用默认参数连接的时候，mysql会提供一个本地库名和表名补全的功能：

  • 执行show databases
  • 切到db1，执行show tables
  • 把这两个命令的结果用于构建一个本地hash表。

  第三步是耗时比较长的操作，也就是我们感知到慢不是连接满，也不是服务端慢，而是客户端慢。如果在这个连接中加上 -A，就可以取消自动补全功能，很快返回。

  自动补全的效果就是，在输入库名或者表名的时候，将输入前缀，可以使用tab自动补全或者显示提示。实际如果自动补全用的不多，可以每次使用都加-A。

  以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。