Secure Remote MySQL Access & Restricted Users

Enable safe remote connections to your MySQL server on Ubuntu and manage user permissions effectively.

Estimated Time: Approximately 20 - 30 minutes

Overview: Why Remote Access and Restricted Users?

By default, MySQL on Ubuntu is configured to only accept connections from the local server (`localhost`). This is a strong security measure, but it prevents you from managing your database using tools on your local computer (like MySQL Workbench, DBeaver, or DataGrip) or connecting from another application server.

This guide will show you how to enable remote access to your MySQL server. More importantly, it will demonstrate how to do so securely by:

Restricting Network Access: Using the Uncomplicated Firewall (UFW) to only allow connections from specific IP addresses.
Creating Restricted Users: Setting up new MySQL users with limited privileges for specific databases and only allowing them to connect from specific remote hosts. This adheres to the "Principle of Least Privilege."
Avoiding Root User: Never use the MySQL root user for remote connections or applications.

Properly configuring remote access is essential for flexible database management without compromising your server's security.

Estimated Time

20 - 30 minutes

Experience Level

Beginner to Intermediate

Assumes basic command-line knowledge and understanding of IP addresses and firewalls.

System Requirements & Prerequisites

Server: An Ubuntu 22.04 LTS or 20.04 LTS server with MySQL installed (e.g., via our LAMP stack guide).
Firewall (UFW): UFW must be enabled and active on your server. If not, refer to our LAMP guide for UFW setup.
Sudo Privileges: Access to a terminal as a non-root user with sudo privileges.
Your Local IP: You need the public IP address of the machine from which you will connect remotely. You can find this by searching "What is my IP" on Google from your local machine.
Server's Public IP: Your Ubuntu server's public IP address.

CRITICAL SECURITY WARNING: Proceed with Caution!

Enabling remote access to your database is a significant security decision. If not done correctly, it can expose your database to the internet, making it vulnerable to attacks.

Always follow the principle of least privilege:

Restrict IP Addresses: Never open MySQL (port 3306) to the entire internet (0.0.0.0/0). Always specify the exact IP address(es) from which connections are allowed.
Use Strong Passwords: For all MySQL users, especially remote ones.
Limited User Permissions: Create users with only the specific database and table permissions they need. Avoid granting `ALL PRIVILEGES` to remote users unless absolutely necessary and understood.
Consider Alternatives: For internal or highly sensitive access, prefer more secure methods like SSH Tunneling or a VPN over direct public access.

Step-by-Step Instructions

Step 1: Update System Packages

Always start by ensuring your system is up-to-date.

sudo apt update && sudo apt upgrade -y

A server reboot might be needed if kernel updates occur. You can check for pending reboots with `ls /var/run/reboot-required`.

Step 2: Backup MySQL Configuration File

Before making any changes to configuration files, it's a good practice to create a backup. The main MySQL configuration file is typically located at `/etc/mysql/mysql.conf.d/mysqld.cnf` on Ubuntu.

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

You now have a backup in case you need to revert any changes.

Step 3: Configure MySQL to Allow Remote Connections

By default, MySQL listens only on `127.0.0.1` (localhost). We need to change this to allow external connections.

Open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Locate the `bind-address` line (usually under the `[mysqld]` section). It will likely look like this:

bind-address            127.0.0.1

You have two main options:

(Recommended for most cases with UFW) Change to `0.0.0.0`: This tells MySQL to listen on all available network interfaces (IPv4). You will *rely heavily* on UFW to restrict access.
(More restrictive) Change to your server's public IP: This tells MySQL to only listen on your server's specific public IP. This is an additional layer of restriction, but `0.0.0.0` combined with strong UFW rules is often more flexible for cloud environments.

For this guide, we'll change it to `0.0.0.0` and use UFW for precise control:

bind-address            0.0.0.0

Save the file (`Ctrl+O`, `Enter`) and exit `nano` (`Ctrl+X`).

Step 4: Adjust Firewall (UFW) to Allow MySQL Traffic

This is the **most critical security step**. You must explicitly tell your firewall to allow connections to MySQL's default port (3306) only from trusted sources.

DO NOT open port 3306 to the entire internet! Only allow connections from your specific remote IP address(es). If you allow from 'any', your database becomes a prime target. Replace `YOUR_LOCAL_IP_ADDRESS` with the public IP of the machine you will connect from.

Allow MySQL access from a specific IP address:

sudo ufw allow from YOUR_LOCAL_IP_ADDRESS to any port 3306

Example (if your local IP is 203.0.113.42):

sudo ufw allow from 203.0.113.42 to any port 3306

Reload UFW to apply changes:

sudo ufw reload

Verify UFW status:

sudo ufw status

You should see an entry like `3306 ALLOW IN From YOUR_LOCAL_IP_ADDRESS`.

Step 5: Restart MySQL Service

For the `bind-address` change in `mysqld.cnf` to take effect, you must restart the MySQL service.

sudo systemctl restart mysql

