14 best methods to ensure MySQL Data Security

Some enterprises use the default option when installing MySQL, which leads to data insecurity, and the server also faces the risk of intrusion, and may suffer performance problems in a short time. This article provides the best way to ensure MySQL security. MySQL database has always been known for its high performance, high availability, and ease of use. It has become the world's most popular open source database.

Some enterprises use the default option when installing MySQL, which leads to data insecurity, and the server also faces the risk of intrusion, and may suffer performance problems in a short time. This article provides the best way to ensure MySQL security. MySQL database has always been known for its high performance, high availability, and ease of use. It has become the world's most popular open source database.

Some enterprises use the default option when installing MySQL, which leads to data insecurity, and the server also faces the risk of intrusion, and may suffer performance problems in a short time. This article provides the best way to ensure MySQL security.

MySQL database has always been known for its high performance, high availability, and ease of use. It has become the world's most popular open source database. A large number of individuals, WEB developers, and large companies all use MySQL databases in their websites, key systems, and software packages.

Generally, when deploying a product, many enterprises usually do not pay attention to security. Enterprises are most concerned about enabling them to run as soon as possible, and thus making profits as soon as possible.

However, some enterprises use the default option when installing MySQL, resulting in insecure data and the risk of server intrusion, performance problems may occur in a short time.

　　The following provides the best way to ensure MySQL security:

　1. Avoid accessing the MySQL database from the Internet and ensure that the access permission is granted to a specific host.

It is dangerous to change the database in the production environment directly through a computer outside the local network. Sometimes, the Administrator opens the host's access to the database:

> Grant all on *. * TO 'root' @ '% ';

This actually completely releases the access to the root user. Therefore, limiting important operations to specific hosts is very important:

> Grant all on *. * TO 'root' @ 'localhost ';

> Grant all on *. * TO 'root' @ 'myip. athome'

> FLUSH PRIVILEGES

At this time, you still have full access, but only the specified IP (whether static or not) can be accessed.

　2. Regular Database Backup

Any system may have a disaster. The server and MySQL will also crash and may also suffer intrusion, and the data may be deleted. Only by fully preparing for the worst case can we quickly recover from a disaster afterwards. It is best for enterprises to take the backup process as a daily task of servers. At present, domestic software multi-Backup has done a good job in this regard. Based on Cloud 5 technology, data exchange and automatic backup on multiple Cloud platforms can help enterprises or individuals protect and manage data. If any problem occurs, immediately restore the original data with one click, and 'Restore the original data with full blood'

　3. Disable or restrict remote access

As mentioned above, if remote access is used, make sure that only the defined host can access the server. This is generally implemented through TCP wrappers, iptables, or any other firewall software or hardware.

To restrict access to the network socket, the administrator should add the following parameters in the [mysqld] section of my. cnf or my. ini:

Skip-networking

These Files are located in the windows C: \ Program Files \ MySQL Server 5.1 folder, or in Linux, my. cnf is located in/etc/or/etc/mysql /. This command disables network connection initialization during MySQL startup. Note that you can still establish a local connection with the MySQL server.

Another feasible solution is to force MySQL to only listen to the local machine by adding the following line in the [mysqld] section of my. cnf:

Bind-address = 127.0.0.1

If enterprise users connect to the server from their own machines or install the web server on another machine, you may not be willing to disable network access. In this case, consider the following restricted access permission:

Mysql> grant select, insert on mydb. * TO 'someuser' @ 'somehost ';

Here, you need to replace someuser with the user name and somehost with the corresponding host.

　4. Set the password of the root user and change the logon name.

In linux, the root user has full access to all databases. Therefore, you must set the root password during Linux installation. Of course, to change the default empty password, the method is as follows:

Access MySQL Console: $ mysql-u root-p

Run the following command on the MySQL console:

> Set password for 'root' @ 'localhost' = PASSWORD ('new _ password ');

In practice, you only need to replace new_password in the previous line with the actual password.

Another way to change the root password in the Linux console is to use the mysqladmin tool:

$ Mysqladmin-u root password new_password

In this case, replace new_password in the previous line with the actual password.

Of course, this requires a strong password to avoid strong attacks.

To improve the security of root users more effectively, another good way is to change the name. Therefore, you must update the mySQL database in the table user. Perform the following operations on the MySQL console:

> USE mysql;

> UPDATE user SET user = "another_username" WHERE user = "root ";

> Flush privileges;

Then, the new user name will be used to access the MySQL console through Linux:

$ Mysql-u another_username-p

　　5. Remove the test Database

In MySQL installed by default, anonymous users can access the test database. We can remove any useless database to avoid unexpected access to the database. Therefore, on the MySQL console, run:

> Drop database test;

　6. Disable LOCAL INFILE

Another change is to disable the "load data local infile" command, which helps prevent unauthorized users from accessing LOCAL files. This is especially important when a new SQL injection vulnerability is found in PHP applications.

In addition, in some cases, the local infile command can be used to access other files on the operating system (such as/etc/passwd). The following command should be used:

Mysql> load data local infile '/etc/passwd' into table table1

