{"id":49959,"date":"2025-07-06T12:36:25","date_gmt":"2025-07-06T12:36:25","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=49959"},"modified":"2025-07-06T12:40:27","modified_gmt":"2025-07-06T12:40:27","slug":"database-backup-recovery-plan-a-complete-guide","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/database-backup-recovery-plan-a-complete-guide\/","title":{"rendered":"Database Backup &amp; Recovery Plan  &#8211; A Complete Guide"},"content":{"rendered":"\n<p>I\u2019ll give you a <strong>full, all-in-one guide<\/strong> for production-grade MySQL (and general relational DB) backups that covers:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>All common strategies and tools<\/li>\n\n\n\n<li>How to combine full, incremental, and binlog-based backups<\/li>\n\n\n\n<li>Detecting changes since last backup (down to table\/row level, where possible)<\/li>\n\n\n\n<li>Restoring easily and reliably<\/li>\n\n\n\n<li>Addressing hacking\/data corruption recovery scenarios<\/li>\n\n\n\n<li>Pros, cons, and best scenarios for each method<\/li>\n<\/ul>\n\n\n\n<p>This will be relevant for <em>MySQL\/MariaDB<\/em> primarily, but the approaches will map to Postgres, MS SQL, Oracle, etc. I\u2019ll also touch on cloud-native and container-native (K8s) scenarios, and key security best practices.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">1. <strong>Why &#8220;Just Full Dumps&#8221; Are Not Enough<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Full dumps (mysqldump, etc.) are simple<\/strong> and give you point-in-time backups, but:\n<ul class=\"wp-block-list\">\n<li>They miss changes since the last dump (can lose days of data).<\/li>\n\n\n\n<li>They are SLOW for large DBs.<\/li>\n\n\n\n<li>You cannot easily audit changes between dumps.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>In production, you need:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><em>Frequent, efficient backups (including incremental)<\/em><\/li>\n\n\n\n<li><em>Change tracking\/auditing<\/em><\/li>\n\n\n\n<li><em>Rapid, reliable restores (including point-in-time)<\/em><\/li>\n\n\n\n<li><em>Ability to detect or recover from corruption\/hacking, ideally restoring to \u201clast good\u201d state<\/em><\/li>\n\n\n\n<li><em>Table-level (or even row-level) recovery, if possible<\/em><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. <strong>All Major Production Database Backup Strategies<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>A. Logical Backups (Full Dumps)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Tool:<\/strong> <code>mysqldump<\/code>, <code>mysqlpump<\/code>, <code>mydumper<\/code><\/li>\n\n\n\n<li><strong>What:<\/strong> Exports data as SQL (<code>CREATE<\/code>, <code>INSERT<\/code> statements), easy to move\/copy.<\/li>\n\n\n\n<li><strong>Pros:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Human-readable, portable across versions<\/li>\n\n\n\n<li>Easy to script and automate<\/li>\n\n\n\n<li>Good for small\/medium DBs, or schema-only backups<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cons:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Slow for large DBs<\/li>\n\n\n\n<li>Can miss changes made <em>during<\/em> dump (unless locking\/consistent snapshot)<\/li>\n\n\n\n<li>Inefficient for incremental backups<\/li>\n\n\n\n<li>No automatic change tracking\/audit<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Best for:<\/strong> Small\/medium DBs, migrations, offsite copies<\/li>\n\n\n\n<li><strong>Restore:<\/strong> Easy (just import SQL)<\/li>\n\n\n\n<li><strong>Tools:<\/strong> <code>mysqldump<\/code>, <code>mysqlpump<\/code>, <code>mydumper<\/code> (faster, parallel)<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>mysqldump -u root -p --single-transaction --all-databases > all_databases.sql<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>B. Physical (Hot) Backups<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Tool:<\/strong> <code>Percona XtraBackup<\/code>, MySQL Enterprise Backup, LVM snapshots, filesystem-level copy with flush<\/li>\n\n\n\n<li><strong>What:<\/strong> Copies DB files <em>exactly<\/em> as on disk, can be done live.<\/li>\n\n\n\n<li><strong>Pros:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Very fast, even for huge DBs (no export\/import step)<\/li>\n\n\n\n<li>Supports incremental and differential backups<\/li>\n\n\n\n<li>Enables point-in-time recovery when combined with binlogs<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cons:<\/strong>\n<ul class=\"wp-block-list\">\n<li>More complex to restore (need compatible server version)<\/li>\n\n\n\n<li>Can\u2019t restore to a different RDBMS or different major version<\/li>\n\n\n\n<li>File-level, so OS\/FS-specific<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Best for:<\/strong> Large production DBs, when uptime is crucial<\/li>\n\n\n\n<li><strong>Restore:<\/strong> Fast, but requires stopping server (copy files back, apply logs)<\/li>\n\n\n\n<li><strong>Tools:<\/strong>\n<ul class=\"wp-block-list\">\n<li><code>Percona XtraBackup<\/code> (open source, highly recommended)<\/li>\n\n\n\n<li><code>MySQL Enterprise Backup<\/code> (paid)<\/li>\n\n\n\n<li>LVM\/ZFS snapshots (requires filesystem support)<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>xtrabackup --backup --target-dir=\/data\/backups\/2024-07-06 # To prepare and restore, follow XtraBackup docs<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>C. Binary Log (Binlog) Based Backups<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Tool:<\/strong> MySQL binary logs, <code>mysqlbinlog<\/code><\/li>\n\n\n\n<li><strong>What:<\/strong> Binlogs record all changes (insert\/update\/delete) as they happen<\/li>\n\n\n\n<li><strong>Pros:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Enables <em>point-in-time<\/em> recovery (PITR)<\/li>\n\n\n\n<li>Can replay all changes up to a precise point (before corruption\/hack)<\/li>\n\n\n\n<li>Used for replication and auditing<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cons:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Needs binlog enabled\/configured<\/li>\n\n\n\n<li>Need full backup to start from, then replay binlogs<\/li>\n\n\n\n<li>Can be complex to manage for large change volumes<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Best for:<\/strong> Recovering to &#8220;just before&#8221; an incident; critical for audit trails<\/li>\n\n\n\n<li><strong>Restore:<\/strong>\n<ol class=\"wp-block-list\">\n<li>Restore last full\/incremental backup<\/li>\n\n\n\n<li>Apply binlogs up to desired timestamp\/transaction<\/li>\n<\/ol>\n<\/li>\n\n\n\n<li><strong>Tools:<\/strong>\n<ul class=\"wp-block-list\">\n<li><code>mysqlbinlog<\/code><\/li>\n\n\n\n<li>XtraBackup (can help with binlog application)<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>mysqlbinlog --start-datetime=\"2024-07-05 10:00:00\" --stop-datetime=\"2024-07-06 13:00:00\" binlog.000123 | mysql -u root -p<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>D. Incremental &amp; Differential Backups<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Tool:<\/strong> <code>Percona XtraBackup<\/code>, <code>MySQL Enterprise Backup<\/code>, some cloud-native solutions<\/li>\n\n\n\n<li><strong>What:<\/strong> Only backs up changes since last backup (incremental: since last <em>any<\/em> backup, differential: since last full backup)<\/li>\n\n\n\n<li><strong>Pros:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Saves time, bandwidth, storage<\/li>\n\n\n\n<li>Enables granular restore<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cons:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Need chain of backups to restore (full + all incrementals since)<\/li>\n\n\n\n<li>Complex management for many backups<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Best for:<\/strong> Large, active DBs where daily full backups are too costly<\/li>\n\n\n\n<li><strong>Restore:<\/strong> Restore full, then each incremental in order<\/li>\n\n\n\n<li><strong>Tools:<\/strong>\n<ul class=\"wp-block-list\">\n<li>XtraBackup incremental mode<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code># Full backup xtrabackup --backup --target-dir=\/data\/backups\/base # Incremental backup xtrabackup --backup --target-dir=\/data\/backups\/inc1 --incremental-basedir=\/data\/backups\/base<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>E. Snapshots (LVM\/ZFS\/Cloud)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Tool:<\/strong> LVM, ZFS, EBS Snapshots (AWS), GCP PD Snapshots, K8s CSI snapshots<\/li>\n\n\n\n<li><strong>What:<\/strong> Filesystem or block-device level snapshot, typically near-instant<\/li>\n\n\n\n<li><strong>Pros:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Extremely fast (seconds\/minutes)<\/li>\n\n\n\n<li>Minimal performance hit<\/li>\n\n\n\n<li>Great for DR, can be automated and stored offsite<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cons:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Often not application-aware (must flush\/suspend DB writes, or use MySQL FLUSH TABLES)<\/li>\n\n\n\n<li>Restores the <em>entire<\/em> volume (not table-level)<\/li>\n\n\n\n<li>Can be expensive for frequent snapshots<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Best for:<\/strong> Large, cloud-hosted DBs, disaster recovery<\/li>\n\n\n\n<li><strong>Restore:<\/strong> Attach volume or mount snapshot, copy back data<\/li>\n\n\n\n<li><strong>Tools:<\/strong>\n<ul class=\"wp-block-list\">\n<li><code>lvm snapshot<\/code>, <code>zfs snapshot<\/code>, AWS CLI, GCP CLI, K8s VolumeSnapshot CRD<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>lvcreate --snapshot --size 10G --name mysql_snap \/dev\/vg0\/mysql<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>F. Change Data Capture (CDC)\/Audit Logging<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Tool:<\/strong> Binlogs, MySQL audit plugins, third-party CDC tools (Debezium, Maxwell)<\/li>\n\n\n\n<li><strong>What:<\/strong> Continuously records\/logs all changes, not just for backup but for auditing or replication<\/li>\n\n\n\n<li><strong>Pros:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Full change visibility (who\/what\/when)<\/li>\n\n\n\n<li>Real-time integration possible (stream to SIEM, analytics, etc.)<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cons:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Not a backup itself, but a supplement<\/li>\n\n\n\n<li>Storage\/management of logs<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Best for:<\/strong> Security, compliance, forensic investigations<\/li>\n\n\n\n<li><strong>Restore:<\/strong> Not for restore, but can reconstruct data or investigate incidents<\/li>\n\n\n\n<li><strong>Tools:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Debezium, Maxwell, native MySQL Audit plugin<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>G. Cloud-Native and K8s-Integrated Backups<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Tool:<\/strong> <code>Velero<\/code>, cloud provider tools (AWS RDS snapshots, GCP Cloud SQL backups)<\/li>\n\n\n\n<li><strong>What:<\/strong> Managed, automated backups with integration to cloud\/k8s lifecycle<\/li>\n\n\n\n<li><strong>Pros:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Highly automated, integrated with orchestration<\/li>\n\n\n\n<li>Offsite\/region support, point-in-time restore<\/li>\n\n\n\n<li>Easy to enable PITR, cross-region<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cons:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Dependent on cloud provider (lock-in)<\/li>\n\n\n\n<li>Not always portable to other clouds\/on-prem<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Best for:<\/strong> Cloud-native, K8s-native, SaaS workloads<\/li>\n\n\n\n<li><strong>Restore:<\/strong> Provider-managed, one-click or API-based<\/li>\n\n\n\n<li><strong>Tools:<\/strong>\n<ul class=\"wp-block-list\">\n<li><code>Velero<\/code>, RDS\/CloudSQL\/Azure backup APIs<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>H. Rsync\/File Copy<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Tool:<\/strong> <code>rsync<\/code>, <code>scp<\/code>, <code>rclone<\/code><\/li>\n\n\n\n<li><strong>What:<\/strong> Copies DB files directly, may be used with downtime<\/li>\n\n\n\n<li><strong>Pros:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Simple, can be efficient if DB is not active<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cons:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Not safe if DB is live (risk of corruption)<\/li>\n\n\n\n<li>Not consistent for running databases unless DB is stopped or flushed<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Best for:<\/strong> Small, non-critical DBs, or as a secondary backup<\/li>\n\n\n\n<li><strong>Restore:<\/strong> Manual file copy<\/li>\n\n\n\n<li><strong>Tools:<\/strong> <code>rsync<\/code>, <code>scp<\/code><\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>rsync -a \/var\/lib\/mysql \/mnt\/backup\/mysql-2024-07-06<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">3. <strong>Detecting and Auditing Changes (Table\/Row-Level Audit)<\/strong><\/h2>\n\n\n\n<p>No backup alone will <em>audit<\/em> all changes. For that, combine with:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Binary Logs:<\/strong> Show all modifications; can use <code>mysqlbinlog<\/code> to analyze changes<\/li>\n\n\n\n<li><strong>CDC Tools:<\/strong> Debezium, Maxwell, or Audit Plugins for near-real-time change streaming<\/li>\n\n\n\n<li><strong>Audit Tables\/Triggers:<\/strong> Custom triggers to log changes (can impact performance)<\/li>\n\n\n\n<li><strong>Row-based Replication:<\/strong> Captures <em>what<\/em> changed, not just SQL statements<\/li>\n<\/ul>\n\n\n\n<p><strong>To audit what changed since last backup:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Dump binlogs for the period<\/strong><\/li>\n\n\n\n<li><strong>Parse them for relevant statements<\/strong><\/li>\n\n\n\n<li><strong>Use tools\/scripts to extract DML\/DDL changes<\/strong><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">4. <strong>Recovery from Hacking or Corruption: Best Practice Flow<\/strong><\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Identify incident window<\/strong> (when hack\/corruption started)<\/li>\n\n\n\n<li><strong>Take snapshot immediately<\/strong> (capture current state for forensics)<\/li>\n\n\n\n<li><strong>Restore last known-good full backup<\/strong><\/li>\n\n\n\n<li><strong>Apply binlogs up to the point BEFORE hack\/corruption<\/strong>\n<ul class=\"wp-block-list\">\n<li>If you have audit logs, review for suspicious changes<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>(Optional) Restore\/merge only clean tables\/rows from latest to avoid data loss<\/strong>\n<ul class=\"wp-block-list\">\n<li>More complex; needs careful scripting and validation<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<p><strong>Tip:<\/strong> Keep several <em>layers<\/em> of backup (full, incremental, binlogs, snapshots). For high-value data, enable CDC\/audit, so you can identify <em>exactly<\/em> what was changed\/deleted.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">5. <strong>How to Set Up a Complete, Production-Grade Backup System<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>A. Enable Binary Logging<\/strong><\/h3>\n\n\n\n<p>In <code>my.cnf<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">&#91;mysqld]\nlog-bin=mysql-bin\nbinlog-format=ROW\nserver-id=1\n<\/code><\/span><\/pre>\n\n\n<h3 class=\"wp-block-heading\"><strong>B. Schedule Regular Full + Incremental + Binlog Backups<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Weekly full physical (XtraBackup)<\/strong><\/li>\n\n\n\n<li><strong>Daily incremental<\/strong><\/li>\n\n\n\n<li><strong>Continuous binlog archiving<\/strong><\/li>\n\n\n\n<li><strong>Optional: snapshot (cloud\/LVM) before dangerous upgrades or major changes<\/strong><\/li>\n\n\n\n<li><strong>Optional: logical dump weekly (mysqldump)<\/strong><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>C. Automate and Monitor<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use cron jobs, Ansible, or backup orchestrators (e.g. Automysqlbackup, Backup Ninja, Barman for Postgres)<\/li>\n\n\n\n<li>Store backups <strong>offsite<\/strong> and\/or in cloud<\/li>\n\n\n\n<li>Regularly test <em>restore<\/em> (don\u2019t trust untested backups!)<\/li>\n\n\n\n<li>Keep backup inventory and hash\/validate files<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>D. Track and Audit Changes<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Archive and parse binlogs<\/li>\n\n\n\n<li>Use audit plugin\/CDC tool if needed<\/li>\n\n\n\n<li>Log all backup\/restore operations (for compliance)<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>E. Security Best Practices<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Encrypt backups at rest and in transit<\/li>\n\n\n\n<li>Protect access to backup storage<\/li>\n\n\n\n<li>Use database users with least privilege for backup tasks<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">6. <strong>Summary Table: Production Backup Methods<\/strong><\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Method<\/th><th>Backup Type<\/th><th>Pros<\/th><th>Cons<\/th><th>Best For<\/th><th>Restore Complexity<\/th><th>Tools<\/th><\/tr><\/thead><tbody><tr><td>Logical dump (mysqldump)<\/td><td>Full\/logical<\/td><td>Simple, portable, readable<\/td><td>Slow for big DBs, can miss new changes<\/td><td>Small\/med, migration<\/td><td>Easy<\/td><td>mysqldump, mydumper<\/td><\/tr><tr><td>Physical (XtraBackup)<\/td><td>Full\/incr<\/td><td>Fast, reliable, point-in-time<\/td><td>Tied to engine\/version, more complex<\/td><td>Large, prod, 24&#215;7<\/td><td>Moderate<\/td><td>XtraBackup, MEB<\/td><\/tr><tr><td>Binlog-based<\/td><td>PITR<\/td><td>Point-in-time, audit trail<\/td><td>Need binlogs + base, management<\/td><td>All prod DBs<\/td><td>Moderate<\/td><td>mysqlbinlog<\/td><\/tr><tr><td>Incremental physical<\/td><td>Incremental<\/td><td>Storage\/bandwidth efficient<\/td><td>Needs full+all increments to restore<\/td><td>Large, active<\/td><td>Moderate<\/td><td>XtraBackup<\/td><\/tr><tr><td>Snapshots (LVM\/EBS)<\/td><td>Volume\/block<\/td><td>Instant, disaster recovery<\/td><td>Not app-aware, full volume restore<\/td><td>Large, cloud, DR<\/td><td>Moderate<\/td><td>LVM, EBS, Velero<\/td><\/tr><tr><td>Rsync\/file copy<\/td><td>File-level<\/td><td>Simple, familiar<\/td><td>Corruption risk, not live safe<\/td><td>Small, non-critical<\/td><td>Easy<\/td><td>rsync, scp<\/td><\/tr><tr><td>CDC\/Audit<\/td><td>Audit\/log<\/td><td>Change tracking, forensic recovery<\/td><td>Not a backup, for audit only<\/td><td>Compliance, security<\/td><td>N\/A<\/td><td>Debezium, audit plugin<\/td><\/tr><tr><td>Cloud-native backup<\/td><td>Full\/incr<\/td><td>Managed, easy, integrated, automated<\/td><td>Cloud lock-in, costs, portability<\/td><td>Cloud, SaaS, K8s<\/td><td>Very easy<\/td><td>AWS RDS, Velero, etc<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">7. <strong>Which is Best for Which Scenario?<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Mission-critical, large DB:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Physical (XtraBackup) + Binlogs + Incremental + Snapshots<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Cloud (RDS, CloudSQL):<\/strong>\n<ul class=\"wp-block-list\">\n<li>Use provider\u2019s managed backup, plus extra logical export for portability<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Compliance\/Audit-heavy:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Binlog archive + CDC\/audit plugin<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Dev\/UAT\/Small apps:<\/strong>\n<ul class=\"wp-block-list\">\n<li>mysqldump\/rsync is fine<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Disaster recovery:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Snapshots (LVM\/cloud) for instant recovery, plus full backup\/restore practice<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">8. <strong>How to Restore: Step-by-Step<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>A. Full Logical Dump<\/strong><\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">mysql<\/span> <span class=\"hljs-selector-tag\">-u<\/span> <span class=\"hljs-selector-tag\">root<\/span> <span class=\"hljs-selector-tag\">-p<\/span> &lt; <span class=\"hljs-selector-tag\">all_databases<\/span><span class=\"hljs-selector-class\">.sql<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\"><strong>B. Physical Backup (XtraBackup)<\/strong><\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Stop MySQL<\/li>\n\n\n\n<li>Restore files from backup dir to data dir<\/li>\n\n\n\n<li>Apply logs (if needed)<\/li>\n\n\n\n<li>Start MySQL<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>C. Binlog Replay (PITR)<\/strong><\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Restore full backup (logical or physical)<\/li>\n\n\n\n<li>Get binlogs for target period<\/li>\n\n\n\n<li>Replay with <code>mysqlbinlog<\/code> (stop before \u201cbad\u201d transaction)<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>D. Cloud\/K8s<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use provider\u2019s restore wizard\/API<\/li>\n\n\n\n<li>For K8s: <code>kubectl apply -f volumesnapshot.yaml<\/code>, or use Velero<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">9. <strong>Production Checklist<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Full backup <strong>tested restore<\/strong> at least monthly<\/li>\n\n\n\n<li>Binlogs archived and tested for PITR<\/li>\n\n\n\n<li>Automated monitoring &amp; alerts for backup failures<\/li>\n\n\n\n<li>Offsite\/cross-region copies<\/li>\n\n\n\n<li>Backups encrypted and access controlled<\/li>\n\n\n\n<li>Backup retention and cleanup policy<\/li>\n\n\n\n<li>Documented restore\/runbook for disaster\/hack\/corruption scenarios<\/li>\n\n\n\n<li>Table\/row-level audit\/logs for sensitive environments<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">10. <strong>Recommended Tools<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Physical\/Incremental:<\/strong> Percona XtraBackup<\/li>\n\n\n\n<li><strong>Logical:<\/strong> <code>mysqldump<\/code>, <code>mysqlpump<\/code>, mydumper<\/li>\n\n\n\n<li><strong>Binlog replay:<\/strong> <code>mysqlbinlog<\/code><\/li>\n\n\n\n<li><strong>Snapshots:<\/strong> LVM\/ZFS, <code>aws ec2 create-snapshot<\/code>, Velero (K8s)<\/li>\n\n\n\n<li><strong>Orchestration:<\/strong> Automysqlbackup, Backup Ninja, Ansible, cron<\/li>\n\n\n\n<li><strong>Audit\/CDC:<\/strong> Debezium, Maxwell, MySQL Audit Plugin<\/li>\n\n\n\n<li><strong>Cloud backup:<\/strong> RDS\/CloudSQL\/GCP\/Azure backup APIs<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">11. <strong>Final Advice: Designing a Bulletproof Backup &amp; Recovery Plan<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Mix full\/incremental\/point-in-time backups<\/strong> for max coverage and minimal data loss.<\/li>\n\n\n\n<li><strong>ALWAYS test restores!<\/strong> (Automate test restores if possible.)<\/li>\n\n\n\n<li><strong>Backups are only as good as your ability to restore<\/strong> \u2014 have step-by-step guides\/runbooks.<\/li>\n\n\n\n<li><strong>Audit for changes<\/strong>: Use binlogs, audit plugins, and regular reports.<\/li>\n\n\n\n<li><strong>Protect backups like production data<\/strong> \u2014 encrypt, limit access, monitor.<\/li>\n\n\n\n<li><strong>Automate and document everything.<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">12. <strong>Sample Architecture for Your Scenario<\/strong><\/h2>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">&#91;MySQL DB] \n   |         |----------------------------|\n   |         |                            |\n&#91;Full Weekly Physical Backup (XtraBackup)]|---&gt; &#91;Offsite Storage]\n   |                                      |\n&#91;Daily Incremental (XtraBackup)]          |\n   |                                      |\n&#91;Continuous Binlog Archive] --------------|\n   |\n&#91;Audit\/CDC Plugin \u2192 Change Log]\n   |\n&#91;LVM or Cloud Snapshot before risky upgrades]\n<\/code><\/span><\/pre>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">13. <strong>Sample Restore Flow (After Hack\/Corruption)<\/strong><\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Identify <em>last known good backup<\/em> before hack\/corruption<\/li>\n\n\n\n<li>Restore full\/incremental backup to new server (safe\/isolated env)<\/li>\n\n\n\n<li>Apply binlogs up to just before attack<\/li>\n\n\n\n<li>Review binlogs\/audit for unwanted changes<\/li>\n\n\n\n<li>Test, validate, <em>then<\/em> switch application traffic back<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p><strong>If you need scripts, tool-specific configs, or automation code for any scenario above, let me know your stack (bare metal, cloud, K8s, etc.) and I&#8217;ll provide hands-on examples!<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>TL;DR:<\/strong><\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p><strong>For serious production:<\/strong><br>Weekly full + daily incremental + continuous binlog backup + audit\/change capture + snapshot before upgrades.<\/p>\n<\/blockquote>\n\n\n\n<p>Use XtraBackup for physical\/incremental, mysqldump\/mydumper for logical, automate and test restores, and use binlogs for point-in-time recovery and change auditing.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Reference<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/sreschool.com\/blog\/the-definitive-guide-to-production-mysql-backup-and-restore\/\" target=\"_blank\" rel=\"noopener\">https:\/\/sreschool.com\/blog\/the-definitive-guide-to-production-mysql-backup-and-restore\/<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.rajeshkumar.xyz\/blog\/the-complete-mysql-database-backup-and-restore-guide-for-production-environments\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.rajeshkumar.xyz\/blog\/the-complete-mysql-database-backup-and-restore-guide-for-production-environments\/<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.devopsschool.com\/blog\/database-backup-recovery-plan-a-complete-guide\/\">https:\/\/www.devopsschool.com\/blog\/database-backup-recovery-plan-a-complete-guide\/<\/a><\/li>\n<\/ul>\n\n\n\n<p>&#8211;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I\u2019ll give you a full, all-in-one guide for production-grade MySQL (and general relational DB) backups that covers: This will be relevant for MySQL\/MariaDB primarily, but the approaches will map to&#8230; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-49959","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/49959","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=49959"}],"version-history":[{"count":3,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/49959\/revisions"}],"predecessor-version":[{"id":49963,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/49959\/revisions\/49963"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=49959"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=49959"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=49959"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}