How To Set Up SSH Encrypted MySQL Replication

Upcoming Events

Are you caught between a desire for the benefits of the cloud and concerns about security and control? Then you should attend this insight-packed webinar to learn how private data networking technologies like MPLS IP-VPNs can address your concerns and allow you to safely and intelligently reap the savings, agility and other benefits associated with cloud computing.

Join us to hear top industry experts discuss the private data network technologies that are best suited for enterprise cloud access requirements. You won't want to miss this opportunity to learn how your organization can best mitigate risk while reaping the full potential benefits of the cloud.

Register Now!

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

Page: 1 | 2 | 3 | Next Page »