WHM & CPANEL 11 Remote MySQL and Backup

[3mnetwork]

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]

Originally Posted by 3mnetwork

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.

Originally Posted by 3mnetwork

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 sales@cpanel.net and I can give you a definitive answer.

Originally Posted by 3mnetwork

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]

It is possible to have external mySQL running on Windows server?

[cPanelDavidG]

Originally Posted by darkelder

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]

Originally Posted by darkelder

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]

Originally Posted by cpanelkenneth

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]

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:

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

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:

# 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:

[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]

Originally Posted by cpanelkenneth

It's a multi-step process and was covered at our Training Seminar

Do you have some presentation or video for download from Training Seminar?

[darkelder]

Thank you very much for this tutorial.

Why not publish it at cPanel website?

[darkelder]

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]

Originally Posted by darkelder

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

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.

[darkelder]

Do you know some proxy implementation?

[cpanelkenneth]

Originally Posted by darkelder

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.

[izmirhost]

ALL PRIVILEGES on *.* to root@host1 IDENTIFIED BY 'password' WITH GRANT OPTION;

I cant run on mysql 4.1

what must I do ?

[DomineauX]

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.