К содержимому

Как сделать бэкап базы данных MySQL на Ubuntu 24.04

Бэкап базы MySQL делается утилитой mysqldump: она выгружает структуру и данные в SQL-файл, который можно восстановить на этом же или другом сервере. Для рабочей базы используйте флаги --single-transaction и --quick, чтобы дамп не блокировал таблицы.

Руководство MySQL 2 мин чтения

01Создайте отдельного пользователя для бэкапов

Не делайте дампы под root. Создайте пользователя с правами только на чтение и блокировку таблиц. Подключитесь к MySQL под администратором и выполните операторы. Замените пароль на свой.

sql
sudo mysql
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
EXIT;

02Сделайте простой дамп базы

Базовая команда mysqldump выгружает одну базу в файл dump.sql. Ключ -p запросит пароль интерактивно, поэтому пароль не попадёт в историю команд. Для базы example_db выполните:

bash
mysqldump -u backup -p example_db > dump.sql

03Сделайте дамп со сжатием и без блокировок

Для рабочей базы добавьте --single-transaction: InnoDB-таблицы выгружаются в согласованном состоянии без блокировки записи. Флаг --quick читает строки по одной и не держит всю таблицу в памяти. Вывод сразу сжимается gzip.

bash
mysqldump --single-transaction --quick -u backup -p example_db | gzip > dump.sql.gz

04Проверьте размер и содержимое дампа

Пустой или подозрительно маленький файл говорит об ошибке. Посмотрите размер и первые строки: в начале должны быть комментарии mysqldump и операторы CREATE TABLE. Для сжатого файла используйте zcat.

bash
ls -lh dump.sql.gz
zcat dump.sql.gz | head -n 20

05Восстановите дамп в тестовую базу

Проверка бэкапа без восстановления ничего не доказывает. Создайте пустую базу example_restore и загрузите в неё дамп. Для gzip-файла распаковывайте потоком через gunzip.

bash
sudo mysql -e "CREATE DATABASE example_restore;"
gunzip < dump.sql.gz | mysql -u backup -p example_restore

06Убедитесь, что данные на месте

Сравните число строк в исходной и восстановленной базе. Если таблиц много, проверьте несколько ключевых. Команда SHOW TABLES покажет список таблиц, а COUNT(*) вернёт количество записей.

bash
mysql -u backup -p -e "SHOW TABLES;" example_restore
mysql -u backup -p -e "SELECT COUNT(*) FROM users;" example_restore

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

  • Дамп сделан без --single-transaction, и на время выгрузки таблицы заблокированы для записи. Сайт отвечает ошибками или зависает.

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

  • Файл дампа пустой или обрывается на середине. Причина в нехватке места на диске или в ошибке, которую перенаправили в тот же файл.

    Проверьте свободное место через df -h и смотрите stderr отдельно: mysqldump... > dump.sql 2> dump.err. Убедитесь, что в dump.err нет ошибок.

  • Восстановление падает с ошибкой доступа: пользователь backup не имеет прав на создание таблиц в целевой базе.

    Дайте backup права на запись в тестовую базу или восстанавливайте под пользователем с правами CREATE, INSERT, DROP.

  • Дамп содержит DEFINER для процедур и триггеров, и при восстановлении на другом сервере возникает ошибка о несуществующем пользователе.

    Добавьте при создании дампа флаг --routines и при необходимости --triggers, а восстанавливайте под пользователем, который есть на целевом сервере.

Deploykin

Как это делается в Deploykin

В Deploykin базы MySQL и пользователи создаются из панели, phpMyAdmin открывается в один клик. Бэкапы баз уходят по расписанию на ваше SFTP-хранилище, сжатыми и проверенными, а восстановление запускается из панели.

7 дней бесплатно на тарифе Про, без привязки карты.

Вопросы и ответы

Чем отличается mysqldump от физического бэкапа?

mysqldump создаёт логический SQL-файл с операторами CREATE TABLE и INSERT. Физический бэкап копирует файлы данных InnoDB и требует остановки или специальных инструментов вроде Percona XtraBackup.

Можно ли делать бэкап без остановки MySQL?

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

Как восстановить только одну таблицу из дампа?

Загрузите дамп в отдельную базу, затем скопируйте нужную таблицу через INSERT INTO... SELECT или mysqldump с флагом --tables.

Почему дамп получается очень большим?

mysqldump выгружает данные в текстовом виде, поэтому файл может быть в несколько раз больше базы. Сжатие gzip уменьшает размер в 5-10 раз.

Ещё по теме

MySQL на своём сервере: установка, настройка и типичные ошибки: все материалы темы

Мы используем cookies и сервис веб-аналитики «Яндекс Метрика» (включая Вебвизор) для улучшения работы сайта. Подробности — в политике конфиденциальности.