Как восстановить базу MySQL

Подготовка базы

Подключаемся к командной оболочке mysql:

mysql -uroot -p

* данной командой мы подключимся к СУБД под пользователем root. Опция -p потребует ввода пароля.

Для восстановления базы сначала необходимо ее создать:

> CREATE DATABASE db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

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

> GRANT ALL PRIVILEGES ON db.* TO dbuser@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;

Подробнее про создание баз читайте на странице Создание и удаление баз в MySQL/MariaDB.

Из файла через командную строку

Если при создании дампа использовалась gzip, сначала распаковываем архив:

gunzip /tmp/dump.sql.gz

Для удобства, создадим переменную с именем базы:

export DBNAME=base

Команда выполняется из UNIX-shell:

mysql -u root -p ${DBNAME} < /tmp/dump.sql

* где root — учетная запись, от которой идет подключение к серверу баз данных; DNBAME — имя базы, которую необходимо восстановить (переменная, которую мы задали ранее); /tmp/recovery.sql — файл дампа, из которого восстанавливаем базу.
* можно также добавить опцию -v — она позволит показать на экране ход процесса, однако, она очень сильно снижает скорость восстановления — не рекомендуется ее использовать для больших баз.

На самом деле, если внутри дампа есть указание на переход к конкретной таблице (USE table), то восстановление будет выполняться в нее, а не ту таблицу, которую мы указали в переменной DBNAME. Как это проверить и изменить сказано ниже.

Если у нас много файлов, которые нужно импортировать, можно выполнить следующую команду:

cat /tmp/*.sql | mysql -u root -p db

* в данном случае мы прочитаем из каталога /tmp все файлы, заканчивающиеся на .sql и импортируем их содержимое в базу.

С помощью phpMyAdmin

Выбираем базу, которую нужно восстановить. Переходим на вкладку Импорт - кликаем по кнопке Выберите файл:

Выбираем файл с резервной копией.

Нажимаем по OK и ждем восстановления данных.

Пропускать ошибки

Данный способ восстановления лучше не применять, так как он может приводить к потере данных. Он может помочь, если нужно срочно восставновить дамп, а он выкидывает различные ошибки, с которыми не удалось разобраться быстро.

Суть сводится к простому добавлению ключа --force или -f:

mysql -v -u root -p -f db < /tmp/dump.sql

Восстановление в другую базу

По умолчанию, восстановление происходит в ту базу, для которой указан переход в самом дампе с помощью инструкции:

USE `database_name`;

* где database_name — имя конкретной базы.

Для смены базы просто редактируем это значение на любое другое, например, строка:

USE `new_database_name`;

... приведет к тому, что восстановление будет выполняться в базу new_database_name.

Если файл дампа большой, открывать его на редактирование может оказаться непростой задачей. Поменять название базы можно с помощью sed:sed 's/USE `database_name`;/USE `new_database_name`;/' -i /tmp/dump.sql* в данном примере мы заменим имя базы database_name на new_database_name в файле /tmp/dump.sql.

Восстановление в другую таблицу

Команда mysql не предусматривает возможности восстановить дамп только для одной таблицы. Есть два варианта это обыграть.

1. Восстановление с применением временной базы.

Чтобы выполнить развертывание конкретной таблицы, нам нужно сначала сделать восстановление в отдельную базу, после чего скопировать таблицу в нужную базу командой на подобие этой (должна выполняться в среде SQL):

> INSERT INTO database_name.table_name SELECT * FROM new_database_name.table_name;

* в данном примере выполняется копирование содержимого таблицы table_name из базы данных new_database_name в базу database_name.

2. Резервирование только одной таблицы.

Если восстановление не является экстренным, и мы имеем доступ к источнику данных, можно выполнить резервирование только нужной нам таблицы. Это делается командой на подобие:

mysqldump -uroot -p database_name table_name > /tmp/dump_base_table.sql

После чего уже выполняем восстановление из дампа.

Возможные ошибки

В процессе восстановления мы можем столкнуться с разными ошибками. Рассмотрим их примеры.

MySQL server has gone away

Во время восстановления базы может выскочить ошибка:

at line xxx: MySQL server has gone away.

Как правило, ее причина в низком значении параметра max_allowed_packet, который отвечает за ограничение выполнения команд из файла. Посмотреть текущее значение можно командой в mysql:

> SHOW VARIABLES LIKE 'max_allowed_packet';

Чтобы увеличить значение параметра, открываем конфигурационный файл my.cnf:

vi /etc/my.cnf

* в некоторых версиях СУБД конфиг может находится по пути /etc/my.cnf.d/server.cnf.

В разделе [mysqldump] редактируем или добавляем:

[mysqldump]
...
max_allowed_packet = 512M

* значение для данного параметра не обязательно должно быть таким большим.

Перезапускаем mysql:

systemctl restart mariadb || systemctl restart mysql

Row size too large

Ошибка выскакивает после небольшого времени работы восстановления. Более полный текст выглядит, примерно, так:

ERROR 1118 (42000) at line 608: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Причина: ошибка встречается, если в нашей базе есть большое количество текстовых полей и мы используем таблицы типа INNODB. По умолчанию, они имеют ограничение на объем данных, которые можно хранить в одной строке таблицы.

Решение:

Для решения проблемы мы можем добавить опцию innodb_strict_mode со значением 0. Данная опция регламентирует более строгий режим работы СУБД. Это грубое решение, которое позволит нам добиться результата, но мы можем выполнить настройку тонко — об этом можно прочитать на соответствующей странице блога mithrandir.ru.

Мы же сделаем все по-быстрому. Открываем конфигурационный файл СУБД — его местоположение зависит от версии и реализации, например:

vi /etc/mysql/mariadb.conf.d/50-server.cnf

* это пример расположения для базы MariaDB 10. Более точное расположение можно найти в файле /etc/my.cnf.

Приводим опцию innodb_strict_mode к виду:

[mysqld]
...
innodb_strict_mode = 0

Перезапускаем сервис:

systemctl restart mariadb

* в данном примере мы перезапустили сервис для mariadb.