How to connect to a MySQL database remotely

This guide outlines the process of creating a user on your MySQL® server to enable remote connections to a MySQL database. Please note that the instructions provided here are for connecting to a MySQL instance that is local to a server.

To proceed with these steps, you will need local server access to log in as the root MySQL user-

Save your IP address-
You must know the Internet Protocol (IP) address of the computer from which you are connecting. You can obtain this information by visiting one of the following websites-
https://icanhazip.com
https://www.whatismyip.com
Grant access- To grant access to a user from a remote host, follow these steps-
1. Log in to your MySQL server locally as the root user using the following command-
  mysql -u root -p
  You will be prompted to enter your MySQL root password.
  
  Note- If you are able to access MySQL without entering a password, it is recommended to run the mysql_secure_installation script to set a MySQL root password and update other settings to enhance security.
2. Use the GRANT command in the following format to enable access for the remote user. Replace 1.2.3.4 with the IP address you obtained previously, and my_password with the password you want fooUser to use-
  mysql> GRANT ALL ON fooDatabase.* TO fooUser@’1.2.3.4′ IDENTIFIED BY ‘my_password’;
  This statement grants all permissions to the new user when the user connects from the specified IP address using the specified password.
Test the connection remotely-
To test the remote connection, access the MySQL server from another Linux® server. In the example below, we use 33.44.55.66. as the IP address of the MySQL server-

# mysql -u fooUser -p –h 33.44.55.66.77
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.45 Source distributionType ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> _

This way, you can connect to a MySQL database remotely. If you no longer need to access your database remotely, you can remove a remote server’s access. This helps keep your database secure by only allowing authorized servers to interact with it.

Hope you liked our article.