Дамп базы данных MySQL: что это такое и как его сделать

Как сохранить базу данных MySQL

MySQL – это служба баз данных для хранения любых типов данных. Он предлагает различные варианты резервного копирования, которые можно выбрать с учетом размера данных, технических характеристик оборудования или объема хранилища.

MySQL предоставляет:

• Логическое резервное копирование, выполняемое с помощью .sql-файла и операторов create/insert. Позже вы можете восстановить базу данных из этого файла. Этот тип резервного копирования считается более медленным, чем другие, и подходит для баз данных среднего размера.
• Физическое резервное копирование выполняется путем копирования файлов в оригинальном формате. Недостатком этого типа резервного копирования является то, что база данных может быть восстановлена только на сервере MySQL.
• Последовательное резервное копирование, выполняется путем автоматического регулярного резервного копирования файлов.

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

Частью пакета базы данных relation является mysqldump, представляющий собой утилиту командной строки для резервного копирования баз данных MySQL. С ее помощью можно воссоздать данные в одном файле, содержащем SQL-операторы, а затем использовать для воссоздания данных в исходном формате. С помощью этой команды можно создать дамп таблиц, нескольких баз данных или всех данных сервера для последующего переноса на другой сервер.

В целом, использовать команду несложно. Однако многие пользователи сталкиваются с трудностями при восстановлении базы данных из файла резервной копии с помощью команд SQL. Процесс восстановления базы данных может занять некоторое время, если у вас большая база данных, поскольку mysqldump выполняет все операторы SQL для вставки данных.

Следует помнить, что mysqldump не может создавать дамп некоторых таблиц, включая временные таблицы, системные таблицы, таблицы схемы производительности (из базы данных Performance Schema) и таблицы информационной схемы (из базы данных Information Schema). Однако команда предлагает возможности настройки: вы можете исключить определенные типы таблиц или данных из дампа с помощью команды --ignore-table или использовать опцию --no-data, чтобы сохранить структуру таблицы, избавившись от данных.

Какие задачи выполняет Mysqldump?

Утилита Mysqldump облегчает работу с:

• Резервным копирование баз данных;
• Миграцией базы данных;
• Версионированием базы данных (вы можете создавать различные версии базы данных, к которым вы можете вернуться позже);
• Репликацией среды (эта команда может помочь реплицировать производственные данные, чтобы создать стабильную среду для разработки или тестирования);

Структура экспортируемых данных

Экспортированные данные будут состоять из:

• Сжатых файлов в форматах gz (gzip) или .bz2 (bzip2);
• SQL-заявлений в виде SQL-скрипта, используемого для воссоздания структуры и данных базы данных;
• Данных в формате XML для облегчения интеграции данных с другими системами;
• Текстовых файлов с разделителями табуляции, используемые для импорта и экспорта данных;
• Файлов с разделителями-запятыми или CSV, используемые для обмена данными;
• Пользовательских форматов, что используются для настройки выходного формата;

Privileges for Mysqldump Usage

To start using mysqldump command, a minimum of full read access privileges is required. To use the extended functionality, the following privileges are needed:

• SHOW VIEW (for accessing view definitions);
• SELECT (for reading data);
• LOCK TABLES (for table locking);
• RELOAD (for using --flush-privileges option);

Привилегии для использования Mysqldump

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

• SHOW VIEW (для доступа к определениям представлений);
• SELECT (для чтения данных);
• LOCK TABLES (для блокировки таблиц);
• RELOAD (для использования опции --flush-privileges);

​Примеры дампа MySQL

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

Резервное копирование определенных таблиц MySQL

Чтобы создать резервную копию определенных таблиц из базы данных MySQL, выполните следующую команду:

mysqldump -u my_user -p database_name table1 table2 > tables.sql

Здесь:

• my_user — имя пользователя MySQL;
• -p — пароль пользователя MySQL;
• database_name — имя базы данных, содержащей необходимые таблицы;
• table1 table2 — имена таблиц, которые вы хотите резервировать;

