怎么避免MYSQL误删除

2018-02-26 本文已影响215人大宽宽

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

由此想到，生产环境下，总会有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慎之又慎

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

总是把要删的表的表名改了
运行一段时间后，程序没有问题，再真的删

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)

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

最终的招数

如果真的删除了怎么办？

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

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

最终的话

严于律己，可得永生。

本文来自大宽宽的碎碎念。如果觉得本文有戳到你，请关注/点赞哦。

另外欢迎加入大宽宽的面试经验交流群参加更多讨论。

大宽宽的面试经验交流群