MySQL replication of WordPress dbs over ssh

I wanted to setup MySQL replication over ssh for a small WordPress database. I have a VM that lives in my house, I wanted to be able to bring it up, make it current, disconnect, and then hack away. Here is my proceedure.

On the master:

Setup the replication user (I had to use becasue % did not let me connect from ssh):

grant replication slave on *.* TO repl@"" identified by '[repl password]';

edit /etc/my.cnf

replicate-do-db=DB_TO_REPLICATE   #the name of the db you want to replicate.

From the slave:

edit /etc/my.cnf

server-id = 2
master-host =
master-user = repl
master-password = password
master-port = 7777
replicate-do-db = DB_TO_REPLICATE   #the name of the db you want to replicate.

Next, tunnel a ssh connection from slave to the source, and then make sure you can see the source databases:

ssh -f -N -L7777:
mysql -h -P 7777 -e "show databases;"

Next create a local database:

mysql -e "create database DB_TO_REPLICATE;"

Next we want to find where the master’s log is and its position:

MASTERLOGPOS=$(mysql -h -P 7777 --batch --skip-column-names -e "SHOW MASTER STATUS;" | cut -f2)
MASTERLOGFILE=$(mysql -h -P 7777 --batch --skip-column-names -e "SHOW MASTER STATUS;" | cut -f1)

next we want to seed the local db

mysqldump -h -P 7777 DB_TO_REPLICATE | mysql -u root DB_TO_REPLICATE;

and finally, tell the db to use the source as the Master:

mysql -e "start slave;"

To test I run:

mysql -e "show slave status\G;"

and I compare the following on the master and the client:

mysql -e "select id,post_parent,post_modified,post_title from jbmurphy_com.wp_posts"


Comments are closed.

Powered by WordPress. Designed by WooThemes