Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как mysqldump.
mysqldump - утилита позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
mysqldump -u root -p -f name_database >
Этой командой мы делаем бэкап базы данных под именем name_database на диск С в файл mydb_backup_name_database.txt
Файл можно не создавать, MySQL создаст его сам.
mysql -u root -p -f name_database < C:\mydb_backup_name_database.txt
этой командой мы импортируем данные бэкапа из файла C:\mydb_backup_name_database.txt
Примечание: -f, --force - опция, которая указывает продолжать даже при получении ошибки SQL, т.е. игнорировать ошибки. Например, если в таблице уже существует во всем идентичная строка.
Чтобы пароль не запрашивался, нужно писать его сразу после -p, то есть без пробелов. Если пароль Pwd , то пример выгладит так:
mysqldump -u root -pPwd -f name_database > C:\mydb_backup_name_database.txt
Если вы часто используете данную команду, то лучше сделать отдельного пользователя с необходимыми правами, дабы поменьше светить пароль root-а
Рассмотрим более тонкие настройки mysqldump:
--databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы "с нуля". То есть, без использования --databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;
--all-databases позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);
Ключ --help . Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа;
--add-drop-table - ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;
--no-data . С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;
--result-file=... - этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой ">", а можно - вот этот ключ. Кому что нравится;
Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка - mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы "заставить" mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ --quick . Это решит проблему.
Приведем еще пару полезных примеров:
mysqldump -u root -pPwd -f --default-character-set=cp1251 DBNAME | gzip -c > filename.txt .gz
распаковывать такой архив можно командой:
gunzip filename.txt .gz
Чтобы знать, за какое число был сделан бэкап, можно написать такую команду:
mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date "+%Y-%m-%d"`.gz
а если приходится делать дампы с разными кодировками, то удобно пользоваться переменными:
set DBCHARACTER = utf8
set DBNAME = breach
mysqldump -u root -pPwd -f --default-character-set=$DBCHARACTER $DBNAME | bzip2 -c > sql.$DBNAME .`date "+%Y-%m-%d"`.bz2
Если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически
find ~/каталог-с-архивами -name "*.gz" -mtime +7 -exec rm -f {} \;
Тем самым Вы будете удалять архивы, которые "старше" семи дней.
Дамп базы MySQL знаком каждому веб-мастеру. Это и неудивительно, ведь эта процедура выполняется в рамках резервного копирования или переноса базы данных на другой хостинг.
Следовательно, умение делать дамп базы данных MySQL немаловажно для любого веб-разработчика. Что это такое? Это файл, содержащий инструкции на языке SQL, за счет которых создается точная копия вашей БД как по содержанию, так и по структуре. Для каких ситуаций вам стоит сделать дамп базы MySQL:
- Перенос БД на другой сервер
Дамп может понадобиться, если вы запланировали перенос базы данных на другой хостинг. С его помощью вы сможете без каких-либо трудностей воссоздать точную копию старой базы данных.
- Резервное копирование базы данных
Виртуальный хостинг сайтов , на котором включено ежедневное резервное копирование. Регулярное резервное копирование данных сэкономит вам немало сил и нервов в случае непредвиденных ситуаций. Неудачный эксперимент с сайтом или крах сервера способен привести к потере всей базы, и лишь вовремя сделанный дамп поможет вам в этой ситуации. Естественно, при этом его нельзя хранить на одном сервере с основной базой, ведь в противном случае вы потеряете всю информацию без возможности ее восстановления.
Как сделать дамп базы MySQL?
Существуют различные способы создания дампа, и далее мы рассмотрим основные варианты:
- Делаем дамп при помощи консоли MySQL
В этом случае для создания дампа БД используется командная строка или консоль MySQL, где нужно ввести команду mysqldump -uuser -ppass db_name > file_to_save. При этом user - это имя пользователя БД с достаточными правами для создания дампа, pass - пароль от базы данных, db_name - имя нужной БД, а вместо file_to_save необходимо указать имя файла, куда будет сохраняться дамп.
После правильно введенной команды в указанном месте появится файл с расширением.sql, который и является дампом базы данных. Этот способ наиболее универсален и популярен среди пользователей Unix-систем, например, Ubuntu, если вдруг потребуется перенос MySQL на другой сервер. В том же случае, если вы не знаете консольных команд, вам потребуется дополнительное программное обеспечение.
- Делаем дамп базы данных при помощи phpMyAdmin
Если вы предпочитаете использовать дополнительное программное обеспечение, то можно сделать дамп базы данных средствами PHP при помощи, например, phpMyAdmin. Для этого выполните следующие действия:
- Войдите в phpMyAdmin.
- Выберите нужную базу данных из общего списка.
- Авторизуйтесь в выбранной БД.
- После авторизации в левой колонке будет сама база данных и служебная информация, которая к ней относится. Теперь вам нужно повторно выбрать вашу БД.
- Перейдите во вкладку "Экспорт", после чего включите некоторые настройки, а именно:
Добавить DROP TABLE/VIEW/PROCEDURE/FUNCTION/EVENT
Данная опция позволит вам в дальнейшем без опаски восстановить базу MySQL из дампа. Все таблицы, имеющие одинаковые имена, сначала будут удалены, то есть исполнится команда dpop, а затем заменятся на аналогичные из вашего дампа. В итоге вы обезопасите себя от создания дублей таблиц и как следствие, неправильной работы базы данных.
Упаковать zip
Включение этой опции обеспечит архивацию вашего дампа БД, и в итоге вы получите файл с расширением.zip. Это позволит вам быстро и корректно сохранить дамп даже при невысокой скорости соединения.
- Получаете готовый к использованию дамп БД. Если все было правильно сделано, то у вас будет архив, внутри которого находится файл с расширением.sql - дампом нужной вам базы данных.
Теперь вы знаете, как сделать дамп MySQL, если вам вдруг потребуется перенос базы на другой сервер или возникнет любая другая ситуация, когда может пригодиться резервная копия БД. Конечно, существует еще достаточно много способов создания дампа, помимо описанных выше. Но даже владение самыми простыми вариантами и регулярное применение их на практике позволит вам более уверенно чувствовать себя в любой непредвиденной ситуации, ведь достаточно будет просто развернуть дамп MySQL и быстро восстановить все базы данных.
Если вы решились купить dedicated server в нашей компании, то Вам выдается 100 ГБ на удаленном сервере бекапов, куда можно настроить автоматическое резервное копирование сайтов и баз данных. В таком случае, у вас всегда будет свежий дамп MySQL.
В том случае, когда у вас все же возникают сложности при создании дампа и смене сервера или хостинга, вы можете обратиться к сотрудникам техподдержки RigWEB, и они ответят на любые ваши вопросы в рамках своей компетенции. Если же вы по каким-то причинам не можете заниматься этим самостоятельно, наши специалисты бесплатно займутся переносом вашего сайта с другого хостинга на наш. Также мы обеспечим регулярное резервное копирование и своевременное предоставление актуальных копий ваших данных при необходимости. Пользуясь профессиональным хостингом RigWEB, вы можете быть уверены в максимальной защите своего проекта от любых неожиданностей!
Наверяка вам не раз приходилось импортировать дампы базы данных. И не смотря на всю тривиальность этой задачи, часто возникают вопросы, а иногда и проблемы связанные с этим. Ниже приведен весь код, который необходим, чтобы быстро применить дамп базы из командной строки.
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 по умолчанию изменяет значения переменных только в контексте текущей сессии.
Большую часть времени, вы можете легко управлять своей базы данных с веб-инструментов, например через PHPMyAdmin. К сожалению, бывают случаи, когда необходимо восстановить большое количество данных. Это не может быть достигнуто через PHPMyAdmin из-за пределов РНР (либо максимальное время работы скрипта не позволяет или невозможно загрузить большой архив с БД).
Использование MySQL с командной строки
Если вы используете MySQL на Linux или MS Windows, вы можете использовать командную строку для выполнения большинства задач. Если вы используете MS Windows, убедитесь, что путь к исполняемым файлам командной строки входит в системном пути. В противном случае, вам придется ввести полный путь к каждой команды.
Как сделать резервную копию базы данных MySQL
MySQL предоставляет утилиту под названием «mysqldump.» В принципе, этот инструмент создает плоский файл, содержащий инструкции MySQL, чтобы восстановить базу данных. Вот несколько примеров использования mysqldump:
Создание простого резервной копии базы данных
# mysqldump -u username -p DB_name > your_file.sqlЭто создает файл, содержащий все операторы MySQL для создания таблиц и восстановление данных в существующую базу данных. Если целевая база данных содержит таблицы с одинаковыми именами, они будут перезаписаны. Если вы хотите существующие таблицы, которые будут отброшены и заново использованы, то используйте опцию » add-drop-table»:
# mysqldump --add-drop-table -u username -p DB_name > your_file.sql
Кроме того, вы можете выбрать это в качестве альтернативы:
# mysqldump --add-drop-databases -u username -p DB_name > your_file.sql
Резервное копирование нескольких баз данных
Вы можете сделать копию нескольких баз данных в один файл, используя опцию базы данных:
# mysqldump -u username -p --databases your_database1 your_database2 your_database3 > your_file.sql
Создание резервной копии всех баз данных может быть достигнуто с помощью опции все-базы данных:
# mysqldump -u username -p --all-databases > your_file.sql
Резервное копирование таблиц InnoDB
Если в базе данных есть столбцы InnoDB, вам нужно будет отключить ссылочную целостность при восстановлении данных. К сожалению, это не может быть сделано с помощью утилиты mysqldump. Для этого нужно открыть сам файл и добавить:
SET FOREIGN_KEY_CHECKS = 0;
… И добавить следующее в конец файла:
SET FOREIGN_KEY_CHECKS = 1;
Сжатие MySQL Dump файлов
Если вы используете mysqldump в оболочке Linux, вы можете сжать через Gzip, чтобы сжать файл дампа (при условии, что вы установили gzip):
# mysqldump -u username -p your_database_name | gzip -c your_file.sql.gz
Восстановление из резервной копии MySQL
Чтобы восстановить резервную копию, созданную с mysqldump, вам нужно будет использовать команду MySQL. Если ваш файл дампа MySQL не содержит «Создать базу данных» о, вы можете использовать следующую команду:
# mysql -u username -p your_database_name < your_file.sql
Но, если это выполнится, использовать ту же команду без указания имени базы данных:
# mysql -u username -p < your_file.sql
Более подробную информацию о mysqldumpможно найти на официальном сайте MySQL.
Странно, но в интернете найти эту информацию довольно непросто. Куча левых всяких рекомендаций. А на самом деле всё просто. Восстановить дамп можно командой:
# mysql -u -p < mysql_dump.sql.
Вместо подставьте имя пользователя, я лично восстановление делаю от рута. А в mysql_dump,sql, собственно, дамп сделанный mysqldump утилитой. Опция -p указывает на авторизацию с паролем, после нажатия ентера у вас спросят пароль. Вот и всё, собственно. Сделать дамп можно командой:
# mysqldump -u root -p -f database_name > file_with_dump.sql
Здесь параметр -f database_name задаёт имя базы, которую сохраняем. Если надо всё — заменяем на —all-databases. Эта опция укажет сохранять все базы на сервере. Чтобы не вводить каждый раз пароль, можно указать его прямо в команде (это надо, если вы делаете дамп по крону из скрипта), впрочем, с точки зрения безопасности тут возникают вопросы, особенно если пароль рутовый для мускуля. Вот если бы мускуль понимал хотя бы md5 пароли… Впрочем не факт, что не понимает, просто может я не знаю:)
Автор
Алекс Разгибалов
Сумасшедший мужчина, неопределённого возраста, наслаждающийся манией преследования. Паталогически недоверчив, эгоистичен, авторитарен. Вторичные диагнозы - программист и поц. Владеет английским языком на уровне около хренового разговорного. Также знаком с некоторыми другими языками. Интересуется всем и вся, за счёт чего в любой области знания являются поверхностными, неглубокими. Характер невыдержанный. Крепость - 55 градусов.
