Как восстановить базу MySQL из резервной копии
Обновлено:
Опубликовано:
Тематические термины: MySQL, MariaDB, phpMyAdmin
Действия, описанные в данной инструкции помогут нам:
- Восстановить удаленную базу MySQL/MariaDB.
- Выполнить прафилактические работы по проверке бэкапа.
- Восстановить часть данных (конкретную запись или таблицу).
В нашем примере показано восстановление из заранее сделанного dump-файла (sql-файла), созданного с помощью утилиты mysqldump. Если нужна инструкция по созданию резервной копии, читайте Как сделать дамп базы MySQL.
Создание базы и пользователя
Восстановление с помощью командной строки
Использование phpMyAdmin
Пропуск ошибок
Указать целевую базу для восстановления
Восстановление конкретной таблицы
Устранение ошибок
MySQL server has gone away
Row size too large
Дополнительные материалы
Подготовка базы
Подключаемся к командной оболочке mysql:
mysql -uroot -p
* данной командой мы подключимся к СУБД под пользователем root. Опция -p потребует ввода пароля.
Для восстановления базы сначала необходимо ее создать:
> CREATE DATABASE db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
* подробнее про создание баз читайте на странице Создание и удаление баз в MySQL/MariaDB.
При необходимости, также создаем пользователя, который будет иметь доступ к базе:
> CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password';
> GRANT ALL PRIVILEGES ON db.* TO 'dbuser'@'localhost';
* подробнее про работу с пользователями MySQL/MariaDB читайте на странице Создание пользователей 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
Во время восстановления базы может выскочить ошибка:
ERROR 2006 (HY000) 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 mysqld
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.
Читайте также
Вам также может быть полезным:
1. Создание пользователей MySQL/MariaDB и предоставление прав доступа.
2. Создание и удаление баз в MySQL/MariaDB.
3. Примеры SQL-запросов в MariaDB (MySQL).
4. Пример скрипта для автоматического создания резервной копии MySQL.
