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: -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 -P 3305 -uUSER -p

You must use, 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'@'' IDENTIFIED BY 'replicationpassword'; GRANT REPLICATION SLAVE ON . TO 'repuser'@'';

On the Slave DB Server:

server-id = 2 master-host = 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 ( 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.