How to Backup a MySQL Database (Complete Guide)

Introduction

Database backups are your last line of defense. This guide covers every method to backup MySQL — from quick one-liners to production-grade automated systems.

Quick Backup with mysqldump

mysqldump is the standard tool for MySQL backups. It creates a SQL file that can recreate the database.

# Backup a single database:
mysqldump -u root -p my_database > backup.sql

# Backup with compression:
mysqldump -u root -p my_database | gzip > backup_$(date +%Y%m%d).sql.gz

# Backup all databases:
mysqldump -u root -p --all-databases > all_databases.sql

# Backup specific tables:
mysqldump -u root -p my_database users orders > tables_backup.sql

Production-Safe Backup Options

For production databases, use options that minimize locking and ensure consistency.

# InnoDB-safe backup (no table locking):
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  my_database > backup.sql

# With compression and progress:
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  my_database | pv | gzip > backup.sql.gz

Restore from Backup

Restoring a mysqldump backup is straightforward.

# Restore a database:
mysql -u root -p my_database < backup.sql

# Restore from compressed backup:
gunzip < backup.sql.gz | mysql -u root -p my_database

# Create database first if needed:
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS my_database;"
mysql -u root -p my_database < backup.sql

Automated Backup Script

A production backup script with rotation, compression, and error handling.

#!/bin/bash
# MySQL Automated Backup Script

DB_USER="backup_user"
DB_PASS="secure_password"
DB_NAME="my_database"
BACKUP_DIR="/backups/mysql"
DAYS_TO_KEEP=7
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup with compression
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines --triggers --events \
  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Check if backup succeeded
if [ $? -eq 0 ]; then
  echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"
else
  echo "ERROR: Backup failed!" >&2
  exit 1
fi

# Delete backups older than N days
find $BACKUP_DIR -name "*.sql.gz" -mtime +$DAYS_TO_KEEP -delete
echo "Cleaned up backups older than $DAYS_TO_KEEP days"

Binary Backup with Percona XtraBackup

For large databases (100GB+), XtraBackup is faster and supports incremental backups.

# Full binary backup (no locks, no downtime):
xtrabackup --backup \
  --target-dir=/backups/full \
  --user=root --password=secret

# Incremental backup (only changes since last full):
xtrabackup --backup \
  --target-dir=/backups/inc1 \
  --incremental-basedir=/backups/full \
  --user=root --password=secret

# Prepare and restore:
xtrabackup --prepare --target-dir=/backups/full
xtrabackup --copy-back --target-dir=/backups/full

Best Practices

• Automate backups — never rely on manual backups
• Test restores regularly (at least monthly)
• Use --single-transaction for InnoDB to avoid locks
• Keep at least 7 days of daily backups and 4 weeks of weekly backups
• Store backups off-site (S3, GCS) in case of server failure

Generate MySQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct MySQL query instantly.

Frequently Asked Questions