Основные команды mysqldump
mysqldump — стандартная утилита для создания логических резервных копий MySQL и MariaDB. Она создаёт SQL-файл с командами для воссоздания структуры и данных базы.
Бэкап одной базы данных
# Базовый бэкап
mysqldump -u root -p mydb > mydb_backup.sql
# С сжатием (рекомендуется)
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d_%H%M%S).sql.gz
# Без блокировки таблиц (для InnoDB)
mysqldump -u root -p --single-transaction --quick mydb | gzip > mydb.sql.gzВсегда используйте
--single-transaction для баз данных InnoDB — это создаёт консистентный снапшот без блокировки таблиц. Без этого флага сайт может "зависнуть" во время бэкапа.Полезные флаги mysqldump
| Флаг | Описание |
|---|---|
--single-transaction | Консистентный бэкап InnoDB без блокировок |
--quick | Читать строки по одной (экономит RAM) |
--no-data | Только структура, без данных |
--no-create-info | Только данные, без CREATE TABLE |
--all-databases | Бэкап всех баз данных |
--routines | Включить хранимые процедуры |
--triggers | Включить триггеры (по умолчанию ON) |
Бэкап всех баз данных
mysqldump -u root -p --all-databases --single-transaction \
--routines --triggers | gzip > all_databases_$(date +%Y%m%d).sql.gzВосстановление из дампа
# Восстановление обычного дампа
mysql -u root -p mydb < mydb_backup.sql
# Восстановление сжатого дампа
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb
# Или через zcat
zcat mydb_backup.sql.gz | mysql -u root -p mydbАвтоматизация через cron
sudo nano /etc/cron.d/mysql-backup# Бэкап каждый день в 2:00, хранить 7 дней
0 2 * * * root mysqldump -u backup_user -pPassword --single-transaction \
--all-databases | gzip > /backups/mysql/all_$(date +\%Y\%m\%d).sql.gz && \
find /backups/mysql/ -name "*.sql.gz" -mtime +7 -deleteСоздание пользователя только для бэкапов
mysql -u root -p -e "
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongBackupPass';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;"Не используйте root для автоматических бэкапов — создайте отдельного пользователя с минимальными привилегиями. Пароль храните в
~/.my.cnf, а не в скриптах.Пароль в ~/.my.cnf (без ввода пароля)
cat ~/.my.cnf
[mysqldump]
user=backup_user
password=StrongBackupPass
chmod 600 ~/.my.cnf
# Теперь можно без -p:
mysqldump mydb > backup.sqlТипичные ошибки mysqldump
| Ошибка | Причина | Что делать |
|---|---|---|
| Got error: 1044: Access denied for user to database | У пользователя нет привилегии SELECT на конкретную базу, хотя GRANT выдан не на все схемы | Выдайте права явно: GRANT SELECT, LOCK TABLES ON mydb.* TO 'backup_user'@'localhost'; |
| Error 2013: Lost connection to MySQL server during query | Дамп большой таблицы превысил wait_timeout, либо не использован флаг --quick | Добавьте --quick и увеличьте wait_timeout в my.cnf для сессии бэкапа |
| Warning: A partial dump from a server that has GTIDs... | На сервере включён GTID, и дамп без явного указания режима может сломать репликацию при восстановлении на другом сервере | Добавьте флаг --set-gtid-purged=OFF, если дамп переносится не на реплику того же кластера |
| ERROR 1146 (42S02): Table doesn't exist при восстановлении | Дамп был снят с флагом --no-data или процесс дампа прервался на середине | Проверьте полноту дампа и пересоздайте его без --no-data, если нужны и структура, и данные |
Сравнение способов бэкапа MySQL
| Способ | Тип бэкапа | Блокировка таблиц | Подходит для |
|---|---|---|---|
| mysqldump | Логический (SQL-файл) | Нет при --single-transaction для InnoDB | Малых и средних баз, переносимости между версиями |
| Percona XtraBackup | Физический (файлы данных) | Нет, полностью горячий бэкап | Больших баз, где важна скорость восстановления |
| Binlog + периодический снапшот | Инкрементальный, point-in-time | Нет | Восстановления состояния на точную секунду перед сбоем |
Когда mysqldump не подходит
- База весит сотни гигабайт — логический дамп и его восстановление могут занимать часы, физический бэкап (XtraBackup) в разы быстрее
- Нужно восстановление на произвольную секунду перед инцидентом — для этого нужны бинарные логи, а не дамп раз в сутки
- Таблицы в движке MyISAM с постоянной активной записью — даже с флагами блокировка на момент снятия дампа заметна пользователям
- Дамп снимается с master-сервера под высокой нагрузкой репликации — предпочтительнее снимать с реплики, чтобы не создавать лишнюю нагрузку
Для баз, где логический дамп уже не справляется по времени, есть статья «Горячий бэкап MySQL с Percona XtraBackup», а для восстановления на точное время — «Binlog MySQL: восстановление базы на точку во времени».
Проверка результата
Не доверяйте бэкапу, который никогда не проверялся на восстановление:
# Дамп не должен быть пустым или обрываться на середине
tail -5 mydb_backup.sql
# Последняя строка обычно "-- Dump completed on ..."
# Количество таблиц в дампе должно совпадать с базой-источником
grep -c "^CREATE TABLE" mydb_backup.sql
mysql -u root -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mydb';"
# Пробное восстановление во временную базу
mysql -u root -p -e "CREATE DATABASE mydb_restore_test;"
mysql -u root -p mydb_restore_test < mydb_backup.sql
mysql -u root -p -e "DROP DATABASE mydb_restore_test;"Если бэкапится сайт на WordPress, стоит свериться с отдельным чеклистом в статье «WordPress бэкап: UpdraftPlus и ручной метод» — там учтены ещё и файлы, а не только база.