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.
Note: Updated the scp command per Tim’s comment
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-A:/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
and living in Sunnyvale, CA.
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.
March 2nd, 2009 at 1:58 am
This line:
tunnel@Server-B# scp ~/.ssh/id_rsa.pub Server-B:/home/tunnel/
Should read:
tunnel@Server-B# scp ~/.ssh/id_rsa.pub Server-A:/home/tunnel/
You may need to chmod 400 ~/.ssh*
on Server-A or your connections will be denied.
Also you will want to manually test the connection to both ports as it will ask you to verify the host before running the script.
The script should also use a variable for user for the test connection. It’s been parametrized for the mysql connection so any change in user will mess up your checks.
March 3rd, 2009 at 11:05 pm
@Tim, Thanks for the tip. I updated the instructions for the scp.
March 19th, 2009 at 3:40 am
A very interesting article!
June 16th, 2009 at 6:41 pm
I was trying to reinvent this wheel. I had a Yugo. Then I came across your post. Now, I have a Ferrari.
Thank you!
June 16th, 2009 at 7:01 pm
I made some modifications to make this a little more portable. This will also allow you to create multiple tunnels.
#!/bin/bash # Creates an SSH tunnel to allow local access to a remote mysql server. echo "*******************************" echo `date` user=$1 server=$2 remote_port=$3 local_port=$4 createTunnel() { /usr/bin/ssh -T -f -N -L${local_port}:127.0.0.1:${remote_port} ${user}@${server} if [[ $? -eq 0 ]]; then echo ${local_port} Tunnel to ${server} created successfully else echo An error occurred creating tunnel ${local_port} to ${server} RC was $? fi /usr/bin/ssh -T -f -N -L19922:127.0.0.1:22 ${user}@${server} if [[ $? -eq 0 ]]; then echo L19922 Tunnel to ${server} created successfully else echo An error occurred creating tunnel L19922 to ${server} RC was $? fi } ## Run the 'ls' command remotely. If it returns non-zero, then create a new connection echo Remote Directory Listing Verifies Connection echo "----------------------------------" /usr/bin/ssh -p 19922 ${user}@localhost ls if [[ $? -ne 0 ]]; then echo Creating new tunnel connection createTunnel else echo Tunnel already exists fi echo "*******************************"You can then cron this by calling it as follows :
July 2nd, 2009 at 8:35 am
Hi thank you very much for sharing the knowledge. One minor issue, I don’t have to login to the remote server following the steps above, but the tunnel check (ls) to localhost prompts me to login. Is there a way to fix this?
July 2nd, 2009 at 8:47 am
Sorry.. found it. Have to use the tunnel user to login. Not another account.
July 2nd, 2009 at 9:07 am
I’ve actually modified this a bit more. Now, you no longer need to create the additional forwarded port to 19922. That was used to confirm the port did exist. However, I found it did NOT prove the forwarded 3306 port was up.
The modifications now use the actual database on the remove server to prove you still have a good connection.
In order to use this, you have to create a new user on the remote db. That user should be named ’status_test’. Do NOT give the user any privileges.
Now, this user can do nothing except connect to the db and check it’s status.
Here is the script :
#!/bin/bash # Creates an SSH tunnel to allow local access to a remote mysql server. # Call as follows : ./tunnel_setup.bsh user_name_here server_address_here remote_port_here local_port_here # Requires ssh keys for the user running the script or the user that CRON is setup under echo "*******************************" echo `date` user=$1 server=$2 remote_port=$3 local_port=$4 createTunnel() { /usr/bin/ssh -T -f -N -L${local_port}:127.0.0.1:${remote_port} ${user}@${server} if [[ $? -eq 0 ]]; then echo ${local_port} Tunnel to ${server} created successfully else echo An error occurred creating tunnel ${local_port} to ${server} RC was $? fi } ## Run the mysqladmin status command remotely. If it returns non-zero, then create a new connection echo Verifying Database Connection echo "----------------------------------" /opt/local/bin/mysqladmin -u status_test -p my_bogus_password -h127.0.0.1 -P${local_port} status if [[ $? -ne 0 ]]; then echo Creating new tunnel connection createTunnel else echo Tunnel already exists fi echo "*******************************"Then, create a cron that starts this script every X minutes. It will run the the mysqladmin status command. If the connection does not exist, the script will automatically recreate the tunnel.
Hope this helps everyone.
July 2nd, 2009 at 9:11 am
@Justin, I haven’t had a chance to try out your script but it looks like a better way to verify the connection. Thanks for sharing.
February 18th, 2010 at 2:51 pm
@Justin,
The problem with your initial script is that you have two separate SSH commands that create your two tunnels. That allows them to fail independently, so you can’t use one to infer the status of the other.
If you look at the original script from Brandon’s site, it forwards two ports within the same tunnel (2 -L options). Because they both are part of the same SSH, they’ll either both be up or both be down, so I can use the SSH port-forward to check on the MySQL port-forward. This way, you don’t need to create additional MySQL users.
Hope this simplifies things a bit.
March 7th, 2010 at 11:56 am
Hi. Great article, thanks! It’s been extremely helpful!
One small addition – you might probably want to add a lock file to this script, so that it can only be executed once at a time, and have it exit if the lock file already exists, otherwise you get a lot of problems if for some reason the scripts doesn’t exit, due to some socket issues, and cron executes a new copy of script every minute. :)
March 7th, 2010 at 11:57 am
@Justin
Great idea with using mysqladmin to check the database status!