Setting up a remote MySql database

vincentg

Well-Known Member
May 12, 2004
159
4
168
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
 

JaredR.

Well-Known Member
Feb 25, 2010
1,834
23
143
Houston, TX
cPanel Access Level
Root Administrator
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.
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.
 

vincentg

Well-Known Member
May 12, 2004
159
4
168
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 [email protected] 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.