How to Disable Remote Access to MySQL on Your VPS

This guide walks you through disabling remote access to MySQL on your VPS, which is a critical security step to prevent unauthorized connections from the internet. By default, MySQL listens on all network interfaces, but you can restrict it to localhost only, ensuring only local applications can connect. You’ll need root or sudo access to your VPS and the ability to edit MySQL configuration files.

What You’ll Need

• Root or sudo access to your VPS
• SSH client (e.g., Terminal on macOS/Linux, PuTTY on Windows)
• MySQL or MariaDB installed and running
• Basic familiarity with the command line

Step-by-Step

1. Connect to your VPS via SSH. Use your SSH client to log in to your server. For example: ssh user@your-server-ip
2. Locate the MySQL configuration file. The main configuration file is typically /etc/mysql/my.cnf or /etc/my.cnf. On some systems, it may be in /etc/mysql/mysql.conf.d/mysqld.cnf (Debian/Ubuntu) or /etc/my.cnf.d/ (CentOS/RHEL). You can find it by running: mysql --help | grep 'Default options' or sudo find /etc -name '*.cnf' -type f 2>/dev/null
3. Open the configuration file with a text editor. Use sudo nano or sudo vi to edit the file. For example: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
4. Find the [mysqld] section. Look for a line that starts with [mysqld]. If it doesn’t exist, add it at the top of the file.
5. Add or modify the bind-address directive. Under the [mysqld] section, add a line: bind-address = 127.0.0.1. If there is already a bind-address line, change its value to 127.0.0.1. This tells MySQL to listen only on the loopback interface, which is localhost.
6. Optionally, disable networking entirely (if you only need local connections). If you want to completely disable TCP/IP networking, add skip-networking under the same section. This is more restrictive but prevents any remote connections, including from localhost via TCP. Note that some applications may rely on TCP even for local connections; in that case, keep bind-address instead.
7. Save and exit the editor. In nano, press Ctrl+O to save, then Ctrl+X to exit. In vi, press Esc, type :wq, and press Enter.
8. Restart MySQL to apply changes. Run: sudo systemctl restart mysql (or sudo systemctl restart mariadb for MariaDB). If your system uses init.d, use sudo service mysql restart.
9. Verify that MySQL is no longer listening on external interfaces. Use the command: sudo netstat -tlnp | grep mysql or sudo ss -tlnp | grep mysql. You should see only 127.0.0.1:3306 (or similar) in the output, not 0.0.0.0:3306 or ::.
10. Test local connections. Try connecting locally with: mysql -u root -p. If it works, remote connections should now be blocked.

Common Mistakes

• Editing the wrong configuration file. Some systems have multiple .cnf files, and changes to the wrong one may be ignored. Always verify which file MySQL is reading.
• Forgetting to restart MySQL. Changes only take effect after a restart. If you skip this step, the old settings remain active.
• Using skip-networking when you still need TCP for local tools. Some monitoring tools or applications connect via 127.0.0.1 using TCP; disabling networking entirely will break them. Prefer bind-address unless you’re sure.
• Not checking firewall rules. Even if MySQL is bound to localhost, a misconfigured firewall might still forward ports. Ensure your firewall (like UFW or iptables) blocks external access to port 3306 as well.
• Changing the port instead of the bind address. Changing the port number alone does not restrict remote access; it just moves the open port. You must set the bind address.

Tips

• Use a strong root password. Even with remote access disabled, a weak root password is a risk if someone gains local access.
• Create separate user accounts for applications. Avoid using root for everyday connections. Create limited users with only the necessary privileges.
• Regularly audit MySQL users. Run SELECT user, host FROM mysql.user; to see which hosts are allowed. Remove any entries with '%' as the host, which means any host.
• Consider using a VPN or SSH tunnel if you need remote access. Instead of exposing MySQL directly, you can securely connect to your VPS and then access MySQL through a tunnel.
• Backup your configuration file before editing. Copy the original file to a backup location, so you can restore it if something goes wrong.

Troubleshooting

• MySQL fails to start after changes. Check the error log, usually at /var/log/mysql/error.log or via journalctl -u mysql. Common issues include typos in the configuration or conflicting directives. Revert to the backup and try again.
• Connections from localhost still fail. Ensure that the bind-address is set to 127.0.0.1 and not localhost (which can resolve to IPv6 ::1). Also, check if MySQL is listening on IPv6; you may need to add bind-address = ::1 as well, or disable IPv6 in MySQL.
• You see 0.0.0.0 after restart. This means the configuration change didn’t take effect. Double-check that you edited the correct file and that the [mysqld] section is used. Also, verify that MySQL is reading the file by running mysqld --print-defaults to see the effective options.
• Remote connections still work after restart. If you have multiple MySQL instances or a proxy, they might be handling connections. Also, check if your firewall is redirecting traffic. Use sudo lsof -i -P -n | grep 3306 to see which process is listening.
• Can’t connect via socket. If you used skip-networking, ensure your client is using the Unix socket (e.g., mysql -u root without -h). If you need TCP, remove that directive and use bind-address instead.

Frequently Asked Questions

Will disabling remote access affect my local applications?

No, local applications that connect to MySQL via localhost (127.0.0.1) will continue to work normally. Only remote connections from other machines will be blocked.

Can I still connect remotely if I need to?

Yes, you can temporarily re-enable remote access by changing the bind-address back to 0.0.0.0 and restarting MySQL. For a secure approach, use SSH tunneling or a VPN instead of opening MySQL to the internet.

What is the difference between bind-address and skip-networking?

bind-address restricts the network interface MySQL listens on. Setting it to 127.0.0.1 allows only local connections. skip-networking completely disables TCP/IP networking, so only Unix socket connections are allowed (on Linux).

Do I need to change anything in the MySQL user table?

No, the bind-address change is sufficient to block remote connections. However, it’s a good practice to also remove any user accounts that have '%' as their host, as they are designed for remote access.

Will this work for MariaDB as well?

Yes, MariaDB uses the same configuration directives and the steps are identical. Just replace mysql with mariadb in service commands.

How can I verify that remote access is disabled?

From another machine, try connecting to your VPS’s IP address on port 3306 using a MySQL client. If the connection times out or is refused, remote access is disabled. Alternatively, check the listening ports with netstat or ss.

Related

• Shokz OpenRun vs OpenRun Pro: Which to Choose
• AI SRE Tools Evaluation Checklist for Reliability Teams
• Enable Mojave on Older Mac: Full Compatibility Guide