Бэкап MySQL и MariaDB по отдельным базам: готовый скрипт и проверка восстановления

Ниже — рабочий скрипт, который сохраняет каждую пользовательскую базу MySQL/MariaDB в отдельный сжатый файл, пишет журнал, проверяет ошибки и удаляет старые ежедневные копии. После настройки обязательно выполните тестовое восстановление.

1. Проверяем клиент и доступ

mysql --version
mysqldump --version
mysql -e 'SELECT VERSION(); SHOW DATABASES;'

На Debian/Ubuntu клиент можно установить так:

apt update
apt install default-mysql-client gzip

2. Создаём отдельного пользователя для backup

В MySQL:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'CHANGE_ME_LONG_RANDOM_PASSWORD';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

Для InnoDB с --single-transaction длительные блокировки обычно не нужны, но набор прав зависит от вашей версии и объектов. Сначала проверяйте на тестовой базе.

3. Храним credentials не в командной строке

cat > /root/.my-backup.cnf <<'EOF'
[client]
user=backup
password=CHANGE_ME_LONG_RANDOM_PASSWORD
host=localhost
EOF
chmod 600 /root/.my-backup.cnf
mysql --defaults-extra-file=/root/.my-backup.cnf -e 'SELECT 1;'

4. Создаём каталог и скрипт

install -d -m 700 /var/backups/mysql
cat > /usr/local/sbin/mysql-backup.sh <<'EOF'
#!/usr/bin/env bash
set -Eeuo pipefail

DEST=/var/backups/mysql
CNF=/root/.my-backup.cnf
STAMP=$(date +%F_%H-%M-%S)
LOG=/var/log/mysql-backup.log
TMP=$(mktemp -d)
trap 'rm -rf "$TMP"' EXIT

exec >>"$LOG" 2>&1
echo "[$(date -Is)] backup started"

mapfile -t DBS < <(mysql --defaults-extra-file="$CNF" -NBe \
  "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|mysql|sys)$')

for DB in "${DBS[@]}"; do
  SAFE_DB=${DB//[^a-zA-Z0-9_.-]/_}
  FILE="$DEST/${SAFE_DB}_${STAMP}.sql.gz"
  echo "Dumping $DB to $FILE"
  mysqldump --defaults-extra-file="$CNF" \
    --single-transaction --quick \
    --routines --events --triggers \
    --hex-blob --default-character-set=utf8mb4 \
    --databases "$DB" | gzip -c > "$FILE.tmp"
  test -s "$FILE.tmp"
  gzip -t "$FILE.tmp"
  mv "$FILE.tmp" "$FILE"
done

find "$DEST" -type f -name '*.sql.gz' -mtime +14 -delete
echo "[$(date -Is)] backup completed: ${#DBS[@]} databases"
EOF
chmod 700 /usr/local/sbin/mysql-backup.sh
touch /var/log/mysql-backup.log
chmod 600 /var/log/mysql-backup.log

5. Запускаем вручную и проверяем результат

/usr/local/sbin/mysql-backup.sh
echo $?
tail -n 100 /var/log/mysql-backup.log
ls -lh /var/backups/mysql
find /var/backups/mysql -type f -name '*.sql.gz' -exec gzip -t {} \;

Не переходите к cron, пока ручной запуск не возвращает код 0, а все файлы проходят gzip -t.

6. Добавляем systemd timer

cat > /etc/systemd/system/mysql-backup.service <<'EOF'
[Unit]
Description=MySQL per-database backup

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/mysql-backup.sh
EOF

cat > /etc/systemd/system/mysql-backup.timer <<'EOF'
[Unit]
Description=Daily MySQL backup

[Timer]
OnCalendar=*-*-* 02:30:00
Persistent=true
RandomizedDelaySec=10m

[Install]
WantedBy=timers.target
EOF

systemctl daemon-reload
systemctl enable --now mysql-backup.timer
systemctl list-timers mysql-backup.timer

7. Тестовое восстановление одной базы

mysql -e 'DROP DATABASE IF EXISTS restore_test;'
FILE=$(ls -1t /var/backups/mysql/example_*sql.gz | head -1)
gzip -dc "$FILE" | sed 's/`example`/`restore_test`/g' | mysql
mysql -e 'SHOW TABLES FROM restore_test;'
mysql -e 'SELECT COUNT(*) FROM restore_test.some_table;'
mysql -e 'DROP DATABASE restore_test;'

Замена имени через sed подходит не для всех dump-файлов и объектов. Более безопасный вариант — восстановление в отдельном тестовом экземпляре MySQL или контейнере.

8. Проверка через временный контейнер

docker run --rm --name mysql-restore-test \
  -e MYSQL_ROOT_PASSWORD=testpass \
  -p 127.0.0.1:3307:3306 -d mysql:8

until mysql -h 127.0.0.1 -P 3307 -uroot -ptestpass -e 'SELECT 1'; do sleep 2; done
gzip -dc "$FILE" | mysql -h 127.0.0.1 -P 3307 -uroot -ptestpass
mysql -h 127.0.0.1 -P 3307 -uroot -ptestpass -e 'SHOW DATABASES;'
docker stop mysql-restore-test

9. Rollback

systemctl disable --now mysql-backup.timer
rm -f /etc/systemd/system/mysql-backup.timer /etc/systemd/system/mysql-backup.service
systemctl daemon-reload
rm -f /usr/local/sbin/mysql-backup.sh

Критерий успеха: задание возвращает 0, каждая база имеет ненулевой gzip-файл, архивы проходят проверку, timer запланирован, а выбранный dump реально восстанавливается в тестовую MySQL.