~/icsd.ir — bash
SYSTEM_ONLINE
// chapter_12.md

Backup و Restore در MySQL

قبل از روش‌ها، فلسفه:

۱۲.۱ سه قانون backup

قبل از روش‌ها، فلسفه:

  1. Backup که تست نشده، backup نیست. هر ماه یک بار restore را تست کنید.
  2. Off-site: backup روی همان سرور کافی نیست. حداقل یک کپی روی سرور/سرویس دیگر
  3. Recovery Time Objective (RTO): چقدر طول می‌کشد دیتابیس برگردد؟ چقدر داده می‌توانید از دست بدهید (RPO)؟

قانون 3-2-1

  • ۳ نسخه از داده
  • ۲ نوع رسانه (دیسک محلی + cloud یا…)
  • ۱ نسخه off-site

۱۲.۲ mysqldump – Logical Backup

mysqldump یک فایل SQL تولید می‌کند که شامل CREATE TABLE و INSERT است. مزیت: قابل خواندن، قابل انتقال بین نسخه‌ها. عیب: روی دیتابیس‌های بزرگ کند.

Bash
# backup ساده یک دیتابیس
mysqldump -u root -p my_database > backup.sql

# با گزینه‌های production
mysqldump 
    -u backup_user 
    -p 
    --single-transaction         # consistency بدون lock
    --quick                      # سطر به سطر بخوان
    --master-data=2              # binlog position در فایل
    --routines                   # شامل procedure/function
    --events                     # شامل events
    --triggers 
    --default-character-set=utf8mb4 
    my_database > backup_$(date +%Y%m%d_%H%M).sql

# همه دیتابیس‌ها
mysqldump --all-databases [...] > all_databases.sql

# فقط structure (بدون داده)
mysqldump --no-data my_database > schema.sql

# فقط داده‌ی یک جدول
mysqldump my_database orders > orders_data.sql

# فقط ردیف‌های خاص
mysqldump my_database orders --where="created_at >= '2026-01-01'" > recent.sql

# فشرده با gzip
mysqldump [...] my_database | gzip > backup.sql.gz

Restore

Bash
# بازگردانی
mysql -u root -p my_database < backup.sql

# از gzip
gunzip < backup.sql.gz | mysql -u root -p my_database

# فقط جدول‌های انتخابی
mysql my_database < schema.sql
sed -n '/CREATE TABLE `orders`/,/UNLOCK TABLES;/p' backup.sql | mysql my_database
💡 کاربر اختصاصی Backup:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongPass!';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup'@'localhost';

۱۲.۳ mysqlpump - نسخه parallel

MySQL 5.7+ - تقریباً مثل mysqldump اما parallel. سریع‌تر روی سرورهای چند هسته‌ای:

Bash
mysqlpump 
    --user=backup 
    --password 
    --default-parallelism=4      # 4 thread
    --compress-output=lz4 
    --result-file=backup.sql.lz4 
    --include-databases=my_database

۱۲.۴ Percona XtraBackup / Mariabackup - Physical Backup

به‌جای SELECT، فایل‌های دیتابیس را مستقیم کپی می‌کند. سریع‌تر، اما به همان نسخه/پلتفرم محدود است.

برای MySQL: Percona XtraBackup

Bash
# نصب (Ubuntu)
sudo apt install percona-xtrabackup-80 -y

# Full backup
xtrabackup --backup --target-dir=/backup/full 
    --user=backup --password=...

# Apply log (آماده‌سازی برای restore)
xtrabackup --prepare --target-dir=/backup/full

# Incremental backup (فقط تغییرات از backup قبلی)
xtrabackup --backup --target-dir=/backup/inc1 
    --incremental-basedir=/backup/full 
    --user=backup --password=...

# Restore
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

برای MariaDB: Mariabackup

Bash
sudo apt install mariadb-backup -y

mariabackup --backup --target-dir=/backup/full --user=backup --password=...
mariabackup --prepare --target-dir=/backup/full
# restore: مثل xtrabackup

۱۲.۵ Point-in-Time Recovery (PITR)

سناریو: ساعت ۲ صبح backup گرفتید. ساعت ۱۰ صبح کسی به اشتباه DROP TABLE زد. می‌خواهید به ۹:۵۹ برگردید.

مراحل:

  1. Restore کامل از backup ساعت ۲
  2. اعمال binlog از ساعت ۲ تا ۹:۵۹
Bash
# 1. restore backup
mysql my_database < backup_2am.sql

# 2. لیست binlog‌های موجود
mysql -e "SHOW BINARY LOGS;"
# mysql-bin.000123, 000124, 000125

# 3. اعمال binlog تا قبل از حادثه
mysqlbinlog 
    --start-datetime="2026-05-01 02:00:00" 
    --stop-datetime="2026-05-01 09:59:00" 
    /var/log/mysql/mysql-bin.00012{3,4,5} | mysql my_database

# یا بر اساس position دقیق
mysqlbinlog 
    --start-position=4521 
    --stop-position=789012 
    mysql-bin.000123 | mysql my_database

