Master MySQL Backup & Disaster Recovery: Strategies, Tools, and Automation

Introduction

Data is a critical, often non‑renewable asset for enterprises; loss of key data such as orders or transaction records can cripple a business. Therefore MySQL databases must be protected with reliable backup and disaster‑recovery mechanisms.

Backup Types

MySQL backup can be classified by state, method, and scope:

Cold backup – database stopped, copy physical files.

Hot backup – online backup without service interruption (e.g., mysqldump, XtraBackup).

Warm backup – online backup that locks tables briefly.

Physical backup – copy of data files and logs.

Logical backup – export of SQL statements (mysqldump, mydumper, mysqlpump).

Local backup – stored on the same server.

Remote backup – transferred to another site.

Full, incremental and binary‑log backups.

Common Backup Tools

Typical tools used in production:

mysqldump – single‑threaded logical backup.

mydumper – multi‑threaded logical export.

mysqlpump – parallel logical backup introduced in MySQL 5.7.

Percona XtraBackup (or MySQL Enterprise Backup) – fast physical backup for InnoDB/MyISAM.

Backup Principles and Schedule

In the “Mobile Cloud” environment the policy is:

Full backup once a week.

Incremental backup every day.

Local backups retained for 21 days; older data moved to remote storage (NetBackup).

Backups are triggered by crontab at 02:00 am and stored under a dedicated script directory (e.g., /path/sh/backup.sh) with logs in /path/logs/backuplog/.

Full Backup Procedure

Execute a physical backup with XtraBackup:

innobackupex --defaults-file=/path/conf/my.cnf --host=127.0.0.1 --port=3306 --user=mysql --password=backup /path/backup_dir/

The command prints the backup directory, binlog position and a success message.

Prepare Phase

After the backup, make the backup set consistent:

innobackupex --defaults-file=/path/conf/my.cnf --apply-log /path/backup_dir/2019-08-27_17-36-16

The output ends with “completed OK”, indicating the data is ready for restore.

Restore Phase

Stop MySQL, then copy files back:

innobackupex --defaults-file=/path/conf/my.cnf --copy-back /path/backup_dir/2019-08-27_17-36-16

Adjust ownership: chown -R mysql:mysql /path/datadir If datadir is not empty, add --force-non-empty-directories to the copy‑back command.

Incremental Backup

First create a full backup, then run incremental backups based on the LSN:

innobackupex --defaults-file=/path/conf/my.cnf --incremental --incremental-basedir=/path/backup_dir/ /path/incremental_backup_dir

The resulting xtrabackup‑checkpoints file records backup_type=incremental and the LSN range.

Prepare incremental backups with:

Apply log on the base full backup with --redo-only.

Apply each incremental backup (except the last) with --redo-only and --incremental-dir.

Apply the final incremental backup without --redo-only.

Remote Backup

Two remote strategies are used:

Rate‑limited stream backup

On the remote host:

mkdir -p /tmp/backup
nc -l port | pv -q -L 100m | tar -xi

On the DB host:

innobackupex --defaults-file=/path/conf/my.cnf --stream=tar --tmpdir=/tmp/tmpbackup --salve-info /tmp/backup /path/backup | gzip > /path/backup/test.tar.gz

NetBackup

NetBackup transfers data to a dedicated backup server, where policies decide storage media and retention. It also supports point‑in‑time recovery using combined full and binary‑log backups.

Common Pitfalls

Local backups consume disk, CPU, memory and I/O, affecting production load.

Lack of centralized management leads to inconsistent scripts and difficult recovery drills.

Backup file validation is often omitted, risking unusable backups.

Running backups on primary nodes can impact service; prefer slaves or read‑only replicas.

Backup Management Platform

A web‑based platform built with Django (backend) and Vue.js (frontend) integrates the above tools, provides role‑based menus, audit logs, and asynchronous task execution via Celery. Ansible distributes backup scripts, Redis caches dashboard data, and MySQL stores metadata.

Key functions include:

Execute backups and restores on demand.

View history, status, size and validation results.

Configure per‑database backup policies (schedule, retention, method).

Monitor backup jobs on the homepage.

Disaster Recovery (RPO / RTO)

RTO (Recovery Time Objective) measures the maximum acceptable downtime; RPO (Recovery Point Objective) measures the maximum tolerable data loss. The platform defines backup tiers (A‑D) based on data importance, retention period and type, then calculates appropriate RPO/RTO values.

Database DR Architecture

The production database is replicated asynchronously to a remote site (North‑South data centers). In case of a site failure, the standby node takes over, and after the primary site is restored, data is synchronized back using full copy.

Conclusion

By combining full, incremental, log and remote backups with an automated management platform, “Mobile Cloud” achieves a comprehensive MySQL disaster‑recovery solution that safeguards data across multiple data centers and supports rapid recovery, high availability and fine‑grained backup control.