How to Connect a Remote MySQL Database Using the Command Line

Remote connections are needed when we work in a distributed environment. This tutorial is a step-by-step guide that educates how to connect a remote MySQL database using the command line.

Connect a Remote MySQL Database Using the Command Line

Before moving on, it is necessary to have a few things first - access to the command line or terminal window and a MySQL server running on another location (a remote MySQL server).

We will also need root or sudo privileges on remote and local machines. You can also use ssh to remotely connect if you don’t have direct access to the MySQL server.

You can use this article if you want to connect using ssh.

This tutorial will guide you in establishing a remote connection with a MySQL database using a command line. It includes the following steps.

1. Edit the MySQL config file
2. Configure the Firewall to allow remote connections
3. Allow the root login remotely
4. Connect to a remote MySQL Server

The following is the detail for every step.

Edit the MySQL config File

Use the following command to open the config file in a text editor. We use a nano text editor, but you can use any text editor you feel comfortable with.

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

Suppose we have forgotten or don’t know the path of the config file, then we can use the find command to know the location of the config file. Once the config file is opened, search for the [mysqld] section.

Under the [mysqld] section, find bind-address and change its value from 127.0.0.1 to 0.0.0.0 or comment on this line using the # symbol.

What is the reason for updating the bind-address option? Due to default settings, we can connect to this server using a local machine whose IP address is 127.0.0.1.

We allow all the machines to connect with this server remotely by commenting on this option using the # symbol or replacing its value with 0.0.0.0.

If we want only one machine connected remotely, we can write our host machine’s IP address instead of 0.0.0.0. Once we are done with the changes, save them and exit from the file.

To apply the updates, restart the MySQL server using the following command.

sudo systemctl restart mysql

Configure Firewall to Allow Remote Connections

While configuring the bind-address in the config file, you may have observed that the default MySQL port is 3306. Here, we will adjust the settings to allow remote traffic to the default port of MySQL, which is 3306.

Before opening the traffic, we must configure the firewall properly. Once it is done, we can use the command given below to open the traffic for this particular port.

Since we are using Ubuntu 20.04, we will use ufw (uncomplicated firewall).

sudo ufw allow from your_remote_ip_address to any port 3306

As soon as we press Enter, we will see that the rules are updated successfully.

Allow Root Login Remotely

Due to its default settings, we can connect to root by using a local machine. We need to make a few changes to allow remote machines to connect as a root.

To do that, use the following commands.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
mysql> UPDATE mysql.user SET host='%' WHERE user='root';

Then, restart the MySQL server using systemctl as follows.

sudo systemctl restart mysql

Connect to a Remote MySQL Server

Once the remote MySQL server is ready to accept remote connections, we can use the following command to make a connection with our remote MySQL server.

mysql -u your_username -h your_mysql_server_ip -p

Here, replace your_username with your user name and your_mysql_server_ip with your IP. You can also use your MySQL server’s hostname.

The -p will ask you to enter the password for the username that you used in the command given above.

You will be finally connected to a remote MySQL server if you have done everything correctly. To grant remote access to a database, we can execute the following queries.

mysql> update db set Host='ip_address' where
    -> Db='yourDB_name';

mysql> update user set Host='ip_address' where
    -> user='username';

Replace the username, Host, and Db with your specified values. After that, you can now access the specified database from a remote location.