It's a multi-step process and was covered at our Training Seminar
On the Remote MySQL Server:
In Mysql, you need to GRANT full access to the root account on the cPanel server.
For this example: mysql5-1 will represent the remote MySQL server and host1 will represent the cPanel server.
On mysql5-1, log into MySQL and do this:
Code:
GRANT ALL PRIVILEGES on *.* to [email protected] IDENTIFIED BY 'password' WITH GRANT OPTION;
If using hostnames for the entire setup, make certain both mysql5-1 and host1 can resolve the hostnames properly (DNS, /etc/hosts, etc).
If you are migrating from an existing cPanel server, you will also need to grant permissions to the users (both real and virtual), using similar statements. The absolutely easiest method here, but not guaranteed to to work 100% is to copy /var/lib/mysql/mysql from host1 to mysql5-1 and restart the MySQL process on mysql5-1. If you do this, then you need to:
1. Redo the GRANT statement above as it was just wiped out
2. Update the Host columns in the mysql.user and mysql.db tables for the users
Code:
UPDATE user SET Host='host1' WHERE User != 'root';
UPDATE db SET Host='host1' WHERE User != 'root';
FLUSH PRIVILEGES;
At this point, on host1, try logging into the remote server both as a user and as root:
Code:
# mysql -h mysql5-1 -u root -p
...
# mysql -h mysql5-1 -u user -p
Once you determined access is working, then on host1, you need to modify
/root/.my.cnf:
Code:
[client]
user=root
password=password
host=mysql5-1
Be certain that the password is the same as used in the GRANT statement earlier.
Now, cPanel will use the remote MySQL server, but there are a few things left:
1. Horde
Modify the horde configuration:
/usr/local/cpanel/base/horde/config/conf.php
Change the 'localhost' entry to 'mysql5-1'
Execute /usr/local/cpanel/bin/updatehorde
2. PHPMyAdmin
Modify the configuraiton at
/usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php
Again, change the entry for 'localhost' to 'mysql5-1'
3. Stop the local MySQL service
4. Move the database over to the remote server. Restart the remote MySQL process.
5. Remove (rename) /var/lib/mysql (/var/db/mysql on FreeBSD) to take care of a bug in some versions of cPanel.
At this point you should have a usable remote MySQL setup.
"SQL Services -> Additional MySQL Access Hosts"
By default, MySQL users can only access the MySQL process from localhost, this feature allows the admin to define extra hosts, either IP Address or domain names. Note, this function will provided access to any and all cPanel accounts and cPanel MySQL users from these hosts.
