MySQL Backup and Recovery: Strategies, Tools, and Best Practices

Introduction Backup and recovery are the last line of defense for DBAs. Recent project experiences prompted a review of backup and recovery knowledge to reinforce best practices.

Understanding Backup and Recovery In typical MySQL high‑availability setups, a primary‑replica architecture is used; when the primary fails, the replica can take over. Situations that may require recovery include accidental DROP TABLE (recoverable via binlog in row mode), DROP DATABASE (not recoverable), migration failures, data loss for recent days, or a complete cloud‑platform outage. The goal of backup is to enable restoration after a disaster.

Recommendations for Effective Backup

Deploy a dedicated backup server that pulls data only from local data‑center instances.

Implement mechanisms to handle backup failures so the next scheduled backup can proceed.

Combine logical, physical, and image‑based backups.

Recovery Validation

Build a unified recovery verification server to regularly test backup integrity.

Conduct periodic restore drills.

Perform daily basic checks using file MD5 comparisons when full restores are impractical.

Backup Tools for MySQL

Common backup methods include logical export (mysqldump, mysqlpump, mydumper) and physical export (xtrabackup). The following tools are frequently used:

mysqldump : MySQL's built‑in logical backup tool that extracts data via the client protocol and generates SQL statements for restoration.

xtrabackup : An open‑source physical backup tool from Percona.

mydumper : A high‑performance, multithreaded logical backup tool for MySQL and Drizzle.

Guidelines:

For data < 10 GB, use mydumper or mysqldump; otherwise prefer xtrabackup.

For single‑table or schema exports, use logical tools.

mysqldump is single‑threaded, mydumper is multithreaded and generally faster (performance order: mysqlpump > mydumper > mysqldump, though mysqlpump has some bugs).

MySQL 8.0’s clone feature shows promise but lacks widespread adoption.

Backup Strategy

Adopt a mix of full and incremental backups, and ensure binlog files are also backed up regularly. Different data sets may require tailored strategies, as illustrated in the accompanying diagrams.

Best Practices

Use xtrabackup for physical backups.

Use mydumper for parallel logical backups.

Store backup files locally or on NFS.

Leverage binlog2sql for point‑in‑time recovery.

Consider delayed replicas for additional recovery options.

When possible, perform daily system‑level image backups with deduplication.

Backup Failure Issues

Common causes of backup failures include GTID mismatches, DDL‑related errors, log‑copy speed bottlenecks, insufficient disk space, and ungraceful termination of backup threads. The article provides sample error messages and analysis.

Production Cases

Case 1 – GTID inconsistency : xtrabackup’s GTID info differed from the instance’s GTID. Solution: flush logs before backup and verify GTID via SHOW MASTER STATUS and SELECT * FROM mysql.gtid_executed , or use GTID_PURGED.

Case 2 – SQL‑induced failures : Blocking statements, long‑query timeouts, and lock settings caused backup interruptions.

Case 3 – DDL‑induced failures InnoDB: Last flushed lsn ... PXB will not be able take a consistent backup...

Case 4 – Heavy transaction log flushing / I/O bottlenecks xtrabackup: error: log block numbers mismatch...

Case 5 – Insufficient space innobackupex: Error writing file ... (Errcode: 28 - No space left on device)

Case 6 – Backup software handling issues failed to execute query SET SESSION lock_wait_timeout=31536000, MySQL server has gone away.

Additional Considerations

MySQL connection timeout, kill signals, or server restarts can cause "MySQL server has gone away" errors.

When backing up to NFS, ensure data is flushed to the NFS server to avoid inconsistent views.

During recovery, reset permissions on the MySQL data directory, remove old data and binlog files, and verify GTID consistency.

Conclusion

Regular backups are essential; they enable quick resolution of unexpected issues.

Leverage multiple backup methods and platforms to ensure data safety and operational efficiency.