An example of MySQL database protection against human error

Many developers have encountered the situation of wrong operation when operating MySQL database, such as the update statement forgetting to add where condition when updating the database, which will cause extremely tragic results. This paper gives the following detailed explanation for the methods to prevent the misoperation of MySQL database:

1. mysql help

# mysql --help|grep dummy  
 -U, --i-am-a-dummy Synonym for option --safe-updates, -U. 
i-am-a-dummy  FALSE

With the option -ES12en added to the mysql command, the mysql program refuses to execute when issuing UPDATE or DELETE without the WHERE or LIMIT keyword

2. Specify the -ES20en login test

# 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

Tip: the purpose can not be deleted without conditions.

3. Make the alias to prevent DBA from misoperation

# 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'

Conclusion: The mysql program refuses to execute when issuing UPDATE or DELETE without WHERE or LIMIT keywords with the option -ES31en added to the mysql command