How to Allow Remote Connections to MySQL?

Introduction 

Do you know MySQL database is built to run independently? Well, this system allows easy access to clients from any location. But what exactly is MySQL, and what is the importance of MySQL remote access? MySQL is an open-source RDBMS ( relational database management system). An RDBMS acts like a digital cabinet where you can store all your information. Moreover, this digital cabinet allows you to organize the information as per your choice. The organizational feature makes MySQL a pro at handling large datasets. 

As far as the importance of remote access is concerned, we will discuss this in the next heading with a simple yet effective example. You need to go through the entire article to understand how MySQL allows remote connections and methods to provide remote access to existing and non-existing databases. 

MySQL Server Remote Connection 

Before delving deeper, let’s explore the significance of MySQL remote connection through a simple example. Imagine you own a clothing factory that sources raw materials such as fabric, threads, and buttons from various locations. 

Similarly, in a complex application, different segments of the software are allocated to separate servers to accommodate the size of the database. This could apply to a banking website or a social media platform, for instance. With multiple servers managing operations, remote access is necessary. Now we will look at the steps in detail to set up MySQL remote access.

Step 1: Edit MySQL Config File 

• Every operating system has a different location of the configuration file. So your first task is locating the configuration file per your OS. Go through the below locations to complete the task quickly.

Linux: '/etc/mysql/my.cnf or /etc/my.cnf'

macOS: '/usr/local/mysql/my.cnf'

Windows(WAMP): 'C:\wamp\bin\mysql\mysql{version}\my.ini'

• Now you will have to open the configuration file through a text editor. If you are using Linux, then the below command will help you open the configuration file.

vi /etc/mysql/my.cnf

• Now comes the most important task of finding the bind-address parameter. For those who don’t know about the bind-address parameter, take it as a door that connects your computer to outside visitors. By default, this bind-address parameter is set to 127.0.0.1 (allowing the entry or access of only local machines). When MySQL allows remote connections, this bind-address needs to be changed. You should try to set the bind address parameter to a specific IP address to avoid security risks. Use the below command to do so. 

bind-address = 192.168.1.100

• Now simply save the file and exit from the text editor.

• MySQL requires restarting the server to apply the changes. You can use the below command on a Linux system. 

sudo service mysql restart

•  That’s it; the MySQL configuration file is edited successfully.  

Step 2: Set up a Firewall to Allow Remote MySQL Connection 

When you set up a firewall to allow remote connection, you need to make sure that the default port ‘3306’ is in use for incoming connections. Now, what exactly is a port, and how does an incoming connection relate to it? 

To explain ports simply, let’s use an example of a corporate building with ten different rooms, each assigned to a specific department like marketing or finance. Similarly, a port is like a room number assigned to a computer. When an external computer wants to connect to the MySQL database on your server or computer, it needs to provide the correct port, as each port has a different function. The connection between the external computer and your server or computer with the database can be referred to as an incoming connection. Now let us move forward to the steps to set up the firewall. 

Ensure that the default port ‘3306’ is active and ready to take the incoming connections. Now before moving to further steps, you need to check whether the firewall is enabled or no; if not, then quickly enable the firewall. 

Also Read: How To Remove or Delete MySQL User or Database Account?

Option 1: UFW (Uncomplicated Firewall) 

The ‘ufw’ firewall utility is used when you are using Ubuntu or Debian-based operating systems. You can use the below command for Linux.

sudo ufw allow 3306

Option 2: FirewallD 

FirewallID firewall utility is used for CentOS and Red Hat based operating systems. The command will be as follows.

sudo firewall-cmd --new-zone=mysqlrule --permanent

sudo firewall-cmd --reload

sudo firewall-cmd --permanent --zone=mysqlrule --add-     source=133.155.44.103

sudo firewall-cmd --permanent --zone=mysqlrule --add-   port=3306/tcp

sudo firewall-cmd --reload

Option 3: Open Port 3306 with iptables 

To open port 3306 on a Linux system using iptables, you need to have root or administrative privileges. Follow these steps to do it:

• First, make sure iptables is installed on your system. Most Linux distributions come with iptables pre-installed. You can check its status using the following command:

iptables --version

• Before making changes, it’s a good practice to create a backup of your current iptables rules in case anything goes wrong. You can use the following command to back up the rules to a file:

iptables-save > iptables_backup.txt

• To open port 3306 for incoming connections, add a rule to allow incoming traffic on that port. Use the following command:

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

• After adding the rule, save the iptables configuration to apply the changes:

iptables-save > /etc/iptables/rules.v4

• Some systems may require you to restart the iptables service for the changes to take effect. Use the appropriate command for your Linux distribution:

sudo service iptables restart # For CentOS 6 and earlier

sudo systemctl restart iptables # For CentOS 7 and later

Alternatively, you can reboot your system to apply the changes automatically.

Step 3: Connect to Remote MySQL Server 

Your remote server is now ready to accept connections. Use the following command to establish a connection with your remote MySQL server:

mysql -u username -h mysql_server_ip -p

The -u username in the command represents your MySQL username. The -h mysql_server_ip is the IP or the hostname of your MySQL server. The -p option prompts you to enter the password for the MySQL username.

You should see an output similar to the one below:

Connection to mysql_server_ip 3306 port [tcp/mysql] succeeded!

How to Grant Remote Access to New MySQL Database? 

If you do not have any databases yet, you can easily create a database by typing the following command in your MySQL shell:

CREATE DATABASE 'yourDB';

To grant remote user access to a specific database:

GRANT ALL PRIVILEGES ON yourDB.* TO user1@'133.155.44.103' IDENTIFIED BY 'password1';

The name of the database, the username, the remote IP, and the password need to match the information you want to use for the remote connection.

Also Read: How to Check the MySQL Version with Command in Linux?

Conclusion

MySQL is a powerful and open-source relational database management system that can help you store and organize information digitally. It is a popular choice for websites and software because it can handle large datasets and manage complex applications efficiently.

One of MySQL’s most important features is its ability to support remote connections. This allows different parts of your program to interact and manage operations efficiently. It is similar to a clothing factory sourcing materials from different locations or a complex software application distributed across multiple servers.

To enable remote access to MySQL, you need to adjust the MySQL configuration file to allow the server to listen for connections from external devices. It is also crucial to set up a firewall to control and secure incoming connections on the MySQL port (usually 3306) from specific IP addresses or networks. This way, only trusted sources can access the MySQL server remotely, reducing the risk of unauthorized access and potential threats.

In summary, MySQL’s remote access feature is essential for managing complex applications and allowing remote clients or servers to interact with the database securely. With the correct configuration of MySQL and the firewall, efficient communication and collaboration between different parts of your software infrastructure can be achieved while maintaining data security and integrity.