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

怎么避免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 条评论
登录 后参与评论

相关文章

  • 既然Java反射可以访问和修改私有成员变量,那封装成private还有意义么?

    安全是指不让代码被非法看到/访问。但是只要人能拿到代码,总会有办法去查看和改变代码。其他答案提到反射可以用SecurityManager来防止private被访...

    大宽宽
  • 支付状态与分布式一致性

    大宽宽
  • 如何避免问渣问题?

    大宽宽
  • mysql导入sql脚本出现there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE

    https://dev.mysql.com/downloads/mysql/5.7.html#downloads

    韩旭051
  • Satpy基础系列教程(3)-Satpy总览

    Satpy is designed to provide easy access to common operations for processing met...

    zhangqibot
  • Linux发行版该如何选择?

    对于很多刚使用Linux的人来说,选择使用哪个Linux发行版着实是一件头疼的事情,特别是对于有选择困难症的人们就更是如此了。其实对于那些著名的Linux发行版...

    用户6543014
  • EQUS-帮助查看公式(CS SE)

    可视化通常是简化信息和帮助人们理解复杂数据的一种方式。在本文中,我们描述了电子表格公式(EQUS)交互式可视化的设计,开发和评估。这项工作是合理的,理由是这些工...

    刘子蔚
  • 010.使用DBus贴源采集MySQL增量bin-log日志

    下载canal安装包:https://github.com/BriData/DBus/releases,官网给了百度网盘下载链接,文件为v0.6.1/deplo...

    CoderJed
  • 基于技能要求的开放式教育视频推荐系统(CS CS)

    在本文中,我们建议一种新颖的方法来帮助学习者找到相关的开放式教育视频,以掌握劳动力市场上所需的技能。我们建立了一个原型,该原型包括:1)在职位空缺公告中应用文本...

    小童
  • R语言dataframe数据转换

    df_melt<-reshape2::melt(df,id.vars="x",variable.name="year",value.name="value")

    爱学习的小明明

扫码关注云+社区

领取腾讯云代金券