[眼会手也要会]避免误操作导致数据库数据丢失

网上分享的一些技巧方法:

生产环境中，业务代码尽量不明文保存数据库连接账号密码信息；
重要的DML、DDL通过平台型工具自动实施，减少人工操作；
部署延迟复制从库，万一误删除时用于数据回档。且从库设置为read-only；
确认备份制度及时有效；
启用SQL审计功能，养成良好SQL习惯；
启用 sql_safe_updates 选项，不允许没 WHERE 条件的更新/删除；
将系统层的 rm 改为 mv；
线上不进行物理删除，改为逻辑删除（将row data标记为不可用）；
启用堡垒机，屏蔽高危SQL；
降低数据库中普通账号的权限级别；
务必开启binlog;
使用触发器对操作限制;

1.说下sql_safe_updates

在mysql中，如果在update和delete没有加上where条件，数据将会全部修改。不只是初识mysql的开发者会遇到这个问题，工作有一定经验的工程师难免也会忘记写入where条件。为了避免失误造成的数据全部修改和删除，可开启mysql的安全模式。

(1)查询是否开启sql_safe_updates

show variables like 'sql_safe_updates';
#set sql_safe_updates=1; //安全模式打开状态
#set sql_safe_updates=0; //安全模式关闭状态

(2)使用方式:

安全更新操作:

#无where条件update,操作失败;
mysql> update users set status=1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

#无where但是有limit,则正常执行;
mysql> update users set status=1 limit 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

#条件字段不是索引字段并且没有加入limit限制,操作失败;
mysql> update users set status=1 where create_time>'2021-01-01 00:00:00';
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> update users set status=1 where phone='13501270000';

#如果使用非索引条件,但是带limit,执行成功;
mysql> update users set status=1 where create_time>'2021-01-01 00:00:00' limit 1;

安全删除操作限制同以上update相关条件;

2.触发器限制操作

(1)查询所有触发器

#当前数据库的触发器
show triggers;
#实例下所有数据库存在的触发器
SELECT * FROM information_schema.`TRIGGERS`

(2)删除触发器

DROP TRIGGER IF EXISTS '触发器名称'

(3)创建触发器

触发器基本格式:

DELIMITER $$  #自定义sql的结束符,不写则默认为 ;
create trigger `triggerName`  
after/before insert/update/delete on `表名`  
for each row   #固定语句  
begin  
    sql语句;  
end$$
DELIMITER;

①创建一个禁止删除表内数据的触发器:

DELIMITER $$ 
CREATE
    TRIGGER cant_delete BEFORE DELETE
    ON ceshi
    FOR EACH ROW BEGIN
    
    declare msg varchar(255);         #定义一个变量
    set msg="不允许删除ceshi下的数据";   
    
    #抛出错误码HY000异常,使事务回滚或无法正常提交
    SIGNAL SQLSTATE 'HY000' SET  MESSAGE_TEXT = msg; 

    END$$
DELIMITER ;

#执行sql
DELETE FROM `ceshi`;
#结果:
1644 - 不允许删除ceshi下的数据

本文由 GY 创作，采用知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: 2021/12/08 10:08