cPanel typically has some decent documentation that helps you get something done, but with the remote mysql server they really screwed the pooch. So heres a very rough guide and aggressive way of setting up a remote mysql server with all the data from the local one.
Not very helpful is it? Okay here's how we do this.
DB# yum install mysql server -y
DB#service mysqld stop
Web# scp -r /var/lib/mysql/ root@db:/var/lib/mysql
At this point you have NO idea what the root password on the db mysql server is.
Reset it with this guide > http://www.cyberciti.biz/tips/recover-mysql-root-password.html
Install phpMyAdmin to the DB server so we don’t have to work with 19th century cli tools.
On the Web box go to SQL > “Setup Remote MySQL server” then type in the ip and ssh port, select login as: root and select authentication method as password. Those are your SSH credentials, you can use ssh keys and all that crap but its only for running a script one time on the remote server.
Once you do that it will fail and be all nasty, don’t get upset its literally just to taunt you.
DB# cat /root/.my.cnf
This will contain a password that you need to setup in a moment. Memorize it or something useful…
Login to phpMyAdmin on the DB server, then click permissions.
Okay so here’s the part where you need to use your brain to make a decision. I don’t know how your setup works so I can’t really tell you what to do, but what I can show you should put you on the right path. Essentially the IP you use to SSH over is what cPanel will stick in the config as the DB server address or FQDN (Fully Qualified Domain Name) So under privileges we need to create a user with the following:
User Name: root
Host: the ip or FQDN you plan on feeding cPanel. Personally I think IP makes more sense here!
Password: remember what I had you waste your time memorizing earlier? Yeah type that here.
Global Privileges, you need to "check all" this. This makes the magic happen!
Now go back to cPanel setup for the database and run it again, it should work…