Simple MySQL security settings and Optimization

Note that this article is not a principle, but a simple summary. If you have any questions, pay attention to the following two sites:
Http: // I MySQL . CN/
Http://dev.mysql.com/doc/refman/5.1/zh/index.html

1. Modify RootUserPassword
By default, no password is set for MySQL. You can set the password header in three ways:

* Use mysqladminCommandTo change the root user password
# Mysqladmin-uroot password 54 netseek // set MySQLManagementThe root password of the Operating User is 52 netseek.

* Use set password to change the password:
Mysql> set password for root @ localhost = PASSWORD ('54netseek );

* Directly modify the root user password of the User table
Mysql> use MySQL;
Mysql> Update user SET Password = PASSWORD ('54netseek ') where user = 'root ';
Mysql> flush privileges;

2. delete default databases and users
Our database is local and only needs localPHPScriptRead MySQL, which is not required by many users. M
After ysql initialization, empty users and Test Databases are automatically generated, which poses a threat to the database and we will delete all of them.
We use the mysql clientProgramConnect to the local MySQLServerThe following prompt appears:
Mysql> drop database test;
Mysql> use MySQL;
Mysql> Delete from dB;
Mysql> Delete from user where not (host = "localhost" and user = "root ");
Mysql> flush privileges;

3. Change the default MySQL Administrator name // only recommended
According to my habits, because the default MySQL Administrator name is root, if you can modify it,
It can prevent some script kiddies fromSystem. We can directly modify the database and change the root user to "admin"
Mysql> use MySQL;
Mysql> Update user set user = "admin" where user = "root ";
Mysql> flush privileges;

4. Improve Local Security
Improves local security, mainly to prevent MySQL fromFileFor example, if a hacker acquires/etc/passwd through MySQL, the system is threatened.
MySQL accesses local files through SQL statements, mainly through load data local infile. we can disable this function.
Prevents hackers from obtaining system core files through SQL injection. To disable this function, you must add a parameter in [mysqld] Of My. CNF:
Set-variable = Local-infile = 0

5. Disable remote connection to MySQL
Because our MySQL only needs a local PHP script for connection, we do not need to enable socket for listening, so we can completely disable the listening function.
There are two methods to achieve this:
*ConfigurationIn the my. CNF file, add the skip-networking parameter in the [mysqld] section.
* The -- skip-networking startup parameter is added to the parameters of the mysqld server so that MySQL does not listen to any TCP/IP connections, increasing security. If you want to manage MySQL, you canInstallOne phpMyAdmin for management.

6. Control Database Access Permissions
For PHP scripts for interaction, it is best to create a user with the update, select, delete, insert, drop table, create table, and other permissions only for a database, in this way, the minimum loss is avoided after the database user name and password are viewed by hackers.
For example, we create a database named db1 and create a user named test1 to access the database.
Mysql> Create Database db1;
Mysql> grant select, insert, update, delete, create, drop privileges on db1. * To test1 @ localhost identified by 'admindb ';
The preceding SQL statement creates a database db1 and adds a user named test1. The password is admindb, but it can only connect to MySQL locally. For database db1, select, insert, update, and delete are available, create and drop operation permissions.

7. Restrict normal users to browse other user Databases
If you have multiple databases and each database has one user, you must restrict the user to browse other databases, you can add the -- skip-show-database startup parameter when starting the MySQL server.

Forgot the MySQL passwordSolutionSolution
If you accidentally forget the MySQL Root Password, you can add the -- skip-grant-tables parameter when starting the MySQL server to skip the authentication of the authorization table (. /safe_mysqld -- skip-grant-tables &), so that we can directly log on to the MySQL server, then modify the password of the root user, and restart MySQL to log on with the new password.

8. database file security
By default, MySQL is installed in the/usr/local/MySQL directory, and the corresponding database file is in the/usr/local/MySQL/var directory, therefore, we need to ensure that this directory cannot allow unauthorized users to package and copy the database, so we need to restrict access to this directory.
We modify the user and group of the directory to be MySQL, and change the access permission at the same time:
# Chown-r mysql. MySQL/usr/local/MySQL/var
# Chmod-r go-rwx/usr/local/MySQL/var

9. Delete historical records
If you run the preceding commandShellIt is recorded in history files. For example, Bash will write the. bash_history files in the user directory. If these files are accidentally read,
Then the database password will be leaked. The SQL commands executed after you log on to the database are also recorded in the. mysql_history file in the user directory by MySQL.
If the database user uses an SQL statement to modify the Database Password, The. mysql_history file will also leak. So when we log on to and back up shell data
Do not add a password after-P, but enter the database password after prompt. In addition, we should not allow these two files to record our operations, just in case.
# Rm. bash_history. mysql_history
# Ln-S/dev/null. bash_history
# Ln-S/dev/null. mysql_history

10. Others
In addition, you can also use chroot and other methods to control the MySQL running directory to better control permissions. For details, referArticle.
Debugging parameters of my. CNF

11. quickly fix MySQL Databases
Restore database
# Mysqlcheck-a-o-r-p
Fix a specified database
# Mysqlcheck-o-r database_name-P

------------------------------------
12. Select the mysql load , and copy the file name to/etc/My. CNF
for RPM package installation: the/usr/share/doc/MySQL-xxx/directory contains
Source Code compile and install: the files are available in the support-files/directory.
CP/usr/local/share/MySQL-***/my-large.cnf/etc/My. CNF
my-small.cnf #> my-medium.cnf #32 m-64 m
my-large.cnf # Memory = 512 m
my-huge.cnf #1G-2g
my-innodb-heavy-4G.cnf #4 GB