Наверяка вам не раз приходилось импортировать дампы базы данных. И не смотря на всю тривиальность этой задачи, часто возникают вопросы, а иногда и проблемы связанные с этим. Ниже приведен весь код, который необходим, чтобы быстро применить дамп базы из командной строки.
SET NAMES utf8; DROP DATABASE `mydb`; CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8; USE `mydb`; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET autocommit=0; SET foreign_key_checks=0; SET unique_checks=0; SOURCE /path/to/db_dump.sql; COMMIT; SET autocommit=1; SET foreign_key_checks=1; SET unique_checks=1;
Эти команды позволят вам быстро импортировать дамп базы данных и избежать проблем. Причем некоторые из этих проблем не решают даже распространенные mysql-клиенты, такие как phpMyAdmin и другие.
Попробуем разобрать данный код по порядку.
SET NAMES utf8; говорит, что обмен данными будет происходить в кодировке UTF-8 и именно в ней находится исходный файл дампа базы и именно её будет иметь конечная база данных.
DROP DATABASE `mydb`; и CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8; выполняют уничтожение базы данных и её последующее создание с кодировкой по умолчанию UTF-8. Понятное дело, что это сотрет все таблицы и данные в них, но это и даст нам уверенность в том, что полученная БД будет полностью соответствовать данным в файле дампа.
USE `mydb`; указывает, что надо использовать именно эту базу данных для всех команд из файла дампа (проверьте файл дампа на наличие этой директивы с указанием другой базы, дабы избежать конфуза).
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; отключает увеличение счетчика авто-инкремента при записи в поле авто-инкремента значения 0. По умолчанию, при записи в такое поле значения NULL или 0 оно автоматически инкрементируется. И хотя иметь значение 0 в авто-инкрементном поле – не лучшая практика, иногда все-таки случается что в дампе есть такие строки (я встретился с подобным в дампе от magento). Стоит также заменить, что mysqldump с некоторых пор включает данную директиву по умолчанию в своих дампах.
SET autocommit=0; перед заливкой дампа и COMMIT; после него - ускоряют выполнение INSERT'ов для InnoDB таблиц, поскольку при включенном режиме авто-коммита логи сохраняются на диск при каждом INSERT'е. Может помочь при накатывании больших дампов.
SET foreign_key_checks=0; наиболее очевидная опция, без которой большая часть дампов вообще не могла бы быть применена. Отключает проверки внешних ключей, что позволяет записывать данные в любом порядке и в том числе с циклическими ссылками по внешним ключам. Эта опция также ускоряет импорт дампа.
SET unique_checks=0; отключает проверки на уникальность для вторичных ключей, что опять таки может ускорить процесс импорта.
Ну и наконец, основная команда, ради которой все это было задумано – SOURCE /path/to/db_dump.sql; непосредственно импортирует дамп базы mysql из указанного файла. Путь до файла может быть абсолютными, либо относительным от той директории, откуда вы зашли в консоль mysql.
А после выполнения импорта – восстанавливаем значение флагов, хотя этого можно и не делать, ибо команда SET в mysql по умолчанию изменяет значения переменных только в контексте текущей сессии.
Стоит отметить, что подход с накатыванием дампа базы может пригодиться для быстрого развертывания слепка базы в development среде для работы над ошибками, однако не стоит использовать его в разработке - используйте механизм миграций БД!
Существуют open-source реализации данного механизма для различных языков программирования, в том числе и в составе популярных фреймворков, таких как Yii или Ruby on Rails. Так, например, для PHP легко можно найти следующие библиотеки, которые можно применить при работе с унаследованным кодом:
Кстати, самый простой способ сделать дамп базы - это выполнить команду
mysqldump --user=user --password --add-drop-database --compact --create-options --force --routines --triggers --databases dbname > backup_YYYYMMDD.sql