Connect a Remote MySQL Database Using the Command Line

Before you can execute commands on the MySQL Server, you need to connect to the target database first. In some cases, you may be connecting to a remote MySQL server.

In this tutorial, we will learn how you can connect to a remote MySQL server without using tools such as SSH tunnels or pivoting.

NOTE: Ensure that the MySQL server is running and accepting remote connections. Similarly, ensure that your firewall allows connections to the MySQL server.

Connect to the Remote MySQL Server

To connect to a remote MySQL Server, you can use the mysql utility as shown in the syntax below:

mysql -h {hostname} -P {port} -u {username} -p

Where the:

1. hostname - represents to the hostname or IP address of the remote MySQL server.
2. port - represents the port of the target MySQL Server.
3. username - specifies the username you wish to login.

For example:

mysql -h db.enc.geekbits.io -P 3306 -u root -p

The command above should login to the MySQL server on the specified hostname and port 3306. The command will prompt you for the root user password

Test Your Connection

Once connected, you should be greeted with a mysql> prompt. You can try to run a SQL statement to confirm everything is working fine.

For example, to show all databases, you can run:

SHOW DATABASES;

Select a Database

To interact with a specific database, you can select it using the USE statement.

USE <database_name>;

Replace mydatabase with your actual database name.

Exit MySQL Prompt

Once finished with your operations, you can exit the MySQL prompt by using the EXIT command

EXIT;

This will close the MySQL session and drop you back into your regular terminal session.

Troubleshooting

If you are unable to connect, you can check the following possible causes.

1. Your MySQL server is running and is accessible over the network.
2. The IP address of your client machine is allowed to access the MySQL server.
3. Your MySQL username and password are correct.
4. Your MySQL user has the right privileges.
5. The MySQL port (usually 3306) is open in your server’s firewall.

Conclusion

In this tutorial, we discussed how you can use the mysql CLI utility to connect to a remote MySQL server by specifying the target hostname/IP address and the server port.