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

В статье рассмотрены два доступных способа восстановления баз данных MySQL: с помощью командной строки и через интерфейс phpMyAdmin.

Пошаговая инструкция по восстановлению через терминал

Подключаемся к СУБД MySQL от имени рут-пользователя; так как указан флаг -p, то нужно будет ввести пароль:

mysql -u root -p

Не забудьте создать новую таблицу, в которую будете восстанавливать данные из дампа:

mysql> CREATE DATABASE new_data_base_name

Если резервная копия была упакована в архив, предварительно распаковываем её. В примере dumpDB.sql.gz — имя архива, упакованного утилитой gzip:

gunzip /tmp/dumpDB.sql.gz

Для более краткой записи можно присвоить переменную имени БД:

export DB_name=new_data_base_name

Восстанавливаем резервную копию из dumpDB.sql (потребуется ввод пароля). Команда имеет вид:

mysql -u [имя рут-пользователя] -p[пароль] [целевая БД] < /директория/хранения/резервной/копии/ [DB_dump.sql]

Если на данном этапе указать флаг -v, то на экране будет отображаться ход процесса восстановления, но это значительно снижает скорость распаковки.

Если копия содержит указание на восстановление в определённую таблицу (параметр USE table), то данные будут импортированы именно в нее, независимо от указанной в команде таблицы.

Если в одной директории лежит несколько дампов, то можно восстановить их все одной командой:

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

Команда восстановит все файлы дампов (с расширением .sql) из папки /tmp/.

Про архиваторы для Linux ранее рассказали в блоге.

Пропуск ошибок при восстановлении базы

Если бекап-файл содержит ошибки, но базу данных нужно срочно восстановить, то к команде добавляют флаг -f или —force (игнорировать ошибки):

mysql -v -u root -p -f  [целевая БД] < /директория/хранения/резервной/копии/ [DB_dump.sql]

Этот способ используется только в исключительных случаях из-за возможной утери данных.

Установка целевой БД в файле бекапа

Обычно бекап содержит инструкцию с базой, куда будет импортироваться его содержание, она имеет вид:

USE [целевая БД для импорта]

Чтобы изменить целевую базу данных, отредактируем значение в этой строке, параметр [целевая БД для импорта] меняем на имя нужной БД:

USE new_data_base_name

Отредактировать запись можно и не открывая файл бекапа:

sed 's/USE [целевая БД для импорта]/USE new_data_base_name/' -i /tmp/dumpDB.sql

Команда из примера заменит USE [целевая БД для импорта] на USE new_data_base_name в файле /tmp/dumpDB.sql

Выборочное восстановление таблиц

Утилита mysql не позволяет восстановить только часть резервной копии (отдельные таблицы). Обойти это ограничение можно двумя способами.

Инструкция по восстановлению через промежуточную БД

Предлагаем такой вариант действий.

1. Экспортируем все данные из резервной копии в специально созданную промежуточную базу данных.

2. Затем копируем выбранную таблицу в конечную целевую БД. Вызовите среду выполнения SQL.

Выполните следующую команду:

> INSERT INTO имя_БД.имя_таблицы SELECT * FROM имя_вспомогательной_БД.имя_таблицы

Например:

> INSERT INTO data_base_01.table_name01 SELECT * FROM intermediate_data_base01.table_name01

В результате содержимое таблицы table_name01 из intermediate_data_base01 будет импортировано в базу данных data_base_01.

Резервное копирование выбранной таблицы

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

mysqldump -u root -p [исходная_БД] [tab_name01] > /директория/dump_tab_name01.sql

Бекап dump_tab_name01.sql будет создан по указанному адресу /директория/.

2. Затем можно выполнить восстановление таблицы из копии в отдельную БД.

Исправление возможных ошибок

Перечислим основные ошибки, которые могут появиться во время импорта.

MySQL server has gone away

Чаще всего её вызывает недостаточно высокое значение опции max_allowed_packet. Данный параметр задаёт максимально допустимый размер данных, которые могут быть переданы за один запрос.

1. Проверяем установленное значение командой:

> SHOW VARIABLES LIKE 'max_allowed_packet'

2. Затем откройте для редактирования файл конфигурации my.cnf (откроется в текстовом редакторе vi):

vi /etc/my.cnf

3. В содержимом находим блок [mysqldump] и добавляем новую строку (или меняем значение, если она уже есть):

max_аllowed_packet = ***M

Установите допустимый размер так, чтобы снять прежние ограничения.

4. Запускаем mysql заново:

systemctl restart mysql

5. Возобновляем импорт из бекапа.

Row size too large

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

Как исправить ошибку:

1. Проверим параметр mysql innodb_strict_mode и зададим ему значение «0». Файл с настройками может находиться в директориях:

/etc/my.cnf
/etc/mysql/my.cnf

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

Как восстановить БД MySQL в phpMyAdmin

Алгоритм действий при восстановлении

Зайдём в phpMyAdmin, выберем базу данных, куда будем импортировать данные.

Откроем вкладку «Импорт»:

Выбираем источник бекапа по нажатию на «Выберите файл».

Для запуска процесса нажимаем «Вперёд».

По окончании перезагружаем сервер MySQL.

Увеличение разрешённого объёма дампа

По умолчанию phpMyAdmin ограничивает максимальный размер импортируемых файлов до 2 Мб. Если требуется восстановить большую базу данных — снимите ограничения либо в phpMyАdmin, либо на сервере.

Изменение параметров конфигурации

В php.ini и .htaccess увеличим дефолтные значения, мешающие импорту.

1. Путь к php.ini можно посмотреть в php-файле, лежащем в root-директории сайта; его содержат строки «Loaded Configuration File» или «Configuration File (php.ini) Path». Для этого введите в адресной строке браузера https://sitename.ru/*.php. В примере sitename.ru — имя вашего сайта, а *.php — php-файл в его корне.

2. Прописываем в php.ini:

post_max_size = 400M
upload_max_filesize = 300M
max_exеcution_time = 2000
max_input_time = 5000

3. В .htaccess дублируем те же параметры:

php_value post_max_size 400M
php_value upload_max_filesize 300M
php_value max_exеcution_time 2000
php_value max_input_time 5000

Как повысить лимит времени выполнения PHP-скриптов

1. В config.inc.php пропишем следующий параметр:

cfg['UploadDir'] = './upload';

2. Затем временно добавим запись, которая снимет ограничения.

cfg['ExecTimeLimit'] = 0

3. Удалим последнюю запись после восстановления базы данных MySQL.

Теперь вы знаете, как восстановить базу данных MySQL разными способами.