防止人为误操作MySQL数据库技巧一例_MySQL

防止人为误操作MySQL数据库技巧一例

（本题来自老男孩培训内部学生问题，属于数据库安全技巧）

在若干年前，老男孩亲自遇到一个“命案”，老大登录数据库update一个记录，结果忘了加where，于是悲剧发生了，这使得我对MySQL的增量恢复熟练度远超过其他的知识点，同时也很在意对内的数据库安全，并且每次讲课讲到此处，都会给学生讲这个领导的故事。

1、mysql帮助说明

<ol class="dp-sql">
<li class="alt"><span><span>[oldboy_c64 ~]# mysql </span><span class="comment">--help|grep dummy      </span><span> </span></span></li>
<li><span> -U, <span class="comment">--i-am-a-dummy Synonym for option --safe-updates, -U.</span><span> </span></span></li>
<li class="alt"><span>i-am-a-dummy      <span class="keyword">FALSE</span><span> </span></span></li>
</ol>

在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序就会拒绝执行

2、指定-U登录测试

<ol class="dp-sql">
<li class="alt"><span><span>[oldboy_c64 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -U </span></span></li>
<li><span>Welcome <span class="keyword">to</span><span> the MySQL monitor. Commands </span><span class="keyword">end</span><span> </span><span class="keyword">with</span><span> ; </span><span class="op">or</span><span> \g. </span></span></li>
<li class="alt"><span>Your MySQL <span class="keyword">connection</span><span> id </span><span class="keyword">is</span><span> 14 </span></span></li>
<li><span>Server version: 5.5.32-log MySQL Community Server (GPL) </span></li>
<li class="alt"><span>Copyright (c) 2000, 2013, Oracle <span class="op">and</span><span>/</span><span class="op">or</span><span> its affiliates. </span><span class="op">All</span><span> rights reserved. </span></span></li>
<li><span>Oracle <span class="keyword">is</span><span> a registered trademark </span><span class="keyword">of</span><span> Oracle Corporation </span><span class="op">and</span><span>/</span><span class="op">or</span><span> its </span></span></li>
<li class="alt"><span>affiliates. Other names may be trademarks <span class="keyword">of</span><span> their respective </span></span></li>
<li><span>owners. </span></li>
<li class="alt"><span>Type <span class="string">'help;'</span><span> </span><span class="op">or</span><span> </span><span class="string">'\h'</span><span> </span><span class="keyword">for</span><span> help. Type </span><span class="string">'\c'</span><span> </span><span class="keyword">to</span><span> clear the </span><span class="keyword">current</span><span> input statement. </span></span></li>
<li><span>mysql> <span class="keyword">delete</span><span> </span><span class="keyword">from</span><span> oldboy.student; </span></span></li>
<li class="alt"><span>ERROR 1175 (HY000): You are using safe <span class="keyword">update</span><span> mode </span><span class="op">and</span><span> you tried </span><span class="keyword">to</span><span> </span><span class="keyword">update</span><span> a </span><span class="keyword">table</span><span> without a </span><span class="keyword">WHERE</span><span> that uses a </span><span class="keyword">KEY</span><span> </span><span class="keyword">column</span><span> </span></span></li>
<li><span>mysql> quit </span></li>
<li class="alt"><span>Bye </span></li>
</ol>

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

3、做成别名防止老大和DBA误操作

<ol class="dp-sql">
<li class="alt"><span><span>[oldboy_c64 ~]# alias mysql=</span><span class="string">'mysql -U'</span><span> </span></span></li>
<li><span>[oldboy_c64 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock </span></li>
<li class="alt"><span>Welcome <span class="keyword">to</span><span> the MySQL monitor. Commands </span><span class="keyword">end</span><span> </span><span class="keyword">with</span><span> ; </span><span class="op">or</span><span> \g. </span></span></li>
<li><span>Your MySQL <span class="keyword">connection</span><span> id </span><span class="keyword">is</span><span> 15 </span></span></li>
<li class="alt"><span>Server version: 5.5.32-log MySQL Community Server (GPL) </span></li>
<li><span>Type <span class="string">'help;'</span><span> </span><span class="op">or</span><span> </span><span class="string">'\h'</span><span> </span><span class="keyword">for</span><span> help. Type </span><span class="string">'\c'</span><span> </span><span class="keyword">to</span><span> clear the </span><span class="keyword">current</span><span> input statement. </span></span></li>
<li class="alt"><span>mysql> <span class="keyword">delete</span><span> </span><span class="keyword">from</span><span> oldboy.student; </span></span></li>
<li><span>ERROR 1175 (HY000): You are using safe <span class="keyword">update</span><span> mode </span><span class="op">and</span><span> you tried </span><span class="keyword">to</span><span> </span><span class="keyword">update</span><span> a </span><span class="keyword">table</span><span> without a </span><span class="keyword">WHERE</span><span> that uses a </span><span class="keyword">KEY</span><span> </span><span class="keyword">column</span><span> </span></span></li>
<li class="alt"><span>mysql> <span class="keyword">delete</span><span> </span><span class="keyword">from</span><span> oldboy.student </span><span class="keyword">where</span><span> Sno=5; </span></span></li>
<li><span>Query OK, 1 row affected (0.02 sec) </span></li>
<li class="alt"><span>mysql> quit </span></li>
<li><span>Bye </span></li>
<li class="alt"><span>[oldboy_c64 ~]# echo <span class="string">"alias mysql='mysql -U'"</span><span> >>/etc/profile </span></span></li>
<li><span>[oldboy_c64 ~]# . /etc/profile </span></li>
<li class="alt"><span>[oldboy_c64 ~]# tail -1 /etc/profile </span></li>
<li><span>alias mysql=<span class="string">'mysql -U'</span><span> </span></span></li>
</ol>

结论：

在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行