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?

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!

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 information on when it will be available!

Course Outline:

The Concepts

  • InnoDB, transactions & isolation
  • Binary Logs and Global Transaction IDs

The Magic

  • 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

$ 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.