The MySQL database server is a foundational component for countless content management systems and e-commerce applications. It delivers the secure and reliable data storage and retrieval capabilities essential for dynamic, personalized websites. cPanel & WHM offers a comprehensive MySQL management solution, automating many common web hosting database tasks while providing granular control for advanced users and web hosts.
While websites and cPanel’s MySQL instance are typically co-located on the same server, there are compelling reasons to separate them. Hosting a site’s data on a remote MySQL database server can offer significant advantages.
Why Use a Remote MySQL Server?
You might consider deploying a remote MySQL server to:
- Offload demanding database workloads from the webserver, thereby enhancing the overall performance of busy websites and the database itself.
- Utilize a server specifically optimized and configured for high-performance database hosting.
- Centralize database management operations across multiple web servers, simplifying administration.
- Enhance security by isolating the database from web servers that are directly exposed via a public IP address.
It’s crucial to understand that directly exposing a MySQL server to the internet and allowing connections from untrusted IP addresses is a critical security vulnerability. This insecure practice is frequently exploited to compromise sensitive data. This article will guide you through the correct and secure method for configuring MySQL, using cPanel to enable web applications hosted on different servers to connect safely.
Setting Up Your Environment for Remote Database Access
To successfully follow this tutorial and configure your remote MySQL database, you will need the following:
- A server with cPanel & WHM installed. This server will function as your dedicated remote database server.
- The IP address or domain name of your database server, along with your cPanel authentication credentials, and the necessary MySQL username and password.
- A separate server where your web applications will be installed. While the described method supports manual site installation and configuration, using cPanel on this server can streamline the process significantly.
We will begin by explaining how cPanel users can configure a remote MySQL database before exploring WHM’s more advanced MySQL Profile management tools.
Configuring a Remote MySQL Database with cPanel
To start, log in to cPanel on the server hosting the MySQL instance you wish to grant remote access to. From the main page menu, locate and select the Remote MySQL® tool.
Within the Add Access Host form, you will enter the domain name or IP address of the server that hosts your web application. For greater flexibility, you can include a wildcard character (%) to permit multiple IP addresses to access the database. For example, entering 192.68.0% would allow any IP address within that subnet. When utilizing a wildcard, always ensure you limit its scope to addresses under your direct control or those you are absolutely confident pose no security risk.
After entering the host information, click the Add Host button. cPanel will then automatically configure the MySQL database to accept connections from the specified remote site.
The next crucial step involves configuring your web application to utilize this remote database. The exact procedure varies depending on the specific application you are running. For instance, on a newly installed WordPress site, you would typically begin by creating the database using cPanel’s MySQL® Database Wizard.
Following database creation, you would then edit the WordPress site’s wp-config.php file. This file requires updates to include the newly created database name, authentication credentials (username and a secure password), and importantly, the remote database server’s domain name or IP address. An example configuration would look like this:
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 as an Administrator
System administrators have additional capabilities to configure MySQL to accept incoming connections from sites hosted elsewhere within WHM. There are a few key distinctions between remote database access configuration in cPanel and WHM:
- Hosts added by an administrator in WHM are applied universally to all cPanel user accounts and their associated MySQL users.
- cPanel users do not have the ability to permanently remove hosts that have been added by system administrators in WHM.
To enable remote hosts to access your MySQL databases, navigate to the SQL Services section in the WHM sidebar menu and select Additional MySQL Access Hosts.
Here, you will enter the domain names or IP addresses of any remote hosts that should be permitted to connect to the server’s databases. After entering the desired hosts, click Save. To begin using databases on this server, ensure your sites are configured with the appropriate IP address and authentication credentials, as detailed in the preceding section. For more in-depth information about database access hosts, consult the official feature documentation page.
Managing MySQL Profiles in WHM
Beyond simply configuring remote database access, server administrators can also establish and manage MySQL profiles within WHM to connect with various remote database servers. A MySQL Profile allows administrators to precisely define which remote database server cPanel & WHM will utilize across the system. In fact, even the local machine’s default database configuration is managed via such a profile.
These profiles are versatile and can be configured to integrate with several types of remote database solutions. This includes databases running on other cPanel & WHM servers, dedicated standalone MySQL servers, and even cloud-based database services like Amazon’s Relational Database™ Service (RDS).
Before proceeding with the creation and activation of a new profile, it is important to note that only one MySQL profile can be active at any given time. To add a new profile, log in to WHM and navigate to SQL Services in the sidebar menu, then select Manage MySQL® Profiles.
WHM provides the capability to log in to the remote server, create a new MySQL superuser for managing databases, and comprehensively configure the MySQL instance. Alternatively, if you have already set up the remote MySQL installation with a superuser, you can simply input those existing details.
Once you have successfully activated the new profile, all subsequent new databases will be created on the designated remote server. However, it is essential to remember that existing databases are not automatically migrated; you will need to transfer any existing data manually to the new remote server.
Conclusion
cPanel & WHM empowers web hosts and their clients with exceptional flexibility in configuring and managing MySQL databases. This robust platform facilitates:
- Outstanding out-of-the-box default settings for hosting MySQL databases directly on the local server.
- Straightforward database configuration for websites and online stores hosted on external, remote servers.
- Powerful system administrator tools that provide complete command over MySQL installations and the hosting location of their databases.
