MySQL backup and recovery
You protect MySQL data with mysqldump, mydumper and physical backups, recover to a point in time with the binlog, measure restore times and test backups regularly.
Level 301 · 15 lessons · 12 practice labs
Lessons
Logical backups
- Backup types: logical, physical, hot and cold
- mysqldump options and consistent backups
- Parallel backups with mydumper
- Restoring a single table or database
- Short task: a nightly dump with rotation
Physical backups and the binlog
- A full physical backup
- Incremental backups
- Binlog formats and retention
- Point-in-time recovery with mysqlbinlog
- Short task: restore an incremental backup chain
Assurance
- Backup encryption and off-site copies
- Restore drills and timing them
- Monitoring backup jobs and alerting
- Copying a server with the clone plugin
- Capstone: recover accidentally deleted customer data
Practice labs
- Take a consistent logical backup: Use the right mysqldump options to back up InnoDB tables consistently without locking.
- Set up a nightly job with rotation: Write a nightly job that takes a backup, compresses it and deletes ones older than seven days.
- Back up and restore a big database in parallel: Compare parallel dump and restore with single-threaded mysqldump in elapsed time.
- Take a physical backup while running: Take a hot physical backup of a running server and prepare it for restore.
- Restore an incremental backup chain: Merge one full and two incremental backups in the right order and restore them to an empty server.
- Diagnose a chain with a missing link: Find the broken link in a failing incremental chain from LSN values and determine the latest recoverable point.
- Set binary log retention: Set a binary log retention period that stops logs from filling the disk and purge old logs safely.
- Recover a dropped table by time: Bring back an accidentally dropped invoice table from a backup and binary log, filtering only that table.
- Replay forward while skipping a bad transaction: Skip a single bad DELETE in the middle of the log while keeping every change before and after it.
- Encrypt a backup and copy it off-site: Encrypt the nightly backup, send it to a second server and verify only the key opens it.
- Time a restore drill: Restore the latest backup to a clean server, time it and compare row counts with the source.
- Catch a backup that silently fails: Find a job that produces zero-byte backups while appearing successful and add alerts on size and exit code.
About this course
You protect MySQL data with mysqldump, mydumper and physical backups, recover to a point in time with the binlog, measure restore times and test backups regularly.
What you will learn
- Logical backups
- Physical backups and the binlog
- Assurance
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.