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.

setting up remote mysql

Discussion in 'General Discussion' started by tom11011, Apr 21, 2009.

  1. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Hi All,

    I have been reading some threads on setting up remote mysql. I really would like to do this to relieve some load on the cpanel server.

    From what I gather, I will install a second network card in the cpanel server and assign the interface a lan address. The new mysql server will have an ip address in the same network.

    After it is setup, what I don't understand is what happens next? Do I have to manually move databases to the new server? And, if so, do I need to edit my clients web files in order to tell the config or dbsetup files the new ip address of the server? What about new databases?

    There are so many unanswered questions on this topic that I Cannot find the answers too.

    Thanks.
     
  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
    If you use WHM -> SQL Services -> Setup Remote MySQL server, the databases used by system services and webmail will be transferred. However, user-created databases will need to be migrated manually. You will also need to instruct your users to connect to that remote SQL server rather than localhost.

    Comprehensive information about setting up remote MySQL servers is available in the slides for the Remote MySQL Server presentation from the 2008 cPanel Conference which is available at: http://twiki.cpanel.net/twiki/pub/A...ources/TrainingSlides08/RemoteMYSQLServer.pdf
     
  3. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Thank you for that. The link that was posted in a different thread was dead.

    Tom
     
  4. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    There was a helpful hint from another forum to copy the scipts folder over to the new server. That got me a little further. It wasn't in the powerpoint doc for some reason. Anyways, it's still not working, here is the output from the attempt.

    Also, I am confused about the part in the doc that says the following

    Code:
    Resolving
    
    /etc/hosts
    
    On cpanel server - add remote host info to local /root/.my.cnf
    
    [client]
    user="root"
    pass="mypassword"
    host="db1.example.local"
    [host=192.168.10.10]
    
    To I overwrite what is in there? or just add it at the bottom?

    ------------------------------------------------

    Using Remote mySQL Server
    Fetching password from remote mysql server.... Copying mysql configuration from remote host......Done
    Done
    Generating Setup Script....Done

    Copying MySQL Script.....0%.. ..100%.. ...Done
    Running MySQL Script...
    Using 192.168.1.52 as the access ip
    Sync Source: http://httpupdate.cpanel.net/RELEASE/scripts
    Testing connection speed for httpupdate.cpanel.net (25 servers)...(using fast method)....Done
    Ping:1000 Testing connection speed to 208.82.118.100 using pureperl...(17577.08 bytes/s)...Done
    Ping:1000 Testing connection speed to 72.36.255.218 using pureperl...(13585.48 bytes/s)...Done
    Ping:1000 Testing connection speed to 209.85.80.214 using pureperl...(9907.06 bytes/s)...Done
    Ping:1000 Testing connection speed to 67.159.2.2 using pureperl...(14276.27 bytes/s)...Done
    Ping:1000 Testing connection speed to 67.205.110.4 using pureperl...(18748.89 bytes/s)...Done
    5 usable mirrors located
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.lock (0)....@208.82.118.100......connected......receiving......Done
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.bz2 (1)....@208.82.118.100......connected......receiving...100%......Done
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/pkgacct.static.bz2 (1)....@208.82.118.100......connected......receiving...48%...97%...100%......Done
    Got file ./pkgacct.static ok (md5 matches)
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/updatenow.static.bz2 (1)....@208.82.118.100......connected......receiving...100%......Done
    Got file ./updatenow.static ok (md5 matches)
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/cpanelsync.static.bz2 (1)....@208.82.118.100......connected......receiving...100%......Done
    Got file ./cpanelsync.static ok (md5 matches)
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/updateuserdomains.static.bz2 (1)....@208.82.118.100......connected......receiving...65%...100%......Done
    Got file ./updateuserdomains.static ok (md5 matches)
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/initinstall.static.bz2 (1)....@208.82.118.100......connected......receiving...100%......Done
    Got file ./initinstall.static ok (md5 matches)
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/generate_maildirsize.static.bz2 (1)....@208.82.118.100......connected......receiving...100%......Done
    Got file ./generate_maildirsize.static ok (md5 matches)
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/cPScript/Version.pm.bz2 (1)....@208.82.118.100......connected......receiving...100%......Done
    Got file ./cPScript/Version.pm ok (md5 matches)
    Sync Source: http://httpupdate.cpanel.net/RELEASE/scripts
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.lock (0)....@67.205.110.4......connected......receiving......Done
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.bz2 (1)....@67.205.110.4......connected......receiving...100%......Done
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    Waiting for mysql to restart..............finished.

    2009-04-23 12:33:08 info [restartsrv_mysql] BSD::Resource not available. Resource Limits not adjusted
    mysqld_safe (/bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid) running as root with PID 7104
    mysqld (/usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock) running as mysql with PID 7164

    2009-04-23 12:33:14 info [restartsrv_mysql] BSD::Resource not available. Resource Limits not adjusted
    /usr/bin/mysqladmin: connect to server at 'localhost' failed
    error: 'Access denied for user 'root'@'localhost' (using password: NO)'
    mysql has failed, please contact the sysadmin (result was "mysql has failed").

    Done
    Testing new mysql setup...Failed to connect to the remote mysql host:
    /usr/bin/mysqladmin: connect to server at '192.168.1.51' failed
    error: 'Can't connect to MySQL server on '192.168.1.51' (4)'
    Check that mysqld is running on 192.168.1.51 and that the port is 3306.
    You can check this by doing 'telnet 192.168.1.51 3306'



    Restoring old mysql config. Please resolve the above error and try again.
     
  5. 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
    Are you setting up the Remote MySQL server manually (by editing the configuration files) or just using the WHM interface that does all of that for you?
     
  6. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    I'm letting whm handle it but am getting the error noted above.

    I think if you could answer this question, it might fix it. This is from cpanels pdf doc.

    Code:
    On cpanel server - add remote host info to local /root/.my.cnf
    
    [client]
    user="root"
    pass="mypassword"
    host="db1.example.local"
    [host=192.168.10.10]
    
    do I add this code into the file or do I replace what is in there?
     
  7. 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
    This information is appended to /root/.my.cnf
     
  8. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Still no luck. I added the info as outlined. Here is what the /root/.my.cnf looks like.

    Code:
    [client]
    user="root"
    pass="mypass1"
    
    [client]
    user="root"
    pass="mypass2"
    host="192.168.1.51"
    
    Here is the output

    Using Remote mySQL Server
    Fetching password from remote mysql server.... Copying mysql configuration from remote host......Done
    Done
    Generating Setup Script....Done

    Copying MySQL Script.....0%.. ..100%.. ...Done
    Running MySQL Script...
    Using 192.168.1.52 as the access ip
    Sync Source: http://httpupdate.cpanel.net/RELEASE/scripts
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.lock (0)....@67.205.110.4......connected......receiving......Done
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.bz2 (1)....@67.205.110.4......connected......receiving...100%......Done
    Sync Source: http://httpupdate.cpanel.net/RELEASE/scripts
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.lock (0)....@67.205.110.4......connected......receiving......Done
    Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.bz2 (1)....@67.205.110.4......connected......receiving...100%......Done
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    Waiting for mysql to restart..............finished.

    2009-04-23 17:08:13 info [restartsrv_mysql] BSD::Resource not available. Resource Limits not adjusted
    mysqld_safe (/bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid) running as root with PID 8223
    mysqld (/usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock) running as mysql with PID 8283

    2009-04-23 17:08:18 info [restartsrv_mysql] BSD::Resource not available. Resource Limits not adjusted
    /usr/bin/mysqladmin: connect to server at 'localhost' failed
    error: 'Access denied for user 'root'@'localhost' (using password: YES)'
    mysql has failed, please contact the sysadmin (result was "mysql has failed").

    Done
    Testing new mysql setup...Failed to connect to the remote mysql host:
    /usr/bin/mysqladmin: connect to server at '192.168.1.51' failed
    error: 'Can't connect to MySQL server on '192.168.1.51' (4)'
    Check that mysqld is running on 192.168.1.51 and that the port is 3306.
    You can check this by doing 'telnet 192.168.1.51 3306'



    Restoring old mysql config. Please resolve the above error and try again.

    (I am able to telnet on port 3306)
     
  9. 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
  10. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    If I buy through a reseller, am I able to open a ticket with you?
     
  11. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    The reseller has given me a link.

    Thanks.

    I'll post what I learn.
     
  12. 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
    If you contact your licensing provider, they will likely be able to acquire an answer from our staff faster than you would be able to get by entering our complimentary ticket queue.
     
  13. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Tech support was able to get me fixed up. I want to place some notes here so hopefully this will be useful to someone.

    There was 2 problems.

    The first one was the firewall on the cpanel server itself. It was a little confusing. First, you generally only think about inbound traffic and not outbound traffic when it comes to firewalls but cpanel was blocking some outbound traffic. Cpanel was allowing ssh traffic port 22 out to the database server ok but it was not allowing tcp port 3306 for mysql. Both are required so basically it was doing what it needed to do with ssh but not sql. Very confusing, only half configured!

    The other thing the tech had me do was to edit the /root/.my.cnf file on the database server and not the cpanel server. But the cpanel pdf document clearly stated the edit needed to occur on the cpanel server but that must be wrong I guess.

    So, the next question. What happens after all this is setup? Nothing is very clear beyond this point. Here is what I have found out.

    My new database server is online and happy. Basically both cpanel server with it's built in mysql database server and the new database server are co-existing nicely.

    The only database that seemed to get copied over was the horde datbase.

    From here on out, it is a manual process to move the existing databases. You basically have to do a sql dump and restore them on the new server. Don't forget to edit your conf files. Maybe one day cpanel will have an automation tool for this.

    Also, any new database will be created on the new db server automatically.

    My final note is this.

    Someone in another thread mentioned to copy the /scripts directory to the new db server and it fixed their problem. I don't know if this was required or not but I did it anyways while trying to troubleshoot.

    I hope this is helpful to someone.
     
  14. tom11011

    tom11011 Member

    Joined:
    Apr 19, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    I thought I would add another helpful update.

    If you are implementing an external mysql server on a server that already has databases on the cpanel mysql server itself, there is a period of strangeness that will occur. Basically, any new databases that are created, whether from an existing user or a new user, are created on the new external sql server. Also, your customers phpmyadmin wants to also point to this new server.

    Helpdesk tickets will come in saying they cannot see there databases in phpmyadmin because phpmyadmin is pointing to the new server and there databases are on the cpanel mysql server.

    Here is how to fix this so the customer can see both servers in phpmyadmin.

    On the cpanel server, edit this file.

    /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

    near the end, but before the closing ?> tag, add in your new server like this:

    Code:
    $i++;
    $cfg['Servers'][$i]['host'] = '192.168.1.5';
    $cfg['Servers'][$i]['extension'] = 'mysql';
    $cfg['Servers'][$i]['connect_type'] = 'tcp';
    $cfg['Servers'][$i]['compress'] = false;
    $cfg['Servers'][$i]['auth_type'] = 'http';
    
    where 192.168.1.5 should be substituted for your new sql server ip address.

    Now, when a customer clicks their phpmyadmin button in cpanel, once phpmyadmin comes up, there will be a new drop down box on the right hand side called Server where they can change between the servers.

    Hope this helps someone. I guess ideally the new remote mysql server would be setup on a new cpanel server before customers are added to the cpanel server. Absent that, this procedure should work ok.
     
  15. Zenit

    Zenit Registered

    Joined:
    Apr 15, 2004
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Well, I have a question.
    If I remove an user account and create it again using "Restore a Full Backup/cpmove file" mysql will be restored to the new mysql server?
     
  16. 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
    In most circumstances, no.
     
  17. Zenit

    Zenit Registered

    Joined:
    Apr 15, 2004
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Thanks, could you give me a tutorial how to move users db to new mysql server? I have seen CPanel presentation but there is only limited information.
     
  18. 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
    You could dump all the databases using mysqldump at the command line. You can find information on how to use this tool at:

    MySQL :: MySQL 5.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program

    To import this database dump, you can use mysqlimport. You can find information on how to use this tool at:

    MySQL :: MySQL 5.0 Reference Manual :: 4.5.5 mysqlimport — A Data Import Program

    I provided links to the MySQL 5.0 documentation on this subject as that's the default version of MySQL we use nowadays. If you need links to the MySQL 4.1 documentation, let me know.
     
Loading...

Share This Page