Резервное копирование MySQL: инструкция

В статье рассмотрим способы резервного копирования СУБД MySQL, приведем примеры по наиболее востребованным ключам и параметрам. В качестве тестовой платформы можно использовать облачные базы данных Timeweb Cloud.

Синтаксис команд

Создать дамп базы данных можно через командную строку с помощью утилиты mysqldump. Ее устанавливают вместе с пакетом MySQL (идет в стандартном комплекте). Возможности одинаковы на Linux и на Windows. Запуск приложения допускается как на локальном, так и на удаленном сервере СУБД. Общий синтаксис:

Пример:

Здесь мы сделали резервный архив БД под названием base, разместили его в каталоге /tmp. Коннект осуществляется к локальной базе (IP 127.0.0.1) под аккаунтом root. Это был простейший вариант по созданию дампа для резервного копирования базы MySQL. Теперь перейдем к описанию базовых параметров утилиты mysqldump:

1. -h – адрес хоста, куда требуется подключаться для создания дампа.
2. -u – аккаунт, от имени которого будет выполнено резервирование (важно заранее убедиться в наличии соответствующих прав).
3. -p – пароль используемого аккаунта. При работе через скрипт его вводят в формате p12345, в остальных случаях лучше оставить только символ -p, это безопаснее.

Мы перечислили только наиболее востребованные параметры. Если интересует их полный перечень для более глубокого изучения вопроса, он доступен на официальном сайте в документации по ссылке.

Несколько примеров по созданию дампа MySQL

Приведем варианты команд для нескольких частных случаев.

Последующее архивирование

Команда:

Мы сделали переменную DBNAME, где затем разместили значение «имя базы», подлежащей архивации. И только потом запустили утилиту mysqldump, чтобы по завершении передать очередь исполнения архиватору GZIP. 

Итог: дамп, размещенный в каталоге /tmp/<имя базы>.sql.gz. Если пользователю надо, чтобы процесс отображался на экране, команда примет вид:

Одновременная архивация двух и более баз

Задача простая, достаточно перечислить названия БД через пробел и использовать опцию -B. Вот так будет выглядеть команда:

Заархивировать все имеющиеся базы

Также можно запускать резервное копирование баз данных MySQL при помощи параметра --all-databases, указывая его вместо имени базы. Пример команды:

Резервировать только структуру БД

Важно учитывать, что в таком режиме в архив будут сохранены таблицы без каких-либо данных. Режим выбирается при помощи параметра --no-data:

Создать резервную копию указанной таблицы

В этом случае к имени базы добавляет наименование таблиц. Например, как в команде:

Перенос прав доступа к СУБД

Система способна выгрузить действующие учетки вместе с актуальными паролями. Такая функция нужна для экспорта СУБД на другой хост без риска утраты доступа к базе. Команда будет выглядеть так:

Чтобы обеспечить работоспособность после переноса при помощи такого дампа, нужно выполнить команду flush privileges (через SQL Shell).

При архивации игнорировать указанную таблицу

Подразумевается выполнение команды с ключом ignore-table:

Резервирование под любой учетной записью

Примеры, приведенные выше, были рассчитаны на подключение к БД при помощи пользователя root. На практике же процедуру выполняют и под другими учетками. Главное, чтобы у выбранного пользователя были права для доступа к базе, дамп которой планируется создать. Можно создать отдельный аккаунт с ограниченными правами для выполнения архивации. Выполним это командой SQL:

Здесь мы создали учетку backup, задали пароль backup123. Прав у пользователя будет достаточно, чтобы подключиться к БД на локальном сервере. Чтобы не вводить вручную при каждом запуске одни и те же параметры, можно записать файл конфигурации:

Теперь команда примет «облегченный» вид:

Резервирование БД через phpMyAdmin

Если работать в консоли неудобно, можно воспользоваться штатным графическим инструментом phpMyAdmin. 

Зайдем в пункт «Экспорт» верхнего меню и в разделе «Способ экспорта» поставим переключатель в положение «Обычный». После этого откроется ряд дополнительных опций. Из них нас интересует способ компрессии, например ZIP. Остается кликнуть ОК – запустится загрузка дампа с выполнением резервной копии и ее сохранением на локальном компьютере.

Типовые проблемы

Иногда можно столкнуться с ошибкой Incorrect key file for table, которая выглядит так:

Причин может быть несколько. Например, обнаружена логическая ошибка таблицы в БД или закончилось место на накопителе, где система сохраняет временные данные. Соответственно, для решения проблемы нужны разные подходы. Так, первым делом желательно проверить объем места на диске. В файле конфигурации, размещенном по пути /etc/my.cnf, указана опция tmpdir, которая определяет папку, куда система сохраняет временные таблицы.

Если ее нет, тогда по умолчанию будет использован путь /tmp. Проверить, достаточно ли места для создания дампа, можно командой:

Иная ситуация, если повреждена таблица. Ее необходимо восстановить, но решение зависит от типа проблемного участка базы.

MyISAM. Действия пользователя следующие. В командной строке SQL вводим команду:

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

INNODB. Откроем файл конфигурации СУБД:

Найдем раздел [mysqld] и в нем добавим опцию:

Перезапустим сервер БД:

При повторе ошибки изменим значение innodb_force_recovery на 2. Если проблема все еще сохраняется, то на 3, и так вплоть до 6, пока резервирование не пройдет успешно. Важно учитывать, что применяемый параметр иногда приводит к потере данных. Если пришлось поднять его значение выше 2, стоит вручную проверить сохранность ценной информации после завершения резервирования. 

Выводы

Команда mysqldump обладает широким функционалом, позволяющим резервировать базы в разной комбинации. При желании ее легко заменить удобным инструментом с графическим интерфейсом phpMyAdmin. Эксперименты желательно проводить на пустой БД, где нет ценной информации. Например, можно арендовать соответствующие ресурсы у Timeweb Cloud.