The MySQL database server is a foundational component for millions of content management systems and e-commerce applications globally. It offers secure and dependable data storage and retrieval capabilities, which are essential for powering dynamic and personalized websites. cPanel & WHM provides a comprehensive MySQL management solution that streamlines many common web hosting database tasks, while also offering granular control to web hosts requiring advanced configuration options.
Typically, websites and their corresponding cPanel MySQL instances are installed on the same server. However, it can sometimes be advantageous to decouple them, allowing websites to utilize a remote MySQL database hosted on a separate server. This architectural choice can offer several significant benefits for web application performance and management.
You might consider employing a remote MySQL server for the following strategic reasons:
- Offload database workloads: By separating the database from the webserver, you can significantly improve the performance of busy sites and the database itself, ensuring each component has dedicated resources.
- Utilize specialized database servers: Implement servers specifically optimized and configured for high-performance database hosting, leveraging hardware and software tailored for data management.
- Centralize database management: Streamline the administration of databases across multiple servers, simplifying maintenance, backups, and security policies.
- Enhance security: Isolate the database from servers that are directly accessible via a public IP address, adding an extra layer of security against potential external threats.
It is crucial to understand that improperly configuring remote database access, such as exposing the MySQL server directly to the internet and permitting connections from untrusted IP addresses, is a critical security risk. This method often creates a severe security vulnerability that malicious actors frequently exploit to steal sensitive data. This article will guide you through the correct and secure method, demonstrating how to use cPanel to configure MySQL to safely accept connections from web applications hosted on different servers.
Using a Remote Database to Host Your Site’s Data
To successfully follow this tutorial and implement a secure remote MySQL setup, you will need to ensure you have the following prerequisites:
- A server with cPanel & WHM already installed. This server will function as your dedicated remote database server.
- The specific IP address or domain name of the database server, along with its cPanel authentication credentials, and the necessary MySQL username and password for database access.
- A separate server designated for installing your web applications. While the method described herein will work for manually installed and configured sites, it is generally more efficient and convenient to manage your web applications with cPanel.
We will first detail how individual cPanel users can configure a remote MySQL database. Following that, we will explore WHM’s more advanced MySQL Profile management tools, which offer broader administrative control.
Configuring a Remote MySQL Database with cPanel
To begin, log in to cPanel on the server hosting the MySQL instance to which you intend to grant remote access. From the main page menu within cPanel, select the Remote MySQL® tool.
In the Add Access Host form, you will need to enter either the domain name or the 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 instance, entering 192.68.0.% would allow any IP address within that subnet. When utilizing a wildcard, it is imperative to restrict the 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 connection requests originating from the specified remote site.
The next crucial step is to configure your web application to utilize this newly accessible remote database. The exact process for this configuration will vary depending on the specific application you are using. For example, when setting up a new WordPress site, you would typically begin by creating the database within cPanel’s MySQL® Database Wizard.
Subsequently, you would need to edit the WordPress site’s wp-config.php file to include the correct database name, authentication credentials (username and a secure password), and the domain name or IP address of your remote database server. An example configuration snippet is shown below:
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 possess the capability to configure MySQL to accept incoming connections from sites hosted on different servers directly through WHM. There are notable distinctions between managing remote database access via cPanel and WHM:
- Hosts added within WHM are universally applied to all cPanel user accounts on the server, as well as to all MySQL users associated with those accounts.
- cPanel users are unable to permanently remove hosts that have been added by system administrators through WHM, ensuring consistent server-wide policies.
To allow remote hosts to access MySQL databases, navigate to Additional MySQL Access Hosts, located within the SQL Services section of the sidebar menu in WHM.
Enter the domain names or IP addresses of the remote hosts that should be permitted to establish connections to the server’s databases, and then click the "Save" button. To begin leveraging these databases on the server, you must configure your respective sites with the appropriate IP address and authentication credentials, as detailed in the preceding section for cPanel users. For more comprehensive information on database access hosts, we recommend consulting the feature’s official documentation page.
Managing MySQL Profiles in WHM
Beyond configuring remote databases through individual cPanel accounts and WHM access hosts, server administrators have the powerful option to create and manage MySQL profiles within WHM. These profiles allow administrators to precisely define the remote database server used across all of cPanel & WHM on the machine; in fact, even the local machine’s default database configuration is managed via such a profile.
MySQL profiles offer versatility, enabling the configuration of various types of remote databases. 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 be aware that only one MySQL profile can be active on the system at any given time. To add a new profile, log in to WHM and navigate to Manage MySQL® Profiles, found under SQL Services in the sidebar menu.
WHM provides the capability to log into the remote server, create a new MySQL superuser for managing databases, and comprehensively configure the MySQL instance. If your MySQL installation is already configured with an existing superuser, you can simply input those credentials instead.
Once you have successfully activated the new profile, all subsequent new databases created will be provisioned on the designated remote server. However, it is important to note that existing databases are not automatically migrated; you will need to transfer any existing data manually to the new remote database server.
cPanel & WHM empowers web hosts and their clients with exceptional flexibility in configuring and managing MySQL databases. Key advantages include:
- Out-of-the-box defaults: Excellent default configurations for hosting MySQL databases directly on the local server.
- Easy remote database configuration: Simplified setup for databases used by sites and stores hosted on external, remote servers.
- Comprehensive system administrator tools: Powerful tools that provide administrators complete control over MySQL installations and the hosting location of their databases.
