Shulou(Shulou.com)05/31 Report--

This article shows you how to improve the security of MySQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Guide reading

How to improve the security of MySQL?

There is no doubt that the security of the database is very important. Here are some simple ways to improve security.

1. Set the datadir permission mode correctly

The correct permission mode for datadir is 0750, or even 0700.

That is, at most, only the owner of the mysqld process and its user group can access it, but only the owner can modify the file.

* is directly set to 0700, which is relatively more secure to avoid accidental leakage of data files.

[yejr@imysql.com] # chown-R mysql.mysql / data/mysql57 [yejr@imysql.com] # chmod 0700 / data/mysql57 [yejr@imysql.com] # ls-la / data/ drwxr-x---. 8 mysql mysql 4096 Feb 14 08:08 mysql57

two。 Put the mysql socket file under datadir

Many people are used to putting the mysql socket file in the / tmp directory.

Especially when running multiple instances, there may be multiple such files as mysql3306.sock, mysql3307.sock, mysql3308.sock and so on in the / tmp directory.

Note that the default permission mode for mysql.sock files is 0777, that is, anyone has the opportunity to log in to mysql directly through the socket file in the / tmp directory, especially when the root password is empty or weak, and allows local socket to log in, which is a dangerous security hazard.

Therefore, we strongly recommend that you put the mysql socket file under each instance's own datadir, and refer to the * * section to set the correct permission mode. At the same time, you can even change the mysql.sock file permission mode to 0700.

[yejr@imysql.com] # chmod 0700 / data/mysql57/mysql.sock [yejr@imysql.com] # ls-la / data/mysql57/mysql.sock srwx-. 1 mysql mysql 0 Feb 12 16:00 / data/mysql57/mysql.sock

3. Use login-path

Generally speaking, we will set a password for each mysql account, which is secure, but it is not easy to use and maintain.

Enter your password every time you log in, especially when you call the mysql client tool. If you write the password directly in the options of the client tool, it is very dangerous. You can see the password from the history command, and there will be prompts like the following:

Mysql: [Warning] Using a password on the command line interface can be insecure.

At this time, we can actually take advantage of the login-path function to improve security and convenience.

The login-path feature is new to MySQL 5.6.

First, configure login-path with mysql_config_editor:

# option "- G lp-mysql57-3306" sets the alias of login-path mysql_config_editor set-G lp-mysql57-3306-S / data/mysql57/mysql.sock-uroot-p

Once set up, the .mylogin.cnf file is generated in the user's $HOME directory:

[yejr@imysql.com] # ls-la ~ / .mylogin.cnf-rw-. 1 yejr users 152 Feb 11 22:42 / home/yejr/.mylogin.cnf [yejr@imysql.com] # file ~ / .mylogin.cnf / home/yejr/.mylogin.cnf: data

This is an encrypted binary file, and the password cannot be displayed even if viewed in clear text:

[yejr@imysql.com] # mysql_config_editor print-all mysql_config_editor print-all [lp-mysql57-13306] user = root password = * socket = / data/mysql57/mysql.sock

Next, you can easily log in to mysqld using login-path without the need for an additional password:

[yejr@imysql.com] # mysql-- login-path=lp-mysql57-13306-e "select 1 from dual" +-+ | 1 select 1 | +-+ | 2 | +-+ [yejr@imysql.com] # mysqladmin-- login-path=lp-mysql57-13306 pr +-+ -+ | Id | User | Host | db | Command | Time | State | Info | + -+ | 3 | root | localhost | | Query | 0 | starting | show processlist | +

Under the premise of the first two security rules, even if the .mylogin.cnf file of a high-level user is stolen by other ordinary users, it is impossible to log in to mysql using socket.

Of course, unless you set the tcp/ip mode in login-path before, it will be a tragedy.

The following is the assumption that an ordinary yejr account wants to log in using the .mylogin.cnf file of the root account and reports a failure because the / data/mysql57/mysql.sock file cannot be accessed:

[yejr@imysql] $/ usr/local/mysql57/bin/mysql-- login-path=lp-mysql57-13306 ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ data/mysql57/mysql.sock' the above is how to improve MySQL security. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.