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:
- hostname - represents to the hostname or IP address of the remote MySQL server.
- port - represents the port of the target MySQL Server.
- 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.
- Your MySQL server is running and is accessible over the network.
- The IP address of your client machine is allowed to access the MySQL server.
- Your MySQL username and password are correct.
- Your MySQL user has the right privileges.
- 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.
