В сие посте я заведу речь о том как же можно импортировать большую по размеру базу данных на сервере, т.е. как поднять файл с расширением .sql на боевом сервере баз данных MySQL. Естественно по традиции своего блога я поведаю о всех известных и популярных способах. На других блогах как правило пишется только о каком нибудь одном способе импорта базы, я же опишу сразу все, хотя я пожалуй уже начинаю повторяться. Давайте уже начнём.
Идти будем по стандартному пути, от самого простого и известного до самого сложного и не изведанного для многих новичков. Итак нам нужно поднять бекам базы данных на сервере.
PhpMyAdmin
Первое, что приходит на ум это использовать программный комплекс под названием phpMyAdmin. Да у него по умолчанию стоят ограничения на загрузку файлов. Однако их легко обойти особенно когда у вас есть на то необходимые права. Ну я думаю об этом и так все знают поэтому пойду далее.
Sypex Dumper
Превосходное решение для подобных целей. На сайте производителя имеется как платная так и упрощённая бесплатная версия плагина. Скачиваем его и устанавливаем себе на сайт. Там всё просто поэтому долго останавливаться на этом не стану. Скажу лишь, что скрипт поистине великолепный и выручает в самых сложнейших ситуациях. В установке прост. Закидывайте например по FTP попку sxd (по умолчанию) к себе на сайт, ставите права 777 на папку backup и в эту же папку помещаете бекап файл базы, а на файлы cfg.php, ses.php — права 666. После просто набираете в браузере URL данной папки, вводите данные для соединения с БД и можете импортировать. Естественно не забудьте посмотреть системные требования для той версии скрипта, которую будите использовать у себя на сайте.
BigDump
Так же очень полезная вещь выполненная в минималистическом стиле одного файла php. Редактируете данный файл для соединения с вашей БД, а так же (при необходимости) указываете там же название вашего файла базы данных. После чего закидываете файл на web сервер, открываете через браузер и следуете дальнейшим инструкциям. По сути там потребуется только нажать одну кнопку и скрипт сам выполнит всю работу.
mysqldump
У сервера баз данных MySQL есть специальная утилита создания дампов баз данных, которая поставляется в базовом комплекте программы. Называется она mysqldump и лежит в папке bin вместе со всеми остальными консольными утилитами. Программа имеет текстовый (консольный) интерфейс и множество ключей. Я рассмотрю самый тривиальный пример создания дампа базы данных при помощи утилиты mysqldump. Делаем копию базы:
mysqldump -uroot -ppassword db_name > dump_file_name.sql
Собственно всё. Передаём программе пользовательские данные, название базы данных и название файла для дампа. Если не перевести вывод данных в файл, то весь SQL скрипт будет вывален в консоль, чего нам не надо. Естественно параметры входа и названия файлов и баз пишем свои.
Что бы импортировать базу пишем следующее:
mysql -uroot -ppassword < dump_file_name.sql
Т.е. вводим файл дампа. Клиент mysql выполнит его по шагам и база будет импортирована.
MySQL пакетный режим (batch mode) в командной строке
Если ничего не помогает или просто нет желания возиться, то можно прибегнуть к «последней инстанции» — пакетному режиму СУБД MySQL. Данный режим позволяет импортировать файлы баз данных напрямую. Для этого нам потребуется командная строка (в случае если сервер на ОС MS Windows) или терминал (в случае использования GNU Linux систем). Думаю с этим проблем возникнуть не должно ведь даже на тарифе виртуального хостинга большинство хостинг провайдеров предоставляют своим пользователям доступ к серверу по протоколу ssh. В общем заходим в консоль и пишем примерно следующее:
mysql -h <ip_сервера_СУБД_MySQL> -u <имя_пользователя> -p <имя_базы_данных> < <имя_файла>
Таким образом запускаем утилиту mysql от имени пользователя, указываем то, что используется парольный доступ и далее передаём туда файл базы данных для импортирования. Далее понадобится ввести пароль и дело пойдёт. Естественно если сервер СУБД MySQL находится на этой же машине то указывать ключ -h (host) не требуется или можно там просто прописать localhost. Если для входа пароль не используется то ключ -p так же не нужен. Т.е. этом может выглядеть так:
mysql -u root -p base < /home/user/base.sql
Общий вид в общем таков:
mysql -u user_name -p db_name < file.sql
Вообще говоря самая короткая форма может выглядеть так:
shell> mysql db_name < text_file
Так же можно «отдать» sql файл при помощи ключа -e:
mysql -e "source batch-file"
При этом обращайте пристальное внимание на кодировку файла базы данных. Стандартная кодировка для MySQL – это UTF-8, поэтому если у Вас кодировка, импортируемой базы данных, например CP1251, то стоит до импорта ее перекодировать. Для этого есть команда iconv:
iconv -f CP1251 -t UTF-8 /home/user/base.sql > /home/user/base.utf8.sql
Т.е. думаю синтаксис импорта понятен?
mysql -u пользователь_БД -p имя_БД < файл_дампа_БД
Кстати так же легко пакетный режим MySQL можно использовать и для экспорта базы данных в файл (создание бэкапа):
mysqldump -u пользователь_БД -p имя_БД > имя_файла
Если в первом случае в БД будет загружено содержимое указанного вами файла (в SQL-формате), это определяется символом <, то во втором наоборот — содержимое указанной базы данных будет скопировано в файл, (символ >).
Например, вот таким запросом для импорта дампа файла пользуется родной для СУБД MySQL графический клиент-менеджер Workbench:
mysql.exe --defaults-file="c:\users\admin\appdata\local\temp\tmpprnyiy.cnf" --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments --database=title_database < "D:\\Dumps\\dump_file.sql"
Всё очень легко. Просто передаём ключами консольному клиенту mysql.exe (он же mySQL monitor) все необходимые параметры включая сам sql файл дампа базы данных.
MySQL Пакетный режим в интерактивном режиме
Естественно это ещё не всё о чём я хотел поведать. Ведь на этом ещё не конец. Естественно импортировать базу данных можно непосредственно и в интерактивном режиме при помощи ввода SQL команд. Для этого понятное дело необходимо будет войти в MySQL Monitor. Для этого по уже известному синтаксису пишем следующее:
mysql -u userName -p;
Когда оказываемся «внутри» выбираем необходимую базу данных:
USE <data_base_name>;
Естественно не забываем указать кодировку соединения с базой:
SET NAMES utf8;
А дальше самое интересное, для импортирования (запуска) sql файлов в MySQL есть специальный оператор SOURCE. Именно он нам и понадобится для импорта базы данных. Синтаксис следующий:
SOURCE c:\имя_файла.sql;
После чего начнётся импорт файла, точнее его выполнение т.к. это может быть не только импорт а вообще любые SQL команды. Все логи импорта файла будут тут же выводится на консоль. Если возникнут какие-либо ошибки то внимательно читайте сообщения.
Возможные проблемы
Естественно могут возникнуть разные проблемы. Как правило это связано с настройками СУБД MySQL, точнее с переменными. Некоторые переменные можно поменять на ходу в то время как другие нужно будет менять только в самом конфигурационном файле MySQL my.cnf.
К примеру может стоять маленькое ограничение в переменной innodb_additional_mem_pool_size или в max_allowed_packet (максимально разрешённый размер пакета/файла). Тут уж никуда без прав рута или без помощи технической поддержки хостинга. Можно конечно попытаться поизменять значения этих переменных к примеру так:
mysqld --max_allowed_packet=16M ...
Т.е. при запуске mysql из консоли сразу указываем название параметра (переменной) и её новое значение. Либо тоже самое можно сделать уже в самом MySQL Monitor для текущего дескриптора так:
SET max_allowed_packet=16M;
или попытаться установить её вообще глобально так:
SET GLOBAL max_allowed_packet=32M;
Кстати посмотреть переменные представляется возможным примерно следующим образом:
SHOW VARIABLES LIKE 'max_allowed_packet'
Разбивка файла дамба на части
Решил рассказать и об этом способе как о самом экстренном и крайнем. Ведь одно дело архивировать дамп базы, но ведь это не всегда спасает и порой просто уже ничего не остаётся как разбивать большой файл базы данных на части. Естественно это делать надо правильно, что бы команды SQL не обрезались не законченными. Для разбивки текстовых файлов можно использовать к примеру программу TextFileSplitter. Я помню разбивал файл по таблицам, т.е. к качестве разделителя указывал программе литерал «CREATE TABLE». В общем было всякое, надеюсь у вас до такого не дойдёт.
Общие проблемы экспорта и импорта баз данных
В своей практике так же встречал совсем несуразные проблемы в процессе работы. Бывает экспортируешь базу в файл к примеру через Sypex Dumper и на другом сервере через этот же скрипт пытаешь произвести импорт и в процессе получаешь различные ошибки связанные с кокой либо отдельной таблице в базе. Тут сразу стоит обратить внимание на проблемный участок, т.е. попробовать произвести импорт бекапа без таблицы вызывающей проблемы. Связанно это с тем, что таблицы бывают «битые». Поэтому всегда перед экспортом базы данных проверяйте её полностью на испорченность, делается это простыми SQL запросами или же скриптами наподобие phpMyAdmin или тот же Sypex Dumper, и в случае чего обязательно восстанавливайте повреждённые таблицы. Бывает, что именно это становится камнем преткновения, поэтому всегда бекапьте полностью чистые не повреждённые базы, что бы патом не было проблем с их «поднятием».
Так же рекомендую всегда проверять сколько таблиц в файле бекапа, т.к. такие утилиты как phpMyAdmin зачастую грешат на этот счёт, т.е. отдают вам файл казалось бы полной файл базы (во всяком случае вы так заказывали), а на деле в нём лишь часть таблиц. Лучше использовать для этого более продвинутые средства, например всё тот же Sypex Dumper.
В общем, подводя итог, скажу, что способов импортировать файл базы данных MySQL достаточно. Думаю после прочтения данного поста у вас с этим не возникнет проблем.