Backup Strategies

Introduction

Regular backups are essential for protecting your data against loss, corruption, or disasters. This guide covers comprehensive backup strategies for MySQL databases in WAMP/XAMPP environments.

Types of Backups

1. Logical Backups (mysqldump)

Advantages:

Disadvantages:

2. Physical Backups

Advantages:

Disadvantages:

3. Incremental Backups

Advantages:

Disadvantages:

Backup Methods

Method 1: mysqldump (Logical Backup)

Basic Database Backup

# Windows (WAMP)
cd C:\wamp64\bin\mysql\mysql[version]\bin
mysqldump -u root -p database_name > C:\backups\database_name.sql

# Windows (XAMPP)
cd C:\xampp\mysql\bin
mysqldump -u root -p database_name > C:\backups\database_name.sql

# Linux/Mac
mysqldump -u root -p database_name > /backups/database_name.sql

All Databases

mysqldump -u root -p --all-databases > all_databases.sql

Specific Tables

mysqldump -u root -p database_name table1 table2 > tables.sql

With Compression

mysqldump -u root -p database_name | gzip > database_name.sql.gz

Complete Backup with Options

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --flush-logs \
  database_name > database_name_$(date +%Y%m%d_%H%M%S).sql

Options Explained:

Method 2: Using phpMyAdmin

Export

  1. Open phpMyAdmin
  2. Select database
  3. Click “Export” tab
  4. Choose “Custom” method
  5. Configure options:
    • Format: SQL
    • Add DROP TABLE
    • Add CREATE TABLE
    • Data: Complete inserts
  6. Click “Go”

Automated Export

// PHP script for automated phpMyAdmin-style export
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$db = 'database_name';

$filename = "backup_" . date('Ymd_His') . ".sql";

// Execute mysqldump
exec("mysqldump -u $user -p$pass $db > $filename");

echo "Backup created: $filename";
?>

Method 3: Physical File Backup

Stop MySQL First:

# WAMP: Stop from system tray
# XAMPP: Stop from control panel

# Or via command line
net stop mysql

Copy Data Directory:

# WAMP
xcopy C:\wamp64\bin\mysql\mysql[version]\data\database_name C:\backups\database_name /E /I

# XAMPP
xcopy C:\xampp\mysql\data\database_name C:\backups\database_name /E /I

Start MySQL Again:

net start mysql

Method 4: Binary Log Backups (Point-in-Time Recovery)

Enable Binary Logging in my.ini:

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
server-id=1

Backup Binary Logs:

mysqlbinlog mysql-bin.000001 > binlog_backup.sql

Backup Schedules

Small Databases (< 1GB):

Medium Databases (1-10GB):

Large Databases (> 10GB):

Example Schedule

Type Frequency Retention Notes
Full Sunday 2 AM 4 weeks Complete database
Differential Daily 2 AM 1 week Changes since last full
Incremental Hourly 24 hours Changes since last backup
Transaction logs Continuous 1 week For point-in-time recovery

Automated Backup Scripts

Windows Batch Script

Create backup.bat:

@echo off
REM MySQL Backup Script

REM Configuration
set MYSQL_BIN=C:\xampp\mysql\bin
set BACKUP_DIR=C:\backups
set DB_NAME=mydatabase
set DB_USER=root
set DB_PASS=yourpassword

REM Create backup directory if not exists
if not exist %BACKUP_DIR% mkdir %BACKUP_DIR%

REM Generate filename with timestamp
set TIMESTAMP=%date:~-4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%%time:~6,2%
set TIMESTAMP=%TIMESTAMP: =0%
set FILENAME=%DB_NAME%_%TIMESTAMP%.sql

REM Perform backup
echo Backing up %DB_NAME%...
%MYSQL_BIN%\mysqldump -u %DB_USER% -p%DB_PASS% ^
  --single-transaction ^
  --routines ^
  --triggers ^
  %DB_NAME% > %BACKUP_DIR%\%FILENAME%

REM Compress backup
echo Compressing backup...
powershell Compress-Archive -Path %BACKUP_DIR%\%FILENAME% -DestinationPath %BACKUP_DIR%\%FILENAME%.zip
del %BACKUP_DIR%\%FILENAME%

REM Delete old backups (keep last 7 days)
forfiles /p %BACKUP_DIR% /s /m *.zip /d -7 /c "cmd /c del @path"

echo Backup completed: %FILENAME%.zip

Schedule with Task Scheduler:

  1. Open Task Scheduler
  2. Create Basic Task
  3. Name: “MySQL Daily Backup”
  4. Trigger: Daily at 2:00 AM
  5. Action: Start a program
  6. Program: C:\path\to\backup.bat
  7. Finish

Linux/Mac Shell Script

Create backup.sh:

#!/bin/bash

# Configuration
MYSQL_USER="root"
MYSQL_PASS="yourpassword"
DB_NAME="mydatabase"
BACKUP_DIR="/backups"
RETENTION_DAYS=7

# Create backup directory
mkdir -p $BACKUP_DIR

# Generate filename
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILENAME="${DB_NAME}_${TIMESTAMP}.sql"