# قبل از این: گاهی بهتر است در یک دیتابیس staging تست شود

۱۲.۶ استراتژی Backup Production

الگوی پیشنهادی

زمان         | کار
─────────────|──────────────────────────────────
هر ساعت      | binlog flush + sync to S3
هر روز ۲am   | mysqldump یا xtrabackup full
هر هفته      | full backup + verify restore
هر ماه       | backup off-site به cloud
هر سه ماه    | تست کامل disaster recovery

Cron Script نمونه

Bash - /usr/local/bin/mysql_backup.sh
#!/bin/bash
set -e

BACKUP_DIR=/backup/mysql
DATE=$(date +%Y%m%d_%H%M%S)
RETAIN_DAYS=30

# Backup
mysqldump 
    --user=backup 
    --password="$MYSQL_BACKUP_PASS" 
    --all-databases 
    --single-transaction 
    --master-data=2 
    --routines --events --triggers 
    --default-character-set=utf8mb4 | 
    gzip > "$BACKUP_DIR/all_${DATE}.sql.gz"

# verify
if [ ! -s "$BACKUP_DIR/all_${DATE}.sql.gz" ]; then
    echo "ERROR: Backup failed" | mail -s "MySQL Backup Failed" admin@icsd.ir
    exit 1
fi

# upload to S3 (یا storage ابری ایرانی مثل ابر آروان)
aws s3 cp "$BACKUP_DIR/all_${DATE}.sql.gz" "s3://my-backups/mysql/"

# پاکسازی قدیمی‌ها
find "$BACKUP_DIR" -name "all_*.sql.gz" -mtime +$RETAIN_DAYS -delete

echo "Backup successful: $DATE"
Crontab
# /etc/cron.d/mysql-backup
0 2 * * * root /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

۱۲.۷ Backup در محیط Windows / XAMPP

برای محیط توسعه یا production کوچک:

Batch - backup.bat
@echo off
set BACKUP_DIR=D:BackupMySQL
set DATE_STAMP=%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%
set DATE_STAMP=%DATE_STAMP: =0%

if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"

C:xamppmysqlbinmysqldump.exe ^
    --user=root ^
    --single-transaction ^
    --routines --events --triggers ^
    --default-character-set=utf8mb4 ^
    --databases my_database > "%BACKUP_DIR%backup_%DATE_STAMP%.sql"

echo Backup: backup_%DATE_STAMP%.sql

REM پاکسازی فایل‌های بیش از 30 روز
forfiles /p "%BACKUP_DIR%" /m *.sql /d -30 /c "cmd /c del @path" 2>nul

برای زمان‌بندی، Task Scheduler ویندوز را استفاده کنید.

۱۲.۸ رمزنگاری Backup

Bash
# رمزنگاری با OpenSSL
mysqldump my_database | gzip | openssl enc -aes-256-cbc -salt -out backup.sql.gz.enc -k "STRONG_KEY"

# decrypt
openssl enc -d -aes-256-cbc -in backup.sql.gz.enc -k "STRONG_KEY" | gunzip | mysql my_database

# با gpg (بهتر)
mysqldump my_database | gzip | gpg --symmetric --cipher-algo AES256 > backup.sql.gz.gpg
gpg --decrypt backup.sql.gz.gpg | gunzip | mysql my_database

۱۲.۹ Verify Backup - مهم‌ترین گام فراموش‌شده

Bash
# 1. چک کردن سایز
ls -la /backup/all_*.sql.gz
# اگر یک‌دفعه نصف شد، مشکلی پیش آمده

# 2. تست restore در staging
gunzip < backup.sql.gz | mysql -h staging-server my_database_test

# 3. تست consistency
mysqlcheck --all-databases --check

# 4. اسکریپت verify خودکار
#!/bin/bash
LATEST=$(ls -t /backup/all_*.sql.gz | head -1)
TEMP_DB="verify_$(date +%s)"

mysql -e "CREATE DATABASE $TEMP_DB"
gunzip < "$LATEST" | sed "s/USE `my_database`/USE `$TEMP_DB`/" | mysql

# چک تعداد جدول‌ها
COUNT=$(mysql -Nse "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$TEMP_DB'")
if [ "$COUNT" -lt 10 ]; then
    echo "ALERT: Backup verify failed - only $COUNT tables"
fi

mysql -e "DROP DATABASE $TEMP_DB"

۱۲.۱۰ خلاصه فصل

  • قانون 3-2-1: سه نسخه، دو رسانه، یک off-site
  • mysqldump برای دیتابیس‌های متوسط، XtraBackup برای بزرگ
  • --single-transaction روی InnoDB consistency بدون lock می‌دهد
  • binlog‌ها را برای PITR نگه دارید
  • Backup که verify نشده، backup نیست. هر ماه restore را تست کنید
  • Production همیشه off-site (S3, cloud ایرانی، سرور دیگر)

نمایش سایت

رنگ سایت
حالت نمایش
اندازهٔ متن
خوانایی

این تنظیمات فقط روی مرورگر شما ذخیره می‌شود.