The MySQL database server is a core component of millions of content management systems and ecommerce apps. It provides the secure and reliable data storage and retrieval that makes dynamic, personalized websites possible. cPanel & WHM is a complete MySQL management solution that automates many common web hosting database tasks while offering fine-grained control to web hosts who need it.
Typically, websites and cPanel’s MySQL instance are installed on the same server. However, it’s sometimes useful to separate them so that sites use a remote MySQL database hosted on a different server.
You might use a remote MySQL server to:
- Offload database workloads from the webserver to improve the performance of busy sites and the database.
- Use a server specifically configured for database hosting.
- Centralize database management for multiple servers.
- Isolate the database from servers accessible via a public IP address.
The wrong way to achieve remote database access is to expose the MySQL server to the internet and allow it to accept connections from untrusted IPs. As you may appreciate, this method creates a critical security vulnerability that is often exploited to steal sensitive data.
In this article, we’ll show you how to do it the right way, using cPanel to securely configure MySQL to accept connections from web applications hosted on different servers.
Using a Remote Database to Host Your Site’s Data
To follow this tutorial, you will need:
- A server with cPanel & WHM installed. This will be the remote database server.
- The database server’s IP address or domain name, cPanel authentication credentials, and MySQL username and password.
- A server on which to install web applications. The method we’re about to describe will work if you install and configure sites manually, but it is faster and more convenient with cPanel.
First, we’ll explain how cPanel users can configure a remote MySQL database before looking at WHM’s MySQL Profile management tools.
Configuring a Remote MySQL Database with cPanel
Log in to cPanel on the server with the MySQL instance you would like to grant access to. Select the Remote MySQL® tool from the main page menu.
In the Add Access Host form, enter the domain name or IP address of the server that hosts the web application. You can add a wildcard (%) to allow multiple IP addresses to access the database; for example, 192.68.0%. When using a wildcard, be sure to limit the scope to addresses under your control or that you are confident pose no security risk.
Click the Add Host button, and cPanel configures the MySQL database to accept requests from the remote site.
Next, you should configure the site to use the remote database. The process differs depending on the application. For example, on a newly installed WordPress site, you would first create the database in cPanel’s MySQL® Database Wizard.
Then, edit the WordPress site’s wpconfig.php to include the database name and authentication credentials, as well as the server’s domain name or IP address.
define( 'DB_NAME', 'wp_database' );
define( 'DB_USER', 'user' );
define( 'DB_PASSWORD', 'a-secure-password' );
define( 'DB_HOST', '203.0.113.0' );Adding MySQL Access Hosts in WHM
System administrators can also configure MySQL to accept incoming connections from sites hosted elsewhere. There are some differences between cPanel and WHM where remote database access is concerned:
- Hosts added in WHM are applied to all cPanel user accounts and the MySQL users associated with those accounts.
- cPanel users cannot permanently remove hosts added by system administrators in WHM.
To allow remote hosts to access MySQL databases, navigate to Additional MySQL Access Hosts in the SQL Services section of the sidebar menu.
Enter the domain names or IP addresses of remote hosts that should be allowed to connect to the server’s databases and click save. To begin to use databases on the server, you should configure your sites with the relevant IP address and authentication credentials, as described in the previous section.
To learn more about database access hosts, take a look at the feature’s documentation page.
Managing MySQL Profiles in WHM
In addition to configuring remote databases in cPanel & WHM, server administrators can create MySQL profiles in WHM to connect with remote database servers. A MySQL Profile allows administrators to define the remote database used throughout cPanel & WHM; in fact, the local machine’s default database is configured via a profile.
Profiles can be used to configure several types of remote databases, including those running on other cPanel & WHM servers, dedicated MySQL servers, and databases running on Amazon’s Relational Database™ Service.
Before you create and activate a new profile, you should be aware that only one MySQL profile can be active at any time. To add a new profile, log in to WHM and navigate to Manage MySQL® Profiles under SQL Services in the sidebar menu.
WHM can log in to the server, create a new MySQL superuser to manage databases, and configure MySQL. If you have already configured the MySQL installation with a superuser, you can add those details instead.
Once you have activated the profile, new databases are created on the remote server. However, existing databases are not automatically migrated, and you will need to transfer data manually.
cPanel & WHM gives web hosts and their clients incredible flexibility when configuring and managing MySQL databases. They can take advantage of:
- Outstanding out-of-the-box defaults for hosting MySQL databases on the local server.
- Easy database configuration for sites and stores hosted on remote servers.
- System administrator tools that give admins complete control over MySQL and where their databases are hosted.
As always, if you have any feedback or comments, please let us know. We are here to help in the best ways we can. You’ll find us on Discord, the cPanel forums, and Reddit. Be sure to also follow us on Facebook, Instagram, and Twitter.