Storing WHM/cPanel data on a remote MySQL server without using SSH involves configuring MySQL to accept remote connections and securely allowing cPanel to access this remote database server. This setup can help with load balancing, data management, and improving database performance.

Here’s a detailed guide on how to achieve this setup:

Prerequisites

1. Access to WHM/cPanel on the primary server.
2. Access to a remote MySQL server.
3. Administrative privileges on both servers.
4. Network access between the WHM server and the remote MySQL server.
5. Backup: Always back up your current configuration and databases before making changes.

Steps to Store WHM Data on a Remote MySQL Server

1. Prepare the Remote MySQL Server

a. Install MySQL Server

Ensure that MySQL is installed and running on the remote server.

For example, on a Debian-based system:

sudo apt-get update
sudo apt-get install mysql-server

Or on a CentOS-based system:

sudo yum install mysql-server

b. Configure MySQL to Accept Remote Connections

1. Edit the MySQL configuration file (my.cnf or mysqld.cnf):

sudo nano /etc/mysql/my.cnf
# Or for newer systems
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2. Bind to all IP addresses or a specific IP:

Look for the line with bind-address and change it:

# Default is usually 127.0.0.1 (localhost)
bind-address = 0.0.0.0  # To allow connections from any IP address
# Or specify a specific IP address
bind-address = your_server_ip

3. Restart MySQL to apply changes:

sudo systemctl restart mysql

c. Create and Configure a MySQL User for Remote Access

1. Log in to MySQL with administrative privileges:

mysql -u root -p

2. Create a new MySQL user for WHM and grant necessary privileges:

CREATE USER 'whm_user'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'whm_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

• Replace 'whm_user' with a desired username.
• Replace 'your_password' with a strong password.
• The '%' wildcard allows the user to connect from any IP address. For enhanced security, replace it with the WHM server’s IP address.

2. Configure WHM to Use Remote MySQL Server

a. Access WHM

Log in to WHM as the root user.

b. Configure Remote MySQL Server

1. Navigate to Home > SQL Services > Manage MySQL Profiles.
2. Add a New MySQL Profile:

• Click on Add Profile.
• Enter a profile name (e.g., RemoteMySQL).
• Select Remote Server.
• Enter the remote MySQL server’s IP address or hostname.
• Enter the username (whm_user) and password that you created earlier.
• Test the connection to ensure it works.

3. Apply the New MySQL Profile:

• Once the profile is added and tested successfully, select it and apply it as the default MySQL server for cPanel accounts.

c. Update Remote MySQL Profile

Ensure that the new profile is the default for new databases and that existing databases are migrated as needed. This may involve:

• Exporting and importing databases manually.
• Using cPanel’s database tools to manage migrations.

3. Firewall and Security Considerations

a. Configure Firewall on the Remote MySQL Server

Ensure that the remote MySQL server’s firewall allows connections from the WHM server’s IP address on the MySQL port (default is 3306).

For example, using iptables:

sudo iptables -A INPUT -p tcp -s your_whm_server_ip --dport 3306 -j ACCEPT

Or using ufw:

sudo ufw allow from your_whm_server_ip to any port 3306

b. Secure MySQL Connection

1. Use SSL/TLS to encrypt connections between WHM and the remote MySQL server. Configure MySQL to support SSL and update WHM’s MySQL profile to use SSL.
2. Restrict MySQL user access by IP address for enhanced security instead of using the ‘%’ wildcard.

c. Monitor and Log Connections

Monitor connections to your MySQL server and review logs to ensure there are no unauthorized access attempts. Use tools like fail2ban to automate this process.

Summary

By following these steps, you can configure WHM to store its data on a remote MySQL server without using SSH. This setup can help distribute the load and improve performance, especially for larger hosting environments. Always ensure your connections are secure and monitor both servers for any issues.