同事删库跑路后,我连表名都不能修改了?

从语句中可以拿到delete命令执行时每一行数据的值,这样就可以进行数据的恢复了。如果需要恢复的数据量非常大的话,建议使用脚本批量将delete语句转换为insert语句,减轻恢复数据的工作量。

同事删库跑路后,我连表名都不能修改了?

事情是这样的,前几天隔壁部门的哥们在生产环境的数据库上,执行了一下drop命令,好嘛,活生生的删库跑路的例子居然真的在我身边发生了,好在运维同学给力,后来恢复了数据。事后听说这哥们虽然没被开除,但也吃了个公司的警告。

再然后,运维那边回收了所有环境下数据库的drop命令的权限,甚至包括了开发环境,本来觉得对我们也没啥影响,一般我们也没有啥需要删表的需求。但是隔了没几天,我在重命名一个表的时候,突然弹出了这样一个报错:

同事删库跑路后,我连表名都不能修改了?

仔细看了一眼报错:

  • 1142-DROPcommanddeniedtouser'hydra'@'localhost'fortable't_orders'
  • 什么情况,重命名表和drop命令还有什么关系?本着怀疑的态度,就想探究一下没有drop权限后,对我们的日常数据库操作都有什么影响,于是就有了后面一系列在本地进行的测试。

    首先需要一个没有drop权限的mysql用户,我们先在本地环境使用root用户登录mysql,取消用户hydra的drop权限。和grant授权命令相对应的,可以使用revoke命令取消对用户的授权:

  • revokedropon*.*fromhydra@'localhost';
  • 好了,准备工作做完了,It's show time~

    修改表名

    前面直接使用navicat来修改表名失败,那我们再用sql命令来尝试一下:

    同事删库跑路后,我连表名都不能修改了?

    上面测试了两种重命名表的命令,无论是ALTER还是RENAME都不能正常使用,看来drop的权限确实会对修改表名造成影响。至于重命名失败的原因,看一下官方文档的说明:

    RENAME TABLE renames one or more tables. You must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.

    简单来说就是在重命名表时,必须有原始表的ALTER和DROP权限,以及新表的CREATE和INSERT权限。

    truncate

    当我需要清空一张表、顺带把AUTO_INCREMENT的主键置为初始值时,突然发现truncate命令也无法执行了:

    同事删库跑路后,我连表名都不能修改了?

    有了上面的经验,还是看一下官方文档的说明:

    Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways:

    Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

    文档给出的解释是尽管truncate和delete的功能很像,但是truncate被归类为DDL语言,而delete则是DML语言。相对于delete一行行删除数据,truncate会删除表后重新新建表,这一操作相对delete会快很多,尤其是对大表而言。

    从分类也可以看出两者之间的不同,DML(data manipulation language)作为数据操作语言,主要是针对数据进行一些操作,例如常用的增删改查。而DDL(data definition language)则是数据定义语言,主要应用于定义或改变表的结构等操作,并且这一操作过程是隐性提交的,不能回滚。

    在truncate无法使用的情况下,来执行一下delete试试:

    同事删库跑路后,我连表名都不能修改了?

    虽然说不带where条件的delete删除语句很不推荐使用,但是在功能上还是可以执行成功的。那么再看看另一个问题,表中的自增id重置了吗?

    我们知道,如果执行了truncate的话,那么自增列id的值会被重置为1。下面看看delete执行后的情况,插入一条数据并查询:

    同事删库跑路后,我连表名都不能修改了?

    通过上面的结果,可以看到使用delete清表后,自增列的值还是在原先的基础上进行自增。如果需要重置这个值的话,需要我们手动在表上执行alter命令修改:

  • altertablet_ordersauto_increment=1;
  • drop作用范围

    那么,是否存在即使在没有权限的情况下,也可以执行成功的drop指令?我们对不同对象分别进行测试,首先尝试对数据库、表、视图的drop操作:

  • dropDATABASEmall;
  • >1044-Accessdeniedforuser'hydra'@'localhost'todatabase'mall'
  • >时间:0.005s
  • dropTABLEt_orders;
  • >1142-DROPcommanddeniedtouser'hydra'@'localhost'fortable't_orders'
  • >时间:0s
  • dropVIEWorder_view;
  • >1142-DROPcommanddeniedtouser'hydra'@'localhost'fortable'order_view'
  • >时间:0.001s
  • 上面这些命令理所当然的没有执行成功,但是在尝试到使用drop删除存储过程时,意料之外的结果出现了。在没有drop权限的情况下,对存储过程的drop操作,居然可以执行成功:

    同事删库跑路后,我连表名都不能修改了?

    翻到官方文档中授权这一章节,看一下这张图就明白了:

    同事删库跑路后,我连表名都不能修改了?

    上面的表进行了解释,drop命令的作用范围仅仅是数据库、表以及视图,而存储过程的权限被单独放在alter routine中了,因此即使没有drop权限,我们仍可以用drop命令来删除存储过程。

    delete后如何恢复数据

    通过前面的实验可以看到,虽然在回收drop权限后不能使用truncate清空数据表了,但我们仍然可以使用delete语句达到相同的效果,那么为什么delete就不害怕删库的风险呢?

    前面我们提到过,delete语句属于DDL语言,其实在实际的删除过程中是一行行的进行删除的,并且会将每行数据的删除日志记录在日志中,下面我们就看看如何利用binlog来恢复删除的数据。

    首先要求数据库开启binlog,使用下面的语句来查询是否开启:

  • showvariableslike'%log_bin%';
  • 在值为ON的情况下,表示开启了binglog:

    同事删库跑路后,我连表名都不能修改了?

    确保开启了binlog后,我们使用delete来删除表中的全部数据:

  • deletefromt_orders;
  • 在恢复删除的数据前,需要先找到存放数据文件的目录:

    同事删库跑路后,我连表名都不能修改了?

    在该目录下,存在若干名称为mysql-bin.*****的文件,我们需要根据删除操作发生的时间找到临近的binglog文件:

    同事删库跑路后,我连表名都不能修改了?

    找到目标binlog文件后,这里先将它拷贝到D:\\tmp目录下,然后到mysql安装目录的bin目录下,执行下面的指令:

  • mysqlbinlog–base64-output=decode-rows-v
  • –database=mall
  • –start-datetime="2021-09-1720:50:00"
  • –stop-datetime="2021-09-1721:30:00"
  • D:\\tmp\\mysql-bin.000001>mysqllog.sql
  • 对参数进行一下说明:

    • base64-output=decode-rows:基于行事件解析成sql语句,并将数据转换正常的字符
    • database:数据库名
    • start-datetime:从binlog中第一个等于或晚于该时间戳的事件开始读取,也就是恢复数据的起始时间
    • stop-datetime:与上面对应的,是恢复数据的结束时间
    • D:\\tmp\\mysql-bin.000001:恢复数据的日志文件
    • mysqllog.sql:恢复数据的输出文件

    执行完成后,在bin目录下会生成一个mysqllog.sql的文件,打开文件看一下,可以找到删除时执行的delete语句:

    同事删库跑路后,我连表名都不能修改了?

    从语句中可以拿到delete命令执行时每一行数据的值,这样就可以进行数据的恢复了。如果需要恢复的数据量非常大的话,建议使用脚本批量将delete语句转换为insert语句,减轻恢复数据的工作量。

    好了,如果你坚持看到这里,答应我,以后删库前,先看一下有没有开启binlog好吗?

    官方文档:https://dev.mysql.com/doc/refman/5.7/en

    原文链接:https://mp.weixin.qq.com/s/XjsX86Vn8ELZchAs9Wk2xQ

    版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/5956/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

    (0)
    上一篇 2022-07-27 9:52:30
    下一篇 2022-07-27 9:52:48

    软件定制开发公司

    相关阅读

    发表回复

    登录后才能评论
    通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!