remote mysql with 'Manage MySQL® Profiles' - /root/.my.cnf

morrow95

Well-Known Member
Oct 8, 2006
164
8
168
Setting up a remote MySQL profile in Home > SQL Services > Manage MySQL Profiles.

Not sure what user I should be specifying in /root/.my.cnf for both the local and remote server.

Let's say I created a MySQL superuser on the remote server called 'whmremote'. I add a profile for the remote server and choose 'Manually enter an existing MySQL superuser’s credentials' and enter in 'whmremote' as my user with its pass. All is fine... but...

what MySQL user/pass should I be putting in /root/.my.cnf for both the local and remote server? My new 'whmremote' superuser for both, only the remote and normal 'root' for local.... does it even matter?

If someone can clear this up for me I would appreciate it.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463
what MySQL user/pass should I be putting in /root/.my.cnf for both the local and remote server? My new 'whmremote' superuser for both, only the remote and normal 'root' for local.... does it even matter?
Hello :)

You should not have to manually populate this file if you are configuring the remote MySQL server via the "Manage MySQL Profiles" option in Web Host Manager. This option should complete that step for you.

Thank you.
 

morrow95

Well-Known Member
Oct 8, 2006
164
8
168
That didn't seem to be the case other than I see the following added for the local/whm side :

host=192.168.10.2
port=3306

With that said, what is the correct user/pass which should be here for 'each side' when this is setup in my case. I manually entered a superuser called 'whmremote' in the profile.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463
There's a section of our documentation on the /root/.my.cnf file at:

Troubleshoot MySQL® Profiles - Documentation - cPanel Documentation

  • If you change the MySQL root password on the hosting server, you must update the remote MySQL server's /root/.my.cnf configuration file.
  • If you change the MySQL root password on the remote MySQL server, you must update the MySQL profile on the hosting server, and then re-activate the profile.
Thank you.
 

morrow95

Well-Known Member
Oct 8, 2006
164
8
168
I'm sorry, but this still doesn't help me out.

Based on the above it sounds as though the hosting root MySQL user/pass goes into both my.cnf files. Are they supposed to be setup as the same password or something? Unless activating the profile edits our remote root user pass then these are different password as they are different users being on two servers.

As for the second comment we are not using the remote root user, but rather a superuser we created called whmremote. And that is 'only' used for the profile connection... not needed in either my.cnf file?

EDIT :

I also just noticed this key line in the linked page 'When the remote MySQL server is also a cPanel server, you must perform additional steps:'. In our case the remote MySQL is NOT a cpanel server. So those rules would not apply to us. So, again, in this case which user should we be putting in these. I am thinking our superuser 'whmremote' should go into both as this is the one we used for the profile connection - right?
 
Last edited:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463
So, again, in this case which user should we be putting in these. I am thinking our superuser 'whmremote' should go into both as this is the one we used for the profile connection - right?
Hello :)

1. You are using a cPanel server for hosting, and a remote server without cPanel to handle MySQL databases. Thus, on the cPanel server, your /root/.my.cnf file should look like this:

Code:
[client]
password="XXXXXXXXXXXX"
user=root
port=3306
host=10.1.1.1
The host line is the IP address of the remote MySQL server. The username and password are the MySQL root credentials for the remote MySQL server. There's no requirement to use the "root" username. The user just has to have the same privileges that "root" has (e.g. superusers).

2. The /root/.my.cnf file on the remote MySQL server only needs the credentials for the local MySQL installation on that system. It does not have to connect back to the cPanel server. Thus, it looks like this:

Code:
[client]
password="XXXXXXX"
user=root
It's okay to use a superuser instead of "root".

Thank you.