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 routine web hosting database tasks while providing granular control for system administrators and web hosts.
Typically, websites and cPanel’s MySQL instance reside on the same server. However, there are distinct advantages to separating them, allowing websites to utilize a remote MySQL database hosted on an independent server.
Employing a remote MySQL server can yield several significant benefits:
- Offload database workloads from the primary web server, thereby enhancing the performance of busy sites and improving database responsiveness.
- Utilize a server specifically optimized and configured for high-performance database hosting.
- Centralize database management across multiple web servers, streamlining administration.
- Enhance security by isolating the database from servers directly exposed to public IP addresses.
It is crucial to understand that exposing the MySQL server directly to the internet and allowing connections from untrusted IP addresses is a critical security vulnerability. This insecure practice is frequently exploited to compromise and steal sensitive data, as highlighted in official MySQL security guidelines.
This article will guide you through the correct and secure method for configuring MySQL, using cPanel, to accept connections from web applications hosted on different servers.
Using a Remote Database to Host Your Site’s Data
To effectively follow this tutorial and implement a remote MySQL setup, you will require the following:
- A robust server with cPanel & WHM already installed. This server will function as your dedicated remote database server.
- Essential access details for the database server: its public IP address or domain name, your cPanel authentication credentials, and the necessary MySQL username and password.
- A separate server designated for installing and hosting your web applications. While the methods described herein are compatible with manual site installations and configurations, using cPanel on this web application server can significantly expedite and simplify the process.
Our explanation will first cover how individual cPanel users can configure a remote MySQL database, followed by an exploration of WHM’s powerful MySQL Profile management tools for system-wide configurations.
Configuring a Remote MySQL Database with cPanel
To begin, log in to your cPanel account on the server where your MySQL instance is hosted, and which you intend to grant remote access to. From the main cPanel interface, locate and select the Remote MySQL® tool.
Within the Add Access Host form, input the domain name or the specific IP address of the server hosting your web application. For scenarios requiring access from multiple IP addresses, you can employ a wildcard character (e.g., 192.68.0.%) to broaden the scope. When utilizing wildcards, exercise extreme caution and ensure that the scope is strictly limited to IP addresses under your direct control or those from sources you implicitly trust, to mitigate potential security risks.
After entering the host details, click the Add Host button. cPanel will then automatically configure the MySQL database to securely accept connection requests originating from the specified remote site.
The next critical step involves configuring your web application to communicate with this newly enabled remote database. The exact procedure varies depending on the specific application you are using. For instance, with a freshly installed WordPress site, you would first create the necessary database using cPanel’s MySQL® Database Wizard.
Subsequently, you would modify the WordPress site’s wp-config.php file to incorporate the new database name, authentication credentials, and the remote database server’s domain name or IP address. 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. It’s important to note the key distinctions between configuring remote database access in cPanel versus WHM:
- Hosts added via WHM apply universally to all cPanel user accounts and their associated MySQL users on that server.
- cPanel users do not have the authorization to permanently remove access hosts that have been configured by a system administrator in WHM.
To grant remote hosts access to your MySQL databases, navigate to the SQL Services section in WHM’s sidebar menu and select Additional MySQL Access Hosts.
Enter the domain names or IP addresses of the remote hosts that require permission to connect to the server’s databases, then click the Save button. Following this, you must configure your web applications with the appropriate remote server IP address and authentication credentials, as detailed in the previous section, to begin utilizing the databases on the server.
For a more in-depth understanding of database access hosts, we recommend consulting the feature’s official documentation page.
Managing MySQL Profiles in WHM
Beyond configuring individual remote databases through cPanel & WHM, server administrators can leverage WHM’s functionality to create and manage MySQL profiles. These profiles allow administrators to define the remote database infrastructure used throughout the entire cPanel & WHM environment. In fact, even the local machine’s default database configuration is managed through a designated profile.
MySQL profiles offer versatility, enabling administrators to configure various types of remote databases. This includes databases running on other cPanel & WHM servers, dedicated MySQL servers, and even managed database services like Amazon’s Relational Database™ Service.
Before proceeding with the creation and activation of a new profile, it is crucial to remember that only one MySQL profile can be active at any given time. To add a new profile, log in to WHM and navigate to Manage MySQL® Profiles, located under SQL Services in the sidebar menu.
WHM provides options to automatically log in to the remote server, create a new MySQL superuser for database management, and 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 a new profile is successfully activated, 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 manually transfer any existing data.
cPanel & WHM empowers web hosts and their clients with remarkable flexibility in configuring and managing MySQL databases. This robust platform facilitates:
- Exceptional default configurations for hosting MySQL databases directly on the local server.
- Streamlined database configuration for websites and online stores hosted on external, remote servers.
- Comprehensive system administrator tools that provide complete command over MySQL deployments and the chosen hosting location of their databases.
