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.

database howto linux mysql ssh
Tweet Post Share Update Email RSS

Leader, Mentor, Challenger, Educator, Network Engineer, System Administrator, Developer, Hacker, Writer, Diver, and Technology Explorer.