How To Connect To A Remote Database
WordPress lives in its database. The files and folders on your server are for appearances only. CSS, Javascript, and PHP files can usually be replaced from other sources. But your content - that is what makes your site special and unique. And that content lives in the database. If you had to pluck out one component of your WordPress site and carry it with you, it would be the MySQL database.
In this article, I'll show you how to connect to MySQL remotely. While it's possible to do this from cPanel using phpMyAdmin, it's a bit clumsy and requires you to go through several steps each time. By accessing the database from a local client, you can do things faster, and also set up complex operations like schedules etc. So let's get started.
Step 1: Download and Install HeidiSQL
When it comes to the choice of MySQL clients, my vote goes to HeidiSQL. It's smaller and less cumbersome than the MySQL workbench, so that's what I'll use in this tutorial. It's free and open source - what's not to love? You can download it from here. It's also available in the Windows store, so if you're on Windows 10, it might be easier to get it from there.
Step 2: Gather the Necessary Information
To make a remote connection to your MySQL database, you need the following pieces of information:
- Your database username
- Your database password
- The server hostname or IP address
- Your LOCAL IP address from which you will access the server
Get the Database Username and Password
Go into your WordPress root folder and open the file called "wp-config.php". Here, you will find your database username and password as shown here:
Get your Hostname or IP Address
Log into the cPanel interface of NameHero, and you will see your primary domain on the right-hand side as shown here. We'll use this as the hostname. You can also click the "Server Information" link on the bottom to get the IP address instead.
Get your Local IP Address
This is easy. Go to google.com, and type in "what is my ip". Google will respond with the IP address of your local system as shown here:
Step 3: Setting up Remote MySQL Access
By default, servers don't allow remote access to their databases except from specific IP addresses. So we need to add our IP address to the whitelist.
Log into your cPanel, and find the icon titled Remote MySQL as shown here:
In the following screen, add the LOCAL IP address you got in step 2 and click "Add Host".
You should see a confirmation screen as shown here:
Now we have permissions for remote access. Keep in mind that your local IP address can change at the whims of your ISP. It may not happen often, but it can happen. Also if you connect through a VPN, the IP address will change as well. So take all this into consideration!
Step 4: Set up a New Session with HeidiSQL
Open HeidiSQL and click "New" at the bottom left as shown here:
Enter a name for the new connection on the left-hand panel. On the right, enter the credentials you got in step 2. Namely, the IP address or hostname, your username, and the password for your database.
Now click the "Save" button for the session. Once done, click "Open".
If all goes well, it should initiate a remote MySQL connection to your database as shown here:
It will display a list of all the databases attached to your account. From here, you can run SQL queries, view data, take backups and restore stuff with the click of a button. It's much faster and easier to access than phpMyAdmin!
