MySQL数据库防止人为误操作的实例讲解

有不少开发人员在操作mysql数据库的时候都遇到过误操作的情况，例如更新数据库的时候update语句忘记加上where条件，就会造成极为悲剧的结果。本文就针对防止mysql数据库误操作的方法做出如下详解：

1、mysql帮助说明

# mysql --help|grep dummy  
 -u, --i-am-a-dummy synonym for option --safe-updates, -u. 
i-am-a-dummy  false

在mysql命令加上选项-u后，当发出没有where或limit关键字的update或delete时，mysql程序就会拒绝执行

2、指定-u登录测试

# mysql -uroot -poldboy123 -s /data/3306/mysql.sock -u 
welcome to the mysql monitor. commands end with ; or \g. 
your mysql connection id is 14 
server version: 5.5.32-log mysql community server (gpl) 
copyright (c) 2000, 2013, oracle and/or its affiliates. all rights reserved. 
oracle is a registered trademark of oracle corporation and/or its 
affiliates. other names may be trademarks of their respective 
owners. 
type 'help;' or '\h' for help. type '\c' to clear the current input statement. 
mysql> delete from oldboy.student; 
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> quit 
bye

提示：此处不加条件无法删除，目的达到。

3、做成别名防止dba误操作

# alias mysql='mysql -u' 
# mysql -uroot -poldboy123 -s /data/3306/mysql.sock 
welcome to the mysql monitor. commands end with ; or \g. 
your mysql connection id is 15 
server version: 5.5.32-log mysql community server (gpl) 
type 'help;' or '\h' for help. type '\c' to clear the current input statement. 
mysql> delete from oldboy.student; 
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> delete from oldboy.student where sno=5; 
query ok, 1 row affected (0.02 sec) 
mysql> quit 
bye 
# echo "alias mysql='mysql -u'" >>/etc/profile 
# . /etc/profile 
# tail -1 /etc/profile 
alias mysql='mysql -u'

结论：在mysql命令加上选项-u后，当发出没有where或limit关键字的update或delete时，mysql程序拒绝执行