Network Computing is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

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:

  • 1