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

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

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

Создать дамп базы данных можно через командную строку с помощью утилиты 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:

1. -h – адрес хоста, куда требуется подключаться для создания дампа.
2. -u – аккаунт, от имени которого будет выполнено резервирование (важно заранее убедиться в наличии соответствующих прав).
3. -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. Проверить, достаточно ли места для создания дампа, можно командой:

df –h

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

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

> REPAIR TABLE <table name> USE_FRM;

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

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

vi /etc/my.cnf

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

innodb_force_recovery = 1

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

systemctl restart mysql

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

Выводы

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