Mysql database security settings and considerations summary _mysql

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:

Copy Code code as follows:

% 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:

Copy Code code as follows:

% 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:

Copy Code code as follows:

% 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:

Copy Code code as follows:

% mysqladmin-u Root Status

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

Copy Code code as follows:

% 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.
First let MySQL run MySQL with normal user privileges. Similar to SQL Server Setup method, if not a friend can use the tool to implement.

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

Copy Code code as follows:

Login MySQL:
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

Copy Code code as follows:

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:

Copy Code code as follows:

Update Mysql.user set user= "admin" where user= "root";
Flush privileges;

4. local File Security:

Copy Code code as follows:

Set-variable=local-infile=0

5. Prohibit remote connection MySQL, remote management can be phpmyadmin, edit my.cnf in [mysqld] add:

Copy Code code as follows:

Skip-networking

6. Least-Privileged User:

Copy Code code as follows:

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]:

Copy Code code as follows:

--skip-show-database8

. quick fix MySQL Database

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

Copy Code code as follows:

Mysqlcheck-o-R Database-p

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

Copy Code code as follows:

MY-SMALL.CNF # > MY-MEDIUM.CNF # 32m-64m
MY-LARGE.CNF # memory = 512M
MY-HUGE.CNF # 1g-2g
MY-INNODB-HEAVY-4G.CNF # 4GB