Remote access to the MySQL database server is usually disabled for security reasons. Yet, there are occasions when you require remote access to a database server from either home or a web server. This article will explain how to setup a user on your MySQL server to connect remotely. The Steps for MySQL Access
In order to go through the steps, you must be able to have local server access to login as the “root” MySQL user.
Next you get your IP address, which is important because you must know the IP address of the computer you are working on. You can obtain this information by visiting one of the below websites:
https://icanhazip.com
https://www.whatismyip.com
Grant access to a user from a remote host by logging in locally to your MySQL server as the root user. This can be done by typing in the following command:
# mysql -u root -p
This will prompt you to provide your MySQL root password. (Also, if you get into MySQL without a password, consider running the ‘mysql_secure_installation,’ script, which sets a MySQL root password and ultimately increases security).
Next step is to issue the GRANT command which enables access for the remote user. The following example shows how a new user (oneUser) that will have full access to the oneDatabase database:
mysql> GRANT ALL ON fooDatabase.* TO fooUser@’1.2.3.4’ IDENTIFIED BY ‘my_password’;
Please note that this declaration is not complete and will need some changes. Change 1.2.3.4 to the IP address that we obtained above. You will also need to change my_password with the password that you would like to use for oneUser.
This statement allows ALL permissions to the newly created user with the stated password when the user links from the specified IP address.
Finally, you can test the connection remotely. To test the connection remotely, you may access the MySQL server from another Linux server.
Final Thoughts
When setting up remote users, always consider these items:
For one, a local user is not the same as a remote user. For example, oneUser@localhost is not the same as oneUser@1.2.3.4. If you want both users to have the same permissions, then the permissions will need to be duplicated.
It is not recommended to grant ALL permissions.
For a normal user, it is recommended to use GRANT SELECT,INSERT,UPDATE,DELETE. If you want to grant access to only a specific table, you can use database.table. And if you are utilizing iptables, then you must create an entry in your firewall for TCP port 3306. When you create the firewall rule you can easily use the name MySQL for the port number.