MySQL: резервное копирование через mysqldump

mysqldump — стандартная утилита для резервного копирования баз данных MySQL и MariaDB. Команда выгружает базу в текстовый SQL-файл с инструкциями для воссоздания структуры и данных. Восстановление — это просто выполнение этого файла на чистой базе. Дальше разберём не только базовые команды, но и моменты которые ломают бэкапы на реальных серверах: блокировки таблиц, несовместимость версий клиента и сервера, безопасное хранение пароля.

Базовая команда mysqldump

Выгрузить одну базу данных в файл:

mysqldump -u root -p mydb > backup.sql

Система запросит пароль интерактивно. Флаг -u задаёт пользователя, -p без значения — попросит ввести пароль скрытым вводом.

Выгрузить все базы данных сразу:

mysqldump -u root -p --all-databases > all-databases.sql

Это включает и системную базу mysql с пользователями и правами — полезно при полном переносе сервера.

Корректный бэкап для production

Команда выше работает, но для боевой базы недостаточна. Вот версия с флагами которые реально нужны:

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  mydb > backup.sql

Что делает каждый флаг и почему он важен:

--single-transaction — снимает консистентный снапшот без блокировки таблиц на чтение/запись. Без этого флага mysqldump блокирует таблицы на время выгрузки, и любые INSERT или UPDATE от приложения будут ждать пока бэкап не закончится. На активной базе это означает простой приложения на время дампа.

Важный нюанс: --single-transaction работает только для таблиц InnoDB. Если в базе остались таблицы MyISAM (часто бывает в старых проектах) — для них всё равно применяется блокировка, и полной консистентности между InnoDB и MyISAM таблицами в одном дампе не получится. Проверить движок таблиц:

SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'mydb';

--routines — без этого флага хранимые процедуры и функции не попадут в дамп. Многие проекты используют процедуры для сложной бизнес-логики, и их потеря при восстановлении замечают далеко не сразу.

--triggers — на самом деле включён по умолчанию, но указывать явно — хорошая практика, особенно если кто-то менял my.cnf с нестандартными настройками.

--events — события (запланированные задачи внутри MySQL, аналог cron для базы данных) тоже не попадают в дамп без этого флага.

Сжатие бэкапа mysqldump

SQL-дампы хорошо сжимаются — текстовые файлы с повторяющимися структурами обычно ужимаются в 5-10 раз.

mysqldump -u root -p mydb | gzip > backup.sql.gz

Размер до и после сжатия можно сравнить:

mysqldump -u root -p mydb > backup.sql
ls -lh backup.sql
gzip backup.sql
ls -lh backup.sql.gz

Восстановление из бэкапа

Восстановить базу из обычного SQL-файла:

mysql -u root -p mydb < backup.sql

База mydb должна существовать заранее. Если её нет — создайте:

mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

Восстановить из сжатого бэкапа без предварительной распаковки на диск:

gunzip < backup.sql.gz | mysql -u root -p mydb

Бэкап и восстановление одной таблицы

Если нужна не вся база, а конкретная таблица:

mysqldump -u root -p mydb table_name > table-backup.sql

Восстанавливается так же, через mysql ... < table-backup.sql.

Если у вас уже есть полный дамп базы, а нужно вытащить из него одну таблицу — придётся вырезать соответствующий блок. SQL-дамп от mysqldump содержит маркеры начала и конца каждой таблицы в комментариях. Вытащить блок для конкретной таблицы:

sed -n '/-- Table structure for table `table_name`/,/-- Table structure for table `next_table`/p' backup.sql > table-extracted.sql

Это не самый изящный способ, но рабочий когда отдельного бэкапа таблицы не делали заранее.

Автоматизация через cron

Скрипт ежедневного бэкапа с автоматическим удалением старых копий:

nano /home/user/scripts/mysql-backup.sh

#!/bin/bash
DATE=$(date +%F)
BACKUP_DIR="/backup/mysql"
DB_NAME="mydb"

mkdir -p "$BACKUP_DIR"

mysqldump -u root -p"ВАШ_ПАРОЛЬ" \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  "$DB_NAME" | gzip > "$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"

# Удаляем бэкапы старше 14 дней
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +14 -delete

Сделайте скрипт исполняемым:

chmod +x /home/user/scripts/mysql-backup.sh

Добавьте в crontab запуск каждую ночь в 3:00:

0 3 * * * /home/user/scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Хранить пароль прямо в скрипте — плохая практика по соображениям безопасности. Решение ниже.

Безопасное хранение пароля

