The MySQL database server is a foundational element for millions of content management systems and e-commerce applications. It delivers secure and dependable data storage and retrieval, which is essential for dynamic and personalized websites. cPanel & WHM offers a comprehensive MySQL management solution, streamlining many routine web hosting database tasks while providing detailed control for web hosts.
Typically, websites and cPanel's MySQL instance reside on the same server. However, there are scenarios where it's beneficial to separate them, allowing websites to utilize a remote MySQL database hosted on a distinct server.
You might consider using a remote MySQL server for several reasons:
- To offload database workloads from the webserver, thereby enhancing the performance of busy websites and the database itself.
- To leverage a server specifically optimized and configured for database hosting.
- To centralize database management across multiple servers, simplifying administration.
- To isolate the database from servers that are directly accessible via a public IP address, improving security.
It's crucial to understand that directly exposing the MySQL server to the internet and permitting connections from untrusted IP addresses is an insecure approach. This method introduces a critical security vulnerability, frequently exploited for unauthorized data access.
This article will guide you through the correct procedure, demonstrating how to use cPanel to securely configure MySQL to accept connections from web applications hosted on different servers, ensuring both efficiency and robust security.
Using a Remote Database for Your Website's Data
To effectively follow this tutorial and implement a remote MySQL setup, you will need the following prerequisites:
- A server with cPanel & WHM installed, which 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.
- A separate server where your web applications will be installed. While the method described herein is compatible with manual site installations and configurations, utilizing cPanel on this server can significantly expedite and simplify the process.
First, we will detail how cPanel users can set up a remote MySQL database. Following that, we will explore WHM's powerful MySQL Profile management tools, which offer advanced administrative capabilities.
Configuring a Remote MySQL Database Using cPanel
Begin by logging into cPanel on the server hosting the MySQL instance you wish to grant external access to. From the main page menu, locate and select the Remote MySQL® tool.
Within the Add Access Host form, input the domain name or IP address of the server that hosts your web application. For greater flexibility, you can employ a wildcard character (%) to permit access from multiple IP addresses; for instance, 192.68.0%. When using a wildcard, ensure you restrict 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 incoming requests from the specified remote site.
The next critical step involves configuring your website or application to utilize this remote database. The exact procedure varies depending on the specific application you are using. For example, when setting up a new 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 include the newly created database name, authentication credentials, and the remote 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 websites hosted on different servers. It's important to note some distinctions between cPanel and WHM regarding remote database access management:
- Hosts added through WHM are universally applied to all cPanel user accounts and their associated MySQL users on the server.
- cPanel users do not have the ability to permanently remove hosts that have been configured by system administrators within WHM.
To authorize remote hosts to access your 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 require permission to connect to the server’s databases, then click "Save." To begin utilizing databases on this server, you must configure your websites with the appropriate remote IP address and authentication credentials, as detailed in the preceding section.
For a more in-depth understanding of database access hosts, refer to the feature’s official documentation page.
Managing MySQL Profiles within WHM
Beyond simply configuring remote databases, server administrators can also create and manage MySQL profiles within WHM. These profiles facilitate connections with remote database servers. In fact, the local machine’s default database configuration is managed through a specific profile.
MySQL profiles are versatile and can be used to set up various types of remote databases, including those running on other cPanel & WHM servers, dedicated MySQL servers, and even databases hosted on Amazon’s Relational Database™ Service (RDS).
Before proceeding to create and activate a new profile, 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 offers robust capabilities to log into the remote server, create a new MySQL superuser for database management, and meticulously configure MySQL settings. If you have already established a superuser for the MySQL installation, you can simply input those existing credentials instead.
Once the new profile has been successfully activated, all subsequent new databases will be created on the designated remote server. It is important to note, however, that existing databases are not automatically migrated; you will need to transfer any existing data manually to the remote server.
cPanel & WHM empowers web hosts and their clients with remarkable flexibility in configuring and managing MySQL databases. This comprehensive control offers several key advantages:
- Exceptional out-of-the-box default settings for hosting MySQL databases directly on the local server.
- Effortless database configuration for websites and online stores hosted on external, remote servers.
- Powerful system administrator tools that provide complete authority over MySQL deployments and the location where databases are hosted.