Если вы хотите создать резервную копию большего количества таблиц, вы можете использовать тот же принцип и добавить имена баз данных, которые вы хотите дополнительно резервировать.

После завершения работы файл "tables.sql", созданный mysqldump, будет содержать SQL-команды, необходимые для воссоздания таблиц и данных в них.

Резервное копирование одной базы данных

Если вы хотите создать резервную копию одной базы данных, воспользуйтесь следующей командой:

mysqldump -u my_user -p database_name > backup.sql

Здесь:

• my_user – имя пользователя MySQL;
• -p – пароль пользователя MySQL;
• database_name – имя базы данных, которую вы хотите резервировать;

После выполнения команды файл "backup.sql" будет содержать данные, необходимые для восстановления базы данных в исходном виде.

Резервное копирование нескольких баз данных

Для резервного копирования нескольких баз данных необходимо добавить флаг --databases к команде mysqldump, как показано ниже:

mysqldump -u my_user -p --databases db1 db2 db3 > backup.sql

Здесь:

• my_user – имя пользователя MySQL;
• -p – пароль пользователя MySQL;
• db1 db2 db3 – имена баз данных, которые вы хотите резервировать;

Резервное копирование всех баз данных с сервера MySQL

Если вы хотите создать резервную копию всех баз данных с сервера MySQL, вам нужно добавить флаг --all-databases при использовании команды mysqldump:

mysqldump -u my_user -p --all-databases > all_databases.sql

Итоговый файл all_databases.sql будет содержать необходимые SQL-команды для воссоздания резервных копий баз данных.

Как восстановить базу данных на удаленном сервере

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

Для этого можно проверить, есть ли на сервере база данных, или создать ее, если ее нет:

mysql -u root -pYOUR_PASSWORD -e "CREATE DATABASE destination_db

Эта команда создаст базу данных на новом сервере.

Затем вы сможете легко восстановить таблицы или базы данных из созданных резервных копий. На примере одной базы данных вы можете использовать следующую команду для восстановления базы данных из резервных файлов:

mysql -u root -pYOUR_PASSWORD destination_db < single_table_dump.sql

Заключение

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

FAQ

На что влияет флаг --single-transaction в mysqldump?

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

Какие методы можно использовать для эффективного резервного копирования больших таблиц?

Вы можете комбинировать --single-transaction и -quick для более быстрого создания дампа больших баз данных.

Такой подход наиболее подходит для таблиц InnoDB, поскольку он использует меньше оперативной памяти и обеспечивает согласованность процесса дампа.

Как можно игнорировать таблицы в процессе mysqldump?

Вы можете игнорировать определенные таблицы с помощью опции --ignore-table. Она может выглядеть примерно так:

mysqldump -u root -pmypassword my_db --ignore-table=my_db.table_to_ignore > my_db.sql

Таким образом, таблицы, которые вы укажете игнорировать, будут уведомлены с помощью --ignore-table=DATABASE_NAME.TABLE_TO_IGNORE.

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

mysqldump -u root -pmypassword –-ignore-table="my_db.table1" –-ignore-table="my_db.table2" –-ignore-table="my_db.table3" > all_databases.sql

Какой подход следует использовать для дампа бинарных BLOB-данных с помощью mysqldump?

Если база данных содержит двоичные данные, вы можете столкнуться с некоторыми проблемами. Если вам нужно сделать дамп базы данных MySQL, содержащей бинарные данные, используйте флаг --hex-blob. Эта опция сортирует двоичные строки и помещает их в различимый формат. Вот как вы хотите сделать дамп бинарных данных:

mysqldump -u root -pmypassword my_bin_db --hex-blob > my_bin_db.sql

Можно ли использовать предложение "where" в mysqldump?

Вы можете использовать предложение “where” с mysqldump, чтобы упростить сортировку данных, которые вы хотите сохранить в базе данных. Предложение “where” инициирует строку условий и берет данные, соответствующие критериям:

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2017-04-27'" > wpdb_myrecord.sql