MySQL Database Replication over an SSH Tunnel

SSH Tunnel Setup

Create an SSH key (Version 2) to allow passwordless replication:

$ ssh-keygen -t rsa

Copy the SSH key to the Master (assumes same user account on Master and Slave):

$ ssh-copy-id master.machine.dom

Setup the SSH Tunnel from the Slave to the Master on Slave Port 3305:

$ ssh -f master.machine.dom -L 3305:127.0.0.1:3306 -N

Now local port 3305 is actually port 3306 on the Master.

Test that you can access the Master Database from the Slave:

$ mysql -h 127.0.0.1 -P 3305 -uUSER -p

You must use 127.0.0.1, not localhost, because MySQL treats “localhost” differently.

If you are able to get a MySQL connection and verify access to the remote DB, then proceed to configuring Replication.

Configuring MySQL Replication

On the Master DB Server:
Edit the my.cnf file, adding (or uncommenting) the following lines:

log-bin=/var/log/mysql/mysql-bin.log server-id=1

Create the Replication user within the Master DB:

CREATE USER 'repuser'@'127.0.0.1' IDENTIFIED BY 'replicationpassword'; GRANT REPLICATION SLAVE ON . TO 'repuser'@'127.0.0.1';

On the Slave DB Server:

server-id = 2 master-host = 127.0.0.1 master-user = repuser master-password = replicationpassword master-port = 3305

If you only want certain databases, add:

replicate-do-db=database1 replicate-do-db=database2

Restart MySQL on both servers and check the mysql.err log files. When I first did the restart my Slave server listed the following error:

[ERROR] Error reading packet from server: Binary log is not open.

I waited a bit and then I restarted MySQL on my Slave and the error did not repeat.

Test the replication:

Log into your Master server
Update a record in one of your databases
Log into your Slave server
The record should show as updated, immediately

Left to do:

The biggest issue at this point is that the tunnel could drop and the replication will stop. There are solutions to this, the most popular being the AutoSSH program (http://en.wikipedia.org/wiki/Autossh). I’m currently working on setting it up and getting it to execute at system startup. I’ll post another article, using my SSH tunnel, if I get it working as desired.