setting up remote mysql

T

tom11011

Member
Apr 19, 2009
21
0
51
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.
 
cPanelDavidG

cPanelDavidG

Technical Product Specialist
Nov 29, 2006
11,212
13
313
Houston, TX
cPanel Access Level
Root Administrator
tom11011 said:
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.
Click to expand...
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
 
T

tom11011

Member
Apr 19, 2009
21
0
51
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)[email protected]
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.bz2 (1)[email protected]%......Done
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/pkgacct.static.bz2 (1)[email protected]%...97%...100%......Done
Got file ./pkgacct.static ok (md5 matches)
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/updatenow.static.bz2 (1)[email protected]%......Done
Got file ./updatenow.static ok (md5 matches)
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/cpanelsync.static.bz2 (1)[email protected]%......Done
Got file ./cpanelsync.static ok (md5 matches)
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/updateuserdomains.static.bz2 (1)[email protected]%...100%......Done
Got file ./updateuserdomains.static ok (md5 matches)
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/initinstall.static.bz2 (1)[email protected]%......Done
Got file ./initinstall.static ok (md5 matches)
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/generate_maildirsize.static.bz2 (1)[email protected]%......Done
Got file ./generate_maildirsize.static ok (md5 matches)
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/cPScript/Version.pm.bz2 (1)[email protected]%......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)[email protected]
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.bz2 (1)[email protected]%......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.
 
T

tom11011

Member
Apr 19, 2009
21
0
51
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?
 
cPanelDavidG

cPanelDavidG

Technical Product Specialist
Nov 29, 2006
11,212
13
313
Houston, TX
cPanel Access Level
Root Administrator
tom11011 said:
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?
Click to expand...
This information is appended to /root/.my.cnf
 
T

tom11011

Member
Apr 19, 2009
21
0
51
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)[email protected]
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.bz2 (1)[email protected]%......Done
Sync Source: http://httpupdate.cpanel.net/RELEASE/scripts
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.lock (0)[email protected]
Fetching http://httpupdate.cpanel.net/cpanelsync/RELEASE/scripts/.cpanelsync.bz2 (1)[email protected]%......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)
 
T

tom11011

Member
Apr 19, 2009
21
0
51
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.
 
T

tom11011

Member
Apr 19, 2009
21
0
51
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.
 
Z

Zenit

Registered
Apr 15, 2004
3
0
151
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?
 
cPanelDavidG

cPanelDavidG

Technical Product Specialist
Nov 29, 2006
11,212
13
313
Houston, TX
cPanel Access Level
Root Administrator
Zenit said:
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.
Click to expand...
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.
 
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
V Setting up a remote MySql database Databases 2
R Mysql Failed error after setting up Remote MySQL server Databases 6
D Setting up remote mysql server. I got this error Databases 0
S Setting up MySQL Remote Access... Databases 1
P Setting up remote MySQL Server Databases 7
Similar threads
Setting up a remote MySql database
Mysql Failed error after setting up Remote MySQL server
Setting up remote mysql server. I got this error
Setting up MySQL Remote Access...
Setting up remote MySQL Server
Top Bottom