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:
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:
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.