How To Set Up SSH Encrypted MySQL Replication
Posted by
Michael J. DeMaria
April 30, 2010
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
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:
Page: 1 | 2 | 3 | Next Page »
Related Reading
More Insights
