MySQL database servers are essential components of millions of content management systems and eCommerce applications. It enables dynamic, personalized websites by storing and retrieving data in a secure and reliable manner. cPanel & WHM is a complete MySQL management solution that automates many common web hosting database tasks while providing web hosts with fine-grained control.
Websites and cPanel’s MySQL instance are typically installed on the same server. It is, however, sometimes necessary to separate them so that sites use a remote MySQL database hosted on a different server.
A remote MySQL server could be used to:
- Offload database workloads from the webserver to improve site and database performance.
- Use a server that has been configured specifically for database hosting.
- Consolidate database management across multiple servers.
- Isolate the database from servers that can be accessed via a public IP address.
The incorrect method for achieving remote database access is to expose the MySQL server to the internet and allow connections from untrusted IP addresses. As you might expect, this method introduces a critical security flaw that is frequently exploited to steal sensitive data.
In this article, we’ll show you how to do it right by securely configuring MySQL to accept connections from web applications hosted on different servers using cPanel.
Using a Remote Database to Host Your Site’s Data
You will need the following items to complete this tutorial:
- A server that has cPanel and WHM installed. This is going to be the remote database server.
- IP address or domain name of the database server, cPanel authentication credentials, and MySQL username and password
- A server on which web applications can be installed. This method will work if you install and configure sites manually, but it is faster and more convenient with cPanel.
Before we look at WHM’s MySQL Profile management tools, we’ll explain how cPanel users can configure a remote MySQL database.
Configuring a Remote MySQL Database with cPanel
Log in to cPanel on the server hosting the MySQL instance to which you want to grant access. From the main page menu, select the Remote MySQL® tool.
Enter the domain name or IP address of the server that hosts the web application in the Add Access Host form. To allow multiple IP addresses to access the database, use a wildcard (percent), such as 192.68.0 percent. When using a wildcard, make sure to limit the scope to addresses that you control or are confident do not pose a security risk.
When you click the Add Host button, cPanel configures the MySQL database so that it can accept requests from the remote site.
Next, you should configure the site to use the remote database. The procedure varies according to the application. On a newly installed WordPress site, for example, you would first create the database using cPanel’s MySQL® Database Wizard.
Then, modify wpconfig.php on the WordPress site 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
MySQL can also be configured to accept incoming connections from sites hosted elsewhere. When it comes to remote database access, there are some distinctions between cPanel and WHM:
- WHM hosts are applied to all cPanel user accounts as well as the MySQL users associated with those accounts.
- Users of cPanel cannot permanently delete hosts added by system administrators in WHM.
Navigate to Additional MySQL Access Hosts in the SQL Services section of the sidebar menu to allow remote hosts to access MySQL databases.
Enter the domain names or IP addresses of remote hosts who should be permitted to connect to the server’s databases, then click Save. To start using databases on the server, configure your sites with the appropriate IP address and authentication credentials, as described in the preceding section.
Managing MySQL Profiles in WHM
Server administrators can create MySQL profiles in WHM to connect with remote database servers in addition to configuring remote databases in cPanel and WHM. A MySQL Profile allows administrators to define the remote database that is used throughout cPanel and WHM; in fact, a profile is used to configure the local machine’s default database.
Profiles can be used to configure a variety of remote databases, including those running on other cPanel & WHM servers, dedicated MySQL servers, and Amazon’s Relational Database Service.
Before creating and activating a new profile, keep in mind that only one MySQL profile can be active at any given time. Log in to WHM and select Manage MySQL® Profiles from the SQL Services sidebar menu to create a new profile.
WHM can access the server, create a new MySQL superuser for database management, and configure MySQL. You can enter those details instead if you have already configured the MySQL installation with a superuser.
Once the profile is activated, new databases are created on the remote server. Existing databases, on the other hand, are not automatically migrated, and you must manually transfer data.
When it comes to configuring and managing MySQL databases, cPanel and WHM provide web hosts and their clients with tremendous flexibility. They can benefit from:
- Outstanding default settings for hosting MySQL databases on a local server.
- Simple database setup for sites and stores hosted on remote servers.
- System administrator tools that give administrators complete control over MySQL and the location of their databases.