Best Practices for Securing Remote Access to MySQL

The security of remote access to MySQL is guaranteed by restricting permissions, encrypting communications, and regular audits. 1. Set a strong password and enable SSL encryption. Force --ssl-mode=REQUIRED when connecting to the client; 2. Restrict access to IP and user rights, create a dedicated account and grant the minimum necessary permissions, and disable root remote login; 3. Configure firewall rules, close unnecessary ports, and use springboard machines or SSH tunnels to enhance access control; 4. Enable logging and regularly audit connection behavior, use monitoring tools to detect abnormal activities in a timely manner, and ensure database security.

Remote access to MySQL is part of the daily work of many developers and system administrators, but if not configured correctly, it can become the source of security vulnerabilities. The most direct point is that the security of remote access to MySQL depends on how you restrict access, encrypt communications, and regularly audit connection behavior .

Below are some practical suggestions that can help you do this more securely.

1. Use a strong password and enable SSL encryption

MySQL uses plain text to transmit data by default. If someone monitors network traffic in the middle, the account password may be intercepted. so:

• Set complex passwords to avoid using common vocabulary or default account names.
• Enable SSL connection , enable ssl in the MySQL configuration file and require the client to force it.
• When connecting to the client, add --ssl-mode=REQUIRED parameter to ensure that the connection does not go in plain text.

Although SSL is not omnipotent, it can at least add a layer of protection to the transport layer to prevent "running naked".

2. Restrict access to IP and user permissions

For convenience, many people directly open % permissions to a certain user, that is, allow access from any IP, which is actually very dangerous.

• Only specific IP or IP segments are allowed to access . For example, if you work in the office, you will bind the company's export IP; if it is a dynamic IP, you can consider using a springboard machine or an intranet penetration tool.
• Do not use the root user to log in remotely , create a dedicated account, and grant only the minimum necessary permissions (such as read-only accounts cannot be written).
• Deleting anonymous users and testing databases is often the first step for an attacker.

For example:

 CREATE USER 'remote_user'@'192.168.1.100' IDENTIFIED BY 'StrongPass!123';
GRANT SELECT, INSERT ON mydb.* TO 'remote_user'@'192.168.1.100';

The advantage of this is that even if the account is leaked, the scope of impact is limited.

3. Isolate with firewalls and services

In addition to the configuration of MySQL itself, security policies at the operating system level are also critical:

• Close unnecessary ports . MySQL defaults to 3306, ensuring that only this port is exposed to the outside world.
• Configure firewall rules on the server, such as iptables or ufw, to allow only specific IP or subnet access.
• If conditions permit, place the database in a private network and access it through a springboard (Jump Host) or VPN.

For example, you can do this:

• Setting up a firewall allows only IPs from your development machine to access port 3306
• Use SSH tunnel to connect to MySQL, so that even if the 3306 is not open to the public, it can operate remotely

Although this approach is a little bit troublesome, it greatly reduces the risk of being scanned.

4. Regular audit logs and connection records

Many times, security problems do not break out suddenly, but accumulate slowly. so:

• Enable MySQL's slow query log, error log, and general query log to see if there are any abnormal connection attempts.
• Check the current connection regularly and use SHOW PROCESSLIST; see if someone has occupied resources for a long time.
• Use monitoring tools such as Prometheus Grafana or Zabbix to observe database status in real time.

If you find that a strange IP fails to frequently try to connect, it is likely that it is brute-force cracking. At this time, you should update your password or block the IP immediately.

Basically that's it. Remote access to MySQL itself is not difficult, but to be truly secure, you need to start from multiple levels, not just changing a few configurations.

The above is the detailed content of Best Practices for Securing Remote Access to MySQL. For more information, please follow other related articles on the PHP Chinese website!