MySQL database needs security Configuration and precautions

When you first install MySQL on a machine, the authorization table in the MySQL database is initialized like this:

You can connect as root from the local host (localhost) without specifying a password. Root user has all permissions (including administrative privileges)
and can do anything. (By the way, MySQL Superuser and Unix superuser have the same name, they have nothing to do with each other.) ）

Anonymous access is granted to a database in which the user can start with a local connection named Test and any name Test_. Anonymous users can be allowed to do the database
What things, but no administrative rights.

A multiple-server connection from a local host is allowed, regardless of whether the connected user is using a localhost hostname or a real host name. Such as:

The code is as follows	Copy Code
% mysql-h localhost test % mysql-h pit.snake.net Test

The fact that you're connecting to MySQL with root and not even specifying a password simply means that the initial installation is unsafe, so the first thing you need to do as an administrator
You should set the root password, and then depending on how you set the password, you can also tell the server to overload the authorization table it knows the change
Change. (When the server starts, it overloads the table into memory and may not know that you have modified them.) ）

For MySQL 3.22 and above, you can set the password with mysqladmin:

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

For any version of MySQL, you can use the MySQL program and directly modify the user authorization form 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 the old version of MySQL, use MySQL and update.

After you have set up your password, check to see if you need to tell the server to overload the authorization table by running the following command:

The code is as follows	Copy Code
% mysqladmin-u Root Status

If the server still lets you connect to the server with root without specifying a password, overload the authorization table:

The code is as follows	Copy Code
% mysqladmin-u Root Reload

After you have set the password for root (and if you need to overload the authorization table), you will need to specify at any time when you connect to the server as root

Here are some methods I've sorted out.

1. Set or modify the MySQL root password:
Null password after default installation, set password with mysqladmin command:

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

2. Delete the default database and user

The code is as follows	Copy Code
drop database test; Use MySQL; Delete from DB; Delete from the 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. Prohibit remote connection MySQL, remote management can be phpmyadmin, edit my.cnf in [mysqld] add:

The code is as follows	Copy Code
Skip-networking

6. Least-Privileged 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 ordinary users to browse other databases, edit my.cnf to add in [mysqld]:

The code is as follows	Copy Code
--skip-show-database8

. quick fix MySQL Database

Repairing the database

Mysqlcheck-a-o-r-P repairs the specified database

The code is as follows	Copy Code
Mysqlcheck-o-R Database-p

9. According to the size of the memory to choose the MySQL configuration file:

code is as follows	copy code
my-small.cnf # > MY-MEDIUM.CNF # 32m-64m my-large.cnf # memory = 512M My-huge.cnf # 1g-2g My-innodb-heavy-4g.cnf # 4G B