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)
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.
- 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)
2. Once on your cPanel dashboard, click on Remote MySQL
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:
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:
Hope this helps you enabling your Remote Access to your MySQL Database, but always remember to be careful.