Создание дампа базы в phpMyAdmin
В одной из предыдущих статей «Переносим сайт с локального сервера на удаленный» я описывал процедуру переноса готового сайта на сервер удаленного хостера. Там я говорил о необходимости сделать дамп базы, но как это делать не уточнил, так как посчитал, что это выполнить не сложно. Пообщавшись с людьми я обнаружил, что не все знают как правильно следует выполнять дамп базы в PhpAdmin. Поэтому сегодняшняя статья призвана восполнить образовавшийся пробел.
Перенос данных из БД состоит из двух этапов:
В phpMyAdmin выбираем необходимую базу данных из списка. Потом выбираем вкладку ‘Export’, в блоке ‘Export’ кликаем Select All (чтобы выбрать для экспорта все таблицы), выбираем здесь же в чекбоксе SQL.
В блоке ‘Structure’ ставим чекбокс, если нужно чтобы при импорте дампа перед записью данных были созданы таблицы (если на удаленном MySQL сервере уже созданы таблицы, то чекбокс нужно убрать). В этом же блоке (‘Structure’) оставляем как предлагается по умолчанию чекбоксы ‘Add AUTO_INCREMENT value’ и ‘Enclose table and field names with backquotes’. В блоке ‘Data’ ставим чекбокс чтобы импортировать записи таблиц. Оставляем отмеченными как предлагается чекбоксы ‘Complete inserts’, ‘Extended inserts’ и ‘Use hexadecimal for binary fields’. Тип экспорта INSERT (если данные заносятся в таблицу впервые). Затем отмечаем чекбокс ‘Save as file’ и нажимаем ‘Отправить’. В результате получаем на локальном диске дамп базы данных (файл с расширением .sql). Теперь нужно этот дамп залить на хост.
На удаленном сервере в phpMyAdmin выбираем вкладку «Import», указываем местоположение дампа на своем локальном диске, указываем кодировку в которой сохранен дамп, который собираемся импортировать. Формат импортируемого файла — SQL.
Всё, после этого разрешаем процедуру импорта. Если дамп корректен (по синтаксису и структуре), то все должно пройти успешно! 🙂 Желаю удачи.
Как восстановить базу MySQL
Обновлено: 13.04.2023 Опубликовано: 25.10.2016
В данном примере показано восстановление из заранее сделанного dump-файла (с помощью mysqldump). Если нужна инструкция по созданию резервной копии, читайте Как сделать дамп базы MySQL.
Подготовка базы
Подключаемся к командной оболочке mysql:
* данной командой мы подключимся к СУБД под пользователем 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’;
Из файла через командную строку
Если при создании дампа использовалась gzip, сначала распаковываем архив:
Для удобства, создадим переменную с именем базы:
Команда выполняется из UNIX-shell:
* где root — учетная запись, от которой идет подключение к серверу баз данных; DNBAME — имя базы, которую необходимо восстановить (переменная, которую мы задали ранее); /tmp/recovery.sql — файл дампа, из которого восстанавливаем базу.
* можно также добавить опцию -v — она позволит показать на экране ход процесса, однако, она очень сильно снижает скорость восстановления — не рекомендуется ее использовать для больших баз.
На самом деле, если внутри дампа есть указание на переход к конкретной таблице (USE table), то восстановление будет выполняться в нее, а не ту таблицу, которую мы указали в переменной DBNAME. Как это проверить и изменить сказано ниже.
Если у нас много файлов, которые нужно импортировать, можно выполнить следующую команду:
cat /tmp/*.sql | mysql -u root -p db
* в данном случае мы прочитаем из каталога /tmp все файлы, заканчивающиеся на .sql и импортируем их содержимое в базу.
С помощью phpMyAdmin
Выбираем базу, которую нужно восстановить. Переходим на вкладку Импорт — кликаем по кнопке Выберите файл:
Выбираем файл с резервной копией.
Нажимаем по OK и ждем восстановления данных.
Пропускать ошибки
Данный способ восстановления лучше не применять, так как он может приводить к потере данных. Он может помочь, если нужно срочно восставновить дамп, а он выкидывает различные ошибки, с которыми не удалось разобраться быстро.
Суть сводится к простому добавлению ключа —force или -f:
Восстановление в другую базу
По умолчанию, восстановление происходит в ту базу, для которой указан переход в самом дампе с помощью инструкции:
* где 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:
* в некоторых версиях СУБД конфиг может находится по пути /etc/my.cnf.d/server.cnf.
В разделе [mysqldump] редактируем или добавляем:
[mysqldump].
max_allowed_packet = 512M
* значение для данного параметра не обязательно должно быть таким большим.
Перезапускаем mysql одной из команд:
systemctl restart mariadb
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.
Мы же сделаем все по-быстрому. Открываем конфигурационный файл СУБД — его местоположение зависит от версии и реализации, например:
* это пример расположения для базы MariaDB 10. Более точное расположение можно найти в файле /etc/my.cnf.
Приводим опцию innodb_strict_mode к виду:
[mysqld].
innodb_strict_mode = 0
systemctl restart mariadb
* в данном примере мы перезапустили сервис для mariadb.