Полное руководство по mysqldump: резервное копирование, восстановление и автоматизация базы данных MySQL

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

В отличие от инструментов физического резервного копирования, таких как Percona XtraBackup или MySQL Enterprise Backup, mysqldump работает на уровне SQL — он считывает живые данные через протокол MySQL и записывает переносимый, удобочитаемый SQL. Эта переносимость является его главным преимуществом и, при масштабировании, основным ограничением.

Что mysqldump делает под капотом

При вызове mysqldump клиент подключается к серверу MySQL, запрашивает информационную схему и словарь данных, а затем выводит поток операторов `CREATE DATABASE`, `CREATE TABLE`, `INSERT` и DDL на стандартный вывод. Вы перенаправляете этот поток в файл, канал или утилиту сжатия.

Для таблиц InnoDB с `–single-transaction` mysqldump открывает транзакцию с повторяемым чтением перед считыванием данных. Это обеспечивает согласованный снимок на определённый момент времени без установки глобальных блокировок чтения — база данных остаётся полностью доступной для записи во время дампа. Для таблиц MyISAM такой механизм отсутствует; mysqldump возвращается к `FLUSH TABLES WITH READ LOCK`, что кратковременно блокирует запись.

Понимание этого различия критически важно перед выбором mysqldump для производственных нагрузок. Если ваша схема содержит таблицы InnoDB и MyISAM, одного `–single-transaction` недостаточно — вам потребуется `–lock-all-tables` или окно технического обслуживания.

Предварительные требования и необходимые привилегии

Перед выполнением любой команды дампа убедитесь в следующем:

• MySQL или MariaDB установлен и доступен (локальный сокет или TCP/IP).
• Пользователь резервного копирования имеет минимально необходимые привилегии:
• `SELECT` для всех целевых таблиц
• `LOCK TABLES` (обязательно, если только `–single-transaction` не используется исключительно с InnoDB)
• `SHOW VIEW` для включения представлений
• `TRIGGER` для включения триггеров
• `PROCESS` при использовании `–single-transaction` в MySQL 8+
• `RELOAD` для `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` если вам нужны координаты бинарного лога для настройки репликации

Создайте выделенного пользователя для резервного копирования вместо выполнения дампов от имени root:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Запуск mysqldump от имени root с паролем, встроенным в команду оболочки, раскрывает учётные данные в списках процессов и истории оболочки — это существенная угроза безопасности на любой общей или многопользовательской системе.

Базовый синтаксис

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Полный справочник параметров

Параметры подключения

Параметры области охвата

Параметры согласованности и блокировки

Параметры вывода и содержимого

mysqldump в сравнении с альтернативными методами резервного копирования

Выбор правильной стратегии резервного копирования зависит от размера базы данных, требований RTO/RPO и инфраструктуры. Вот как mysqldump сравнивается с наиболее распространёнными альтернативами:

Для баз данных размером до 10–20 GB в среде VPS Хостинга mysqldump остаётся наиболее практичным и переносимым решением. За этим порогом инструменты физического резервного копирования обеспечивают значительно более быстрое резервное копирование и восстановление.

Практические примеры использования

Пример 1: Резервное копирование одной базы данных

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

Подстановка `$(date +%F)` автоматически добавляет дату в формате ISO (например, `2025-07-15`) к имени файла, предотвращая перезапись.

Пример 2: Резервное копирование нескольких конкретных баз данных

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

Флаг `–databases` заставляет mysqldump выводить операторы `CREATE DATABASE` и `USE`, делая дамп самодостаточным для восстановления.

Пример 3: Резервное копирование всех баз данных

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Всегда включайте `–events`, `–routines` и `–triggers` в полные дампы сервера. Эти объекты молча пропускаются без явных флагов.

