Главная » Гайд

Резервное копирование MySQL с помощью MySQL dump

На чтение 9 мин. Просмотров 88 Опубликовано

MySQL dump можно использовать для создания резервной копии базы данных. Данные переносятся из внутренней древовидной структуры MySQL в текстовый формат. Создание резервной копии MySQL является стандартной задачей для веб- и системных администраторов. Мы объясним, что такое дамп MySQL и как его создать.

Содержание
  1. Что такое резервная копия MySQL и как она создается?
  2. Что означает «дамп MySQL»?
  3. MySQL dump как файл экспорта базы данных MySQL
  4. MySQL dump как инструмент для создания экспорта базы данных
  5. Пошаговое руководство по созданию дампа MySQL
  6. Ввод команды mysqldump в командной строке
  7. Использование файла конфигурации дампа базы данных MySQL
  8. Использование скрипта резервного копирования MySQL
  9. Импорт дампа MySQL в базу данных

Что такое резервная копия MySQL и как она создается?

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

Примечание

На вопрос «Что такое резервное копирование?» мы отвечаем в Цифровом руководстве.

Дамп SQL — это обычный текстовый файл, содержащий SQL-запросы. Существует несколько способов создания резервной копии MySQL. Вы можете использовать интерфейс администрирования phpMyAdmin или создать резервную копию с помощью MySQLDumper. Мы рассмотрим создание дампа MySQL с помощью инструмента командной строки mysqldump.

Совет

MySQL является наиболее широко используемой системой управления реляционными базами данных. Postgre — мощный конкурент, который становится все более популярным. О том, как создать резервную копию PostgreSQL, мы рассказываем в другой статье.

Что означает «дамп MySQL»?

Термин «дамп MySQL» имеет два значения:

  1. Файл экспорта базы данных MySQL.
  2. Инструмент командной строки для создания экспорта базы данных.

Давайте рассмотрим эти два термина подробнее.

MySQL dump как файл экспорта базы данных MySQL

База данных MySQL организует данные в оптимизированных структурах данных. Для общих данных используется B-дерево, а географические данные хранятся в R-дереве. «Дамп» данных — это процесс переноса данных из этих специальных древовидных структур в линейное представление, известное как «дамп».

При «дампе» данных дерева создается текстовый файл, который можно хранить и перемещать между системами обычными методами. Это необходимо для создания резервной копии базы данных MySQL. Итак, что именно содержится в дампе MySQL?

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

Полный процесс от создания резервной копии MySQL до импорта дампа приводит к следующей схеме:

mysql_import(mysql_dump(database)) == database

Возможно, вы уже открывали дамп MySQL в редакторе кода или обычного текста. Содержимое дампа MySQL поначалу может показаться довольно загадочным. Ниже приведен обзор наиболее часто встречающихся SQL-запросов в дампе MySQL:

Утверждение в дампе MySQL Объяснение
DROP TABLE IF EXISTS Удалить существующую таблицу во время импорта
СОЗДАТЬ ТАБЛИЦУ Создать новую таблицу во время импорта
ВСТАВИТЬ В … VALUES Заполнение столбцов значениями во время импорта

MySQL dump как инструмент для создания экспорта базы данных

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

which mysqldump

Команда вернет сообщение об ошибке, если инструмент не установлен.

Используйте следующую команду для отображения версии установки mysqldump:

mysqldump --version

Так можно получить помощь от команды mysqldump:

mysqldump --help

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

Совет

Если ваша установка MySQL запущена в контейнере Docker, вы можете использовать MySQL dump внутри контейнера для создания резервной копии MySQL. Также можно сохранить весь контейнер в качестве резервной копии Docker.

Пошаговое руководство по созданию дампа MySQL

Создание резервной копии MySQL с помощью инструмента mysqldump происходит в командной строке. Сначала необходимо войти на сервер базы данных или в контейнер Docker через SSH. Для создания резервной копии MySQL необходимы как минимум три части информации.

  1. Имя экспортируемой базы данных
  2. Имя пользователя базы данных
  3. Пароль пользователя базы данных.

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

Ввод команды mysqldump в командной строке

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

mysqldump --user=<username> --password=<password> <database> > <path/to/mysql_dump.sql>

Типичная команда mysqldump состоит из следующих шести компонентов:

  1. команда mysqldump
  2. Пользователь базы данных
  3. Пароль базы данных
  4. Имя базы данных
  5. Перенаправление вывода
  6. Путь к выходному файлу

Команда mysqldump может быть написана по-разному в зависимости от источника. Вместо описательных имен, таких как «—user», могут использоваться более короткие версии. Для пояснения мы приводим схему ниже. Следующий пример mysqldump аналогичен уже представленному:

mysqldump -u<username> -p<password> <database> > <path/to/mysql_dump.sql>

Преобразование данных из древовидной структуры обычно приводит к высокой избыточности SQL-операторов, содержащихся в дампе MySQL. Дамп легко сжимается, поскольку дамп MySQL является обычным текстовым файлом. Обычно принято направлять вывод команды mysqldump в инструмент сжатия Gzip. В соответствии со следующим соглашением получается файл с окончанием .sql.gz:

