JIYIK CN >

Remote connectivity is required when we work in a distributed environment. This tutorial is a step-by-step guide on how to connect to a remote MySQL database using the command line.

Connect to a remote MySQL database using the command line

Before you proceed, it's necessary to have a few things in place - access to a command line or terminal window and a MySQL server running in another location (the remote MySQL server).

rootWe also need or sudopermissions on both the remote and local machines . If you don't have direct access to the MySQL server, you can also sshconnect remotely using .

If you want to sshconnect using , you can use this article.

This tutorial will guide you to establish a remote connection to a MySQL database using the command line. It includes the following steps.

Below are the details for each step.

Edit MySQL configFiles

Open the file in a text editor using the following command config. We use nanothe ./opt/text editor, but you can use any text editor you feel comfortable with.

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

Suppose we forget or do not know configthe path of the file, then we can use the find command to know configthe location of the file. configAfter opening the file, search for [mysqld]the part.

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

What is the reason for the Update option? Due to the default settings, we can connect to this server bind-addressusing our local computer with IP address .127.0.0.1

We allow all machines to remotely connect to this server by #commenting this option with a -sign or replacing its value with 0.0.0.0.

If we only want to connect to one machine remotely, we can write our host's IP address instead 0.0.0.0. After making changes, save and exit the file.

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

sudo systemctl restart mysql

Configure the firewall to allow remote connections

configWhile configuring in the mysql.port file , bind-addressyou might have observed that the default MySQL port is 3306. Here, we will adjust the settings to allow remote traffic to MySQL’s default port, which is 3306.

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

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

sudo ufw allow from your_remote_ip_address to any port 3306

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

Allow Root Remote Login

Due to its default settings, we can connect to as using our local computer root. We need to make some changes to allow remote machines to rootconnect as .

To do this, use the following command.

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

Then, systemctlrestart the MySQL server using as shown below.

sudo systemctl restart mysql

Connecting to a Remote MySQL Server

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

mysql -u your_username -h your_mysql_server_ip -p

Here, your_usernamereplace with your username and your_mysql_server_ipwith your IP. You can also use your MySQL server's IP hostname.

-pYou will be asked to enter the password for the username you used in the command given above.

If you have done everything correctly, you will end up connected to the remote MySQL server. To grant remote access to the database, we can execute the following query.

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

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

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