Пример 4: Согласованное резервное копирование InnoDB (безопасное для производства)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` выполняет ротацию бинарного лога в начале дампа. `–source-data=2` записывает текущее имя файла бинарного лога и позицию в виде SQL-комментария, обеспечивая восстановление на момент времени путём воспроизведения последующих binlog с этой позиции.

Пример 5: Сжатое резервное копирование с gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

Для серверов с ограниченными ресурсами CPU замените на `pigz` (параллельный gzip) для использования нескольких ядер:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Пример 6: Резервное копирование только схемы (структура без данных)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Полезно для управления версиями схемы в Git или развёртывания в тестовой среде без копирования производственных данных.

Пример 7: Резервное копирование только данных (без схемы)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Используйте это, когда целевая схема уже существует и вам нужно только заполнить или обновить данные.

Пример 8: Резервное копирование одной таблицы

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Пример 9: Экспорт отфильтрованного подмножества строк

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

Параметр `–where` используется редко, но чрезвычайно эффективен для частичного экспорта, архивирования данных и отладки конкретных наборов записей.

Пример 10: Исключение конкретных таблиц

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Исключение больших временных таблиц (кэши, хранилища сессий, таблицы логов) может сократить размер и продолжительность дампа на порядок.

Пример 11: Включение хранимых процедур, функций и триггеров

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Пример 12: Резервное копирование удалённой базы данных

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

При резервном копировании удалённого сервера трафик по умолчанию передаётся по сети в незашифрованном виде. Добавьте флаги `–ssl-ca`, `–ssl-cert` и `–ssl-key` или используйте туннель через SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Восстановление из резервной копии mysqldump

Восстановление одной базы данных

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Если целевая база данных ещё не существует, сначала создайте её:

“`bash

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

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Восстановление всех баз данных из полного дампа сервера

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Поскольку `–all-databases` содержит операторы `CREATE DATABASE` и `USE`, аргумент целевой базы данных не требуется.

Восстановление из сжатой резервной копии

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Или с использованием подстановки процессов:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Восстановление одной таблицы из полного дампа базы данных

Это распространённый операционный сценарий, который исходный файл дампа делает нетривиальным. Используйте `sed` или `grep` для извлечения соответствующего раздела:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Либо используйте `mysql_extract_table.sh` или импортируйте во временную базу данных и скопируйте таблицу:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Восстановление на момент времени с использованием бинарных логов

Если дамп был создан с `–source-data=2` и бинарное логирование включено, вы можете восстановить данные на любой момент после создания дампа:

1. Определите позицию бинарного лога из комментария в заголовке файла дампа.
2. Восстановите базовый дамп.
3. Примените последующие события бинарного лога до нужной временной метки:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Автоматизация резервного копирования с помощью Cron

Базовое задание ежедневного резервного копирования

Храните учётные данные в `~/.my.cnf` вместо их встраивания в команды cron:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Установите строгие права доступа:

“`bash

chmod 600 ~/.my.cnf

“`

Затем создайте задание cron:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Скрипт резервного копирования производственного уровня

Для Выделенных серверов, обслуживающих несколько баз данных, более надёжный скрипт обрабатывает журналирование ошибок, проверку дискового пространства и удалённую выгрузку:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Усиление безопасности операций mysqldump

Управление учётными данными — наиболее часто игнорируемый аспект безопасности резервного копирования. Никогда не передавайте `-pYourPassword` непосредственно в командной строке — он виден в выводе `ps aux` и истории оболочки. Вместо этого используйте один из следующих подходов:

• `~/.my.cnf` с `chmod 600` (для каждого пользователя)
• `/etc/mysql/backup.cnf` с `chmod 640`, принадлежащий root, доступный для чтения группой резервного копирования
• Переменная среды `MYSQL_PWD` (видна в `/proc`, используйте только в изолированных контейнерах)
• MySQL Vault или HashiCorp Vault для корпоративных сред

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

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Шифрование в состоянии покоя: Для конфиденциальных данных шифруйте файлы резервных копий перед их хранением или передачей:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Шифрование при передаче: При создании дампа с удалённого сервера всегда используйте SSL/TLS или SSH-туннель. В среде VPS с cPanel интерфейс резервного копирования cPanel обрабатывает это автоматически, но ручные операции mysqldump требуют явных флагов SSL.

Распространённые ошибки и способы их избежать

Несоответствие кодировок — наиболее частая причина повреждённых восстановлений. Всегда явно указывайте кодировку:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

Отсутствие `–column-statistics=0` вызывает сбои, когда клиент MySQL 8.0 создаёт дамп с сервера MySQL 5.7 или MariaDB. Клиент MySQL 8 пытается создать дамп статистики столбцов, которую более старые серверы не поддерживают:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Забытые `–routines`, `–triggers` и `–events` молча пропускают критически важные объекты базы данных. Эти флаги не включены по умолчанию (за исключением `–triggers`) и часто забываются в разовых дампах.

Дамп больших таблиц вызывает OOM: mysqldump по умолчанию буферизует целые результирующие наборы в памяти. Для очень больших таблиц добавьте `–quick` (включено по умолчанию в большинстве версий, но стоит проверить) для потоковой передачи строк по одной вместо буферизации:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Восстановление в другой версии MySQL: Дампы из MySQL 8.0 могут содержать синтаксис, не поддерживаемый в MySQL 5.7 (например, функциональные индексы, невидимые столбцы). Всегда тестируйте восстановление в среде с соответствующей версией перед тем, как полагаться на межверсионные миграции.

