[MySQL] how to prevent people from operating MySQL database by mistake? I get it this time!!

Time:2021-6-10

Write on the front

Today, a friend called me and asked me how to recover the data in their company’s database after misoperation. His original idea was to log in to the database to update a record, but he forgot to add the where condition, so the tragedy happened. Today, we will not talk about how to recover the misoperated data (later, we will focus on how to recover the Misdeleted data). We will talk about how to avoid such problems from the source. This is the fundamental measure to avoid similar problems.

If the article is helpful to you, please don’t grudge your likes, comments and forwarding. Your support is the biggest driving force for my continuous creation!

After adding the option – u to the MySQL command, when the update or delete without where or limit keyword is issued, the MySQL program will refuse to execute. Then, based on this setting provided by mysql, we can easily realize how to prevent human misoperation of MySQL database to the greatest extent. what? You don’t believe it? If you don’t believe it, let’s start with the help instructions of MySQL. Let’s see how to implement the – U option based on MySQL and how to prevent human error operation of MySQL database to the greatest extent.

MySQL help

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

After adding the option – u to the MySQL command, when the update or delete without where or limit keyword is issued, the MySQL program will refuse to execute.

Specify – U login test

[[email protected]~]# 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.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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: it can’t be deleted without conditions, and the purpose can be achieved.

assign an alias

We can make the command to operate MySQL as an alias to prevent other people and DBAs from operating the database by mistake. It’s also very simple to make the command to operate MySQL as an alias. Here, we have a direct example, as shown below.

[[email protected]~]# alias mysql='mysql -U'
[[email protected]~]# 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.7.24-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

After setting alias MySQL = – MySQL – U ‘in the command line session, only the current session is valid. After closing the command line of the current connection server, if the session is invalid, the alias setting will also be invalid. If you want to close the session terminal connecting to the server, the alias setting is still valid, or multiple sessions can use the alias to operate the database, you can add the command to the / etc / profile system environment variable, as shown below.

[[email protected]~]# echo "alias mysql='mysql -U'" >>/etc/profile
[[email protected]~]# . /etc/profile
[[email protected]~]# tail -1 /etc/profile
alias mysql='mysql -U'

In this way, when we exit the session terminal of the current connection server, the MySQL alias setting is still valid. Each time we connect to the server, we do not need to reset the MySQL command alias in the current session, we can use it directly.

summary

After adding the option – u to the MySQL command, when the update or delete without where or limit keyword is issued, the MySQL program refuses to execute.

Heavyweight benefits

WeChat search official account official, WeChat’s official account, pay attention to this deep programmer, read hard core technology dry cargo every day, reply to PDF, and I have prepared a big factory interview information and my original PDF technology document, and I have prepared many resume templates for you (constantly updated). I hope you can find your favorite job. Learning is a way of sometimes depressed and sometimes laughing. Come on. If you succeed in entering the company you like, don’t slack off. Career growth is the same as new technology learning. If you are lucky, I’ll see you in the world!

In addition, I open source PDF, I will continue to update and maintain, thank you for your long-term support to Glacier!!

Write at the end

If you think glacier‘s writing is good, please search on wechat and follow “Glacier TechnologyThe official account of WeChat is learning high concurrent, distributed, micro services, big data, Internet and cloud native technology with glaciers.Glacier TechnologyThe official account of WeChat has updated a lot of technical topics, and every technical article is full of dry cargo. Many readers have read theGlacier TechnologyWeChat’s official account of the public, who has been interviewing the official, has successfully switched to a big factory. There are also many readers to achieve a technological leap, become the company’s technical backbone! If you want to improve your ability, realize the leap of technical ability, enter a large factory, get a promotion and raise salary, then pay attention toGlacier TechnologyWeChat official account, updating the hard core technology dry cargo every day, so that you can’t lose your knowledge of how to improve the technical capability.

[MySQL] how to prevent people from operating MySQL database by mistake? I get it this time!!