同事删库跑路后,我连表名都不能修改了?
点击上方“芋道源码”,选择“设为星标”
管她前浪,还是后浪?
能浪的浪,才是好浪!
每天 10:33 更新文章,每天掉亿点点头发...
源码精品专栏
事情是这样的,前几天隔壁部门的哥们在生产环境的数据库上,执行了一下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
的权限确实会对修改表名造成影响。至于重命名失败的原因,看一下官方文档(链接:https://dev.mysql.com/doc/refman/5.7/en)的说明:
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
权限。
基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
项目地址:https://github.com/YunaiV/ruoyi-vue-pro 视频教程:https://doc.iocoder.cn/video/
truncate
当我需要清空一张表、顺带把AUTO_INCREMENT
的主键置为初始值时,突然发现truncate
命令也无法执行了:
有了上面的经验,还是看一下官方文档(链接:https://dev.mysql.com/doc/refman/5.7/en)的说明:
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
命令修改:
alter table t_orders auto_increment= 1;
基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
项目地址:https://github.com/YunaiV/yudao-cloud 视频教程:https://doc.iocoder.cn/video/
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
操作,居然可以执行成功:
翻到官方文档(链接:https://dev.mysql.com/doc/refman/5.7/en)中授权这一章节,看一下这张图就明白了:
上面的表进行了解释,drop
命令的作用范围仅仅是数据库、表以及视图,而存储过程的权限被单独放在alter routine
中了,因此即使没有drop
权限,我们仍可以用drop
命令来删除存储过程。
delete 后如何恢复数据
通过前面的实验可以看到,虽然在回收drop
权限后不能使用truncate
清空数据表了,但我们仍然可以使用delete
语句达到相同的效果,那么为什么delete
就不害怕删库的风险呢?
前面我们提到过,delete
语句属于 DML 语言,其实在实际的删除过程中是一行行地进行删除的,并且会将每行数据的删除日志记录在日志中,下面我们就看看如何利用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 好吗?
欢迎加入我的知识星球,一起探讨架构,交流源码。加入方式,长按下方二维码噢:
已在知识星球更新源码解析如下:
最近更新《芋道 SpringBoot 2.X 入门》系列,已经 101 余篇,覆盖了 MyBatis、Redis、MongoDB、ES、分库分表、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等内容。
提供近 3W 行代码的 SpringBoot 示例,以及超 4W 行代码的电商微服务项目。
获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。
文章有帮助的话,在看,转发吧。
谢谢支持哟 (*^__^*)
微信扫码关注该文公众号作者