Summary of MySQL database security Settings and considerations

  • 2020-05-27 07:24:36
  • OfStack

When you install MySQL on your machine for the first time, the authorization table in the mysql database is initialized like this:

You can connect with root from local host (localhost) without specifying a password. root users have all permissions (including administrative permissions)
And can do anything. (by the way, MySQL power users have the same name as Unix power users, and they have nothing to do with each other.)

Anonymous access is granted to a user from a local connection to a database named test and any name starting with test_. Anonymous users can do whatever they want with the database
Nothing, but no administrative authority.

Connections from localhost multiple servers are allowed, regardless of whether the connected user USES an localhost hostname or a real hostname. Such as:

% mysql -h localhost test
% mysql -h test

The fact that you connect root to MySQL without even specifying a password simply means that the initial installation is not secure, so the first thing you need to do as an administrator
The root password should be set, and depending on how you set the password to use, you can also tell the server that the overloading authorization table is the one it knows about
The same. (when the server starts, it reloads the tables into memory and may not know you have changed them.)

For MySQL 3.22 and above, you can use mysqladmin to set your password:

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

% mysql -u root mysql
mysql>UPDATE user SET password=PASSWORD("yourpassword") WHERE User="root";

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

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

% mysqladmin -u root status

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

% 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 it whenever you connect to the server with root

Here are some of the ways I've put it together
First, let mysql run mysql with normal user privileges. Similar to the sqlserver setup method, if not friends can use the tool implementation.

1. Set or modify the Mysql root password:
The default password is empty after installation, and the password is set with the mysqladmin command:

 landing mysql : 
mysqladmin -uroot password "password"; 
Mysql Command to 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

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:


5. Remote connection to mysql is prohibited. Remote management can be edited by phpmyadmin, my.


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, and edit my.cnf to add:


. Quick fix of MySQL database

Fix the database
mysqlcheck-A-o-r-p fixed the specified database

mysqlcheck  -o -r database -p

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

my-small.cnf # > my-medium.cnf # 32M - 64M
my-large.cnf # memory = 512M
my-huge.cnf # 1G-2G
my-innodb-heavy-4G.cnf # 4GB

Related articles: