WHM & CPANEL 11 Remote MySQL and Backup

3mnetwork

Registered
Sep 15, 2003
2
0
151
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
 

cPanelDavidG

Technical Product Specialist
Nov 29, 2006
11,216
12
313
Houston, TX
cPanel Access Level
Root Administrator
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?
No, that will be handled automatically.

2. Will the WHM and CPANEL includes the DB on the remote SQL server when it runs CPANEL backup?
Off-hand, I do not believe so. Feel free to send me an email at Support & Customer Service | cPanel, Inc. and I can give you a definitive answer.

3. Will CPANEL totally manage the MYSQL server or do i have to manually manage it myself on the new mysql server?
cPanel/WHM will manage the remote MySQL server.
 

darkelder

Well-Known Member
Oct 8, 2004
74
0
156
It is possible to have external mySQL running on Windows server?
 

cPanelDavidG

Technical Product Specialist
Nov 29, 2006
11,216
12
313
Houston, TX
cPanel Access Level
Root Administrator
It is possible to have external mySQL running on Windows server?
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.
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,608
77
458
cPanel Access Level
Root Administrator
It is possible to have external mySQL running on Windows server?
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.
 

darkelder

Well-Known Member
Oct 8, 2004
74
0
156
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.
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"?
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,608
77
458
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 [email protected] 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.
 

darkelder

Well-Known Member
Oct 8, 2004
74
0
156
Thank you very much for this tutorial.

Why not publish it at cPanel website?
 

darkelder

Well-Known Member
Oct 8, 2004
74
0
156
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?
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,608
77
458
cPanel Access Level
Root Administrator
Do you have some presentation or video for download from Training Seminar?
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

Will cPanel know example_test1 belongs only to example from cPanel Server 1 or it will think both databases belong to both usernames
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.
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,608
77
458
cPanel Access Level
Root Administrator
Do you know some proxy implementation?
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.
 

DomineauX

Well-Known Member
PartnerNOC
Apr 12, 2003
429
11
168
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.
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,608
77
458
cPanel Access Level
Root Administrator
To izmirhost:

Make certain your SQL statement is:
Code:
GRANT ALL PRIVILEGES on *.* to [email protected] IDENTIFIED BY 'password' WITH GRANT OPTION;
and not
Code:
ALL PRIVILEGES on *.* to [email protected] 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.
 

fifawe

Member
Aug 24, 2006
8
0
151
do I have to tell my customer to modify their configuration files to user db_ip instead of localhost?
 

fifawe

Member
Aug 24, 2006
8
0
151
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:
 

cPanelDavidG

Technical Product Specialist
Nov 29, 2006
11,216
12
313
Houston, TX
cPanel Access Level
Root Administrator
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:
I recommend submitting a support ticket: http://tickets.cpanel.net/submit