The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Database Discussions' started by morrow95, Mar 10, 2016.

  1. morrow95

    morrow95 Well-Known Member

    Joined:
    Oct 8, 2006
    Messages:
    84
    Likes Received:
    0
    Trophy Points:
    6
    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.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  3. morrow95

    morrow95 Well-Known Member

    Joined:
    Oct 8, 2006
    Messages:
    84
    Likes Received:
    0
    Trophy Points:
    6
    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.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    There's a section of our documentation on the /root/.my.cnf file at:

    Troubleshoot MySQL® Profiles - Documentation - cPanel Documentation

    Thank you.
     
  5. morrow95

    morrow95 Well-Known Member

    Joined:
    Oct 8, 2006
    Messages:
    84
    Likes Received:
    0
    Trophy Points:
    6
    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?
     
    #5 morrow95, Mar 10, 2016
    Last edited: Mar 10, 2016
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page