How To Enable Remote MySQL Access

Advanced members with knowledge on database, programming and security might want to enable remote MySQL access to their site because of faster development, quicker access or any other reason that would be related to the need of having remote access to the database. With this is mind, we at least have to add a warning on the dangers of enabling remote MySQL access on your site:

Enabling remote access to your database means that you are accepting the fact that you would be jumping several layers of security that make your site safer in many ways and offer you peace of mind as a partner. Some of the issues you could have and that have happened in the past because of remote access, some not even related directly with the partner's but still caused by enabling remote access are:

• 3rd party using partner's PC (Kids, family, friends) and deleting wrong files or changing wrong information
• Running wrong SQL on database (Since partner will have full access including deleting database) like a DELETE, UPDATE or REPLACE statement.
• Updating with wrong query (For example "UPDATE users_data SET first_name='John' WHERE country_code = 'US'" instead of using the id of the user)
• DB Corruption. During a long update/delete/insert and the connection drops from the client for some reason (ADSL issues, ISP problems, Electricity goes out, Godzilla attacks city, Aliens invade, etc..)
• Sanitation/optimization done when Partner was connected externally causing corruption (Cleaning the Database when partner was inserting data to it. Multiple users editing the same table, etc..)
• Middleman attacks (Like taking your sockets, being literally the middleman, grabbing all data including passwords...)
• Virus affecting client (Some viruses can detect and attack database apps). Almost all are Windows users.
• Any other ways that can create a huge problem for partner and company (Including grabbing all your clients accounts and hacking them)

Brilliant Directories does not take any responsibility if a situation arises and the database in compromised in some way (eg: corrupted, deleted, modified, etc..). If you still think you can prevent this type of problems and any other that could happened (We recommend making daily backups just in case), then feel free to follow this guide to have remote access on your site.

IMPORTANT NOTE: Before reading this guide be sure to send an email with your IP address to our support team in order to add the IP to a whitelist that will enable complete and unrestricted access to your database. Brilliant Directories does not offer database support nor does it support any problems that could appear because of the remote access.

Steps to Enable Remote MySQL Database Access

1. To have Remote MySQL access, we need 4 variables:

• MySQL Username (User that can connect to a database via MySQL)
• MySQL Password (Password for said user)
• MySQL Database Name (Database name where user can connect to)
• MySQL Hostname (Server where the database resides)

    In order to grab all 4 variables,  login and navigate to Developers - Cpanel.

**Important Note: The cPanel dashboard layout is continuously being updated, however, all the instructions presented in this article apply in the same way.

We recommend using the keyword search tool to find specific sections:

2. Once on your cPanel dashboard, click on Remote MySQL

3. Here we will be adding YOUR IP to the list of allowed IPs. If you do not know how to grab it, simply go to sites like whatsmyip, ipchicken, iplocation, whatismyip, wtfismyip, ipify, or simply Ask Google for your IP. One you have it we can proceed with this step. 

 For this example, my IP is 191.102.100.12, so we simply add it in the Add Access Host box. If we add 191.102.100.12, then only this IP has remote access to the database, but if you wish to offer access to a range of IPs, you can use the wildcard "%". So for example, in the image below you can see I used 191.102.%.%, which means, any IP that starts with 191.102 can access the database remotely.

4. After Adding in the IP and saving this, we can continue with adding the users (In case you don't have an external user assigned yet). For this we go back to the main cPanel dashboard and click this time on MySQL Databases.

5.  We now go to the MySQL Users section and on the Add New User we fill this with the information for the user.

6. The end result after filling this up should look something like the image below where you can see my new user is named "ninj2860_test", the password fields are filled and I simply click on the blue Create User button. If everything goes well you should see something similar to the 2nd image below.

7. Now we click on the Go Back link and go to the Add User to Database section. Here we select the user we just created and the database we want that user to have permissions on.

8. After clicking on Add, we are shown the following Permission Form. Here we select what permissions that user will have over the whole database. After selecting the permissions we wish to add to the user and clicking on Make Changes, we should see a message similar to the 2nd image below:

9. Up to this point we now have the MySQL Username, MySQL Password and MySQL Database Name. We are only missing the MySQL Hostname which can also be the IP of the server. If you know the IP of the server you can skip the following 3 steps including this one, if not, then we can learn how to search for the Hostname. So let's go to the cPanel Dashboard one last time and click on the PHPMyAdmin icon.

10. Click on Variables

    On the Containing the word, search for hostname. This will return the value for the MySQL Hostname we were looking for. We all 4 variables, we can now connect to our MySQL database.

11. In the following images, as an example, I am using MySQL Workbench to connect to my database using all 4 variables and adding my IP to the whitelist: