dbsend
Начать бесплатно

Бэкап MySQL: автоматизация mysqldump и ротация копий

Бэкапы и восстановление

mysqldump по cron — самый распространённый способ бэкапа MySQL на VPS, и в нём легко ошибиться: пароль в командной строке, неконсистентный дамп MyISAM-таблиц, забытые процедуры и триггеры, бесконечно растущий каталог с копиями. В этой статье — правильные флаги, безопасное хранение пароля, скрипт с ротацией daily/weekly/monthly, загрузка в российское S3 и проверка восстановления.

Флаги, без которых дамп неполный или неконсистентный

Базовая команда для InnoDB-базы:

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --quick \
  --routines --triggers --events \
  --hex-blob --default-character-set=utf8mb4 \
  --no-tablespaces \
  app > app.sql

Что делает каждый флаг:

  • --single-transaction открывает транзакцию с консистентным снимком (START TRANSACTION WITH CONSISTENT SNAPSHOT). Для InnoDB это даёт согласованный дамп без блокировки записи. Для MyISAM-таблиц снимок не работает: их нужно блокировать (--lock-all-tables или --lock-tables), а это останавливает запись на время дампа. Не сочетайте --single-transaction с --lock-tables. И не запускайте DDL (ALTER, DROP, RENAME, TRUNCATE) во время дампа — снимок от них не защищает.
  • --quick читает строки потоком, а не загружает таблицу в память целиком. Включён по умолчанию через --opt, но явное указание не помешает.
  • --routines --events добавляют хранимые процедуры, функции и события планировщика — по умолчанию их в дампе нет. --triggers включён по умолчанию, но лучше указать явно.
  • --hex-blob выгружает бинарные колонки (BLOB, BINARY, VARBINARY) в шестнадцатеричном виде, чтобы байты не испортились при перекодировке.
  • --default-character-set=utf8mb4 гарантирует, что эмодзи и четырёхбайтовые символы доедут без искажений, особенно на старых серверах, где по умолчанию стоял utf8 (он же utf8mb3).
  • --no-tablespaces убирает информацию о табличных пространствах. Начиная с MySQL 8.0.21 для её выгрузки нужна привилегия PROCESS; без флага дамп от ограниченного пользователя упадёт с Access denied ... PROCESS privilege.

--all-databases или по одной базе

--all-databases выгружает всё, включая системную базу mysql с пользователями. Это удобно для полного переезда на ту же версию сервера, но такой дамп сложно загрузить в другую версию и нельзя восстановить одну базу отдельно. На практике удобнее дампить каждую пользовательскую базу в свой файл, а учётные записи сохранять отдельно — через SHOW CREATE USER и SHOW GRANTS.

MariaDB

В MariaDB основной бинарник называется mariadb-dump; имя mysqldump оставлено для совместимости, но в новых сборках этой ссылки может не быть. Флаги из примера выше работают так же. GTID в MariaDB устроены иначе, поэтому --set-gtid-purged там нет — у mariadb-dump для этого свой флаг --gtid.

GTID и позиция бинлога

Если на сервере MySQL включены GTID, mysqldump по умолчанию добавляет в дамп SET @@GLOBAL.GTID_PURGED=.... При загрузке на сервер, где уже есть свои GTID (например, на другой рабочий сервер), это вызовет ошибку. Если дамп не предназначен для создания реплики, добавьте --set-gtid-purged=OFF.

Чтобы потом докатить изменения из бинлогов (восстановление на момент времени), запишите в дамп позицию бинлога: --source-data=2 (MySQL 8.0.26+, пишет CHANGE REPLICATION SOURCE TO ... в виде комментария). Старое имя флага --master-data объявлено устаревшим. Для этого флага пользователю нужна привилегия RELOAD.

Пароль: defaults-extra-file вместо -p

mysqldump -uroot -pSECRET — плохая идея: пароль виден в ps любому пользователю сервера и попадает в историю shell. Положите учётные данные в файл с правами 600:

# /etc/mysql/backup.cnf
[client]
user = backup
password = очень-длинный-пароль
host = 127.0.0.1

