Учимся делать бэкапы MySQL как профи

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

Автор материала

Артем Зернов. Веб-разработчик, создатель проекта Лектория, эксперт MODX Revolution, директор веб-студии OpenColour. Youtube-канал OpenModx.

8
6 минут на прочтение
Теги по этой теме:

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

В этом руководстве рассмотрим, каким образом можно выполнить резервное копирование mysql или mariadb базы данных, а также как восстановить информацию в базе из копии. В рассматриваемых базах для резервного копирования используется дамп. Операции с ним и будем рассматривать. Что же такое дамп, для чего, зачем и как?

Дамп (англ. dump) — снимок информации о состоянии компьютерной системы, в нашем случае базы данных. Дамп базы данных может состоять из описания структуры базы и/или содержащихся в ней данных, обычно в виде команд SQL и, как правило, используется для резервного копирования.

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

Создание дампа

Разберем самый простой пример использования утилиты

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

mysqldump -u username -ppassword database_name  > dump.sql

где:

  • -u - ключ задания имени пользователя
  • username - имя пользователя
  • -p ключ задания пароля
  • password - пароль пользователя username, вводится после ключа -р без пробела
  • databasename - имя базы данных для дампа
  • > - перенаправление потока вывода
  • dump.sql - имя выходного файла содержащий дамп БД. Если вы не указывали путь, то файл dump.sql, будет сохранен в папке home пользователя

При вводе этой команды утилита подключится к указанной базе данных под пользователем username и результат содержимого отправит в файл dump.sql

Как вы поняли, такая команда справедлива только при локальном подключении. А как быть если у нас есть VPS или БД на удаленном компьютере?

Создание бэкапа с удаленного хоста

Тогда соответственно перепишем прошлую команду с указанием хоста.

mysqldump -u username -hhost.ru -p database_name  > dump.sql

Как видите, мы указали имя хоста host.ru с использованием ключа -h. И второе отличие, как вы уже заметили, ключ не сопровождает пароль, а это значит, что перед тем как утилита начнет выполнение, она спросит пароль для подключения.

Бэкап таблиц

Допустим снятие дампа всей базы данных ни к чему, необходимо сделать снимок только одной таблицы, тогда после имени БД необходимо указать название таблицы (table_name).

mysqldump -u username -p database_name table_name  > dump.sql

А если надо две или больше? Соотвественно перечислим их через пробел

mysqldump -u username -p database_name table_name1 table_name2  > dump.sql

Вариации дампа

Допустим, нам нужна "песочница" для эксперимента, а для этих целей вся таблица ни к чему, нужна только ее часть — структура. Не проблема! Укажите атрибут --no-data

mysqldump -u username -p --no-data database_name table_name  > dump.sql

Теперь в файле dump.sql вы увидите SQL команды создания структуры этой таблицы. Такой же прием справедлив и ко всей базе данных. Думаю, пример приводить не надо, не пишите имя таблицы после указания БД.

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

mysqldump -u username -p --no-data --triggers --events --routines database_name   > dump.sql

где:

  • --triggers - ссылка на тригеры
  • --events - ссылка на события
  • --routines - ссылка на процедуры

Дамп отдельных строк

В этот раз мы немного вспомним структуру запросов SQL, а именно предложение WHERE. Допустим, в таблице содержится описание товаров и присутствует поле count, обозначающее количество этого товара. Необходимо сделать дамп товаров с количеством меньше 10.

mysqldump -u username -p database_name table_name --where="count < 10"  > dump.sql

Соотвественно, если необходимо сделать выборку по двум параметрам, то в предложении появляется связка AND

mysqldump -u username -p database_name table_name --where="count < 10 AND date_public='2020-10-24'"  > dump.sql

Как видно из примеров, в использовании утилиты mysqldump нет ничего сложного. Однако, до этого были приведены примеры только снятия дампа, а как быть если его необходимо восстановить?

Развертывание дампа

Команда mysqldump предназначена только для снятия дампа. Если нам необходимо, наоборот, восстановить базу или отдельную таблицу из файла, необходимо воспользоваться командой mysql

mysql -u username -p database_name  < dump.sql

Дополнительные параметры

На использовании параметров, приведённых выше, функциональность не заканчивается. Дополнительные параметры можно посмотреть в документации на утилиту.

Заключение

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

Мы используем куки на нашем сайте. Продолжая просмотр, вы соглашаетесь с условиями пользовательского соглашения
Пожалуйста, подождите. Процесс оформления заказа может занимать до 30 секунд.