MySQL. Различные способы резервного копирования баз данных и таблиц

MySQL используется для хранения критически-важной информации компаниями по всему миру. Рассмотрим различные способы резервного копирования и восстановления

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

Типы резервного копирования в MySQL

В MySQL существуют разные типы резервного копирования.

Выбор стратегии резервного копирования зависит от нескольких факторов, например, от размера данных, используемого оборудования, желаемой производительности, объема хранилища базы данных и т. д.

Кроме того, следует учитывать, сколько времени потребуется для восстановить резервную копию.

MySQL поддерживает следующие типы резервного копирования:

• Логическое резервное копирование выводит структуру базы данных в файл .sql путем создания операторов CREATE или INSERT. Позже этот файл можно восстановить с помощью утилиты mysqldump . Этот тип резервирует только данные без индексов, поэтому имеет небольшой размер. Однако его вариант восстановления медленнее по сравнению с его альтернативами, потому что он должен выполнять все операторы один за другим.
• Физическая резервная копия копирует файлы базы данных в том же формате, в котором они хранятся на диске. Это быстрее, чем логический тип, но его можно восстановить только на сервер MySQL из того же ядра базы данных.
• Непротиворечивое резервное копирование копирует файлы в определенный момент времени — только после того, как сервер перестанет работать или будет заблокирован.

Если во время операции копирования могут возникнуть какие-либо перерывы, резервные копии можно разделить на следующие типы:

• Холодное резервное копирование блокирует доступ к данным во время резервного копирования и не позволяет вносить какие-либо изменения в данные. Это просто, быстро и не влияет на производительность.
• Горячее резервное копирование копирует файлы во время работы базы данных. Пользователи могут читать и манипулировать данными.
• Теплое резервное копирование создает копию базы данных во время ее работы. Пользователи могут читать, но не могут записывать данные во время резервного копирования.

Независимо от того, следует ли копировать данные полностью или частично, резервные копии можно разделить на:

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

Создайте резервную копию базы данных MySQL с помощью команды mysqldump

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

mysqldump — это утилита командной строки, используемая для создания резервной копии логической базы данных MySQL. Он создает один файл .sql, содержащий набор операторов SQL. Этот файл можно использовать для создания таблиц , объектов и/или вставки данных, скопированных из базы данных. С помощью утилиты вы можете сделать дамп таблиц, одной или нескольких баз данных или всего сервера со всеми базами данных и таблицами вместе с их объектами или перенести их на другой сервер.

Кроме того, утилита mysqldump может выводить дамп в файлы форматов CSV или XML. Следует отметить, что mysqldump не может выгружать базу данных или данные в отдельные файлы .sql.

Для восстановления базы данных утилита выполняет все операторы SQL для воссоздания таблиц и заполнения их данными, что, в свою очередь, требует много времени.

Базовый синтаксис  команды mysqldump  включает следующие параметры:

mysqldump -u [user name] –p [password] -h [host name] [options] [database_name] [tablename] > [dumpfilename.sql]

-u (или --user=[username]) — это имя пользователя для подключения к серверу MySQL.
-p это пароль для имени пользователя, которое вы используете для подключения к серверу.
-h (или --host=[hostname]) — это имя сервера, с которого вы хотите подключиться для получения дампа данных.
options — дополнительные параметры для настройки резервного копирования.

database_name — это имя базы данных, резервную копию которой вы хотите создать.

tablename — это имя таблицы, резервную копию которой вы хотите создать.
< или > относится к параметрам, указывающим на процесс резервного копирования (>) или восстановления базы данных (<).

dumpfilename.sql — путь к файлу дампа, содержащему резервную копию базы данных.

Примечание. Как правило,  mysqldump находится в каталоге установки MySQL Server. Например, для MySQL 8.0 путь к каталогу — C:\Program Files\MySQL\MySQL Server 8.0\bin. Таким образом, мы рекомендуем добавить этот каталог в команду mysqldump.

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

В демонстрационных целях мы собираемся подключиться к удаленному серверу dbfmylast . Поэтому мы добавим в mysqldump команду дополнительную информацию, такую ​​как и параметры.--host--port

mysqldump для резервного копирования всех баз данных

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

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

mysqldump --host=dbfmylast --user=root --port=3306 --p --all-databases > D:\backup_all_databases.sql

Это создаст резервную копию всех баз данных с их структурой и данными в файле backup_all_databases.sql .

mysqldump для резервного копирования только данных

Если вы хотите сделать резервную копию данных без структуры базы данных, выполните команду с параметром . Например, дамп данных для базы данных sakila .–no-create-info

mysqldump --host=dbfmylast --user=root --port=3306 -p --no-create-info sakila > D:\backup_data_only.sql

Файл выведет скрипт, содержащий только данные таблицы дампа:

mysqldump для резервного копирования только структуры

Могут быть случаи, когда вам нужно экспортировать базу данных или структуру таблицы без данных. Для этого запустите команду с параметром .-no-data

mysqldump --host=dbfmylast --user=root --port=3306 -p --no-data sakila > D:\backup_database_structure_only.sql

Команда mysqldump выводит в файл только структуру таблицы.

mysqldump для резервного копирования всех таблиц в базе данных

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

Например, выгрузите таблицы категорий , городов и стран , выполнив следующую команду:

mysqldump --host=dbfmylast --user=root --port=3306 -p sakila city country category > D:\backup_selected_tables.sql

Результат выглядит следующим образом:

mysqldump для резервного копирования одной таблицы

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

mysqldump --host=dbfmylast --user=root --port=3306 -p sakila inventory > D:\backup_single_table.sql

MySQL Workbench для резервного копирования базы данных

В этой части мы рассмотрим, как сделать резервную копию базы данных с помощью инструмента MySQL Workbench.

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

1. На панели администрирования нажмите Экспорт данных . Откроется документ Администрирование – Экспорт данных .
2. На вкладке «Выбор объекта » > «Таблицы для экспорта» выберите схему sakila .
3. В разделе «Параметры экспорта» выберите «Экспорт в папку проекта дампа» , если вы хотите, чтобы таблицы базы данных сохранялись в отдельных файлах .sql, или « Экспорт в автономный файл» , чтобы сохранить дамп базы данных в одном файле .sql.
4. Чтобы создать файл резервной копии, нажмите «Начать экспорт» . На индикаторе выполнения вы можете увидеть, что данные были экспортированы в указанный файл.

Примечание. В MySQL Workbench невозможно автоматизировать или запланировать резервное копирование базы данных.

Восстановить базу данных в MySQL из файла

Теперь давайте восстановим базу данных sakila из выходного файла .sql с помощью mysqldump утилиты.

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

mysql --host=dbfmylast --user=root --port=3306 -p sakila < D:\dumps\sakila.sql

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

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

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

mysql> use sakila;
...
mysql> show tables;

Убедитесь, что таблицы были успешно импортированы в базу данных sakila с помощью команды mysql.