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.

Setting up a remote MySql database

Discussion in 'Database Discussions' started by vincentg, Feb 19, 2013.

  1. vincentg

    vincentg Well-Known Member

    Joined:
    May 12, 2004
    Messages:
    140
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    new york
    The info given by Cpanel is not 100%

    Still follow the steps on the manual setup

    You will use the server IP address to connect to it.
    Cpanel is not required to be on the remote server.

    First set your /root/.my.cnf to your remote server IP

    host="IP address"
    user="root"
    pass="db root password"

    On the remote server setup a /root/.my.cnf
    host="localhost"
    user="root"
    pass="db root password"

    Be sure to restart mysql service after changing .my.cnf

    As you see the remote server will just be set to connect to itself while your Web Server ( localhost ) will be set to connect to the remote box

    Make sure your iptables or your firewall has port 3306 open on the remote database server - else mysql can not connect to this box.

    After you finish all the steps as outlined in the Doc supplied by cpanel your ready to go.

    Problems you run into:

    WHM backup will not create SQL data dumps of databases on you localhost.
    Users can not get database backups of localhost databases from Cpanel Backup.
    You also can not copy full directory of remote server but it will copy the localhost mysql files.
    So as you can see backups are a problem if you wish to leave databases on the localhost.

    Remote backups are fine but if you want to do a full directory / file backup you will need to add a cron job to the remote server - I suggest having two drives on this server where the second will be for backup.

    If you create a new database it will be created on the remote server.
    Important point here is you must use Cpanel Mysql Databases to create a database!!
    If you run MySql script in Phpmyadmin to create a database it will not show in cpanel.
    Then you can not use cpanel to manage user access to it.

    You can create a new database with the same name as one on the localhost.
    You will not see two entries after doing this as it will replace the entry that was there.
    You can not drop databases in cpanel - it will only remove the listing but not drop the actual database.

    Localhost databases can only be removed using phpmyadmin

    To copy databases over use mysqldump and mysql to export and import data.
    You can use root as the user to import and export which makes it a bit easier.

    First use Cpanel to create the database and assign the user to it.
    I found that the original users setup for localhost databases can just be re-assigned to the remote database.
    But sometimes it will not work so either delete the user and re-create it or create a new one.

    After you copy data over and set the application config file to the proper IP address and user info your done

    Cpanel has told me the the mysql service can be set to off when all local databases are moved.
    I have not yet done this.

    Hope this all helps
     
  2. cPanelJared

    cPanelJared Technical Analyst
    Staff Member

    Joined:
    Feb 25, 2010
    Messages:
    1,842
    Likes Received:
    18
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    It actually needs to be shut down. cPanel is not designed to have both a local and a remote MySQL server running at once. The local MySQL service needs to be disabled once the databases are moved.
     
  3. vincentg

    vincentg Well-Known Member

    Joined:
    May 12, 2004
    Messages:
    140
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    new york
    This is self apparent as localhost databases can not backup.

    I have at this point moved all databases and unchecked the service in WHM for MySql - all worked fine but ran into one problem which wasn't too hard to fix

    Mod Security would not connect.

    Edit two files:
    /etc/cron.hourly/modsecparse.pl
    /usr/local/cpanel/whostmgr/docroot/cgi/addon_modsec.cgi

    replace localhost with server IP
    Grab the password to use in adding the user to the DB..

    You have to create the user modsec and add the DB entries

    Replace server-IP with your server IP
    grant usage ON modsec.* to modsec@server-IP identified by 'pwd'

    INSERT INTO `mysql`.`db` (`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`) VALUES ('Server-IP', 'modsec', 'modsec', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

    Repeat it for server name

    I think that covers a proper setup for Remote Database Server

    If I missed anything feel free to add it.
     
Loading...

Share This Page