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.

Switch to Remote MySQL Database

Discussion in 'Database Discussions' started by brianmp1, Mar 3, 2017.

Tags:
  1. brianmp1

    brianmp1 Registered

    Joined:
    Mar 3, 2017
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    New York
    cPanel Access Level:
    Root Administrator
    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!
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,064
    Likes Received:
    1,287
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    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!
     
  3. bejbi

    bejbi Well-Known Member

    Joined:
    Jan 20, 2006
    Messages:
    94
    Likes Received:
    1
    Trophy Points:
    158
    Location:
    Poland
    cPanel Access Level:
    DataCenter Provider
    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
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,064
    Likes Received:
    1,287
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Yes, that's correct.

    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.

    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

    Yes, that's correct.

    Thank you.
     
Loading...

Share This Page