Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.
Кратко о задаче
Создать дамп базы данных можно через командную строку с помощью утилиты mysqldump. Ее устанавливают вместе с пакетом MySQL (идет в стандартном комплекте). Возможности одинаковы на Linux и на Windows. Запуск приложения допускается как на локальном, так и на удаленном сервере СУБД. Общий синтаксис:
mysqldump [опции] > <в какой файл сделать дамп>
mysqldump -v -h127.0.0.1 -uroot base -p > /tmp/dump.sqlЗдесь мы сделали резервный архив БД под названием base, разместили его в каталоге /tmp. Коннект осуществляется к локальной базе (IP 127.0.0.1) под аккаунтом root. Это был простейший вариант по созданию дампа для резервного копирования базы MySQL. Теперь перейдем к описанию базовых параметров утилиты mysqldump:
- -h – адрес хоста, куда требуется подключаться для создания дампа.
- -u – аккаунт, от имени которого будет выполнено резервирование (важно заранее убедиться в наличии соответствующих прав).
- -p – пароль используемого аккаунта. При работе через скрипт его вводят в формате p12345, в остальных случаях лучше оставить только символ -p, это безопаснее.
Мы перечислили только наиболее востребованные параметры. Если интересует их полный перечень для более глубокого изучения вопроса, он доступен на официальном сайте в документации по ссылке.
Несколько примеров по созданию дампа MySQL
Приведем варианты команд для нескольких частных случаев.
Последующее архивирование
DBNAME=base
mysqldump -uroot -p ${DBNAME} | gzip -c > /tmp/${DBNAME}.sql.gzМы сделали переменную DBNAME, где затем разместили значение «имя базы», подлежащей архивации. И только потом запустили утилиту mysqldump, чтобы по завершении передать очередь исполнения архиватору GZIP.
Итог: дамп, размещенный в каталоге /tmp/<имя базы>.sql.gz. Если пользователю надо, чтобы процесс отображался на экране, команда примет вид:
mysqldump -v -uroot -p ${DBNAME} | gzip -c > /tmp/${DBNAME}.sql.gzОдновременная архивация двух и более баз
Задача простая, достаточно перечислить названия БД через пробел и использовать опцию -B. Вот так будет выглядеть команда:
mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiplе_bases.sqlЗаархивировать все имеющиеся базы
Также можно запускать резервное копирование баз данных MySQL при помощи параметра --all-databases, указывая его вместо имени базы. Пример команды:
mysqldump -v -uroot -p --all-databases > /tmp/dump_all_bases.sqlРезервировать только структуру БД
Важно учитывать, что в таком режиме в архив будут сохранены таблицы без каких-либо данных. Режим выбирается при помощи параметра --no-data:
mysqldump -v -uroot -p --no-data base1 > /tmp/dump_base1_nodata.sqlСоздать резервную копию указанной таблицы
В этом случае к имени базы добавляет наименование таблиц. Например, как в команде:
mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sqlПеренос прав доступа к СУБД
Система способна выгрузить действующие учетки вместе с актуальными паролями. Такая функция нужна для экспорта СУБД на другой хост без риска утраты доступа к базе. Команда будет выглядеть так:
mysqldump -v -uroot -p mysql user > /tmp/mysql_user.sqlЧтобы обеспечить работоспособность после переноса при помощи такого дампа, нужно выполнить команду flush privileges (через SQL Shell).
При архивации игнорировать указанную таблицу
Подразумевается выполнение команды с ключом ignore-table:
mysqldump -v -uroot -p base --ignore-table=base.logs > /tmp/base.sqlРезервирование под любой учетной записью
Примеры, приведенные выше, были рассчитаны на подключение к БД при помощи пользователя root. На практике же процедуру выполняют и под другими учетками. Главное, чтобы у выбранного пользователя были права для доступа к базе, дамп которой планируется создать. Можно создать отдельный аккаунт с ограниченными правами для выполнения архивации. Выполним это командой SQL:
> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS, EXECUTE ON *.* TO backup@localhost IDENTIFIED BY 'backup123';Здесь мы создали учетку backup, задали пароль backup123. Прав у пользователя будет достаточно, чтобы подключиться к БД на локальном сервере. Чтобы не вводить вручную при каждом запуске одни и те же параметры, можно записать файл конфигурации:
vi ~/.my.cnf
[mysqldump]
host = 127.0.0.1
user=backup
password="backup123"Теперь команда примет «облегченный» вид:
mysqldump base > /tmp/dump.sqlРезервирование БД через phpMyAdmin
Если работать в консоли неудобно, можно воспользоваться штатным графическим инструментом phpMyAdmin.
Зайдем в пункт «Экспорт» верхнего меню и в разделе «Способ экспорта» поставим переключатель в положение «Обычный». После этого откроется ряд дополнительных опций. Из них нас интересует способ компрессии, например ZIP. Остается кликнуть ОК – запустится загрузка дампа с выполнением резервной копии и ее сохранением на локальном компьютере.
Иногда можно столкнуться с ошибкой Incorrect key file for table, которая выглядит так:
mysqldump: Error 1034: Incorrect key file for table '<table name>'; try to repair it when dumping table `<table name>` at row: xxxxxxПричин может быть несколько. Например, обнаружена логическая ошибка таблицы в БД или закончилось место на накопителе, где система сохраняет временные данные. Соответственно, для решения проблемы нужны разные подходы. Так, первым делом желательно проверить объем места на диске. В файле конфигурации, размещенном по пути /etc/my.cnf, указана опция tmpdir, которая определяет папку, куда система сохраняет временные таблицы.
Если ее нет, тогда по умолчанию будет использован путь /tmp. Проверить, достаточно ли места для создания дампа, можно командой:
Иная ситуация, если повреждена таблица. Ее необходимо восстановить, но решение зависит от типа проблемного участка базы.
MyISAM. Действия пользователя следующие. В командной строке SQL вводим команду:
> REPAIR TABLE <table name> USE_FRM;После нее повторяем попытку создания резервной копии.
INNODB. Откроем файл конфигурации СУБД:
Найдем раздел [mysqld] и в нем добавим опцию:
innodb_force_recovery = 1При повторе ошибки изменим значение innodb_force_recovery на 2. Если проблема все еще сохраняется, то на 3, и так вплоть до 6, пока резервирование не пройдет успешно. Важно учитывать, что применяемый параметр иногда приводит к потере данных. Если пришлось поднять его значение выше 2, стоит вручную проверить сохранность ценной информации после завершения резервирования.
Разверните базу данных в облаке
за пару кликов
Команда mysqldump обладает широким функционалом, позволяющим резервировать базы в разной комбинации. При желании ее легко заменить удобным инструментом с графическим интерфейсом phpMyAdmin. Эксперименты желательно проводить на пустой БД, где нет ценной информации. Например, можно арендовать соответствующие ресурсы у Simple-Server.
Нужен сервер для практики? Арендуйте VPS/VDS в России — root-доступ, NVMe, DDoS-защита и поддержка 24/7.