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.

WHM & CPANEL 11 Remote MySQL and Backup

Discussion in 'General Discussion' started by 3mnetwork, Jun 11, 2007.

  1. 3mnetwork

    3mnetwork Registered

    Joined:
    Sep 15, 2003
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    I'm ready to plunge into 2 server setup.

    I'm planning to get another dedicated server for MySQL server alone.

    My questions is

    1. When i setup remote MYSQL server on WHHM do i need to move the database manually to the new MYSQL server?

    2. Will the WHM and CPANEL includes the DB on the remote SQL server when it runs CPANEL backup?

    3. Will CPANEL totally manage the MYSQL server or do i have to manually manage it myself on the new mysql server?

    Thank you in advanced
     
  2. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    No, that will be handled automatically.

    Off-hand, I do not believe so. Feel free to send me an email at sales@cpanel.net and I can give you a definitive answer.

    cPanel/WHM will manage the remote MySQL server.
     
  3. darkelder

    darkelder Well-Known Member

    Joined:
    Oct 8, 2004
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    It is possible to have external mySQL running on Windows server?
     
  4. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Note the requirements for an external MySQL server: MySQL, Perl, and rsync must already be installed on the remote server.

    Essentially, you'll need to be somewhat creative to get it to work properly on a Windows server. Keep in mind this functionality is built assuming the external MySQL server is on some sort of *nix platform.
     
  5. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    As long as you don't use the built in cPanel functionality that sets up the remote server, you can run the remote MySQL process on any platform you desire.
     
  6. darkelder

    darkelder Well-Known Member

    Joined:
    Oct 8, 2004
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    How do I setup external mySQL server without use "SQL Services -> Setup Remote MySQL server"?


    Also, could you tell me more what can be done with "SQL Services -> Additional MySQL Access Hosts"?
     
  7. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    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 root@host1 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.
     
  8. darkelder

    darkelder Well-Known Member

    Joined:
    Oct 8, 2004
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Do you have some presentation or video for download from Training Seminar?
     
  9. darkelder

    darkelder Well-Known Member

    Joined:
    Oct 8, 2004
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Thank you very much for this tutorial.

    Why not publish it at cPanel website?
     
  10. darkelder

    darkelder Well-Known Member

    Joined:
    Oct 8, 2004
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Let suppose I use same mySQL server to several cPanel installations and on 2 cPanel servers differents accounts use same username:

    cPanel Server 1:

    username example create database example_test1

    cPanel Server 2:

    username example create database example_test2


    Will cPanel know example_test1 belongs only to example from cPanel Server 1 or it will think both databases belong to both usernames?
     
  11. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    The videos will be available at some point in the future. I think they are doing some processing on them to make all of the presenters look good :D

    NO, that is a current caveat with two or more cPanel servers attempting to use the same MySQL process. You can get around that in various ways:

    1. Have different MySQL processes listening on different ports (this has many unique problems)
    2. Put a proxy between the cPanel servers and the remote MySQL server(s). Configure the proxy so it knows to forward requests from host1 to mysql5-1 and host2 to mysql5-2. Or from host1 to mysql5-1:3306 and from host2 to mysql5-1:3316 (using different ports with multiple MySQL processes on the same machine).
    3. Have some custom, centralized system that ties in with the account creation process that guarantees unique cPanel account names for all your servers.

    We hope to address this and other issues once we start focusing on cPanel 12.
     
  12. darkelder

    darkelder Well-Known Member

    Joined:
    Oct 8, 2004
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Do you know some proxy implementation?
     
  13. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    The one used in the presentation was HA Proxy

    The presentation didn't go in depth in the setup and use of the proxy. However the manual is fairly easy to read, and the entire setup for simple scenarios is very easy.
     
  14. izmirhost

    izmirhost Member

    Joined:
    Jun 17, 2005
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    ALL PRIVILEGES on *.* to root@host1 IDENTIFIED BY 'password' WITH GRANT OPTION;

    I cant run on mysql 4.1

    what must I do ?
     
  15. DomineauX

    DomineauX Well-Known Member
    PartnerNOC

    Joined:
    Apr 12, 2003
    Messages:
    414
    Likes Received:
    4
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    The "Additional MySQL Access Hosts" ability should really be made to setup remote access hosts for specific databases only, or all databases under a specific username.

    Having the ability to say IP address 123.123.123.123 has remote access to any databases on the server could be ok as an option but it would be much preferred (I would imagine) to specify that user1 can access any of *HIS* databases from the remote IP/Host specified.
     
  16. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    To izmirhost:

    Make certain your SQL statement is:
    Code:
    GRANT ALL PRIVILEGES on *.* to root@host1 IDENTIFIED BY 'password' WITH GRANT OPTION;
    
    and not
    Code:
    ALL PRIVILEGES on *.* to root@host1 IDENTIFIED BY 'password' WITH GRANT OPTION;
    
    as you specified in your post. Also, I assume you are replace the generics I used in the statement with values specific to your setup.

    cPanel 12 will have much improved support for clustering in general. The features and design are still being hammered out and likely won't receive full attention until EA3 reaches the Stable branch. However, one thing that is already known is removing the current MySQL database naming convention, since this is a current stumbling block to 'real' clustering. the how is not know, only that it must happen.
     
  17. fifawe

    fifawe Member

    Joined:
    Aug 24, 2006
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    do I have to tell my customer to modify their configuration files to user db_ip instead of localhost?
     
  18. fifawe

    fifawe Member

    Joined:
    Aug 24, 2006
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    I have already added Remote MySQL server, but I'm facing a lot of
    problems.
    I need your advice to solve these problems.

    1- I can't open PhpMyAdmin from WHM it said "Wrong username/password.
    Access denied."

    2- my users can't see their DB from "MySQL Databases" but they still
    can manage it from PhpMyAdmin.

    3- cpanel can't count the number of Db's that user is already using
    "0/x" although php is showing 1 or more DB.

    4-Error in Fantastico "Fantastico is unable to connect to your MySQL
    server at this time. Please contact your host for assistance".

    5-the server keep sending the following message
    mysql failed @ Sun May 18 15:30:00 2008. A restart was attempted automatically

    6- every time I added the configuration of my remote sql server it works fine for about 15-30min then the configuration disappears
    and I have to add it again to make the server work.
    :confused:
     
  19. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    I recommend submitting a support ticket: http://tickets.cpanel.net/submit
     
Loading...

Share This Page