Verify MySQL is listening on all interfaces (or your server's public IP):

sudo netstat -tulnp | grep 3306

You should see `0.0.0.0:3306` (for IPv4) and possibly `:::3306` (for IPv6) in the output under the `Local Address` column, indicating it's listening publicly.

Step 6: Create a New MySQL User for Remote Access and Grant Specific Permissions

Using the `root` MySQL user for remote connections or applications is a major security risk. Instead, create a new user with strong permissions restricted to a specific database and allowed only from your remote IP.

1. Log in to MySQL as root:

sudo mysql -u root -p

Enter the MySQL root password you set during the `mysql_secure_installation` step of your LAMP setup.

2. Create the Database (if you haven't already):

If your application already has a database, skip this. Otherwise, create one:

CREATE DATABASE your_app_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Create the Remote User:

Replace `remote_user`, `strong_password`, and `YOUR_LOCAL_IP_ADDRESS` with your chosen values. The `%` wildcard below for the host can be used if you have a range of IPs, but it's *more secure* to use a specific IP.

                                            CREATE USER 'remote_user'@'YOUR_LOCAL_IP_ADDRESS' IDENTIFIED BY 'strong_password';

                                            # Or, if you need to access from anywhere, but use with extreme caution:

                                            CREATE USER 'remote_user'@'%' IDENTIFIED BY 'strong_password';

For MySQL 8.0+, if you encounter connection issues with older clients, you might need to specify the authentication plugin:
`ALTER USER 'remote_user'@'YOUR_LOCAL_IP_ADDRESS' IDENTIFIED WITH mysql_native_password BY 'strong_password';`

4. Grant Specific Permissions:

Grant only the necessary privileges on your specific database to this new user. For most web applications, `SELECT`, `INSERT`, `UPDATE`, `DELETE` are sufficient. Avoid `ALL PRIVILEGES` unless you fully understand the implications.

GRANT SELECT, INSERT, UPDATE, DELETE ON your_app_database.* TO 'remote_user'@'YOUR_LOCAL_IP_ADDRESS';

If you used `'%'` for the host when creating the user, use it here too:

GRANT SELECT, INSERT, UPDATE, DELETE ON your_app_database.* TO 'remote_user'@'%';

For a database administrator (not for an application), you might grant more privileges, e.g., `GRANT ALL PRIVILEGES ON your_app_database.* TO 'remote_user'@'YOUR_LOCAL_IP_ADDRESS';` but do so sparingly.

5. Apply Privilege Changes:

FLUSH PRIVILEGES;

6. Exit MySQL:

EXIT;

You have successfully created a restricted MySQL user with remote access!

Step 7: Test Remote Connection from Your Local Machine

Now, try connecting to your MySQL server from your local computer using the new user's credentials.

From your local terminal:

mysql -h YOUR_SERVER_IP -u remote_user -p your_app_database

Replace `YOUR_SERVER_IP` with your Ubuntu server's public IP address. When prompted, enter `strong_password`.

If the connection is successful, you will see the MySQL prompt (`mysql>`). You can then run a simple command:

SHOW TABLES;

Using a GUI tool (e.g., MySQL Workbench, DBeaver):

Configure a new connection with the following details:

Hostname/IP: `YOUR_SERVER_IP`
Port: `3306`
Username: `remote_user`
Password: `strong_password`
Default Schema/Database: `your_app_database`

A successful connection and ability to query your database confirms remote access is working as intended.

Final Verification Checklist

Confirm your remote MySQL access is both functional and secure:

MySQL Listening: `sudo netstat -tulnp | grep 3306` shows MySQL listening on `0.0.0.0:3306` (or your server's public IP).
UFW Rule Active: `sudo ufw status` explicitly shows port 3306 allowed ONLY from `YOUR_LOCAL_IP_ADDRESS`.
Remote Connection Works: You can successfully connect from your local machine using the `remote_user` and `strong_password`.
Root Cannot Connect Remotely: (Optional but good check) Try `mysql -h YOUR_SERVER_IP -u root -p`. This should ideally fail unless specifically allowed, which is not recommended.
Permissions Verified: From within the MySQL prompt (as root), run `SHOW GRANTS FOR 'remote_user'@'YOUR_LOCAL_IP_ADDRESS';` to confirm only the intended permissions are granted.

Conclusion & Next Steps

You have successfully and securely enabled remote access to your MySQL server on Ubuntu, and, more importantly, created a dedicated user with restricted permissions. This setup allows for flexible database management while adhering to critical security best practices.

Consider these advanced steps for even greater security and manageability:

Dynamic IP Addresses: If your local IP address changes frequently, you'll need to update your UFW rule. For business use, a static IP or VPN is highly recommended.
Multiple Remote Users: Create distinct users with specific hostnames and permissions for each application or individual needing remote access. This makes auditing easier.
SSH Tunneling: For maximum security, especially if your local IP is dynamic or you need to access from various locations, learn about SSH tunneling. This routes your MySQL connection securely through your SSH connection, eliminating the need to open port 3306 publicly.
Audit Logging: Enable MySQL audit logging to track all connections and queries, which can be invaluable for security monitoring and troubleshooting.
Regular Password Rotation: Implement a policy for regularly changing database user passwords.
Database Backups: Ensure you have a robust and automated backup strategy for your MySQL databases.

Need Advanced Database Security or Administration? Contact Us!