关于mysql:同事删库跑路后我连表名都不能修改了

原创:码农参上(微信公众号ID:CODER_SANJYOU),欢送分享,转载请保留出处。

事件是这样的,前几天隔壁部门的哥们在生产环境的数据库上,执行了一下drop命令,好嘛,活生生的删库跑路的例子竟然真的在我身边产生了,好在运维同学给力,起初复原了数据。预先据说这哥们尽管没被开革,但也吃了个公司的正告。

再而后,运维那边回收了所有环境下数据库的drop命令的权限,甚至包含了开发环境,原本感觉对咱们也没啥影响,个别咱们也没有啥须要删表的需要。然而隔了没几天,我在重命名一个表的时候,忽然弹出了这样一个报错:

认真看了一眼报错:

1142 - DROP command denied to user 'hydra'@'localhost' for table 't_orders'

什么状况,重命名表和drop命令还有什么关系?本着狐疑的态度,就想探索一下没有drop权限后,对咱们的日常数据库操作都有什么影响,于是就有了前面一系列在本地进行的测试。

首先须要一个没有drop权限的mysql用户,咱们先在本地环境应用root用户登录mysql,勾销用户hydra的drop权限。和grant受权命令绝对应的,能够应用revoke命令勾销对用户的受权:

revoke drop on *.* from hydra@'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.

简略来说就是在重命名表时,必须有原始表的ALTERDROP权限,以及新表的CREATEINSERT权限。

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.

文档给出的解释是只管truncatedelete的性能很像,然而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命令批改:

alter table t_orders auto_increment= 1;

drop作用范畴

那么,是否存在即便在没有权限的状况下,也能够执行胜利的drop指令?咱们对不同对象别离进行测试,首先尝试对数据库、表、视图的drop操作:

drop DATABASE mall;
> 1044 - Access denied for user 'hydra'@'localhost' to database 'mall'
> 工夫: 0.005s

drop TABLE t_orders;
> 1142 - DROP command denied to user 'hydra'@'localhost' for table 't_orders'
> 工夫: 0s

drop VIEW order_view;
> 1142 - DROP command denied to user 'hydra'@'localhost' for table 'order_view'
> 工夫: 0.001s

下面这些命令天经地义的没有执行胜利,然而在尝试到应用drop删除存储过程时,意料之外的后果呈现了。在没有drop权限的状况下,对存储过程的drop操作,竟然能够执行胜利:

翻到官网文档中受权这一章节,看一下这张图就明确了:

下面的表进行了解释,drop命令的作用范畴仅仅是数据库、表以及视图,而存储过程的权限被独自放在alter routine中了,因而即便没有drop权限,咱们仍能够用drop命令来删除存储过程。

delete后如何复原数据

通过后面的试验能够看到,尽管在回收drop权限后不能应用truncate清空数据表了,但咱们依然能够应用delete语句达到雷同的成果,那么为什么delete就不胆怯删库的危险呢?

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

首先要求数据库开启binlog,应用上面的语句来查问是否开启:

show variables like '%log_bin%';

在值为ON的状况下,示意开启了binglog

确保开启了binlog后,咱们应用delete来删除表中的全副数据:

delete from t_orders;

在复原删除的数据前,须要先找到存放数据文件的目录:

在该目录下,存在若干名称为mysql-bin.*****的文件,咱们须要依据删除操作产生的工夫找到邻近的binglog文件:

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

mysqlbinlog --base64-output=decode-rows -v --database=mall --start-datetime="2021-09-17 20:50:00" --stop-datetime="2021-09-17 21: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/ref…

作者简介,码农参上(CODER_SANJYOU),一个酷爱分享的公众号,乏味、深刻、间接,与你聊聊技术。集体微信DrHydra9,欢送增加好友,进一步交换。

【腾讯云】轻量 2核2G4M,首年65元

阿里云限时活动-云数据库 RDS MySQL  1核2G配置 1.88/月 速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据