Configure remote database in MySQL

Configure remote MySQL database

What is a remote MySQL connection?

A remote MySQL connection allows applications or tools to connect to your database from another server or machine, not just from the local server where MySQL is installed. This is useful for distributed applications, management tools, or remote backups.

Prerequisites

• Root or administrator access to the MySQL server
• Know the IP of the server that will connect remotely
• Firewall configured to allow connections to port 3306 (MySQL default port)

Steps to configure remote connection

1. Verify MySQL configuration

First, verify if MySQL is configured to accept remote connections. Edit the configuration file:

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

Or on some distributions:

sudo nano /etc/my.cnf

Find the bind-address line and change it from:

bind-address = 127.0.0.1

To:

bind-address = 0.0.0.0

This allows MySQL to listen on all network interfaces.

2. Create user with remote permissions

It's recommended to create a specific user for remote connections instead of using root:

mysql -u root -p
CREATE USER 'remote_user'@'CLIENT_IP' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'remote_user'@'CLIENT_IP';
FLUSH PRIVILEGES;
EXIT;

To allow connections from any IP (less secure, but more flexible):

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'remote_user'@'%';
FLUSH PRIVILEGES;

3. Configure firewall

If using UFW on Ubuntu/Debian:

sudo ufw allow 3306/tcp
sudo ufw reload

If using firewalld on CentOS/RHEL:

sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

If using iptables directly:

sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
sudo iptables-save

4. Restart MySQL

After changing the configuration, restart MySQL:

sudo systemctl restart mysql

Or on some distributions:

sudo systemctl restart mysqld

5. Verify MySQL is listening on all interfaces

Verify that MySQL is listening on port 3306:

sudo netstat -tlnp | grep 3306

Or with ss:

sudo ss -tlnp | grep 3306

You should see something like: 0.0.0.0:3306 or ::3306

6. Test remote connection

From the client server, test the connection:

mysql -h SERVER_IP -u remote_user -p

If the connection is successful, you'll see the MySQL prompt.

Recommended security configuration

• Use specific IPs: Instead of '%', specify the exact IPs that need access.
• Use dedicated users: Don't use root for remote connections.
• Limit permissions: Grant only necessary permissions, not ALL PRIVILEGES.
• Use secure passwords: Long and complex passwords.
• Consider SSL: For remote connections, it's recommended to use SSL.

Configure SSL for remote connections (optional but recommended)

For greater security, you can configure SSL:

GRANT ALL PRIVILEGES ON database_name.* TO 'remote_user'@'IP' REQUIRE SSL;
FLUSH PRIVILEGES;

Then, when connecting from the client, use:

mysql -h SERVER_IP -u user -p --ssl-mode=REQUIRED

Common problems

Error: "Can't connect to MySQL server"

• Verify that the firewall allows port 3306.
• Confirm that bind-address is configured as 0.0.0.0.
• Verify that MySQL is running: sudo systemctl status mysql

Error: "Access denied"

• Verify that the user has permissions to connect from that IP.
• Confirm that the password is correct.
• Check user permissions: SHOW GRANTS FOR 'user'@'IP';

Slow connection

• Check network latency between servers.
• Consider using a VPN connection for greater security and performance.

Disable remote connections

If you need to disable remote connections for security:

1. Change bind-address back to 127.0.0.1
2. Delete or restrict remote users
3. Restart MySQL
4. Close port 3306 in the firewall

Need help?

If you're having trouble configuring remote connections to MySQL, open a ticket from the billing.baires.host panel or contact us for support.

You can also reach us through our social media:

• Instagram: @baires_host
• Discord (active support): https://discord.gg/dzjauatAFN
• Linktree: https://linktr.ee/baires.host