How To Enable Remote MySQL Access
Link: https://support.brilliantdirectories.com/support/solutions/articles/5000681790
Advanced users with experience in databases, programming, and security may want to enable remote MySQL access for reasons such as faster development, easier external access, or other development-related tasks. However, it’s important to understand the risks associated with enabling this feature.
Allowing remote access to the database bypasses several built-in security layers that are designed to protect the site and its data. While it may offer flexibility, it also introduces potential vulnerabilities. Some of the common issues reported in the past from enabling remote access include:
- Accidental access or misuse by third parties using the authorized computer (e.g., family members or colleagues)
- Running incorrect SQL commands with full privileges (e.g., using DELETE, UPDATE, or REPLACE commands improperly)
- Mistakes in queries that unintentionally affect large portions of the database (e.g., updating multiple rows without using unique identifiers)
- Data corruption during long transactions if the connection is interrupted (due to local network issues, ISP outages, power loss, etc.)
- Conflicts during simultaneous access (e.g., editing tables while maintenance or optimization tasks are in progress)
- Man-in-the-middle attacks that intercept sensitive data, including passwords
- Malware or viruses affecting the database through vulnerable third-party software or infected systems
- Unauthorized access or manipulation of customer data, accounts, or credentials
Brilliant Directories is not responsible for any issues that arise from enabling remote MySQL access, including database corruption, loss, or unauthorized changes. If remote access is still preferred, it is strongly recommended to make regular backups to mitigate potential data loss.
Important Notes
- Before enabling remote access, contact the Brilliant Directories support team with the static IP address that will be connecting. The IP must be whitelisted to allow full access. Please note that Brilliant Directories does not offer database support or assistance with any issues that may result from enabling remote access.
- The cPanel dashboard layout is continuously being updated, however, all the instructions presented in this article apply in the same way.
Enable Remote MySQL Database Access
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
We recommend using the keyword search tool to find specific sections:
Once in the cPanel dashboard, click on Remote MySQL:
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.
For this example, the IP is 191.102.100.12, so we 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 to offer access to a range of IPs, use the wildcard "%".
For example, in the image below 191.102.%.%, was used which means that any IP that starts with 191.102 can access the database remotely.
After Adding in the IP and saving this, continue with adding the users (In case there isn't an external user assigned yet).
For this we go back to the main cPanel dashboard and click this time on MySQL Databases.
Now go to the MySQL Users section and on the Add New User we fill this with the information for the user.
The end result after filling this up should look something like the image below where the new user is named "ninj2860_test", the password fields are filled and click on the blue Create User button.
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.
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:
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 the IP of the server is known, 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.
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.
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: