MySQL replication between VPS and shared host

MySQLreplication

I have a VPS where I have ssh access and shared host with CPanel. Both have MySQL.

I need to create a master/slave setup among the two server.
As per this tutorial: How To Set Up Database Replication In MySQL, I will need to modify the my.conf file.

Is it possible to achieve this with the following permissions on both the servers?

VPS

I have root access with ssh login where I can modify /etc/my.conf file.
Shared host

Cpanel with phpmyadmin.

Best Answer

if you have MySQL root access on the shared host, there is a change that it works, just make sure that you:

Use the VPS as master
Enable the shared host ip to connect on vps host
run a SELECT @@server_id on shared host and set the vps to use a different one.
follow the steps described on tutorial
run the CHANGE MASTER TO on SQL tab on PhpMyAdmin

Related Solutions

Mysql – Does replication with SSL encryption work on MySQL 5.5

To use SSL for encrypting the transfer of the binary log required during replication, both the master and the slave must support SSL network connections. If either host does not support SSL connections (because it has not been compiled or configured for SSL), replication through an SSL connection is not possible.

Setting up replication using an SSL connection is similar to setting up a server and client using SSL. You must obtain (or create) a suitable security certificate that you can use on the master, and a similar certificate (from the same certificate authority) on each slave.

For more information on setting up a server and client for SSL connectivity refer this link

Please follow the steps mentioned here:

http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html

I was searching in bugs.mysql.com related to MySQL 5.5 but there is nothing reported w.r.t "replication with SSL" for v5.5. You can mention exact error message here for analysis.

MySQL Database Migration – Moving Large Databases with Mysqldump

What you need to do is mysqldump separate tables in batches and load them.

On your new CentOS server, write the following

MYSQL_SOURCE_HOST=10.20.30.40
MYSQL_SOURCE_USER=root
MYSQL_SOURCE_PASS=rootpassword
SRC_CONN="-h${MYSQL_SOURCE_HOST} -u${MYSQL_SOURCE_USER} -p${MYSQL_SOURCE_PASS}"

MYSQL_TARGET_HOST=localhost
MYSQL_TARGET_USER=root
MYSQL_TARGET_PASS=rootpassword
TGT_CONN="-h${MYSQL_TARGET_HOST} -u${MYSQL_TARGET_USER} -p${MYSQL_TARGET_PASS}"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema = 'xcart'"
SQL="${SQL} ORDER BY data_length"
mysql ${SRC_CONN} -ANe"${SQL}" > /tmp/ListOfTables.txt

COMMIT_COUNT=0
COMMIT_LIMIT=10
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers --events"
for TBL in `cat /tmp/ListOfTables.txt`
do
    mysqldump ${SRC_CONN} ${MYSQLDUMP_OPTIONS} xcart ${TBL} | mysql ${TGT_CONN} -Dxcart &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ] ; then wait ; fi

I have other ideas from my old post : How can I optimize a mysqldump of a large database?

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Does replication with SSL encryption work on MySQL 5.5

MySQL Database Migration – Moving Large Databases with Mysqldump

Related Question