MySQL database security settings and considerations _ MySQL

MySQL database security settings and considerations summary bitsCN.com

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:

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

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

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

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

% 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
First, let mysql run mysql with normal user permissions. It is similar to the setting method of sqlserver. if not, you can use tools.

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

Log on to mysql:
Mysqladmin-uroot password "password ";
Mysql command to set the 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 default databases and users

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:

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

4. local file security:

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:

Skip-networking

6. minimum permission User:

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:

Skip-show-database8
. Quick restoration of MySQL database

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

Mysqlcheck-o-r database-p

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

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

BitsCN.com