Указание пароля прямо в команде (-pПАРОЛЬ) — заметный риск: пока команда выполняется, пароль виден в выводе ps aux любому пользователю на сервере, а если набирать его в интерактивном терминале — он также остаётся в истории команд ~/.bash_history.

Правильный способ — файл ~/.my.cnf:

nano ~/.my.cnf

[client]
user=root
password=ВАШ_ПАРОЛЬ

Обязательно ограничьте права на файл — иначе MySQL откажется его использовать и предупредит о небезопасных правах:

chmod 600 ~/.my.cnf

После этого пароль и логин подставляются автоматически, и команда сокращается до:

mysqldump --single-transaction --routines --triggers --events mydb > backup.sql

Это же касается обновлённого скрипта для cron — не нужно прописывать -u и -p вообще, если .my.cnf настроен для пользователя от которого запускается cron-задача.

Частые ошибки

Unknown table 'COLUMN_STATISTICS' in information_schema

Возникает когда клиент mysqldump версии MySQL 8 используется против сервера MariaDB (или наоборот, в зависимости от комбинации версий). Решение — добавить флаг:

mysqldump -u root -p --column-statistics=0 mydb > backup.sql

Got error: 1045: Access denied for user

Неверный логин или пароль, либо у пользователя нет прав на базу. Проверьте права:

SHOW GRANTS FOR 'user'@'localhost';

Got a packet bigger than 'max_allowed_packet' bytes

Возникает на базах с большими полями BLOB или TEXT. Решение — увеличить лимит при выгрузке:

mysqldump -u root -p --max_allowed_packet=512M mydb > backup.sql

Если ошибка происходит и при восстановлении — параметр нужно поднять и на стороне сервера в my.cnf, в секции [mysqld].

Дамп выполняется очень долго на большой базе

mysqldump однопоточный — для баз от нескольких десятков гигабайт это может занимать часы. Флаг --quick ускоряет процесс за счёт построчной выгрузки вместо буферизации целой таблицы в памяти:

mysqldump -u root -p --quick --single-transaction mydb > backup.sql

Для очень больших баз (100+ ГБ) стандартный mysqldump может быть просто непрактичен по времени выполнения. В этом случае стоит смотреть в сторону Percona XtraBackup — инструмента для физического бэкапа файлов базы данных без остановки сервера и без блокировок.

Проверка целостности бэкапа

Бэкап который ни разу не восстанавливали — это не бэкап, а файл с неизвестным содержимым. Минимальная проверка — попробовать восстановить дамп в тестовую базу:

mysql -u root -p -e "CREATE DATABASE test_restore;"
gunzip < backup.sql.gz | mysql -u root -p test_restore
mysql -u root -p -e "SHOW TABLES FROM test_restore;"

Если таблицы появились и в них есть данные — бэкап рабочий. После проверки тестовую базу можно удалить:

mysql -u root -p -e "DROP DATABASE test_restore;"

Часто задаваемые вопросы

Блокирует ли mysqldump базу данных во время выполнения?

Зависит от движка таблиц и флагов. Для InnoDB с флагом --single-transaction блокировки нет — снимается консистентный снапшот без остановки записи. Без этого флага, либо для таблиц MyISAM, mysqldump блокирует таблицы на чтение на время выгрузки, и активные записи от приложения будут ждать.

Как сделать бэкап без пароля в команде?

Создайте файл ~/.my.cnf с секцией [client], укажите в нём user и password, и ограничьте права командой chmod 600 ~/.my.cnf. После этого mysqldump подставляет учётные данные автоматически без флагов -u и -p.

Почему хранимые процедуры пропали после восстановления бэкапа?

Скорее всего бэкап делался без флага --routines. По умолчанию mysqldump не включает хранимые процедуры и функции в дамп. Для следующего бэкапа добавьте --routines --triggers --events.

Что делать с ошибкой Unknown table COLUMN_STATISTICS?

Это означает несовместимость версий между клиентом mysqldump и сервером базы данных (обычно MySQL 8 клиент против MariaDB сервера). Добавьте флаг --column-statistics=0 к команде mysqldump.

Как часто нужно делать бэкап базы данных?

Зависит от частоты изменения данных и допустимой потери (RPO). Для активных production-проектов — раз в сутки минимум, для высоконагруженных интернет-магазинов — несколько раз в день или непрерывная репликация. Дополнительно держите бэкапы за последние 2-4 недели, а не только последний.

Резервное копирование — обязательная практика для любой production-базы данных на VPS. На UFO.Hosting каждый сервер предоставляется с root-доступом — настроить cron-задачу для автоматического бэкапа можно сразу после установки MySQL.

Официальная документация: руководство MySQL по резервному копированию.