How To Connect To A Remote Database
The contents of your website consist predominantly of files and folders but almost all the content such pages, posts, theme data and plugin settings are stored in the 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!
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 11, it might be easier to get it from there.
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
Go into your WordPress root folder and open the file called "wp-config.php". In this file, you will find your database username and password as shown here:
Log into the cPanel interface of NameHero, and you will see your primary domain on the right-hand side. We'll use this as the hostname. You can also click the "Server Information" link on the bottom to get the IP address instead.
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:
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, select MySQL (SSH tunnel) as the Network type and enter the credentials you got in step 2. Namely, the IP address or hostname, your username, and the password for your database.
MySQL port 3306 is secured on all of our servers so it is required that you use an SSH tunnel to connect to your databases when accessing it remotely. This ensures your connections are safe.
HeidiSQL necessitates the utilization of plink, a compact application designed to establish connections through an SSH tunnel. In this case plink is not yet installed, you can initiate the process by simply clicking on the "Download plink.exe" hyperlink. It is advisable to save the program in a readily accessible location, such as c:\tools or a similar directory. The path to plink.exe should then be entered in the location field.
The host should be the same as mentioned before and the port number will be 37980 for SSH then enter the SSH username and password. This is the same as your cPanel username and password.
Now click the "Save" button for the session. Once done, click "Open"...HeidiSQL
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!
ℹ️ Please contact our support team if you haven't already to request SSH access be enabled on your account. You can refer to our article How To Connect To Your Website Via SSH And SFTP for SSH setup instructions. It's important to note that these instructions pertain exclusively to the process of connecting to your hosting administration through SSH and are unrelated to the database itself.
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 11, 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". In this file, 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. 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: 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, select MySQL (SSH tunnel) as the Network type and enter the credentials you got in step 2. Namely, the IP address or hostname, your username, and the password for your database.
Step 4. Edit the settings in the SSH tunnel tab
MySQL port 3306 is secured on all of our servers so it is required that you use an SSH tunnel to connect to your databases when accessing it remotely. This ensures your connections are safe.
HeidiSQL necessitates the utilization of plink, a compact application designed to establish connections through an SSH tunnel. In this case plink is not yet installed, you can initiate the process by simply clicking on the "Download plink.exe" hyperlink. It is advisable to save the program in a readily accessible location, such as c:\tools or a similar directory. The path to plink.exe should then be entered in the location field.
The host should be the same as mentioned before and the port number will be 37980 for SSH then enter the SSH username and password. This is the same as your cPanel username and password.
Now click the "Save" button for the session. Once done, click "Open"...HeidiSQL
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!
ℹ️ Please contact our support team if you haven't already to request SSH access be enabled on your account. You can refer to our article How To Connect To Your Website Via SSH And SFTP for SSH setup instructions. It's important to note that these instructions pertain exclusively to the process of connecting to your hosting administration through SSH and are unrelated to the database itself.
Updated on: 11/10/2024
Thank you!