Security Configuration and precautions for MySQL Databases

Many of my friends may just install mysql directly and have almost no security knowledge, because I don't think mysql requires security configuration as php does, in fact, you have a great risk for your database. I will introduce some security configurations during mysql installation.

When you first install MySQL on a machine, the authorization table in the mysql database is initialized as follows:

You can connect to the local host as a root without specifying a password. Root users have all permissions (including management permissions)
And can do anything. (By The Way, MySQL Super Users have the same name as Unix Super Users, and they have nothing to do with each other .)

Anonymous Access authorized users can connect to a database named test and any database named test _ locally. Anonymous Users can perform operations on databases
But has no management permission.

Multi-server connection from the local host is allowed, regardless of whether the connected user uses a localhost host name or a real host name. For example:

The Code is as follows:	Copy code
% Mysql-h localhost test % Mysql-h pit.snke.net test

The fact that you use root to connect to MySQL and even do not specify a password only means that the initial installation is not secure. Therefore, as an administrator, you must first
You should set the root password. Then, based on how you set the password, you can also tell the server to reload the authorization table that it knows the change.
Change. (When the server starts, it reloads the table to the memory and may not know that you have modified them .)

For MySQL 3.22 and later versions, you can use mysqladmin to set a password:

The Code is as follows:	Copy code
% Mysqladmin-u root password yourpassword

For any MySQL version, you can use the mysql program and directly modify the user authorization table in the mysql database:

The Code is as follows:	Copy code
% Mysql-u root mysql Mysql> UPDATE user SET password = PASSWORD ("yourpassword") WHERE User = "root ";

If you have an earlier MySQL version, use mysql and UPDATE.

After you set the password, run the following command to check whether you need to tell the server to reload the authorization table:

The Code is as follows:	Copy code
% Mysqladmin-u root status

If the server still allows you to connect to the server without specifying a password as root, reload the authorization table:

The Code is as follows:	Copy code
% Mysqladmin-u root reload

After you set the root password (and if you need to reload the authorization table), you will need to specify

Below are some of my methods

1. Set or modify the Mysql root Password:
By default, the password is blank after installation. Use the mysqladmin command to set the password:

The Code is as follows:	Copy code
Mysqladmin-uroot password "password" Mysql command to set the password: Mysql> set password for root @ localhost = password ('password); change the password: Update mysql. user set password = password ('Password') where user = 'root '; Flush privileges;

2. delete default databases and users

The Code is as follows:	Copy code
Drop database test; Use mysql; Delete from db; Delete from user where not (host = "localhost" and user = "root "); Flush privileges;

3. Change the default root Account name:

The Code is as follows:	Copy code
Update mysql. user set user = "admin" where user = "root "; Flush privileges;

4. Local file security:

The Code is as follows:	Copy code
Set-variable = local-infile = 0

5. remote connection to mysql is prohibited. For remote management, use phpmyadmin to edit my. cnf and add it in [mysqld:

The Code is as follows:	Copy code
Skip-networking

6. Minimum permission User:

The Code is as follows:	Copy code
Create database db1; Grant select, insert, update, delete, create, drop privileges on database. * to user @ localhost identified by 'passwd ';

7. Restrict normal users to browse other databases. edit my. cnf and add it in [mysqld:

The Code is as follows:	Copy code
Skip-show-database8

. Quick restoration of MySQL database

Restore database

Mysqlcheck-A-o-r-p repairs the specified database

The Code is as follows:	Copy code
Mysqlcheck-o-r database-p

9. Select the MySQL configuration file based on the memory size:

The Code is as follows:	Copy code
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