Как сделать бэкап базы данных MySQL на Ubuntu 24.04
Бэкап базы MySQL делается утилитой mysqldump: она выгружает структуру и данные в SQL-файл, который можно восстановить на этом же или другом сервере. Для рабочей базы используйте флаги --single-transaction и --quick, чтобы дамп не блокировал таблицы.
01Создайте отдельного пользователя для бэкапов
Не делайте дампы под root. Создайте пользователя с правами только на чтение и блокировку таблиц. Подключитесь к MySQL под администратором и выполните операторы. Замените пароль на свой.
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 выполните:
mysqldump -u backup -p example_db > dump.sql
03Сделайте дамп со сжатием и без блокировок
Для рабочей базы добавьте --single-transaction: InnoDB-таблицы выгружаются в согласованном состоянии без блокировки записи. Флаг --quick читает строки по одной и не держит всю таблицу в памяти. Вывод сразу сжимается gzip.
mysqldump --single-transaction --quick -u backup -p example_db | gzip > dump.sql.gz
04Проверьте размер и содержимое дампа
Пустой или подозрительно маленький файл говорит об ошибке. Посмотрите размер и первые строки: в начале должны быть комментарии mysqldump и операторы CREATE TABLE. Для сжатого файла используйте zcat.
ls -lh dump.sql.gz
zcat dump.sql.gz | head -n 20
05Восстановите дамп в тестовую базу
Проверка бэкапа без восстановления ничего не доказывает. Создайте пустую базу example_restore и загрузите в неё дамп. Для gzip-файла распаковывайте потоком через gunzip.
sudo mysql -e "CREATE DATABASE example_restore;"
gunzip < dump.sql.gz | mysql -u backup -p example_restore
06Убедитесь, что данные на месте
Сравните число строк в исходной и восстановленной базе. Если таблиц много, проверьте несколько ключевых. Команда SHOW TABLES покажет список таблиц, а COUNT(*) вернёт количество записей.
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 База данных MySQL на сервере Ubuntu 24.04: пошаговая настройка Пошагово: проверка MySQL 8.0 на Ubuntu 24.04, создание базы и пользователя, подключение, проверка версии и прав. Команды и проверка результата.
- Руководство · MySQL Настройка phpMyAdmin по http на Ubuntu 24.04 Как открыть phpMyAdmin по http на Ubuntu 24.04: установка без Apache, настройка nginx и PHP-FPM, отдельный пользователь MySQL, проверка входа и типичные ошибки.
- Руководство · MySQL MySQL port: настройка внешнего доступа на Ubuntu 24.04 Пошаговая настройка порта MySQL 3306 на Ubuntu 24.04: bind-address, файрвол, пользователь для внешнего хоста, проверка подключения с другого сервера.
- Руководство · MySQL MySQL консоль на Ubuntu 24.04: вход, команды и проверка Как войти в консоль MySQL на Ubuntu 24.04: sudo mysql, создание пользователя, подключение к базе, проверка и типичные ошибки 1045 и 1698.
- Руководство · MySQL MariaDB в Docker: запуск и настройка на Ubuntu 24.04 Запуск MariaDB в Docker Compose на Ubuntu 24.04: volume, пользователь и база, подключение, удаление базы, дамп и проверка работы контейнера.
- Руководство · MySQL Как посмотреть пользователей MySQL и управлять ими Как посмотреть пользователей MySQL, добавить и удалить их, сменить пароль. Команды для Ubuntu 24.04, проверка списка через mysql.user и SHOW GRANTS.
MySQL на своём сервере: установка, настройка и типичные ошибки: все материалы темы