edit-icon download-icon

Harden MySQL service security

Last Updated: May 08, 2018

This article helps database management staff improve the security of databases and guarantee stable, safe, and reliable operation with MySQL database system security hardening configurations.

Vulnerability detection

You can use the Server Guard Enterprise Edition to detect MySQL vulnerabilities in your own servers, or you can check for vulnerabilities by yourself.

Security hardening

  1. Account security

    • Disable running MySQL with the administrator permission.

      Run MySQL with a regular account and prohibit running MySQL with administrator permissions.

      You can configure this in the /etc/my.cnf configuration file as follows:

      1. [mysql.server]
      2. user=mysql
    • Avoid account sharing between different users.

      1. For example, create a user with the following command:

        1. mysql> mysql> insert into
        2. mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_sub
        3. ject) values("localhost","pppadmin",password("passwd"),'','','');
      2. Then, a user with the account name of phplamp is created.

      3. Log on the MySQL service with the user.

        1. mysql>exit;
        2. @>mysql -u phplamp -p
        3. @>[Enter your password]
        4. mysql>[Log on successfully]
    • Delete irrelevant accounts.

      The DROP USER command deletes one or more MySQL accounts. To use DROP USER, you must have the global CREATE USER or DELETE permission for the MySQL database. The user and host of the account name correspond to the values in the User and Host columns recorded in the user table.

      You can use the DROP USER command to delete an account and its permissions.

      DROP USER [user];

      This statement can delete account permission records from all authorization tables.

  2. Password

    Check the account default passwords and avoid weak passwords. The password must be at least eight characters long, and contain at least two of the four types of characters, namely numbers, lowercase letters, uppercase letters and special symbols. Do not repeat your password between the five consecutive passwords. The password must be changed at least every 90 days.

    For example, to change the weak password, run the following command:

    1. mysql> update user set password=password('test!p3') where user='root';
    2. mysql> flush privileges;
  3. Authorization

    Grant minimum account permissions according to the needs of the user and the scope of database permissions.

    To do this configuration, follow these steps:

    1. Check the database authorization status.

      1. mysql> use mysql;
      2. mysql> select * from user;
      3. mysql>select * from db;
      4. mysql>select * from host;
      5. mysql>select * from tables_priv;
      6. mysql>select * from columns_priv;
    2. Run the Revoke command to revoke unnecessary or dangerous authorization.

      1. mysql> help revoke
      2. Name: 'REVOKE'
      3. Description:
      4. Syntax:
      5. REVOKE
      6. priv_type [(column_list)]
      7. [, priv_type [(column_list)]] ...
      8. ON [object_type]
      9. {
      10. *
      11. | *.*
      12. | db_name.*
      13. | db_name.tbl_name
      14. | tbl_name
      15. | db_name.routine_name
      16. }
      17. FROM user [, user] ...
  4. Enable log auditing

    Configure the log auditing feature to log operation statuses and actions.

    MySQL has the following types of logs:

    • Error log: -log-err
    • Query log: -log (optional)
    • Slow query log: -log-slow-queries (optional)
    • Update log: -log-update
    • Binary log: -log-bin

      To enable log auditing, under the MySQL installation directory, open the my.ini file and add the above parameters at the end, save the file, and then restart the MySQL service. For example, enable the error logs as follows:

      1. #Enter a name for the binary log. Otherwise a default name will be used.
      2. #log-bin=
      3. #Enter a name for the query log file. Otherwise a default name will be used.
      4. #log=
      5. #Enter a name for the error log file. Otherwise a default name will be used.
      6. log-error=
      7. #Enter a name for the update log file. Otherwise a default name will be used.
      8. #log-update=

      To enable other log types, remove the leading “#”.

      Additional operation commands

      • show variables like 'log_%';: View all log commands.
      • show variables like 'log_bin';: View a specific log command.
  5. Install the latest patches

    Make sure that the system has installed the latest security patches.

    Note: Test the compatibility of patches and make sure that your service and network are secure, before installing the patches.

  6. Disable remote access if the feature is not required

    Disable network connections to prevent password guessing attacks, overflow attacks and sniffing attacks.

    Note: This operation only applies when applications and databases are deployed on one host.

    If the database does not require remote access, you can disable remote TCP/IP connections. Add the “—skip-networking” startup parameter in MySQL servers to disable MySQL listening for TCP/IP connections and enhance security.

    You can use Alibaba Cloud security groups to manage intranet and extranet accesses. We recommend that you do not expose high-risk database services to the Internet.

  7. Manage accesses by setting trusted IP addresses

    With the firewall restrictions of the operating system of the database, only allow trusted IP addresses to access the database through the listener.

    For example, to do this configuration, run the following command:

    1. mysql> GRANT ALL PRIVILEGES ON db.*
    2. ·-> -> TO Username@'IP subnet/mask';

    In this situation, only users who belong to the specified IP segments are allowed to log on.

  8. Set the number of connections

    Set the maximum and minimum numbers of connections according to your machine performance and business needs.

    To do this, edit the MySQL configuration file (my.cnf or my.ini), add max_connections = 1000 in the [mysqld] configuration section, save the file, and then restart the MySQL service.

Thank you! We've received your feedback.