MySql service security reinforcement

1. Account security

1.1. Prohibit Mysql from running with administrator account privileges
Run mysqld safely with an ordinary account, and prohibit running MySQL service with administrator account permission. In / etc / my Make the following settings in the CNF configuration file.

[mysql.server]
user=mysql

1.2. Avoid sharing accounts between different users

Refer to the following steps.
a. Create user:

mysql> mysql> insert into
mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_sub 
ject) values("localhost","pppadmin",password("passwd"),'','','');

Execute the above command to create a phplan user.

b. Use this user to log in to MySQL service.

mysql>exit; 
@>mysql -u phplamp -p 
@>Input password 
mysql>Login succeeded

1.3. Delete irrelevant accounts

The DROP USER statement can be used to DELETE one or more MySQL accounts. When using the DROP USER command, you must ensure that the current account has the global CREATE USER permission or DELETE permission of the MySQL database. The User and Host parts of the account name correspond to the User and Host column values recorded in the User table respectively.
Execute drop user; Statement, you can cancel an account and its permissions, and delete the account permission records from all authorization tables.

2. Password

Check the account default password and weak password. The password shall be at least eight digits long and include at least two types of numbers, lowercase letters, uppercase letters and special symbols, and the same password shall not be set within five times. Passwords should be changed at least every 90 days.

You can change the password by executing the following command.

 mysql> update user set password=password('test!p3') where user='root';
 mysql> flush privileges;

3. Authorization

Within the scope of database permission configuration capability, configure the minimum permissions required by users according to their business needs.

1.1. Check database authorization.

mysql> use mysql;
mysql> select * from user;
mysql>select * from db;
mysql>select * from host;
mysql>select * from tables_priv;
mysql>select * from columns_priv;

1.2. Reclaim unnecessary or dangerous authorization by revoke command.

mysql> help revoke
Name: 'REVOKE'
Description:
Syntax:
REVOKE
priv_type [(column_list)]
   [, priv_type [(column_list)]] ...
 ON [object_type]
     {
         *
       | *.*
       | db_name.*
       | db_name.tbl_name
       | tbl_name
       | db_name.routine_name
     }
 FROM user [, user] ...

4. Enable log audit function

4.1. The database shall be configured with log function to facilitate recording operation status and operation behavior.

MySQL The service has the following log types:
Error log: -log-err
 Query log: -log ((optional)
Slow query log: -log-slow-queries ((optional)
Update log: -log-update
 Binary log: -log-bin

4.2. Find the MySQL installation directory in my Add the above required log type parameters to the INI configuration file. After saving the configuration file, restart the MySQL service to enable the log function.
For example:

#Enter a name for the binary log. Otherwise a default name will be used. 
#log-bin= 
#Enter a name for the query log file. Otherwise a default name will be used. 
#log= 
#Enter a name for the error log file. Otherwise a default name will be used. 
log-error= 
#Enter a name for the update log file. Otherwise a default name will be used. 
#log-update=
#Error logging is enabled in this parameter. If you need to enable other logs, you only need to set the corresponding parameter in front of the log“#”Delete it.

4.3. Log query instructions

implement show variables like 'log_%';Command to view all log. 
implement show variables like 'log_bin';Command to view specific log. 

5. Install the latest patch

Ensure that the latest security patches are installed on the system.
Note: on the premise of ensuring business and network security, and after compatibility test, install the updated patch.

6. If not required, remote access should be prohibited

Prohibit network connection and prevent password guessing attack, overflow attack and sniffing attack.
Note: only when the application and database are on the same host.
If the database does not require remote access, you can prohibit remote TCP/IP connections. By adding the -- skip networking parameter to the startup parameters of the MySQL server, the MySQL service will not listen to any TCP/IP connections to increase security.
The security group can be used for intranet and intranet access control. It is recommended not to open the database high-risk services to the Internet.

7. Set trusted IP access control

Through the firewall restriction of the operating system where the database is located, only trusted IP can access the database through the listener.

mysql> GRANT ALL PRIVILEGES ON db.*
 ·-> -> TO user name@'IP Subnet/Mask';

8. Connection number setting

Set the maximum and minimum number of connections according to your machine performance and business requirements.
Add Max in the [mysqld] configuration section of the MySQL configuration file (my.conf or my.ini)_ Connections = 1000, save the configuration file and restart the MySQL service.

reference resources:
https://help.aliyun.com/knowledge_detail/49568.html