The simpler method is:

Mysql> SELECT load_file ("/etc/passwd ")

To disable the local infile command, add the following parameters in the [mysqld] section of the MySQL configuration file:

Set-variable = local-infile = 0

　　7. Remove anonymous accounts and discarded accounts

The password of anonymous users in some MySQL databases is empty. Therefore, anyone can connect to these databases. You can use the following command to check:

Mysql> select * from mysql. user where user = "";

In a secure system, no information is returned. Another method is:

Mysql> show grants for ''@ 'localhost ';

Mysql> show grants for ''@ 'myhost ';

If grants exists, anyone can access the database, or at least use the default database "test ". The check method is as follows:

Shell> mysql-u blablabla

To remove an account, run the following command:

Mysql> drop user "";

MySQL 5.0 supports the drop user command. If you are using an old version of MySQL, you can remove the account as follows:

Mysql> use mysql;

Mysql> delete from user WHERE user = "";

Mysql> flush privileges;

　8. Reduce SYSTEM privileges

Common Database Security recommendations refer to the "Reducing privileges to all parties" statement. This is also true for MySQL. In general, developers will use the largest license, not considering the license principle like security management, but this will expose the database to a huge risk.

To protect the database, ensure that the file directories of the MySQL database are owned by the "mysql" users and "mysql" groups.

Shell> ls-l/var/lib/mysql

In addition, make sure that only "mysql" and root users can access the/var/lib/mysql directory.

Mysql binary files exist in the/usr/bin/directory, which should be owned by the root user or a specific "mysql" user. Other users should not have "write" access to these files:

Shell> ls-l/usr/bin/my *

　　9. Reduce the user's database privileges

Some applications connect to MySQL through the user name and password of a specific database table. Security personnel should not give this user full access.

If an attacker gains full access to the user, the attacker will have all the databases. To view a user license, run the show grant command on the MySQL console.

> Show grants for 'user' @ 'localhost ';

To define user access, use the GRANT command. In the following example, user1 can only be selected from the billing table of the dianshang database:

> Grant select on billing. dianshang TO 'user1' @ 'localhost ';

> Flush privileges;

In this way, user1 cannot change any data in the table and other tables in the database.

On the other hand, if you want to remove access permissions from a user, you should use a REVOKE Command similar to the GRANT command:

> Revoke select on billing. ecommerce FROM 'user1' @ 'localhost ';

> Flush privileges;

　　10. Remove and disable the. mysql_history file.

When a user accesses the MySQL console, all command history is recorded in ~ /. Mysql_history. If attackers access this file, they can understand the database structure.

$ Cat ~ /. Mysql_history

To remove and disable this file, logs should be sent to/dev/null.

$ Export MYSQL_HISTFILE =/dev/null

The preceding command directs all log files to/dev/null. You should remove. mysql_history: $ rm ~ from the home folder ~ /. Mysql_history and create a symbolic link to/dev/null.

11. Security Patches

Make sure that the database is the latest version. Attackers can exploit the known vulnerabilities of the previous version to access enterprise databases.

12. Enable Logging

If your database server does not execute any query, we recommend that you enable the tracking record. add log =/var/log/mylogfile to the [Mysql] section of the cnf file.

For MySQL databases with heavy tasks in the production environment, this will cause high costs for servers.

In addition, ensure that only root and mysql can access these log files.

Error Log

Ensure that only root and mysql can access the hostname. err log file. This file is stored in the mysql DATA history. This file contains sensitive information, such as passwords, addresses, table names, stored procedure names, and codes. It can be used for information collection and, in some cases, attackers can also exploit database vulnerabilities. Attackers can also know the machine or internal data on which the database is installed.

MySQL Log

Ensure that only root and mysql can access the logfileXY log file, which is stored in the mysql history directory.

　13. Change the root directory

The chroot in Unix can change the root directory of the currently running process and its sub-processes. The program that re-obtains the root permission of another directory cannot access or name files out of this directory. This directory is called "chroot prison ".

By using the chroot environment, you can restrict write operations of MySQL processes and their sub-processes to improve server security.

Make sure that you have a dedicated directory for the chroot environment, such as/chroot/mysql. In addition, to facilitate the use of database management tools, you can change the following parameters in the [client] section of the MySQL configuration file:

[Client]

Socket =/chroot/mysql/tmp/mysql. sock

　14. Disable the local infile command

Load data local infile can read files from the file system and display them on the screen or saved in the database. If attackers can find the SQL injection vulnerability in the application, this command is quite dangerous. The following commands can be performed on the MySQL console:

> SELECT LOAD_FILE ("/etc/passwd ");

This command lists all users. The best way to solve this problem is to disable it in MySQL configuration and find/etc/my in CentOS. cnf or find/etc/mysql/my In Ubuntu. cnf: Add the following line in [mysqld]: set-variable = local-infile = 0. Done.

Of course, protecting the server security is also crucial to ensuring the security of the MySQL database. Server security is critical to databases. Multiple backups are provided to ensure your data security!

Share from www.dbfen.com reprinted please note