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 127.0.0.1 becasue % did not let me connect from ssh):

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

edit /etc/my.cnf

server-id=1
log-bin=mysql-bin
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 = 127.0.0.1
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:127.0.0.1:3306 [email protected]
mysql -h 127.0.0.1 -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 127.0.0.1 -P 7777 --batch --skip-column-names -e "SHOW MASTER STATUS;" | cut -f2)
MASTERLOGFILE=$(mysql -h 127.0.0.1 -P 7777 --batch --skip-column-names -e "SHOW MASTER STATUS;" | cut -f1)

next we want to seed the local db

mysqldump -h 127.0.0.1 -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 "use DB_TO_REPLICATE;CHANGE MASTER TO MASTER_LOG_FILE='"$MASTERLOGFILE"',MASTER_LOG_POS=$MASTERLOGPOS;"
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"