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]';
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:
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"