# Full backup
mysqldump -u root -p --all-databases > backup_full_2025_12_17.sql

# Specific database
mysqldump -u root -p mstore-monolith > backup_mstore_2025_12_17.sql

# With compression
mysqldump -u root -p mstore-monolith | gzip > backup_mstore_2025_12_17.sql.gz

# Full backup
xtrabackup --backup --target-dir=/backup/full_2025_12_17

# Incremental backup
xtrabackup --backup --target-dir=/backup/incr_2025_12_17 \
  --incremental-basedir=/backup/full_2025_12_17

# Create snapshot
aws rds create-db-snapshot \
  --db-instance-identifier mstore-prod \
  --db-snapshot-identifier mstore-snapshot-2025-12-17

# List snapshots
aws rds describe-db-snapshots \
  --db-instance-identifier mstore-prod

00:00 — Full backup (off-peak)
06:00 — Incremental backup
12:00 — Incremental backup
18:00 — Incremental backup

#!/bin/bash
# backup.sh

BACKUP_DIR="/backups/mysql"
DB_NAME="mstore-monolith"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/backup_${DB_NAME}_${DATE}.sql.gz"

# Create backup
mysqldump -u root -p$DB_PASSWORD $DB_NAME | gzip > $BACKUP_FILE

# Upload to S3
aws s3 cp $BACKUP_FILE s3://mstore-backups/mysql/

# Keep only last 7 days
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

# Log backup
echo "Backup completed: $BACKUP_FILE" >> /var/log/backup.log

# Daily full backup at 00:00
0 0 * * * /usr/local/bin/backup.sh

# Incremental backup every 6 hours
0 */6 * * * /usr/local/bin/backup_incremental.sh

# From SQL dump
mysql -u root -p < backup_mstore_2025_12_17.sql

# From compressed dump
gunzip < backup_mstore_2025_12_17.sql.gz | mysql -u root -p

# From Percona backup
xtrabackup --prepare --target-dir=/backup/full_2025_12_17
xtrabackup --copy-back --target-dir=/backup/full_2025_12_17

# Extract single table from backup
mysqldump -u root -p mstore-monolith merchants > merchants_backup.sql

# Restore specific table
mysql -u root -p mstore-monolith < merchants_backup.sql

# Restore with different name
mysql -u root -p mstore-monolith < merchants_backup.sql --table=merchants_restored

# Find binary log position
mysqlbinlog /var/log/mysql/mysql-bin.000001 | grep "2025-12-17 14:30:00"

# Restore to specific time
mysqlbinlog --stop-datetime="2025-12-17 14:30:00" \
  /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# Restore to specific position
mysqlbinlog --stop-position=12345 \
  /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# Restore to new instance
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier mstore-restored \
  --db-snapshot-identifier mstore-snapshot-2025-12-17

# Restore to point-in-time
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier mstore-prod \
  --target-db-instance-identifier mstore-pitr \
  --restore-time 2025-12-17T14:30:00Z

# Check backup file size
ls -lh backup_mstore_2025_12_17.sql.gz

# Verify SQL syntax
mysql -u root -p --syntax-check < backup_mstore_2025_12_17.sql

# Test restore on staging
mysql -u root -p mstore_staging < backup_mstore_2025_12_17.sql

# Compare row counts
mysql -u root -p -e "SELECT COUNT(*) FROM mstore-monolith.merchants;"

-- Check binary log status
SHOW MASTER STATUS;

-- Check backup status
SELECT * FROM mysql.backup_history;

-- Verify replication lag (if using replication)
SHOW SLAVE STATUS\G