October
10th, 2008
Secure mysql replication between colos over an ssh tunnel
MySql replication is pretty painless to implement. But that doesn’t mean you shouldn’t consider exactly how you roll it out. There’s plenty of things to consider and for me one of them was how to best replicate between colos. Such a deployment raises issues (security, latency, etc) that aren’t as big of a concern when you’re doing it across a LAN. I haven’t had a chance to tackle the latency issue yet (that’s another blog post) but here were my security concerns:
- Data transmitted across the Internet shouldn’t be easily read (in my case mainly email addresses and passwords)
- MySql shouldn’t be listening publicly on ANY port
- Whatever the solution is needs to be simple…complex solutions are often - wrong
For the sake of this post I’ll be referring to the master database server as Server-A and the slave server as Server-B.
Secure transmission of data over the Internet
Before I began searching for options I knew in the back of my head that ssh tunnels were probably the way to go. I began my search for different tips and tricks to creating a persistent ssh tunnel. I came across several methods but liked the one proposed by Brandon Checketts the best. You should, of course, create an unprivileged user (named tunnel perhaps) on both Server-A and Server-B. With a little bit of tweaking I decided to go with the script below which sits on Server-B.
#!/bin/sh
echo "*******************************"
echo date
user=tunnel
server=Server-A
createTunnel() {
/usr/bin/ssh -f -N -L4306:127.0.0.1:3306 -L19922:127.0.0.1:22 ${user}@${server}
if [[ $? -eq 0 ]]; then
echo Tunnel to ${server} created successfully
else
echo An error occurred creating a tunnel to ${server} RC was $?
fi
}
## Run the 'ls' command remotely. If it returns non-zero, then create a new connection
/usr/bin/ssh -p 19922 tunnel@localhost ls
if [[ $? -ne 0 ]]; then
echo Creating new tunnel connection
createTunnel
else
echo Tunnel already exists
fi
echo "*******************************"
I threw in a few extra echos in there since I plan on having this run in cron every minute or so and output to a log file. Let’s quickly go over what this does. The script sets up a few variables. Then the createTunnel function is defined. It attempts to create an ssh connection specifying several things: -f sends it to the background, -N tells it not to execute a command after connecting, -L binds a local port to a remote one over which we can tunnel. Easy enough, no?
You may have noticed that we mapped 2 different ports. Brandon decided to map a port for MySql and one which he later users to check if the connection is up. On to that check now. The first action the script takes is to do a remote ls command issued from Server-B on Server-A. This is done by ssh’ing on port 19922 to localhost which actually goes over the tunnel to Server-A. It then issues the ls command. The next step is to check if the command returned 0 which would tell us that the connection (or more specifically the mapping of local port 19922 to remote port 22) is not working. In that case we call the createTunnel function we defined earlier which should create the tunnel. This script should rarely have to do this once the connection is created the first time.
We’re not finished yet. Since this is being automated in cron we’ll want to set up key based authentication. This will allow the tunnel user to connect from Server-B to Server-A without having to provide a password.
tunnel@Server-B# ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/tunnel/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/tunnel/.ssh/id_rsa. Your public key has been saved in /home/tunnel/.ssh/id_rsa.pub. tunnel@Server-B# scp ~/.ssh/id_rsa.pub Server-B:/home/tunnel/ tunnel@Server-B# ssh Server-A password: tunnel@Server-A# cat id_rsa.pub >> ~/.ssh/authorized_keys tunnel@Server-A# rm id_rsa.pub tunnel@Server-A# vi /etc/sshd/sshd_config # Make sure the following lines are uncommented RSAAuthentication yes PubkeyAuthentication yes tunnel@Server-A# su - privileged_user privileged_user@Server-A# sudo /etc/init.d/sshd restart password: privileged_user@Server-A# exit tunnel@Server-A# exit tunnel@Server-B# ssh Server-A tunnel@Server-A# # Yay no password required
Keeping MySql a secret
Noone needs to know that you’re running MySql. There are several ways to do this using a firewall or binding MySql to 127.0.0.1 depending on your setup. I’m assuming a single webserver with MySql running on it in which case we’d want to bind to 127.0.0.1. Ensure this is the case by looking at my.cnf on both Server-A and Server-B.
user# vi /etc/mysql/my.cnf # Make sure this is uncommented bind-address = 127.0.0.1
Since we specified -L4306:127.0.0.1:3306 the connection request from Server-B to Server-A will appear to MySql as coming from 127.0.0.1.
user@server-B# mysql -u root -p -h 127.0.0.1 -P 4306 Enter password: mysql>
It’s important to connect to host 127.0.0.1 and not localhost. MySql connects to the local socket and bypasses TCP/IP when using localhost.
Time to replicate
At this point you can set up replication as normal. Here’s a basic setup. On Server-A add the following to your my.cnf. I’m not going to go into populating the slave server with data in this post but you’ll undoubtably need to do that.
user@Server-A# sudo vi /etc/mysql/my.cnf log-bin server-id = 1 binlog-do-db = name_of_db binlog-do-db = name_of_another_db user@Server-A# sudo /etc/init.d/mysql restart
On Server-B add the following to your my.cnf
user@Server-B# sudo vi /etc/mysql/my.cnf master-host = 127.0.0.1 master-user = tunnel master-password = tunnel_users_password master-port = 13306 server-id = 2 binlog-do-db = name_of_db binlog-do-db = name_of_another_db binlog-ignore-db= mysql binlog-ignore-db= test binlog-ignore-db= information_schema user@Server-B# sudo /etc/init.d/mysql restart
The beauty of simplicity
This solution in my mind is elegantly simple. It uses completely standard applications which are already installed on both servers. The application doesn’t have to do much custom work since it can connect to the master as needed by specifying port 4306. I haven’t had this running for an extended period of time (just a week so far) but plan on keeping an eye on it. If you have any experiences or suggestions I’d love to hear them in the comments.
Resources
October 10th, 2008 at 4:32 pm
Hi. Glad that you find my instructions helpful. I have been running a server like this with tunnels to 3 other servers for several months now and it works very well.
The only thing I have noticed, is that if the connection between the server goes down for a while, then there can get to be multiple ‘check_tunnel’ scripts running that have to be killed manually. Other than that, I’ve had the connection between the servers die multiple times, and replication always starts right back up.
You might also be interested in the script I created to Monitor the replication to ensure that it doesn’t get behind.
Also, you’ll want to Enable replication compression between those servers to minimize the bandwidth used.
October 10th, 2008 at 5:30 pm
Or, you could just use MySQL’s built in encryption support…
What is the point of this?
October 10th, 2008 at 9:45 pm
@Tom, not sure what built in encryption support you’re referring to. Even if MySql did have some sort of encryption support this method still allows you to keep MySql from listening publicly on port 3306.
October 10th, 2008 at 9:46 pm
@Brandon, Thanks for the tips. I’ll definitely check out the two posts you mentioned (and subscribe via RSS).
October 13th, 2008 at 12:04 pm
Built in MySQL encryption: http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html
Having port 3306 open is probably just as secure or insecure as having a random ssh port open somewhere.
October 13th, 2008 at 12:53 pm
@Tom, Thanks for pointing that out - I’ll add that option to the post. While this addresses #1 and #3, there’s still security to be gained by not having mysql listening publicly.
I’m assuming that there’s some ssh entry point to the machine (if not directly through a firewall). So having ssh open on some port is a given. The more applications you have listening publicly the more chances you have for being exploited. Note, I’m not saying Apache is (or isn’t) more secure than MySql, but the more entry points the more likely something can happen.
Personally, I prefer ports 80, 443 and a non standard port for ssh.
October 15th, 2008 at 3:49 am
Very informative. Thanks for the information!
Raj Malhotra
PHP Programming India