Резервное копирование базы MySQL / MariaDB

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

Синтаксис и базовая команда

Создание дампа выполняется из командной строки Linux или Microsoft с помощью утилиты mysqldump. Она идет в составе с пакетом mysql/mariadb (mysql-client) и может быть запущена как локально на сервере СУБД, так и с удаленного компьютера.

Общий синтаксис:

mysqldump [опции] > <в какой файл сделать дамп>

Пример базовой команды для резервирования базы:

mysqldump -v -h 127.0.0.1 -uroot -p base > /tmp/dump.sql

* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе на сервере 127.0.0.1 происходит от пользователя root. Это самый простой пример создания дампа MySQL.

Базовые параметры команды mysqldump:

Параметр	Описание
-h	Адрес сервера, к которому нужно подключиться.
-u	Учетная запись, от которой выполняется резервное копирование. Необходимо, чтобы у пользователя были соответствующие права.
-p	Пароль учетной записи. Его можно ввести в команде, например -p12345 (для скрипта) или оставить -p (безопаснее).

* полный перечень параметров смотрите в официальном руководстве.

Примеры создания дампа MySQL

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

1. С последующим архивированием

export DBNAME=base
mysqldump -uroot -p ${DBNAME} | gzip > /tmp/${DBNAME}.sql.gz

* в данном примере мы сначала создали переменную DBNAME, в которую внесли значение с именем базы, которую необходимо забэкапить. После выполняем команду mysqldump, результат выполнения которой по конвейеру отдаем архиватору gzip. В результате мы получит дамп по пути /tmp/<имя базы>.sql.gz

Или с подробным выводом информации на экран (дольше по времени):

mysqldump -v -uroot -p ${DBNAME} | gzip > /tmp/${DBNAME}.sql.gz

2. Для одновременно нескольких баз

Просто перечисляем имена баз через пробел и добавляем параметр -B

mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiply_bases.sql

3. Для всех баз одной командой

Для этого ставим --all-databases, вместо имен баз

mysqldump -v -uroot -p --all-databases > /tmp/dump_all_bases.sql

4. Резервирование только структуры базы

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

mysqldump -v -uroot -p --no-data base1 > /tmp/dump_base1_nodata.sql

5. Создание копии определенной таблицы

Для этого после базы через пробел перечисляем названия таблиц

mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql

6. Резервирование прав доступа на СУБД

Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.

mysqldump -v -uroot -p mysql user > /tmp/mysql_user.sql

* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;

7. Проигнорировать определенную таблицу

Выполняется при помощи ключа 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, которому будет разрешено подключаться и выполнять действия с локального сервера (localhost).

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

Создаем файл:

vi ~/.my.cnf

[mysqldump]
host = 127.0.0.1
user=backup
password="backup123"

Теперь можно вводить:

mysqldump base > /tmp/dump.sql

Скрипт для резервного копирования

Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron.

Подробнее процесс описан в статье Пример скрипта для создания резервной копии MySQL (для Linux).

Резервное копирование в phpMyAdmin

В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.

В верхней части меню кликаем по Экспорт:

В разделе «Способ экспорта» ставим переключатель в положение Обычный:

* обычный режим откроет дополнительные опции для резервного экспорта данных.

Выбираем компрессию, например, zip:

И в нижней части окна нажимаем OK.

Начнется загрузка файла с резервной копией на компьютер.

Возможные проблемы

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

Причина: причин может быть несколько:

1. Логическая ошибка таблицы.
2. Нехватка места на разделе с каталогом для временных данных.

Решение: в зависимости от причины, решений будет несколько.

1. Проще всего сначала проверить место на диске. В конфигурационном файле СУБД (как правило, /etc/my.cnf) можно найти опцию tmpdir — она указывает на каталог, который используется под создание временных таблиц. Если опции нет, то используется путь /tmp.

Необходимо, чтобы для данного раздела было достаточно места. Проверить можно командой:

df -h

2. Если наша таблица повреждена, то пробуем ее восстановить. Данный процесс зависит от типа таблицы, с которой возникла проблема.

а) Если тип MyISAM.

В командной оболочке SQL вводим:

> REPAIR TABLE 

USE_FRM;

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

б) Если таблица типа INNODB.

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

vi /etc/my.cnf

В радел [mysqld] добавим опцию:

[mysqld]
...
innodb_force_recovery = 1

Перезапускаем сервер баз данных, например:

systemctl restart mysql

Пробуем сделать резервную копию. Если получим такую же ошибку, меняем значение innodb_force_recovery с 1 на 2:

[mysqld]
...
innodb_force_recovery = 2

И так по кругу, до значения 6, пока не получим положительный результат.

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