怎么避免MYSQL误删除避免混淆开发环境的DB和生产环境的DB用事务保护使用安全更新模式对DROP和TRUNCATE慎之又慎最终的招数最终的话

今天小伙伴手一抖,就把开发环境里一张表给删了。因为是开发环境,又有关联数据,所以最终影响不大,还是恢复了。即便如此,小伙伴手心里已经全是汗了~~

由此想到,生产环境下,总会有DBA和生产库打交道,其中也免不了更新和删除。借此机会总结一下一些常规招数。

避免混淆开发环境的DB和生产环境的DB

这在小公司小团队尤其常见。一个人即负责开发,又管DB。桌面上开了一坨终端,有的是开发的DB,有的是生产的DB。一不留神,就写串了,或者粘贴串了。更郁闷的是,有时候粘贴会顺便粘贴SQL最后的回车,这样一贴就执行了。稍有不慎就有大祸。

这里最重要的就是用很显眼的方式让开发环境的客户端和生产环境的客户端看起来不一样。

有这么几种做法:

启动mysql时设定一个特定的prompt。使用--prompt=<string>来给生产环境设置一个特别的提示符。

 $ mysql -uroot -p --prompt='PRODUCTION> '
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5225
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

PRODUCTION> SELECT 1 + 2;
+-------+
| 1 + 2 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

PRODUCTION> 

这样通过提示就可以看得出来自己在哪。

用初始化命令--init-command来初始设置一个MYSQL的变量。之后每次执行之前都要先确定一下这个变量的值。比如:

 $ mysql -uroot -p --init-command='SET @whereami="production"'
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5227
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @whereami;
+------------+
| @whereami  |
+------------+
| production |
+------------+
1 row in set (0.00 sec)

mysql> 

当然,这个招数不如上一种直观。

此外,还有最重要的一个招数,就是--delimiter=<str>改生产环境的Delimiter。这样就彻底隔绝了将一个普通的SQL贴到终端里直接执行的可能性。

$ mysql -uroot -p --delimiter=_D_
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5228
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> delete mysql.user;
    -> --mysql不认为分号是分隔符

每次执行生产的SQL之前,对SQL进行review后,由DBA将默认的分号替换为专用的分隔符,再贴入执行。这样可以确保万无一失。

用事务保护

在更新数据和删除数据时,要特别留意比如忘记写where或者写错了where的情况。所以始终要预先确定要更新/删除的行有多少条。但DBA不可能总是清楚即将会变更多少行数据,所以需要由SQL执行的提出者先从生产库上执行select拿到大致要修改的数据量,然后与DBA确认。

DBA执行时,应该总是将update/delete语句用事务保护起来:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from user;
Query OK, 47823 rows affected (0.12 sec)

mysql> -- 核对数据,如果发现搞错了,赶紧回滚
mysql> rollback;

通过核对数据和回滚,可以增加一次自我救赎的机会。即便是在开发环境,做更新和删除时,最好也要用这一招,不要怕麻烦。如果管不住自己的手,可以考虑——

使用安全更新模式

mysql提供一个参数--safe-updates启用安全更新模式。启动这个模式后,update和delete就必须用主键/唯一索引才能操作。这时,使用泛泛的where条件只能得到一个错误。

$ mysql -uroot -p --safe-updates
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5230
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delete from mysql.user;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

mysql> 

当然,如果临时你的确想去掉这个模式的保护,不需要重新进入客户端,只需要执行指令

SET SQL_SAFE_UPDATES = 0;

就可以临时禁用掉这个保护。执行

SET SQL_SAFE_UPDATES = 1;

可以再次开启这个保护模式。

MySQL自己图形界面工具Mysql Workbench默认情况下的安全更新模式是打开的。

对DROP和TRUNCATE慎之又慎

不管在什么环境,要删除表时,永远不要直接删。而是要保证

  1. 总是把要删的表的表名改了
  2. 运行一段时间后,程序没有问题,再真的删
ALTER TABLE tbl_name RENAME TO tal_name_tobedel

删除数据库时,因为MySQL不支持改数据库的名字,所以要修改所有这个数据库里的表名。但是怎么拿到一个数据库里的所有表名呢?用information_schema

select concat('alter table ', table_name, ' rename to ', table_name, '_tobedel;') as `sql` from information_schema.`TABLES` where TABLE_SCHEMA='mysite';
+------------------------------------------------------------------------------------+
| sql                                                                                |
+------------------------------------------------------------------------------------+
| alter table user rename to user_tobedel;                                           |
| alter table user_role rename to user_role_tobedel;                                 |
| alter table role rename to role_tobedel;                                           |
+------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

这样一次性产生所有的改名指令后,直接再执行一遍就大功告成。

最终的招数

如果真的删除了怎么办?

生产库必须得有备份。果断停服务,用备份恢复数据,给用户发抱歉通知。越快越好。

如果没有——同时如果你是责任人的话,可以抽空准备简历了。

最终的话

严于律己,可得永生。


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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大数据

Python 101:如何从RottenTomatoes爬取数据

今天,我们将研究如何从热门电影网站Rotten Tomatoes爬取数据。你需要在这里注册一个API key。当你拿到key时,记下你的使用限制(如每分钟限制的...

4746
来自专栏极客慕白的成长之路

数据库系统概述必背知识点整理

1362
来自专栏数据和云

时过境迁:Oracle跨平台迁移之XTTS方案与实践

作者简介 ? 谢金融 云和恩墨东区交付部 Oracle 工程师,多年来从事 Oracle 第三方服务,曾服务过金融、制造业、物流、政府等许多行业的客户,精通数据...

1K10
来自专栏数据和云

【合理授权,安全第一】聊一聊Oracle数据库的用户权限

编辑手记:年底大家最关注数据安全,之前我们说过,数据库的风险分为外部风险和内部风险。外部风险无法预估但概率较小,平时发生最多的还是内部操作的风险,因此合理控制权...

2695
来自专栏智能大石头

每天4亿行SQLite订单大数据测试(源码)

SQLite单表4亿订单,大数据测试 SQLite作为嵌入式数据库的翘楚,广受欢迎! 新生命团队自2010年以来,投入大量精力对SQLite进行学习研究,成功应...

8416
来自专栏杨建荣的学习笔记

记一次数据库的分析和优化建议(r6笔记第24天)

数据库的巡检是DBA工作中的一部分,有时候我们还是希望能够在巡检的基础上发现一些潜在的问题,把尽可能多的问题解决在初始阶段。 今天来给大家举一个数据库巡检和性能...

3658
来自专栏MySQL实战分享

【MySQL经典案例分析】 Waiting for table metadata lock

2018年某个周末,接到连续数据库的告警,看到too many connection的报错信息,基本上可以把问题定位在...

8376
来自专栏智能大石头

每天4亿行SQLite订单大数据测试(源码)

SQLite作为嵌入式数据库的翘楚,广受欢迎! 新生命团队自2010年以来,投入大量精力对SQLite进行学习研究,成功应用于各系统非致命数据场合。

2130
来自专栏idba

死锁案例之十

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋...

1062
来自专栏Guangdong Qi

ios 未能找到使用指定主机名的服务器原因之一

6152

扫码关注云+社区

领取腾讯云代金券