Databases…. Once upon a time most people used databases as a single file. One of my first clients had an Access database that would get corrupted so often and I’d have to fix it for them. Over the years I was introduced to database servers and for a long time they were kind of spooky black boxes. I didn’t like doing much with database servers because I didn’t know much about them. Then I had to start recovering data from one.
It wasn’t that bad. Compared to the corrupted Access files I had seen it was easy. As I learned more I really loved MySQL and now MariaDB. I have made use of PostgreSQL at times as well. What Microsoft Access once had me despising and treating as a black box that you put data in and hope it comes back out…. Open source database servers gave me new confidence that you could pick up the pieces if something bad happened.
Of course, one step along the way is learning how to back up databases. We’ve automated the backups of our clients databases now with BASH scripts usually. Of course, it’s good to test now and then and verify that the database dump is good and can be restored, but outside of those checks it’s really something that “just happens” with our backup structure. For that matter, THOSE database dumps then get backed up as well on a routine basis. Do you need to roll back to the database from yesterday? Ok. three weeks ago. Sure.
Replication is something we have relatively recent experience with. We can have a database copied real time (or with a delay) to another server. So that we can either have high availability (like a high traffic website) or to have disaster recovery. (Main db server is down? Then flip a switch and put the backup in its place.) We’ve taken a couple approaches to replication here. In some cases we use Master-Master when we want two servers to be able to sync changes. Sometimes we just use Master-Slave Replication when we’re looking simply to have a live backup.
One important point though. Replication should NEVER replace backups. Let me repeat this…. replication should NEVER replace backups. It can certainly complement your backup strategy by having a highly available live plan b, but if you rely on replication as your only backup of your database….. you will get burned it’s just a matter of time.