SQL-Ex blog

Что такое mysqldump, и как это работает?

На верхнем уровне mysqldump является утилитой резервного копирования базы данных на основе интерфейса командной строки, которая широко используется для получения логического резервирования. Логический бэкап создает "логическую копию" базы данных путем создания операторов SQL, которые могут использоваться для воссоздания структуры базы данных, индексов, секций и данных таблиц в пределах базы данных.

Утилиту можно вызвать, напечатав mysqldump в командной строке после авторизации на MySQL, и в большинстве случаев использование инструмента выглядит примерно так (все печатается на одной строке).

mysqldump -u[имя_пользователя] -p[пароль]
[одна_или_более_из_имеющихся_опций]
[имя_базы_данных] [имя_таблицы] > output.sql

Здесь

• имя_пользователя - указывается имя пользователя, который имеет доступ к созданию бэкапов.


• пароль - пароль этого пользователя


• одна_или_более_из_имеющихся_опций - позволяет определить опции, необходимые для специфического дампа базы данных (мы обсудим их несколько позже).

Вы можете указать также имя_базы_данных, из которой вы хотите получить дамп данных. Для получения дампа только одной таблицы, вы можете также указать имя_таблицы в этой базе данных.

Команда mysqldump обычно заканчивается заданием файла для вывода - стрелка направлена вправо (">") с именем файла после нее, что говорит MySQL, куда записать вывод команды (файл может иметь любое допустимое имя). Без этого mysqldump будет печатать все результаты на экране. Можно также направить стрелку влево для импорта своих дампов баз данных в MySQL, используя подобную команду:

mysql -uroot -p db_name < backup.sql

Помните, что, как и для большинства инструментов командной строки, не рекомендуется при использовании mysql или mysqldump передавать пароль простым текстом. Для этого есть много оснований, но самым главным является то, что другой человек может залогиниться и увидеть последние введенные команды, содержащие имя пользователя и пароль. Лучше всего включить имя пользователя и пароль в файл my.cnf, чтобы избежать передачи пароля в командной строке, выполнив что-то подобное (выбирайте сильный пароль, как в примере):

[mysqldump]
user=secret
password=useastrongpasswordhere

Тогда наш запрос выглядел бы также, но без опции -p[password], поскольку пароль теперь предоставляется файлом my.cnf, как и имя пользователя, поэтому вы можете не использовать и опцию -u[username].

Обычно вывод mysqldump осуществляется в файл с расширением .sql. Если имя файла указано, MySQL создает указанный файл, но если нет, то имя файла будет составлено из имени базы данных или таблицы, которые резервируются (следующая команда сделает резервную копию всех таблиц в базе данных demo_database в файл с именем demodb_backup.sql):

mysqldump -uroot -p demo_database > demodb_backup.sql

Опции команды mysqldump

Теперь, когда мы выяснили основы работы mysqldump, давайте рассмотрим опции mysqldump. Некоторые из широко используемых опций представлены ниже (все опции должны указываться после имени пользователя и пароля в позиции [одна_или_более_из_имеющихся_опций]). Все опции могут также сочетаться с другими:



