MySQL Replication for Offsite Backups
I'm cheap. I tend to run pet projects on shoe string budgets. For one, it's just a good habit. It's easier to increase spending when it's really necessary (after some growth and revenue) than it is to turn off a service that you've come to rely on.
Whatever the case may be, having backups is crucial. Most businesses do not recover from a failure that includes data loss if they don't have backups. I have a production server that is running out there somewhere and if it were to go down, I need to be certain that I don't lose my data. It could go down for any reason at any time. Network issues, hardware failure, ISP going out of business, etc. So I want to be sure that I have all my important data where I need it, backed up at home on media under my direct control.
Although MySQL on my production servers is typically configured to only listen on localhost (127.0.0.1), I use SSH port forwarding to access it remotely.
The command for that is rather easy. On my laptop (runs Linux) I use the following command:
$ ssh user@example.com -L 3300:localhost:3306
Aside from using SSH to log into my server it also means that any connection made to port 3300 on my laptop is forwarded to the server, and on that side it'll connect to localhost port 3306, which is where MySQL is listening. That way I can fire up the MySQL Query Browser on my laptop connect to localhost port 3300 and work on my server's MySQL instance. Everything else is transparent, not to mention encrypted.
So with that in mind, I configure my production machine's MySQL server for replication (turn on binlog). And on the slave side (A headless Linux machine I have sitting under my desk at home) I setup key based authentication and use the following command:
$ while [ 1 ]; do ssh user@example.com -L 3300:localhost:3306 -N; echo "reconnecting..."; done
This will keep reconnecting in case of a connection failure. The MySQL slave will keep retrying to get to the master and I don't really have to worry about much.
Print This Post