MySQL - How to prevent human errors in operating the MySQL database-Mysql Tutorial-php.cn

MySQL - How to prevent human errors in operating the MySQL database

黄舟

Release： 2017-01-21 13:24:14

Original

1304 people have browsed it

Today, a colleague from the original company called me and asked me how to recover the database data that I had mistakenly manipulated. His original idea was to log in to the database and update a record, but he forgot to add where, and tragedy happened. Today, we are not going to talk about how to recover misused data. We are going to talk about how to avoid such problems from the source. This is the fundamental measure to avoid similar problems. alright. Let’s go directly to the topic:

1. MySQL Help Description

[root@liuyazhuang151 ~]# mysql --help|grep dummy        
 -U, --i-am-a-dummy Synonym for option --safe-updates, -U.  
i-am-a-dummy      FALSE

Copy after login

After adding the option -U to the mysql command, when an UPDATE or DELETE without the WHERE or LIMIT keyword is issued, the mysql program will Will refuse to execute

2. Specify -U login test

[root@liuyazhuang151 ~]# mysql -uroot -proot -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 &#39;help;&#39; or &#39;\h&#39; for help. Type &#39;\c&#39; 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

Copy after login

Tips: It cannot be deleted without adding conditions. The purpose is achieved.

3. Create an alias to prevent others and DBA from misoperation

[root@liuyazhuang151 ~]# alias mysql=&#39;mysql -U&#39;  
[root@liuyazhuang151 ~]# 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 &#39;help;&#39; or &#39;\h&#39; for help. Type &#39;\c&#39; 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  
[root@liuyazhuang151 ~]# echo "alias mysql=&#39;mysql -U&#39;" >>/etc/profile  
[root@liuyazhuang151 ~]# . /etc/profile  
[root@liuyazhuang151 ~]# tail -1 /etc/profile  
alias mysql=&#39;mysql -U&#39;

Copy after login

4. Conclusion:

Add the following to the MySQL command After selecting the -U option, when an UPDATE or DELETE without the WHERE or LIMIT keyword is issued, the mysql program refuses to execute

The above is the content of MySQL - How to prevent human error from operating the MySQL database, and more related content Please pay attention to the PHP Chinese website (m.sbmmt.com)!