I found myself with some spare time the other day and decided that my current mysql backup strategy is not the best in the world. The mysql server is a virtual machine in a Brisbane datacenter and it's backed up via a script that calls mysqldump on each installed database and dumps the content to (compressed) files. These files then get sucked down via rdiff-backup.
This is fine in principle, but does mean it's possible for me to lose 24 hours worth of data due to an accidental '--; DROP table students.
A more ideal way would be for the remote sql server to replicate to a local one, on which I can run mysqldump more often without affecting web site performance. (Replication would replicate the DROP table statement too.. :-)
With a bit of a confluence of attending three days of OpenQuery mysql training and needing to regenerate all ssl keys, I thought I should …
[Read more]