How to Allow Remote Connections to MySQL

A distributed environment is hardly a new notion in the world of software. For teams working with databases, it means that there's no need to host MySQL servers and databases on the same local machine. With a dedicated MySQL server, developers no longer need to store local copies of databases; rather, they access a centrally maintained database serving as a single source of truth. Working via remote access to a MySQL server enhances team collaboration, supports multi-environment setups, enables effective integrations with cloud and hybrid systems, and provides scalability.

In this article, we explain how to allow remote access to MySQL servers and databases, manage user privileges, and, most interestingly, how to get everything done most easily using dbForge Studio, a specialized IDE for MySQL databases.

What is remote MySQL access, and why enable it

Remote access allows users to connect to a MySQL database server from their computers or networks. Rather than maintaining a local database copy, with remote access, users access the database at the server's IP address or domain name.

In software development, there are many cases when the ability to connect to a MySQL server remotely can be useful:

• Development teams. Teams working on the same project can connect to a central MySQL server from different locations. Such a setup improves collaboration and accelerates the project completion, as all team members always have access to the up-to-date data with no need to copy and store the database on their local devices.
• DevOps and system administrators. Remote access to a central database enables automated deployment, monitoring, and backup. Besides, it supports setting up cloud and hybrid environments, where the database and the applications are hosted on different servers.
• Solo developers and analysts. Individual users working on solo projects can benefit from an opportunity to connect to a remote MySQL server and access cloud-hosted databases to query data and test applications.

With all the convenience provided by the remote access to MySQL, it carries certain potential security risks:

• Unauthorized access. When the database port is opened to the public, it increases the risk of exposing the database to outside attacks. To prevent them, make sure to use strong passwords and practice the least-privilege access principle.
• Data exposure. To secure data in transit, always use encryption, which makes it unusable to cyber criminals.

When setting up remote access to a MySQL server, take into account the following performance considerations:

• Network latency. Querying a remote server may be slower than using a local database. To compensate for the network latency, consider data caching or replication.
• Bandwidth limitations. To maintain performance while managing large data transfers, use compression or set up large data processing closer to the database.
• Connection issues. Multiple remote sessions can cause unnecessary overhead. To avoid it, use connection pooling.

Prerequisites for enabling MySQL remote connections

Before proceeding to allow remote connections to MySQL, make sure that your MySQL installation is properly configured:

Required software and access:

• MySQL Server is installed and running on the host machine.
• Root privileges are granted to allow editing MySQL server configuration files and restarting the MySQL service.
• Network access to the MySQL port (default: 3306) is available between the client and server machines.

Firewall settings:

• Inbound connections to TCP port 3306 are allowed.
• As an additional security layer, access only from specific IP addresses can be allowed.

Environment differences:

MySQL servers hosted on Linux and Windows systems have different configuration file locations and flows for firewall rules setting.

How to enable and set up a remote MySQL server connection

1. First, let's set up the MySQL server installed on a Linux or Windows machine to make it allow remote connections. For that, you need root access to the server.

To enable remote access for a root user, run the following SQL command on the server:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
    

2. Now you need to verify that the MySQL server allows connections from the IP addresses of machines that will be able to gain remote access to the MySQL server. In MySQL versions 8.0 and above, the server is configured to allow connections from all IP addresses. If you need to disable some IP addresses for security reasons, use the firewall settings or enable the skip-networking option.

If, however, your server does not allow access from all IP addresses by default, you need to change the bind-address IP (the bind variable) in the MySQL config file. If you have a Linux machine, launch your CLI and run the following command:

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

If you have a Windows machine, the file name will be my.ini, and it will be located in the MySQL installation directory, usually C:\Program Files\MySQL\MySQL Server XX\. The exact location of the configuration file may vary depending on your MySQL version, so you may need to adjust it accordingly.

Open the configuration file and look up the bind-address line. The default IP is 127.0.0.1, which enables access from localhost only (bind 127.0.0.1). You need to set a new IP to match the address of the computer that will be able to gain remote access to the MySQL server. Note that setting it to 0.0.0.0 will enable access from all hosts. When done, save and close the configuration file.

3. Restart the MySQL server to apply the changes. On Linux, run the following command:

sudo systemctl restart mysql
    

On Windows, launch the command prompt, switch to your MySQL directory, and run the following command, specifying your MySQL version:

net stop MySQL80
net start MySQL80
    

Alternatively, you can locate the MySQL service in Services (services.msc) and restart it.

How to grant and set up user access from the remote host

You can enable remote access to a remote user by reconfiguring that account to connect from the remote server instead of localhost. Open your MySQL client as a root/sudo user:

sudo mysql

If you have enabled password authentication for root access, run the following command to access the MySQL shell:

mysql -u root -p

You can allow remote connection for a locally connected user using the RENAME USER command (having specified the user name and the remote server IP beforehand):

mysql> RENAME USER 'user_name'@'localhost' TO 'user_name'@'remote_server_ip';

Alternatively, you can create a new user to connect directly from the remote host:

mysql> CREATE USER 'user_name'@'remote_server_ip' IDENTIFIED BY 'password';

