How To Use A Remote MySQL® Database With cPanel

The MySQL database server is a fundamental component for millions of content management systems and e-commerce applications. It provides 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 detailed control for web hosts.

While websites and cPanel’s MySQL instance are typically installed on the same server, separating them can offer significant advantages, allowing sites to utilize a remote MySQL database hosted on a different server.

Reasons to consider using a remote MySQL server include:

• Offloading Database Workloads: Improve the performance of busy websites and the database by distributing the load from the webserver.
• Specialized Database Hosting: Leverage a server specifically configured and optimized for database hosting, providing better resource allocation and stability.
• Centralized Database Management: Streamline database administration across multiple servers by centralizing management efforts.
• Enhanced Security: Isolate the database from servers that are directly accessible via a public IP address, adding an extra layer of security.

It is crucial to understand that directly exposing a MySQL server to the internet and allowing connections from untrusted IP addresses is not a secure practice. This approach creates a significant security vulnerability that is frequently exploited to compromise sensitive data, as detailed in various security guidelines.

In this article, we will guide you through the correct and secure method of 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 successfully follow this tutorial, you will need to ensure you have the following:

• A server with cPanel & WHM installed, which will serve as your dedicated remote database server.
• The IP address or domain name of your database server, along with the necessary cPanel authentication credentials, and the MySQL username and password.
• A separate server designated for installing and hosting your web applications. While the methods described here will work with manual site installations, utilizing cPanel on your application server can streamline the setup process considerably.

We will first explain how cPanel users can configure access to a remote MySQL database before delving into WHM’s more advanced MySQL Profile management tools.

Configuring a Remote MySQL Database with cPanel

Begin by logging into cPanel on the server that hosts 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, enter the domain name or IP address of the server hosting your web application. For scenarios requiring access from multiple IP addresses, you can use a wildcard character (%); for instance, 192.68.0% would allow connections from any IP address within that subnet. When employing a wildcard, it is imperative to strictly 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 configure the MySQL database to securely accept connection requests from the specified remote site. This step updates the MySQL grant tables, enabling external access under controlled conditions.

Once access is granted, the next crucial step is to configure your website or application to utilize this remote database. The exact procedure varies depending on the specific application. For example, if you are setting up a new WordPress site:

1. First, create the database and a user within cPanel using the MySQL® Database Wizard.
2. Then, edit the WordPress site’s wp-config.php file to include the database name, authentication credentials, and importantly, the remote database server’s domain name or IP address in the DB_HOST directive.

define( 'DB_NAME', 'wp_database' );
define( 'DB_USER', 'user' );
define( 'DB_PASSWORD', 'a-secure-password' );
define( 'DB_HOST', '203.0.113.0' );

Similar configuration steps will be required for other content management systems or custom applications, typically involving updating a configuration file with the remote database details.

Adding MySQL Access Hosts in WHM

System administrators have additional capabilities to configure MySQL to accept incoming connections from sites hosted elsewhere. It's important to note some key distinctions between configuring remote database access in cPanel and WHM:

• Hosts added through WHM are universally applied to all cPanel user accounts and their associated MySQL users on the server. This provides a broader, server-wide access rule.
• cPanel users do not have the ability to permanently remove hosts that have been configured by system administrators in WHM, ensuring centralized control.

To enable remote hosts to access MySQL databases via WHM, navigate to Additional MySQL Access Hosts, found within the SQL Services section of the sidebar menu.

Enter the domain names or IP addresses of the remote hosts that require permission to connect to the server’s databases, and then click save. Once these hosts are added, you should proceed to configure your individual sites with the appropriate remote IP address and authentication credentials, as detailed in the previous section for cPanel users.

For more in-depth information regarding database access hosts and their management, we recommend consulting the official feature documentation page.

Managing MySQL Profiles in WHM

Beyond individual host configurations, server administrators can also establish and manage MySQL profiles within WHM to connect with remote database servers. A MySQL Profile essentially allows administrators to define which remote database server will be used universally across cPanel & WHM for new database creations and management tasks. In fact, the default local machine’s database is managed through an active profile.

These profiles offer flexibility, supporting connections to various types of remote databases, including those running on other cPanel & WHM servers, dedicated MySQL servers, and even cloud-based solutions like Amazon’s Relational Database™ Service (RDS).

Before proceeding to create and activate a new profile, it is vital to remember that only one MySQL profile can be active at any given time. To add a new profile, log into WHM and navigate to Manage MySQL® Profiles, located under SQL Services in the sidebar menu.

WHM provides options to automatically log into the remote server, create a new MySQL superuser for database management, and configure MySQL as needed. Alternatively, if you have already set up the MySQL installation with a superuser, you can simply provide those existing details.

Once you activate a new profile, all subsequent new databases created through cPanel and WHM will be provisioned on the designated remote server. However, it is important to understand that existing databases are not automatically migrated. You will need to manually transfer any existing data to the new remote database instance to ensure continuity, which typically involves exporting and importing database dumps.

Conclusion

cPanel & WHM empowers web hosts and their clients with remarkable flexibility in configuring and managing MySQL databases. This robust platform facilitates:

• Excellent default configurations for hosting MySQL databases directly on the local server.
• Straightforward database setup for websites and e-commerce stores hosted on separate, remote servers.
• Comprehensive system administrator tools that grant complete control over MySQL installations and the location where databases are hosted.

By leveraging these features, users can optimize performance, enhance security, and streamline the management of their database infrastructure effectively.