How to Access a Remote MySQL Database via XAMPP's phpMyAdmin

Intro

I’ll start off by saying, Linux users, this one’s for you! Ever worked on a project, created a remote SQL database but you’re unable to view your database structure or the data in it? Here is one way you can solve that problem.

Problem Statement

Windows users have Navicat to thank for solving this problem for them, but we Linux users, Navicat is a pain to install, and even if we get the installation right, its just a hassle altogether.

Encountering this sort of problem can would occur if you’re using a service like Aiven for your data storage, you will need to have a way to view your data in the database and have a clear view of what your logic is populating your database with and not just keep building based on speculation.

While building the backend for my project, I got stuck in that corner and I had to halt the project to figure it out because of how slow i was due to the amount of care and checks I had to carry out to be certain the structure I had set up in my Prisma schema was as accurate as I wanted it to be. Luckily for me, it took me just one evening to figure out, with the help of my dev friend pythonvista of course.

Solution

There are a few things you need to look out for in order to successfully set up this configuration:

• Your Aiven connection information.

• Your Aiven CA certificate file (download this from the list of connection information Aiven gives you).

• Ensure you have MySQL installed.

• Install XAMPP if you don’t already have it installed and ensure your servers are running.

• Find and open up your php.ini and config.inc.php files (you will find them in the /opt/lampp/ directory).

We will not be covering the in-depth installation steps for MySQL or XAMPP because I assume if you’ve made it far enough to run into this issue or need this solution, you should as well already know how to install and set up those, but here is a link below to guides I use if I’m setting them up on a new pc, I’ve referred to these guides for years: How to Install MySQL on Ubuntu – Step-by-Step Guide, How to Install XAMPP on Ubuntu.

Identifying your Aiven connection information

When you visit your Aiven dashboard and navigate to your project, in the overview tab, you will find your connection information. This is the most important bit of information for this configuration, because without it, you don’t have a remote database to connect to. Of course there are other services out there but this write up will only cover the use of Aiven for this.

Be sure to download your CA certificate file and move it to your /opt/lampp/ directory.

Finding and opening your php.ini and config.inc.php files

Remember this directory /opt/lampp/ we talked about earlier? After navigating to that directory, search for php.ini and open it up.

Then do the same for config.inc.php.

Step-by-Step Configuration for MySQL/MariaDB

Now that we have those both open, there are relevant extensions that must be enabled in the php.ini file. Locate the [Dynamic Extensions] section in your php.ini file and uncomment or add the following lines to enable the mysqli and/or pdo_mysql extensions:

extension=mysqli
extension=pdo_mysql

Also ensure the extension_dir is correctly set to the directory containing the extension files:

extension_dir = "ext"  ; or absolute path, e.g., "C:\php\ext"

Now we need to configure the [MySQLi] settings. In this section, you can optionally set defaults for the connection (though these are often specified in your PHP code):

mysqli.default_host = "remote.database.host"  ; e.g., "db.example.com" or IP address
mysqli.default_port = 3306                   ; Default MySQL port
mysqli.default_user = "your_username"        ; Optional, not recommended for security
mysqli.default_pw = "your_password"          ; Optional, not recommended for security

Now we will replace those values with the information from our Aiven project overview that we took a look at earlier. If you save this configuration so far and restart your server, you will get this error when you try loading your phpMyAdmin:

The error messages indicate a connection timeout and invalid settings when attempting to connect to the Aiven MySQL dtabase. Based on the provided php.ini and the unconfigured phpMyAdmin configuration files (config.inc.php).

Aiven MySQL typically requires SSL for external connections. The current configuration does not specify SSL settings, which might cause the connection to fail if Aiven enforces SSL.

The config.inc.php file uses auth_type = ‘config’, which hardcodes the username and password. If these credentials or the host are misconfigured, it will fail.

Enable SSL in phpMyAdmin Configuration

Since Aiven likely requires SSL, update config.inc.php to include SSL setings. Add the following lines under the server configuration:

$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['ssl_ca'] = '/path/to/ca-cert.pem'; // Replace with the path to Aiven's CA certificate
$cfg['Servers'][$i]['ssl_verify'] = true;

Remember the CA certificate file we downloaded? We need to update that path accordingly to point to that file. It ought to look something like this:

$cfg['Servers'][$i]['ssl_ca'] = '/opt/lampp/ca.pem';

If the port differs (e.g., Aiven provides a non-standard SSL port), update:

$cfg['Servers'][$i]['port'] = 18519;

Now that all that configuration is done, restart your web server and you’re good to go!

Conclusion

In wrapping up our little adventure into the world of remote MySQL database access via XAMPP's phpMyAdmin, it's clear that with the right tools and a sprinkle of patience, even the trickiest database dilemmas can be tamed. So, Linux warriors, fear not! With your newfound skills, you can now peek into your database realms with ease, ensuring your projects are as polished as a freshly compiled kernel. Keep exploring, keep coding, and remember, every challenge is just another opportunity to level up your tech game!