MySQL Backup & Restore

Backup Methods

mysqldump -u root -p
mydb > backup.sql

cp -r /var/lib/mysql
/backup/mysql

mysqlbinlog 
--start-datetime
binlog.000001

lvcreate --snapshot
--name snap
/dev/vg/mysql

🔹 Using mysqldump

mysqldump is the most common backup tool, creating SQL files containing all database structures and data. It works while the database is running and produces portable backups that can be restored on any MySQL server. Perfect for small to medium databases.

-- Backup single database
mysqldump -u root -p mydb > mydb_backup.sql

-- Backup multiple databases
mysqldump -u root -p --databases db1 db2 db3 > multi_backup.sql

-- Backup all databases
mysqldump -u root -p --all-databases > all_backup.sql

-- Backup specific tables
mysqldump -u root -p mydb table1 table2 > tables_backup.sql

-- Backup with compression
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

🔹 Advanced mysqldump Options

mysqldump offers many options to customize backups for different scenarios. Use these flags to control what gets backed up, how data is formatted, and whether to lock tables during backup. Choose options based on your database size and uptime requirements.

-- Backup structure only (no data)
mysqldump -u root -p --no-data mydb > structure.sql

-- Backup data only (no structure)
mysqldump -u root -p --no-create-info mydb > data.sql

-- Include routines and triggers
mysqldump -u root -p --routines --triggers mydb > full_backup.sql

-- Single transaction (for InnoDB)
mysqldump -u root -p --single-transaction mydb > consistent_backup.sql

-- Add drop table statements
mysqldump -u root -p --add-drop-table mydb > backup_with_drops.sql

🔹 Restoring from Backup

Restore databases by importing SQL backup files using the mysql command. The database must exist before restoring unless your backup includes CREATE DATABASE statements. Always test restores on a non-production server first to verify backup integrity.

-- Restore single database
mysql -u root -p mydb < mydb_backup.sql

-- Restore all databases
mysql -u root -p < all_backup.sql

-- Restore compressed backup
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

-- Restore specific tables
mysql -u root -p mydb < tables_backup.sql

-- Create database and restore
mysql -u root -p -e "CREATE DATABASE mydb;"
mysql -u root -p mydb < mydb_backup.sql

🔹 Automated Backup Script

Automate backups with shell scripts scheduled via cron. This ensures regular backups without manual intervention. Include date stamps in filenames, compress backups to save space, and implement retention policies to manage old backups automatically.

#!/bin/bash
# backup_mysql.sh

# Configuration
USER="root"
PASSWORD="your_password"
DATABASE="mydb"
BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup directory
mkdir -p $BACKUP_DIR

# Perform backup
mysqldump -u $USER -p$PASSWORD $DATABASE | gzip > $BACKUP_DIR/${DATABASE}_${DATE}.sql.gz

# Delete backups older than 7 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${DATABASE}_${DATE}.sql.gz"

🔹 Binary Log Backup

Binary logs record all database changes, enabling point-in-time recovery. Combined with full backups, you can restore to any moment between backups. Essential for production systems where data loss must be minimized. Enable binary logging in MySQL configuration.

-- Enable binary logging (in my.cnf)
-- log-bin=mysql-bin
-- server-id=1

-- Show binary logs
SHOW BINARY LOGS;

-- View binary log contents
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- Flush logs (create new binary log file)
FLUSH LOGS;

-- Backup binary logs
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

-- Point-in-time recovery
mysqlbinlog --start-datetime="2024-01-01 10:00:00" \
            --stop-datetime="2024-01-01 11:00:00" \
            mysql-bin.000001 | mysql -u root -p

🔹 Physical Backup Methods

Physical backups copy database files directly from disk, making them faster for large databases. However, they require stopping MySQL or using special tools. Use physical backups for very large databases where mysqldump would take too long to complete.

-- Stop MySQL service
sudo systemctl stop mysql

-- Copy data directory
sudo cp -r /var/lib/mysql /backup/mysql_physical

-- Start MySQL service
sudo systemctl start mysql

-- Using MySQL Enterprise Backup (commercial)
mysqlbackup --user=root --password --backup-dir=/backup backup

-- Restore physical backup
sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql
sudo cp -r /backup/mysql_physical /var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

🔹 Backup Best Practices

Follow these practices to ensure reliable backups: test restores regularly, store backups off-site, encrypt sensitive data, monitor backup success, and document your procedures. A backup is only useful if you can successfully restore from it when needed.

-- Test backup integrity
gunzip < backup.sql.gz | head -n 20

-- Verify backup can be restored
mysql -u root -p test_restore < backup.sql

-- Schedule daily backups (crontab)
# 0 2 * * * /path/to/backup_script.sh

-- Monitor backup size
ls -lh /backups/mysql/*.sql.gz

-- Encrypt backup
mysqldump -u root -p mydb | gzip | openssl enc -aes-256-cbc -salt -out backup.sql.gz.enc

-- Decrypt and restore
openssl enc -d -aes-256-cbc -in backup.sql.gz.enc | gunzip | mysql -u root -p mydb