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

This article introduces the relevant knowledge of "the safe use of MySQL". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Basic knowledge of MySQL

MySQL is a popular relational database management system (Relational Database Management System,RDMS). In combination with PHP, MySQL is one of the best relational database management systems. Improper use of MySQL can often cause fatal security problems.

1. Safe operation

To prevent attackers from gaining higher privileges through the MySQL vulnerability, do not use the system root user to run the MySQL server. Mysqld refuses to run using root by default, and if the mysqld service needs to be run by a specified user, you should run mysqld with an ordinary non-privileged user and establish a separate MySQL account in Linux for the database, which is only used to manage and run MySQL.

Specify the execution account in the MySQL configuration / etc/my.cnf file.

Vim / etc/my.cnf [mysqld] user=mysql

This configuration enables the server to be started with the specified user, whether manually or via mysqld_safe or mysql.server, ensuring that the identity of MySQL is used. You can also configure the startup parameters, plus the user parameter.

/ usr/local/mysql/bin/mysqld_safe-user=mysql &

The default MySQL is installed in / usr/local/mysql, and the corresponding default database file is in the / usr/local/mysql/var directory. You must ensure that this directory does not allow unauthorized users to access and copy the database, so restrict access to this directory. When mysqld runs, it runs only with Linux users who have read or write permissions to the database directory.

The MySQL home directory allows access only to root users.

Chown-R root / usr/local/mysql/

The database directory allows access only to MySQL users.

Chown-R mysql.mysql / usr/local/mysql/var

2. Password security

The root user password for the default installation of MySQL is an empty password, which must be changed to a strong password, that is, an irregular password consisting of letters, numbers and symbols of at least 8 digits for security reasons. Use the mysqladmin command that comes with MySQL to change the root password.

Mysqladmin-u root password "new-password" / / use mysqladmin to change the password

At the same time, you can use the following command to log in to the database to change the password.

Mysql > use mysql mysql > update user set password = password ('new-password') where user='root'; mysql > flush privileges; / / forcibly refresh the memory authorization table, otherwise the password buffered in memory is still used

3. Account security

The default administrator name of the system MySQL is root, but in general, the database administrator does not modify it, which, to a certain extent, facilitates the malicious attack of system users' password brute force cracking. It should be modified to a complex user name to strengthen the security of the account, and do not use admin or administrator, because they are also in the user dictionary that is easy to guess.

Mysql > update user set user= "new-root-name" where user= "root"; mysql > flush privileges; / / forcibly refresh the memory authorization table, otherwise the password buffered in memory is still used

You need to assign permissions to users correctly, do not assign all permissions to ordinary users, and selectively assign read and write permissions, such as only query permissions to users.

Mysql > grant SELECT on db.table to username@'localhost'

Do not authorize with grant option to ordinary users, to prevent ordinary users from delegating permissions to others, resulting in administrative confusion.

Table 1 shows the commonly used permissions and instructions.

Table 1 Common permissions and descriptions

4. Database security

After the default MySQL installation is initialized, empty users and test libraries are automatically generated for installation testing, which poses a threat to the security of the database. It is necessary to delete all of them, and only a single root can be left in the final state. Of course, you can add users and databases as needed later.

Mysql > show databases; mysql > drop database test; / delete database test use mysql; delete from db; / / Delete the table information stored in the database because there is no database information mysql > delete from user where not (user='root'); / / delete the initial non-root user mysql > delete from user where user='root' and password=; / / delete the root Query OK with empty password, 2 rows affected (0.00 sec) mysql > flush privileges; / / force refresh the memory authorization table

5. Restrict unauthorized IP access

If you are running MySQL on a stand-alone machine, it is recommended to enable skip-networking. You can completely disable the TCP/IP connection mode of MySQL.

# my.ini skip-networking

If you have fixed IP access to MySQL, you can add bind-address=IP to the configuration file, as long as skip-networking is turned off.

Bind-address=10.10.1.1

6. File reading security

In MySQL, the load data local infile command is used to provide the ability to read local files. In version 5.0, this option is turned on by default, which uses MySQL to read local files into the database, and then an attacker can illegally obtain sensitive information. If you do not need to read the local file, you should close it.

Load data local infile is useful in some attack methods spread on the network, and it is also a means used by many newly discovered SQL Injection attacks. Attackers can also load "/ etc/passwd" into a database table using load data local infile and display it in SELECT, which is fatal to server security.

You can add the local-infile=0 parameter to the my.cnf.

Vim / etc/my.cnf [mysqld] set-variable=local-infile = 0

Or add the local-infile=0 parameter when MySQL starts.

/ usr/local/mysql/bin/mysqld_safe-user=mysql-local-infile=0 &

7. Common security options

Here are some security options provided by MySQL itself, which can be flexibly configured according to your own needs when using MySQL services.

-- allow-suspicious-udfs

This option controls whether you can load user-defined functions with only xxx symbols for the main function, such as xxx_init (), xxx_deinit (), xxx_reset (), xxx_clear (), xxx_add (), and so on. By default, this option is turned off and only UDF with at least auxiliaries can be loaded. This prevents shared object files that never contain legal UDF from loading functions.

-- local-infile [= {0 | 1}]

If you start the server with-- local-infile=0, the client cannot use the LOCAL IN LOAD DATA statement.

-- old-passwords

Forces the server to generate a short (pre-4.1) hash password for the new password. This is useful for ensuring compatibility when the server must support older versions of client programs.

(OBSOLETE)-- safe-show-database

In previous versions of MySQL, this option caused the SHOW DATABASES statement to display only the database name for which the user had partial permissions. In MySQL 5.1, this option is no longer used as the current default behavior, and there is a SHOW DATABASES permission that can be used to control each account's access to the database name.

-- safe-user-create

If enabled, the user cannot create a new user using the Grant statement unless the user has INSERT permission for the mysql.user table. If you want the user to have authorized permissions to create a new user, the user should be granted the following permissions.

Mysql > GRANT INSERT (user) ON mysql.user TO'user_name'@'host_name'

This ensures that the user cannot change the permission column directly, which must be granted to other users using the Grant statement.

-- secure-auth

Authentication of accounts with old (pre-4.1) passwords is not allowed.

-- skip-grant-tables

This option results in the server not using the permission system at all, giving everyone full access to all databases! You can tell a running server to start using the authorization table again by executing the mysqladmin flush-privileges or mysqladmin eload command, or by executing the FLUSH PRIVILEGES statement. )

-- skip-name-resolve

The hostname is not resolved. The column values of all Host in the authorization table must be IP number or localhost.

-- skip-networking

TCP/IP connections are not allowed on the network. All connections to mysqld must be made via Unix sockets.

-- skip-show-database

With this option, only users with SHOW DATABASES privileges are allowed to execute the SHOW DATABASES statement, which displays all database names. Do not use this option to allow all users to perform SHOW DATABASES, but only display the database name for which the user has SHOW DATABASES privileges or partial database permissions. Note that global permissions refer to the permissions of the database.

8. Data security

In the production environment, the database may encounter a variety of accidents, resulting in data loss, such as hardware failure, software failure, natural disasters, malicious attacker attacks, misoperation and so on. In order to recover the data in time after the data is lost, the data needs to be backed up regularly.

The strategy for backing up data needs to be customized according to different business scenarios. There are roughly several reference values that can be used to customize the data backup strategy in a specific environment.

(1) be able to tolerate how much data is lost.

(2) how long will it take to recover the data.

(3) what data needs to be recovered.

Choose whether the backup method is full backup, incremental backup or differential backup according to the needs of the business scenario.

This is the end of the content of "safe use of MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.