mysqldump --user=<username> --password=<password> <database> | gzip > <path/to/mysql_dump.sql.gz>

Ниже приведен обзор трех вариантов, которые часто встречаются в команде mysqldump для создания резервной копии MySQL:

 

вызов mysqldump

Пользователи базы данных

Пароль базы данных

Имя базы данных

Перенаправление вывода

Путь к выходному файлу

Команда с описательными версиями

mysqldump

—user=<имя пользователя>

—password=<password>

<база данных>

>

<путь/to/mysql_dump.sql>

Команда с короткими версиями

mysqldump

-u<имя пользователя>

-p<пароль>

<база данных>

>

<путь/to/mysql_dump.sql>

Команда со сжатием

mysqldump

—user=<имя пользователя>

—password=<password>

<база данных>

| gzip >

<path/to/mysql_dump.sql.gz>

Использование файла конфигурации дампа базы данных MySQL

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

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

Сейчас мы покажем вам, как создать резервную копию MySQL с помощью конфигурационного файла. Просто скопируйте следующие команды в командную строку и запустите их.

  1. Во-первых, создайте папку для файла конфигурации дампа базы данных MySQL ниже папки пользователя:
mkdir ~/. mysql-dump/
  1. Запишите конфигурацию дампа базы данных MySQL в новый файл, используя следующий код: 
cat << EOF > ~/. mysql-dump/database.conf
DB_NAME= 'your_db_name'
DB_USER= 'your_db_user_name'
DB_PASS= 'your_db_password'
EOF
  1. Откройте конфигурационный файл в редакторе nano и настройте данные доступа к базе данных: 
nano ~/. mysql-dump/database.conf

Замените заполнители ‘your_db’ на значения, соответствующие вашей базе данных. Затем нажмите [Ctrl] + [X], затем [Y] и [Enter], чтобы закрыть файл и сохранить изменения.

Теперь подготовка завершена, и можно создавать резервную копию MySQL.

  1. Чтобы создать дамп MySQL, прочитайте конфигурацию с помощью команды source, а затем запустите MySQL dump: 
source ~/. mysql-dump/database.conf
mysqldump --user="$DB_USER" --password="$DB_PASS" "$DB_NAME" > <path/to/mysql_dump.sql>
Совет

Резервные копии MySQL, созданные с помощью MySQL Dump, обычно хранятся в файловой системе сервера баз данных. Однако, следуя правилу 3-2-1-backup-rule, хотя бы одна резервная копия должна храниться в облаке. Мы объясним, как создать резервную копию сервера с помощью Rsync.

Использование скрипта резервного копирования MySQL

Подходы, которые были представлены до сих пор, подходят для быстрого экспорта одной базы данных MySQL. Хорошей идеей является создание сценария резервного копирования MySQL для регулярного резервного копирования или для создания резервных копий MySQL нескольких баз данных. Он выполняется периодически и создает резервные копии содержимого всех существующих баз данных MySQL в виде дампов MySQL в заранее определенной папке. Использование сценария резервного копирования MySQL стандартизирует процесс резервного копирования и снижает риск ошибок.

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

  1. Сначала создайте дополнительную папку для резервных копий MySQL:
mkdir -p ~/.mysql-dump/backups/
  1. После этого создайте собственно сценарий резервного копирования MySQL:
cat << EOF > ~/. mysql-dump/backup_databases
DB_USER='your_db_user_name'
DB_PASS='your_db_password'
# Create new folder for current backup
backup_dir='./backups/$(date +%F_%H%M)'
# Determine databases
databases=$(mysql --user="$DB_USER" --password="$DB_PASS" -Bs --execute 'SHOW DATABASES')
# Export all databases
for database in $databases; do
  mysqldump --user="$DB_USER" --password="$DB_PASS" "$database" > "${backup_dir}/${database}.sql"
done
# Delete variables from memory
unset DB_USER DB_PASS
EOF
  1. Откройте файл конфигурации в редакторе nano и настройте данные доступа к базе данных:
nano ~/. mysql-dump/backup_databases

Замените заполнители ‘your_db’ на значения, соответствующие вашей базе данных. Нажмите [Ctrl] + [X], затем [Y] и [Enter], чтобы закрыть файл и сохранить изменения.

  1. Запустите сценарий резервного копирования MySQL после завершения подготовки:
cd ~/. mysql-dump/ &amp;&amp; source backup_databases
  1. Проверьте, была ли создана резервная копия на последнем шаге:
ls -la ~/. mysql-dump/backups/*

Резервное копирование прошло, если вы видите файлы с расширением .sql.

Импорт дампа MySQL в базу данных

Команда mysqldump используется только для экспорта базы данных MySQL. Для импорта дампа необходима команда mysql. Ниже мы покажем общую схему:

mysql --user=<username> --password=<password> database_name < <path/to/mysql_dump.sql>

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

source ~/. mysql-dump/database.conf
mysql --user="$DB_USER" --password="$DB_PASS" "$DB_NAME" < <path/to/mysql_dump.sql>

Оцените статью
cdelat.ru
Добавить комментарий