Development

How to Connect a Remote MySQL Database Using the Command Line

In this tutorial, we will learn how you can connect to a remote MySQL server without using tools such as SSH tunnels or pivoting.
Captain Salem 1 min read
How to Connect a Remote MySQL Database Using the Command Line
How to 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.

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.

Share
Comments
More from GeekBits

Join us at GeekBits

Join our members and get a currated list of awesome articles each month.

Great! You’ve successfully signed up.

Welcome back! You've successfully signed in.

You've successfully subscribed to GeekBits.

Success! Check your email for magic link to sign-in.

Success! Your billing info has been updated.

Your billing was not updated.