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?

Available Now!

Get the details on improving your backups,
learn how to prevent data loss,
and undo bad queries.

Pay with PayPal. Add your email to the note!

Luckily, guarding against data loss isn't as hard as you think!

With just a few key concepts and examples, you'll know how to guard against accidents and server failures with minimum or no data loss!

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.

Ever wished for a way to "undo" queries?

We'll cover how to rewrite query history and use point-in-time recovery.

Sound like something you want?

Sign up for more information and videos on improvements you can implement immediately!

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

servers for hackers book

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.