sudo chown root:root /etc/mysql/backup.cnf
sudo chmod 600 /etc/mysql/backup.cnf

--defaults-extra-file обязательно указывается первым аргументом, иначе клиент его не прочтёт. Переменная окружения MYSQL_PWD тоже работает, но документация MySQL называет её небезопасной: окружение процесса можно прочитать.

Отдельный пользователь с минимальными правами:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'очень-длинный-пароль';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';

PROCESS нужен, если вы не указываете --no-tablespaces. RELOAD нужен для --source-data, а в свежих версиях MySQL 8.x — и для дампа с --single-transaction на сервере с GTID, если не указан --set-gtid-purged=OFF. Если ваш сценарий без этого обходится, эти две привилегии можно не выдавать.

Скрипт с GFS-ротацией и загрузкой в S3

GFS (grandfather-father-son) — схема, в которой вы храните последние ежедневные копии, несколько еженедельных и несколько ежемесячных. Так у вас есть и вчерашний бэкап, и состояние трёхмесячной давности, а место не растёт бесконечно.

#!/usr/bin/env bash
# /usr/local/bin/mysql-backup.sh
set -euo pipefail

CNF="/etc/mysql/backup.cnf"
ROOT="/var/backups/mysql"
BUCKET="s3://my-mysql-backups"
ENDPOINT="https://storage.yandexcloud.net"   # Selectel: https://s3.ru-1.storage.selcloud.ru
STAMP="$(date -u +%Y-%m-%dT%H%M%SZ)"

mkdir -p "$ROOT"/{daily,weekly,monthly}

DATABASES=$(mysql --defaults-extra-file="$CNF" -N -e "SHOW DATABASES" \
  | grep -Ev '^(information_schema|performance_schema|sys|mysql)$')

for DB in $DATABASES; do
  FILE="$ROOT/daily/${DB}-${STAMP}.sql.zst"
  mysqldump --defaults-extra-file="$CNF" \
    --single-transaction --quick --routines --triggers --events \
    --hex-blob --default-character-set=utf8mb4 --no-tablespaces \
    --set-gtid-purged=OFF \
    "$DB" | zstd -q -T0 -3 -o "$FILE.part"
  zstd -q -t "$FILE.part"
  mv "$FILE.part" "$FILE"

  # воскресенье — копия в weekly, 1-е число — в monthly
  [ "$(date +%u)" = "7" ]  && cp "$FILE" "$ROOT/weekly/"
  [ "$(date +%d)" = "01" ] && cp "$FILE" "$ROOT/monthly/"

  aws s3 cp "$FILE" "$BUCKET/daily/$(basename "$FILE")" \
    --endpoint-url "$ENDPOINT" --profile backup --only-show-errors
done

find "$ROOT/daily"   -type f -name '*.sql.zst' -mtime +7   -delete
find "$ROOT/weekly"  -type f -name '*.sql.zst' -mtime +35  -delete
find "$ROOT/monthly" -type f -name '*.sql.zst' -mtime +365 -delete

Детали:

  • set -o pipefail обязателен: без него при падении mysqldump код возврата возьмётся от zstd, и вы получите «успешный» пустой архив.
  • Если на сервере нет GTID, --set-gtid-purged=OFF ничего не меняет. Для MariaDB этот флаг уберите.
  • Дамп пишется без --databases, то есть без CREATE DATABASE и USE. Такой файл можно загрузить в базу с любым именем — это удобно для тестового восстановления.
  • zstd обычно сжимает быстрее gzip при сопоставимой степени сжатия; если его нет на сервере, замените на gzip -6 и gzip -t.
  • Если базы называются с пробелами или спецсимволами, цикл по $DATABASES придётся переписать на while read.

В S3 ротацию удобнее отдать правилу жизненного цикла бакета, указав разные сроки для префиксов daily/, weekly/, monthly/ (weekly и monthly загружайте отдельно, по аналогии с daily). Пример JSON-правила и команды aws s3api put-bucket-lifecycle-configuration, а также настройку rclone для Yandex Object Storage и Selectel смотрите в статье про бэкап PostgreSQL в S3 — для MySQL всё то же самое.