# Perform backup
echo "Backing up $DB_NAME..."
mysqldump -u $MYSQL_USER -p$MYSQL_PASS \
  --single-transaction \
  --routines \
  --triggers \
  $DB_NAME > $BACKUP_DIR/$FILENAME

# Compress
echo "Compressing..."
gzip $BACKUP_DIR/$FILENAME

# Delete old backups
echo "Cleaning old backups..."
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: ${FILENAME}.gz"

# Optional: Upload to cloud storage
# aws s3 cp $BACKUP_DIR/${FILENAME}.gz s3://your-bucket/backups/

Make executable and schedule:

chmod +x backup.sh

# Add to crontab (daily at 2 AM)
crontab -e
# Add line:
0 2 * * * /path/to/backup.sh >> /var/log/mysql_backup.log 2>&1

Restore Procedures

Restore from mysqldump

Basic Restore

mysql -u root -p database_name < backup.sql

Create Database First

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS database_name;"
mysql -u root -p database_name < backup.sql

Restore All Databases

mysql -u root -p < all_databases.sql

Restore from Compressed Backup

gunzip < backup.sql.gz | mysql -u root -p database_name
# or
zcat backup.sql.gz | mysql -u root -p database_name

Restore Using phpMyAdmin

  1. Open phpMyAdmin
  2. Select database (or create new one)
  3. Click “Import” tab
  4. Choose file
  5. Click “Go”

For large files: Increase PHP limits or use command line.

Point-in-Time Recovery

# 1. Restore full backup
mysql -u root -p database_name < full_backup.sql

# 2. Apply binary logs up to specific time
mysqlbinlog --stop-datetime="2024-11-15 14:30:00" \
  mysql-bin.000001 mysql-bin.000002 | \
  mysql -u root -p database_name

Backup Verification

Test Restore Regularly

# Restore to test database
mysql -u root -p -e "CREATE DATABASE test_restore;"
mysql -u root -p test_restore < backup.sql

# Verify tables
mysql -u root -p test_restore -e "SHOW TABLES;"

# Check record counts
mysql -u root -p test_restore -e "SELECT COUNT(*) FROM users;"

# Drop test database
mysql -u root -p -e "DROP DATABASE test_restore;"

Checksum Verification

# Create checksum of backup
md5sum backup.sql > backup.sql.md5

# Verify checksum
md5sum -c backup.sql.md5

Automated Testing

#!/bin/bash
# Test restore script

TEST_DB="test_restore_$(date +%Y%m%d)"
BACKUP_FILE=$1

# Create test database
mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

# Restore
mysql -u root -p $TEST_DB < $BACKUP_FILE

# Verify
TABLE_COUNT=$(mysql -u root -p -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$TEST_DB';")

if [ $TABLE_COUNT -gt 0 ]; then
    echo "✓ Backup verified successfully ($TABLE_COUNT tables restored)"
    mysql -u root -p -e "DROP DATABASE $TEST_DB;"
    exit 0
else
    echo "✗ Backup verification failed"
    exit 1
fi

Backup Storage

Local Storage

Pros:

Cons:

Best practices:

Cloud Storage

Options:

Example: Upload to S3:

# Using AWS CLI
aws s3 cp backup.sql.gz s3://your-bucket/backups/

# Sync directory
aws s3 sync /backups s3://your-bucket/backups/

Example: Upload to Google Drive:

# Using rclone
rclone copy /backups remote:mysql-backups

Network Storage

# Copy to network share
# Windows
xcopy C:\backups\*.sql \\server\backups\ /D

# Linux
rsync -avz /backups/ user@server:/remote/backups/

3-2-1 Backup Rule

3 copies of data:

2 different media types:

1 copy offsite:

Backup Security

Encrypt Backups

# Encrypt with GPG
mysqldump -u root -p database_name | \
  gzip | \
  gpg --encrypt --recipient your@email.com \
  > backup.sql.gz.gpg

# Decrypt and restore
gpg --decrypt backup.sql.gz.gpg | \
  gunzip | \
  mysql -u root -p database_name

Secure Storage

Monitoring and Alerts

Log Backups

# Add logging to backup script
LOG_FILE="/var/log/mysql_backup.log"

echo "$(date): Starting backup" >> $LOG_FILE
# backup commands
if [ $? -eq 0 ]; then
    echo "$(date): Backup successful" >> $LOG_FILE
else
    echo "$(date): Backup failed" >> $LOG_FILE
    # Send alert
fi

Email Notifications

# Send email on completion
echo "Backup completed successfully" | \
  mail -s "MySQL Backup Status" admin@example.com

# Send on failure
if [ $? -ne 0 ]; then
    echo "Backup failed! Check logs." | \
      mail -s "ALERT: MySQL Backup Failed" admin@example.com
fi

Disaster Recovery Plan

1. Documentation

2. Recovery Time Objective (RTO)

3. Recovery Point Objective (RPO)

4. Testing

Checklist

Setup

Regular Tasks

Security

Disaster Recovery

Next Steps

Additional Resources