MySQL Backups

Losing production data is not some rite of passage.

Almost everybody has these stories:

Running development tasks against a production database
Deleting the wrong data
Dropping tables

I've been there. It sucks.

The crazy part are that these are seen as eventualities. And maybe they are!

But they don't have to be catastrophic. What if we could ensure they were just minor annoyances?

Your CRON isn't enough.

It's a great start, but it's not enough to prevent future headaches.

Periodic backups are just that - periodic. You'll lose any new data after that backup.
The binary log, disabled by default, enables the most powerful recovery tooling.
Popular utilities don't backup binary logs, and don't stream backups directly off-site.
Tracking & alerting of backup failures & metrics are an afterthought at best.

The Concepts:

There's just a few concepts you need to know to really understand how to make effective backups.

Transactions & Isolation Levels - We'll cover transactions and MySQL's isolation levels, which will teach us how to correctly make use of mysqldump
Binary Logs & Log Format - We'll cover why you always want binary logs enabled, and the pros/cons of the 3 formats they can be saved as

Two styles of backups:

Even done properly, periodic backups on a busy database are out of date before they're even finished. On the flip side, recovering from continuous backups can take hours to rebuild from query history.

We need both styles of backups to be truly effective. This course will cover:

Periodic Backups - In-depth mysqldump, and what you need to know about data consistency
Continuous Backups - Prevent data loss by keeping a continuous backup of binary log files

No, I'm not setting you up to for database replication (although that's good too)!

We'll cover ways to keep your database backups as up to date as possible without forcing you to manage another database.

Manage your backups:

Untested backups are a gamble. We'll power-up our backup strategy by covering:

Automated Backups - Automating your backups beyond a periodic CRON task.
Tested Backups - Strategies for testing your backups so you don't end up like Gitlab.

Course Outline:

The Concepts

InnoDB, transactions & isolation
Binary Logs

The Magic

Configuring the Binary Logs
Recovery with Binary Logs
Point in Time Recovery
"Undoing" accidental queries

Backing Up

Proper use of mysqldump for logical backups
Xtrabackup for "physical" backups
Offsite-backups
Continuous Backups

The Next Level

Testing Backups
Automating Backups

The Videos:

43 videos • 6 hours at 1x

1. InnoDB and Transactions

InnoDB Matters
Transactions Matter
Single Transaction in Practice

2. Mysqldump

Backup Users and the Opt Flag
Mysqldump Utilities
Defaults Files & Env Vars
General Mysqldump Usage
Backing Up Everything

3. Xtrabackup

Intro to Xtrabackup
Installing Xtrabackup
Using Xtrabackup
Xtrabackup Prepare Step
Restoring a Backup

4. Offsite Backups

Intro to Offsite Backups
Backups over SSH
Mysqldump over SSH
Xtrabackup over SSH
Offsite to S3
Restoring a Mysqldump Backup from Offsite
Restoring Xtrabackup from Offsite
Restoring From S3

5. Encrypted Backups

Encrypting Mysqldump Backups
Encrypting Xtrabackup Backups

6. Using the Binlog

The Binary Log
Configuring the Binary Log
Binlog Format
Why the Binary Log
The Mysqlbinlog Utility
Mysqldump with the Binlog
Xtrabackup with the Binlog
Basic Recovery with the Binlog
Undoing Queries
Query Surgery
Point in Time Recovery
Continuous Backups

7. Automation & Testing

How We'll Automate Backups
Shelling Out Commands
Shelling Out to SSH
Running a Backup
Saving Command Results
Backup Metrics
Sending Notifications
Testing Backups

$ whoami

I'm Chris Fidao. I create videos and articles for developers and their servers at Servers for Hackers. I try to help as many developers as I can learn the basics of web servers so they can avoid server-related nightmares.

In case you're interested, I also made Scaling Laravel, Shipping Docker, and the Deploy PHP! video series.