Switch to Remote MySQL Database

[brianmp1]

Hey Everyone,

I've researched this for many hours and still am unclear on the best way to do this. I have an overloaded WHM server and need to get the MySQL server onto its own machine. Here is what I want to do:

Create (optimally MariaDB rather than MySQL) server on local network.

Add MySQL Profile to WHM as per: Manage MySQL Profiles - Documentation - cPanel Documentation

Move all databases over to new server.

Shut down MySQL on the original webserver.

Update all of the websites to connect to the new database server (I am assuming that cPanel will not automatically redirect MySQL to the correct place?).

I have successfully done everything except for moving the databases over. Last time I tried, none of the passwords transferred. Additionally, I recently learned of the /var/cpanel/databases folder, which I imagine has to be updated if I manually move the databases.

Is there a good way to do this? Thank you!

 

[cPanelMichael]

Hello,

Update all of the websites to connect to the new database server (I am assuming that cPanel will not automatically redirect MySQL to the correct place?).

You actually should be able to leave the website configuration as-is. Setting up the remote MySQL profile will ensure database connections are automatically routed to the remote MySQL server.

I have successfully done everything except for moving the databases over. Last time I tried, none of the passwords transferred. Additionally, I recently learned of the /var/cpanel/databases folder, which I imagine has to be updated if I manually move the databases.

You do not have to copy over the /var/cpanel/databases directory, as this directory exists to handle the configuration of which databases belong to each cPanel user. It's not required on the remote server since the remote MySQL server does not utilize cPanel. I recommend using a utility such as "mysqldump" to copy over the databases:

MySQL :: MySQL 5.6 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
Export and Import all MySQL databases at one time

Let us know if you have any additional questions.

Thanks!

 

[bejbi]

Hello,

You actually should be able to leave the website configuration as-is. Setting up the remote MySQL profile will ensure database connections are automatically routed to the remote MySQL server.

You do not have to copy over the /var/cpanel/databases directory, as this directory exists to handle the configuration of which databases belong to each cPanel user. It's not required on the remote server since the remote MySQL server does not utilize cPanel. I recommend using a utility such as "mysqldump" to copy over the databases:

MySQL :: MySQL 5.6 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
Export and Import all MySQL databases at one time

Let us know if you have any additional questions.

Thanks!

The questions:

1. Is it mean: when I set up remote mysql server and make profile to connect to it, when the users have set "localhost" as their mysql host it will be automatically connect to my remote server ? So the users must not change anything in their configs ?

2. Do You provide any tutorial how to set up remote mysql server (I mean about configuration) ? Can I use standard WHM licence on remote mysql server? Or I must set up any linux distribution and set it up on my own ?

3. I found somewhere informations for older WHM version that is not allowed to set up one remote mysql server for many cPanel servers? Is it right ? It can make only problem names of the databases ? Or something else ? Or mybe is it resolved now ?
I thinnk, it will be very usefull to set one strong remote mysql server for 2-4 cpanel servers, is it ?

4. The procedure to set up remote database server is:

a. set up new server with mysql on it
b. make a remote profile in whm and activate it
c. dump all databases and undump them on remote server

thats all ?

WB

 

[cPanelMichael]

1. Is it mean: when I set up remote mysql server and make profile to connect to it, when the users have set "localhost" as their mysql host it will be automatically connect to my remote server ? So the users must not change anything in their configs ?

Yes, that's correct.

2. Do You provide any tutorial how to set up remote mysql server (I mean about configuration) ? Can I use standard WHM licence on remote mysql server? Or I must set up any linux distribution and set it up on my own ?

You can setup MySQL on a cPanel server, or set it up manually on a standalone Linux distribution. There's no requirement to have cPanel installed on the remote MySQL server.

3. I found somewhere informations for older WHM version that is not allowed to set up one remote mysql server for many cPanel servers? Is it right ? It can make only problem names of the databases ? Or something else ? Or mybe is it resolved now ?
I thinnk, it will be very usefull to set one strong remote mysql server for 2-4 cpanel servers, is it ?

While you can use a single remote MySQL server for multiple cPanel & WHM servers, we do not recommend this configuration, because certain databases related to cPanel/WHM features utilize the same database name and thus may conflict when multiple cPanel servers are utilized. I encourage you to open a feature request if you'd like to see support for this:

Submit A Feature Request

4. The procedure to set up remote database server is:

a. set up new server with mysql on it
b. make a remote profile in whm and activate it
c. dump all databases and undump them on remote server

thats all ?

Yes, that's correct.

Thank you.