Complete MySQL/MariaDB Backup and Restore Guide

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

# Compressed backup (gzip)
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

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

# Schema only (no data)
mysqldump -u root -p --no-data mydb > schema_only.sql

# Restore from an SQL file
mysql -u root -p mydb < mydb_backup.sql

# Restore from a compressed file
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# Create the database first if it does not exist
mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p mydb < mydb_backup.sql

#!/bin/bash
DB_USER="backup_user"
DB_PASS="your_password"
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=14

mkdir -p "$BACKUP_DIR"

DATABASES=$(mysql -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" \
  | grep -Ev "Database|information_schema|performance_schema|sys")

for DB in $DATABASES; do
  FILENAME="${BACKUP_DIR}/${DB}_$(date +%Y%m%d_%H%M%S).sql.gz"
  mysqldump -u"$DB_USER" -p"$DB_PASS" \
    --single-transaction \
    --routines \
    --triggers \
    "$DB" | gzip > "$FILENAME"
  echo "Backed up: $FILENAME"
done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete
echo "Backup complete: $(date)"

chmod +x /usr/local/bin/mysql-backup.sh

# Run daily at 3:00 AM
echo "0 3 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1" \
  > /etc/cron.d/mysql-backup

-- Enable binary logging in my.cnf
-- log_bin = /var/log/mysql/mysql-bin.log
-- binlog_format = ROW

-- Check the current binary log position
SHOW MASTER STATUS;

# Restore up to a specific point in time
mysqlbinlog \
  --start-datetime="2024-01-15 10:00:00" \
  --stop-datetime="2024-01-15 11:30:00" \
  /var/log/mysql/mysql-bin.000001 | mysql -u root -p mydb