MySQL — Backup

mysqldump Backup

# Single database
mysqldump -u root -p mydb > backup.sql

# With structure and data
mysqldump -u root -p --routines --triggers mydb > full_backup.sql

# Multiple databases
mysqldump -u root -p --databases db1 db2 > multi_backup.sql

# All databases
mysqldump -u root -p --all-databases > all_backup.sql

Restore from Backup

# Create database first
mysql -u root -p -e "CREATE DATABASE mydb"

# Restore
mysql -u root -p mydb < backup.sql

Backup Specific Tables

mysqldump -u root -p mydb customers orders > tables_backup.sql

Backup Without Data

mysqldump -u root -p --no-data mydb > schema_only.sql

Compressed Backup

mysqldump -u root -p mydb | gzip > backup.sql.gz
gunzip < backup.sql.gz | mysql -u root -p mydb

Automated Backup Script

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -p'password' mydb | gzip > "/backups/mydb_$DATE.sql.gz"
# Keep only last 7 days
find /backups -name "mydb_*.sql.gz" -mtime +7 -delete

Backup Methods Comparison

Method	Pros	Cons
mysqldump	Simple, portable	Slow for large DBs
mysqlpump	Parallel threads	Limited options
xtrabackup	Hot backup, fast	Requires setup
Logical backup	Human readable	Larger files

Point-in-Time Recovery

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

# Apply binary logs up to a point
mysqlbinlog --stop-datetime="2025-01-01 12:00:00" binlog.000001 | mysql -u root -p mydb

Mini Practice

Write shell commands that:

1. Backs up a single database with mysqldump
2. Restores from a backup file
3. Backs up specific tables
4. Creates a compressed backup

Up Next

Congratulations! You've completed the MySQL course. Continue exploring advanced topics like replication, clustering, and performance tuning.