Эта таблица должна позволить вам правильно использовать мощь mysqldump. Только помните, что в ней представлен не полный список доступных опций (все опции можно найти в документации MySQL; но он может послужить хорошей отправной точкой для понимания основных действий mysqldump.

Случаи использования mysqldump

Помимо простых бэкапов, mysqldump может также помочь, когда мы имеем дело с «пограничными» случаями использования. Например, этот инструмент может обеспечить вывод с разделителем-табулятором, который может быть исключительно полезен, если мы хотим работать с данными в просмотрщиках больших текстов типа EmEditor или при использовании Microsoft Excel: мы просто указываем опцию --tab среди других опций, и mysqldump сделает всю остальную работу. Если нам нужно проверить, что определения объектов базы данных загружены в базу данных надлежащим образом, все, что мы должны сделать, это дамп наших данных при использовании комбинации четырех опций, указанных одна за другой:

• Опция --all-databases сделает дамп всех баз данных.


• Опция --no-data воспрепятствует копированию данных.


• Опция --routines сделает дамп процедур и функций в базе данных.


• Опция --events сделает дамп существующих событий, выполняемых по расписанию.

Когда мы используем mysqldump таким образом, будет создан файл дампа (резервная копия), но без данных. Однако загрузка файла обратно на сервер баз данных позволит нам протестировать любые странные предупреждения или выявить другие примечательные ошибки.

mysqldump может быть также использован вместе с другими клиентскими программами, как например, mysql, mysqlimport или в ручном режиме, используя LOAD DATA INFILE: все, что мы должны сделать, это иметь под рукой файл, подготовленный mysqldump, а затем ввести подобный оператор (следующий оператор будет импортировать файл с именем demo.txt в базу данных с именем database_name):

mysqlimport --fields-terminated-by=: --lines-terminated-by=\n
database_name demo.txt

Мы можем таким же образом использовать LOAD DATA INFILE: если наши данные разделяются, скажем, знаком ",", мы могли бы использовать подобный запрос, и загрузить данные, имеющиеся в demo.txt в таблицу с именем demo_table:

LOAD DATA INFILE 'demo.txt' INTO TABLE demo_table FIELDS
TERMINATED BY ',';

Комбинирование опций в mysqldump

Давайте вернемся немного назад. Повсюду в статье упоминалось, что другие опции могут использоваться вместе с mysqldump для выполнения сложных действий с помощью этого инструмента. Возможно, для кого-то погружение в mysqldump окажется немного сложным, но потерпите, и мы объясним все, что вам нужно знать.

Во-первых, имейте в виду, что все компоненты MySQL (включая не только относящиеся к mysqldump) обычно имеют пару дополнительных опций, которые можно указать, и эти опции меняются в зависимости от компонента, с которым они работают. Комбинировать опции просто:

1. Выберите компонент, с которым вам нужно работать (в нашем случае обращаемся к mysqldump).


2. Обратитесь к документации, чтобы ознакомиться с имеющимися опциями, относящимися к этому компоненту (в документации можно найти полный список опций для выбранного компонента - опции для mysqldump можно найти здесь.


3. Оцените возникшую у вас проблему. Как выбранная опция поможет решению этой проблемы. Будут ли результаты удовлетворительными?


4. Выберите одну или несколько опций из имеющихся.

При выборе опций помните, что вывод любой выполняемой команды непосредственно зависит от дополнительных опций. Другими словами, любая указанная опция изменяет функциональность выбранной вами команды. Все опции необходимо указывать, записывая их имя как часть команды после “–“, например, так:

mysqldump --no-data

Этот оператор приведет к выполнению mysqldump совместно с командой “no-data” (как объяснялось выше, эта команда не будет делать дамп данных, а сделает только дамп структуры базы данных). Комбинация с --add-drop-table скажет MySQL обеспечить отсутствие таблиц с совпадающими именами перед созданием любых других таблиц. Кто-то может захотеть задать абсолютный путь к файлу, чтобы гарантировать сохранение файла в выбранном каталоге (это может стать необходимым, если имеется опция –secure-file-priv: эта опция ограничивает возможность пользователей экспортировать или импортировать данные в/из базы данных - в этом случае данные могут экспортироваться только в каталог, указанный в переменной - подробней об этом можно почитать здесь).

Однако, возвращаясь к mysqldump, скажем, что один из наиболее часто используемых сценариев состоит в создании дампа всех баз данных с включением оператора DROP TABLE перед любой создаваемой таблицей - этим мы гарантируем, что даже если бэкап загружается в базу данных, которая имеет таблицы с теми же именами, они будут удалены и созданы заново:

mysqldump -uroot --all-databases --add-drop-table > backup.sql

Как мы выяснили, mysqldump будет делать дамп данных внутри MySQL, но специфика как это будет делаться непосредственно зависит от опций, указанных при его выполнении. Все опции имеют конкретное использование, и, когда мы комбинируем выбранные опции с командой, то должны держать в голове тот факт, что это влияет на вывод, который мы получаем.

Полный список опций, относящихся исключительно к mysqldump, можно найти в документации. Когда вы создаете дамп, тестируйте его: загрузите его в ваш экземпляр базы данных MySQL с помощью phpMyAdmin или просто указав имя файла в CLI, например, так (заметим, что база данных должна быть создана до импорта в нее данных):

mysql demo < backup.sql

Эта команда будет импортировать бэкап в вашу базу данных с именем demo. Следует заметить, что эта команда предполагает, что имя пользователя и пароль указаны в файле my.cnf, как мы это делали для mysqlbackup в примере выше. Если имя пользователя и пароль не заданы в вашем файле my.cnf, вам придется предоставить имя пользователя и пароль, как в примере ниже.



Затем проверьте свою базу - присутствуют ли там ожидаемые данные?

Читайте документацию, но ничто не сравнится с вашим собственным опытом — поэкспериментируйте с mysqldump на локальном сервере, и учитесь на ошибках, допущенных другими разработчиками в прошлом.