Расписание: cron + flock и systemd timer

# /etc/cron.d/mysql-backup
30 2 * * * root flock -n /run/mysql-backup.lock /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

flock -n не даст запустить второй экземпляр, если вчерашний дамп ещё идёт. Вариант с systemd:

# /etc/systemd/system/mysql-backup.service
[Unit]
Description=MySQL logical backup
After=network-online.target mysql.service
Wants=network-online.target

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql-backup.sh
Nice=10
IOSchedulingClass=idle

# /etc/systemd/system/mysql-backup.timer
[Unit]
Description=Nightly MySQL backup

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

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable --now mysql-backup.timer

Persistent=true выполнит пропущенный запуск после перезагрузки, а Nice и IOSchedulingClass=idle снизят влияние дампа на рабочую нагрузку.

Windows

На Windows-сервере тот же дамп запускается из PowerShell. Пишите в файл через --result-file, а не через >: Windows PowerShell 5.1 перекодирует перенаправленный вывод в UTF-16.

$mysqldump = 'C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqldump.exe'
$out = "D:\Backups\mysql\app-$((Get-Date).ToString('yyyy-MM-dd')).sql"
& $mysqldump --defaults-extra-file=C:\Scripts\backup.cnf --single-transaction `
  --routines --triggers --events --hex-blob --no-tablespaces --result-file=$out app
if ($LASTEXITCODE -ne 0) { throw "mysqldump завершился с кодом $LASTEXITCODE" }

Задание в планировщике создаётся через Register-ScheduledTask, как в примере для PostgreSQL по ссылке выше.

Проверка восстановления

Бэкап, который ни разу не восстанавливали, — это предположение. Самый простой тест — одноразовый контейнер той же версии MySQL:

docker run -d --rm --name restore-test -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.4
# ждём, пока сервер начнёт принимать TCP-подключения (временный сервер инициализации их не принимает)
until docker exec restore-test mysql --protocol=tcp -h127.0.0.1 -uroot -e 'SELECT 1' >/dev/null 2>&1; do sleep 2; done

docker exec restore-test mysql --protocol=tcp -h127.0.0.1 -uroot -e 'CREATE DATABASE app_restore'
zstd -dc /var/backups/mysql/daily/app-2026-09-15T023000Z.sql.zst \
  | docker exec -i restore-test mysql --protocol=tcp -h127.0.0.1 -uroot app_restore

docker exec restore-test mysql --protocol=tcp -h127.0.0.1 -uroot -e 'SELECT COUNT(*) FROM app_restore.users'
docker stop restore-test

Обратите внимание на docker exec -i без -t: при передаче дампа через конвейер псевдотерминал не нужен и может испортить поток. Сравнивайте COUNT(*) ключевых таблиц с рабочей базой — TABLE_ROWS из information_schema для InnoDB лишь приблизительная оценка. Полный чек-лист — в статье как проверить, что бэкап рабочий, а команды восстановления для всех СУБД — в материале как восстановить базу из бэкапа.

Когда mysqldump становится тесно

mysqldump однопоточный, а восстановление дампа — это повторное выполнение всех INSERT с перестроением индексов. На больших базах это долго. Варианты:

  • mydumper / myloader — логический дамп и загрузка в несколько потоков, каждая таблица в своём файле, консистентный снимок. Логика та же, что у mysqldump, но быстрее и с выборочным восстановлением.
  • MySQL Shell (util.dumpInstance, util.loadDump) — официальная параллельная выгрузка от Oracle.
  • Percona XtraBackup (для MariaDB — mariadb-backup) — физический «горячий» бэкап InnoDB без остановки сервера. Восстанавливается копированием файлов, а не выполнением SQL. В связке с бинлогами даёт восстановление на момент времени. Версия XtraBackup должна соответствовать версии сервера.

Переходить стоит, когда восстановление из mysqldump перестаёт укладываться в допустимое время простоя или когда суточные потери между бэкапами становятся неприемлемыми.

Как это сделать в dbsend

dbsend не подключается к MySQL и не запускает mysqldump за вас: дамп делает ваш скрипт, а CLI загружает готовый файл. Сервис принимает plain-дампы MySQL и MariaDB (движок mysql_dump), разбирает схему и считает строки по таблицам. Загружайте несжатый .sql — CLI сжимает его при передаче сам.

npm install --global @dbsend/sdk
export DBSEND_API_KEY='sqv_pk_…'

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction \
  --routines --triggers --events --hex-blob --no-tablespaces --result-file=/var/backups/mysql/app.sql app
dbsend backup /var/backups/mysql/app.sql -d "$DBSEND_DATABASE_ID" -e mysql_dump -l "nightly-$(date +%F)"

Каждый дамп становится версией; если он побайтно совпадает с предыдущим, новая версия не создаётся. Между любыми двумя версиями можно посмотреть diff: изменения схемы, таблиц, числа строк и размера — так видно, что ночью из orders пропала половина записей. Если дамп не пришёл по расписанию, dbsend присылает уведомление в Telegram, на почту или в webhook. Хранение — S3 в России с шифрованием AES-256-GCM. Diff и уведомления доступны на платных тарифах, условия — на странице тарифов. Подробнее о возможностях — на странице бэкапов MySQL и в документации CLI. Историю версий и diff можно смотреть и из Claude Code или Cursor через MCP-сервер; сам файл дампа при этом по-прежнему загружает CLI.

Если MySQL работает в контейнере, особенности docker exec и встроенных бэкапов Coolify разобраны в статье о бэкапе базы в Docker и Coolify.

Частые вопросы

Блокирует ли mysqldump таблицы?

С --single-transaction InnoDB-таблицы не блокируются для записи: дамп читает консистентный снимок. MyISAM-таблицы так защитить нельзя — для согласованного дампа их приходится блокировать. Если в базе остались MyISAM-таблицы, переведите их на InnoDB (ALTER TABLE ... ENGINE=InnoDB).

Почему mysqldump пишет «Access denied; you need the PROCESS privilege»?

С MySQL 8.0.21 выгрузка информации о табличных пространствах требует привилегии PROCESS. Добавьте флаг --no-tablespaces или выдайте пользователю бэкапа PROCESS.

Как хранить пароль для mysqldump в cron?

В файле [client] с правами 600 и параметром --defaults-extra-file, указанным первым аргументом. Не передавайте пароль через -p в командной строке: его видно в списке процессов.

Сколько копий хранить?

Зависит от того, как поздно вы можете обнаружить проблему. Схема GFS — 7 ежедневных, 4–5 еженедельных и 12 ежемесячных — покрывает и вчерашнюю ошибку, и порчу данных, замеченную через месяц. Минимум одна копия должна лежать вне сервера с базой.

Подходит ли mysqldump для MariaDB?

Да, но в новых версиях MariaDB используйте mariadb-dump. Флаги --single-transaction, --routines, --events, --hex-blob работают так же; вместо --set-gtid-purged у MariaDB свой механизм GTID.

Похожие статьи

Все статьи →

Восстановить базу из бэкапа: PostgreSQL, MySQL, SQLite, DuckDB

Как восстановить базу из бэкапа без потерь: psql и pg_restore, mysql, замена файла SQLite и DuckDB, одна таблица из дампа, чек-лист до, во время и после.

MCP бэкап базы данных: Claude Code и Cursor перед миграцией

MCP бэкап базы данных из Claude Code, Cursor и Codex: подключение dbsend MCP, ключи с минимальными правами, правило «сначала бэкап», diff после миграции, риски.

Бэкап базы в Docker и Coolify: PostgreSQL, MySQL, SQLite

Бэкап базы в Docker и Coolify: почему нельзя копировать живой volume, docker exec без -t, дампы PostgreSQL, MySQL и SQLite, sidecar, бэкапы Coolify в S3.