Finally, you need to grant privileges to the remote user. Since it is generally not recommended to grant ALL privileges, it is better to specify the required ones, for instance:

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'user_name'@'remote_server_ip' WITH GRANT OPTION;

Afterwards, you can run the FLUSH PRIVILEGES command, which will free up the memory cached by the server following the CREATE USER and GRANT statements:

mysql> FLUSH PRIVILEGES;

How to connect to a MySQL database remotely

When the MySQL server has been set up to enable MySQL remote access, let's test the connection. We recommend three tools that help you establish a remote connection to the MySQL server: the MySQL CLI client, MySQL Workbench, and dbForge Studio for MySQL. Each tool allows you to effectively connect to the MySQL server.

How to connect to a MySQL server remotely using dbForge Studio for MySQL

Among the three connection methods mentioned above, we would like to focus on dbForge Studio for MySQL by Devart. It is a comprehensive MySQL database management environment, offering far superior capabilities than those of similar tools like MySQL Workbench.

dbForge Studio for MySQL offers a smart and intuitive wizard that guides you through the setup of a remote host and database connection, as well as connection to the local server.

To connect to a remote MySQL server, choose New Connection from the Database menu and specify your connection settings. If the MySQL server has been properly configured to enable MySQL remote access, clicking Connect establishes a connection to your database.

How to reduce Internet traffic when connecting to a remote host

One of the most frequently observed database bottlenecks encountered by large projects is high MySQL traffic. To maintain the performance of your application, you can invest in a larger cloud instance, faster cores, and more efficient storage. However, you can try to reduce your database traffic by optimizing the configuration and implementing the following best practices:

• Disable and/or optimize high-load features of your application.
• Use ProxySQL to enable connection pooling. This will prevent your application from overloading MySQL with multiple concurrent connections. Besides, ProxySQL can cache query results for a certain period of time.
• Monitor your databases for performance bottlenecks.
• Identify and optimize queries that cause high load. If you use dbForge Studio for MySQL, it comes with a specialized tool— Query Profiler—that helps you manage queries easily.
• Add missing indexes and eliminate redundant and unused ones.
• Analyze your background operations and check if any of them can be postponed or run on replicas. Some operations can work just as effectively with limited resources, for example, with limited concurrences for batch jobs.

Securing your remote MySQL connection

Allowing remote access to a MySQL server always carries security risks, as such a configuration exposes data to external networks, creating potential breach opportunities. However, you can strengthen the security of your data by following the recommendations below.

Disable remote root access

The root account has administrative privileges and unrestricted access to all databases and settings. To prevent interception of the root account by attackers, take measures to limit root access:

• Restrict the root access to localhost only:

  UPDATE mysql.user 
  SET host = 'localhost' 
  WHERE user = 'root';
  FLUSH PRIVILEGES;
  

• Make sure no root wildcards — usernames containing % characters — exist:

  DROP USER 'root'@'%';
  

Use SSL for encrypted MySQL connections

Always encrypt remote MySQL connections to prevent data theft while in transit. SSL/TLS protocols provide reliable encryption for transmitted data, ensuring that it remains unreadable when intercepted. In addition, SSL encryption helps you maintain compliance with regulatory requirements, such as GDPR, HIPAA, or PCI-DSS.

dbForge Studio for MySQL provides an effective method of enforcing SSL encryption for connections to MySQL databases, protecting data from man-in-the-middle attacks and ensuring data integrity.

Troubleshooting remote MySQL access

If remote connections to your MySQL server fail repeatedly, it may be caused by the following issues:

• Firewall restrictions. If your firewall blocks port 3306, remote users will experience connection time-outs and receive errors on attempts to access the MySQL server. Make sure that port 3306 is open on your server firewall.
• Incorrect or missing host permissions. If a remote user has no permission to access the MySQL server, they will be denied access. Grant the necessary permissions to users that should be allowed to connect.
• Bind-address conflicts. When the MySQL server is configured to listen only on localhost, remote connections are rejected. Check the bind-address settings in the server configuration file and adjust them to allow remote connections.
• DNS resolution issues. DNS settings may prevent clients from resolving the MySQL server's name, blocking the connection. Try using the server's IP address instead of the name, or check if DNS settings are correct.

The following tools can help you troubleshoot remote MySQL connections:

• ping: Checks basic server availability.

  ping servername.com
  

• telnet: Tests whether port 3306 is open.

  telnet servername.com 3306
  

• netstat: Checks that the MySQL server is listening to external connections.

  netstat -an | find "3306"
  

• MySQL command line: Displays the port that accepts remote connections.

  SHOW VARIABLES LIKE 'port';
  

Conclusion

Configuring remote access to a MySQL server is an essential step in setting up a distributed development environment for improved flexibility and performance. However, enabling remote connections to a central MySQL database requires careful attention to security and data protection.

With proper server settings, correct user permissions, and strong data encryption, you can create an efficient MySQL environment that promotes collaboration, improves team performance, and ensures data security. If you are wondering how to allow MySQL remote connections safely and efficiently, try dbForge Studio for MySQL — a comprehensive IDE for MySQL database development and management, where you can set up your MySQL server and clients to enable effective remote access to the database.

FAQ

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;