Смещение значений автоинкремента: При восстановлении таблицы в существующую схему, в которой уже есть строки, операторы `INSERT` завершатся ошибкой из-за конфликтов первичного ключа, если вы не включите `–add-drop-table` или не очистите целевую таблицу вручную заранее.

Использование mysqldump для миграции баз данных

mysqldump является стандартным подходом для миграции баз данных между серверами — например, при переносе сайта на WordPress с Общего веб-хостинга на VPS или переходе на среду VPS с панелями управления с большими ресурсами.

Рекомендуемый рабочий процесс миграции:

1. Создайте дамп исходной базы данных с полными параметрами:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Передайте безопасно с помощью rsync через SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Создайте целевую базу данных с соответствующей кодировкой:

“`bash

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

“`

1. Восстановите и проверьте:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Обновите конфигурацию приложения, указав новый хост базы данных.

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

Проверка целостности резервной копии

Резервная копия, которая никогда не тестировалась, — это не резервная копия, а непроверенное предположение. Реализуйте процедуру проверки:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Запускайте этот скрипт проверки еженедельно через cron и настройте оповещение при сбое.

Матрица решений: когда использовать mysqldump

Технический контрольный список ключевых выводов

• Всегда используйте `–single-transaction` для баз данных только на InnoDB, чтобы избежать блокировок записи во время резервного копирования.
• Всегда включайте `–routines –triggers –events` в любой дамп, предназначенный для полного резервного копирования.
• Храните учётные данные в `~/.my.cnf` или `/etc/mysql/backup.cnf` с `chmod 600/640` — никогда не встраивайте их в скрипты или команды cron.
• Добавляйте `–column-statistics=0` при использовании клиента MySQL 8.0 против сервера MySQL 5.7 или MariaDB.
• Всегда указывайте `–default-character-set=utf8mb4` как при создании дампа, так и при восстановлении, чтобы предотвратить повреждение кодировки символов.
• Сжимайте все резервные копии с помощью gzip или pigz; шифруйте конфиденциальные дампы с помощью AES-256 перед передачей за пределы площадки.
• Включайте `–flush-logs –source-data=2` в производственные дампы для обеспечения восстановления на момент времени через бинарные логи.
• Автоматизируйте очистку устаревших копий с помощью `find … -mtime +N -delete` для предотвращения исчерпания дискового пространства.
• Тестируйте восстановление по расписанию — проверяйте количество строк и выборочно проверяйте целостность данных относительно производственной среды.
• Для схем со смешанными движками используйте `–lock-all-tables` вместо `–single-transaction` для гарантии согласованности.

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

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

При использовании `–single-transaction` на чистой базе данных InnoDB блокировки таблиц не устанавливаются, кроме кратковременного начального сброса. Таблицы MyISAM всегда требуют блокировки чтения (`LOCK TABLES`), поскольку они не поддерживают транзакции. Схемы со смешанными движками требуют `–lock-all-tables` для согласованного снимка, что блокирует запись на время создания дампа.

Как создать резервную копию только схемы базы данных без данных?

Используйте флаг `–no-data`: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Это экспортирует все операторы `CREATE TABLE`, `CREATE VIEW`, хранимые процедуры и триггеры без каких-либо операторов `INSERT`.

Почему mysqldump завершается с ошибками «column statistics»?

Это происходит, когда клиент MySQL 8.0 подключается к серверу MySQL 5.7 или MariaDB. Добавьте `–column-statistics=0` в вашу команду. Либо обновите сервер до MySQL 8.0 или используйте бинарный файл клиента, соответствующий версии сервера.

Может ли mysqldump выполнять инкрементальное резервное копирование?

Нет. mysqldump всегда создаёт полный логический дамп указанной области. Возможность инкрементального резервного копирования требует архивирования бинарных логов (`mysqlbinlog`) в сочетании с базовым дампом mysqldump, созданным с `–flush-logs –source-data=2`. Настоящее инкрементальное физическое резервное копирование требует Percona XtraBackup или MySQL Enterprise Backup.

Каков наиболее безопасный способ автоматизации mysqldump без раскрытия паролей?

Создайте выделенного пользователя MySQL для резервного копирования с минимально необходимыми привилегиями, храните его учётные данные в разделе `[mysqldump]` файла `~/.my.cnf` или отдельного файла параметров с `chmod 600` и ссылайтесь на него с помощью `–defaults-file=/path/to/backup.cnf`. Этот подход полностью исключает учётные данные из списков процессов, истории оболочки и определений заданий cron.