How To Set Up SSH Encrypted MySQL Replication

MySQL offers much to the database administrator, such as its free, open source, and has great documentation and built-in replication support. But the security admin will point out one thing missing: encryption. Governments are cracking down on data privacy, and replicating data across the LAN or WAN may require encryption. 

Although MySQL can be compiled with support for SSL, many binary distributions do not have this feature enabled. Open a SQL prompt and type "show variables like '%ssl%'.  If 'have_ssl' or 'have_openssl' are set to 'No,' you're out of luck. Fortunately, there is an alternative to recompiling from source. Secure Shell (SSH) supports data-tunneling, which sets up a mini VPN-like environment offering transparent encryption. First, to get started, we are going to set-up an SSH tunnel using a username/password. You should use RSA keys for remote authentication. ArchLinux has a nice walk through. Once we have the tunnel working, we will set-up replication.

Setting Up The Tunnel
SSH tunneling uses port-forwarding to connect to a TCP port on the slave computer, TCP port 7777 in our case, which gets forwarded via SSH to the MySQL port, TCP port 3306, on the master. On the MySQL master, be sure that SSH tunneling is enabled. It usually is enabled by default. On the MySQL slave, execute the following command: 'ssh -f user@master_ip -L 7777:master_ip:3306 -N.'  Replace user and master_ip with a system user account and IP address of the master server. You might want to make a user on the master with the shell set to /bin/false just for replication. Replace 7777 with any available port on the slave. You will be prompted for the user account password on the master system.  

Now try connecting to the MySQL master server from the MySQLl slave by running 'mysql -h 127.0.0.1 -P 7777.'  Please note that you cannot use localhost, as MySQL treats that differently.  If necessary, append '-u -p' to specify a MySQL account and password.  If you get a "permission denied" message, check the grant statements on the MySQL master. Grants here should be tied to the master's real IP address, since that is where the forwarded MySQLl connection will be coming from. The forwarded connection does not come from localhost or 127.0.0.1.

Setting Up Replication
Now that the tunnel is up, set up replication. This process is similar to a typical MySQL replication configuration.  Edit the my.cnf file on the master, adding the following two lines:log-bin=mysql-bin
server-id=1

Next, create the replication account.  In MySQL, run the following queries:

CREATE USER 'replicationuser'@'master_ip' IDENTIFIED BY 'replicationpassword';
GRANT REPLICATION SLAVE ON *.* TO 'replicationuser'@'master_ip';

On the slave, add to the my.cnf file:

server-id = 2
master-host = 127.0.0.1
master-user = replicationuser
master-password = replicationpassword
master-port = 7777Restart both MySQL services on the master and slave. For newly created replication environments, you may need to manually copy the database to the slave. Follow the MySQL manual (section 16.1) for more information on creating data snapshots and additional replication options. When everything is set, check if replication is occurring.  Execute a  "select" query on the master and slave; the results will be identical. Perform an insert, update or delete on the master that touches a record from the "select" result.  Wait a few seconds and re-execute the "select" query. If replication is working, the data will still match.

At this point, you may want to consider setting up preshared RSA keys instead of having to type in a password.  By using keys, you can set up watchdog shell scripts to ensure that the SSH tunnel is still active, and if the tunnel fails, it will restart automatically. Also, consider creating a cron job on the master to update a table with the current unix timestamp.  The slaves can have a cron job checking for this value.  If it gets too far behind the current timestamp, replication may be broken and the admin can get an email or page.

Complications With Replication
There are two important items to remember about MySQL replication.  First, this is about disaster recovery and high availability, not backup.  Every data-altering statement executed on the master is repeated on the slave. So if you accidentally type in "DELETE FROM mytables" and forget to put a WHERE clause in there, your data is going to be lost on both systems. The second thing to remember is that you are able to create, update and delete data on the slave. I've seen developers create applications that ran on both the master and slave, for high availability, and update a replicated table, breaking the replication every time.  Logic needs to be built into applications to check if it is on an inactive slave system and not write to replicated tables. Have your developers unit test on a replicated setup.