Skip to content

How to Allow Remote MySQL Connections

This Article will help you to allow Remote Clients to Connect MySQL Server. Allow remote systems to connect mysql server using command line

Steps
5
Category
Databases
Covers
MySQL
Versions & support
About MySQL →
On this page 5 steps

Managing a MySQL database doesn’t always mean accessing it from the same server where it’s hosted. In many situations, especially with the rise of distributed applications and cloud services, you might need to connect to a MySQL server from a remote location. Here’s a guide on how to set this up.

Step 1 of 5Backup Your Database

Before you make any changes to your MySQL configuration or grant remote access, always back up your data. Use the mysqldump command:


mysqldump -u [username] -p [database_name] > backup.sql

Step 2 of 5Modify MySQL Configuration

MySQL’s default setting is to listen only on the localhost address. To enable remote connections, you’ll need to change this setting.

1. Edit the MySQL Configuration File: Locate mysqld.cnf or my.ini, typically found in /etc/mysql/ on Linux systems. The location might vary depending on your OS or MySQL installation.

2. Find the bind-address directive: It might be set as:


bind-address = 127.0.0.1

3. Modify the bind-address: To allow connections from any IP, change it to:


bind-address = 0.0.0.0

However, be cautious about using this setting on production servers, as it exposes the database to connections from any IP. Alternatively, if you know the specific IP you’ll connect from, you can specify that for added security.

4. Restart MySQL server: To apply the changes, restart the MySQL server:

sudo service mysql restart 

Step 3 of 5Granting User Permissions

Now that the MySQL server accepts remote connections, you need to grant specific users the ability to connect remotely.

1. Log in to the MySQL shell:

mysql -u root -p 

2. Grant permissions: The basic syntax for granting permissions is:


GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'remote_IP' IDENTIFIED BY 'password';

Replace database_name, username, remote_IP, and password with appropriate values.

For instance, to allow user john from IP 192.168.1.100 to access the sampledb database:


GRANT ALL PRIVILEGES ON sampledb.* TO 'john'@'192.168.1.100' IDENTIFIED BY 'your-password-here';

3. To allow a user to connect from any IP, use the wildcard %:


GRANT ALL PRIVILEGES ON sampledb.* TO 'john'@'%' IDENTIFIED BY 'your-password-here';

4. Reload the privileges:


FLUSH PRIVILEGES;

Step 4 of 5Testing the Connection

From a remote machine, use the mysql client or any MySQL-compatible tool:

mysql -h server_ip -u username -p 

Replace server_ip with the MySQL server’s IP address and username with the username granted access. You’ll be prompted for a password.

Step 5 of 5Security Considerations

  • Firewall: Ensure that the MySQL port (default 3306) is open on the server’s firewall. However, minimize the range of IPs that can connect if possible.
  • SSL: Consider setting up SSL for MySQL to encrypt data transfers.
  • Limit User Permissions: Only grant the required permissions, rather than ALL PRIVILEGES if not necessary.
  • Monitor Activity: Regularly monitor server and database activity for any unauthorized or suspicious activities.

Conclusion

Enabling remote MySQL connections can enhance your system’s flexibility, but it’s vital to ensure you don’t compromise on security. Always ensure that remote access is secured, monitored, and granted only to trusted entities.

Rahul

Rahul Kumar is the founder and chief editor of TecAdmin.net. A Red Hat Certified Engineer (RHCE), he has worked in IT since 2009, with hands-on experience in Linux system administration, DevOps, web hosting, cloud platforms and automation. He started TecAdmin in 2013 to share practical, step-by-step guides, and leads its editorial work with one aim: making technical learning clear and useful for sysadmins, developers and beginners alike.

Leave a Reply

Your email address will not be published. Required fields are marked *


Back to top