Статья также доступна на украинском (перейти к просмотру).

Содержание:

• Типы резервного копирования MySQL
• Использование mysqldump для резервного копирования
• Автоматизация через cron
• Другие инструменты
• Восстановление из резервной копии
• Безопасное хранение копий и другие вопросы организации системы бекапов
• Выводы

Все современные веб-проекты используют базы данных (БД), которые хранятся на хостинге и требуют особого внимания обслуживающего персонала. Это связано с их уязвимостью перед множеством факторов — человеческий фактор, вирус, взлом, технический сбой и т. д. В такой ситуации единственным надежным способом не потерять важную информацию может быть только ее периодическое копирование на независимый носитель. Такая операция получила название создания бекапов или резервного копирования. Рассмотрим некоторые теоретические аспекты использования БД MySQL, а также вопросы организации автоматического резервного копирования на собственном хостинге.

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

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

Приведем основные типы резервного копирования, согласно документации MySQL:

• Физический;

• Логический;

• Полное и инкрементное резервное копирование. 

Физический тип применяется в случае значительного объема БД и повышенных требований к скорости его восстановления. Резервные копии, сделанные физически, содержат необработанные копии файлов и каталогов со всеми данными БД.

Для физического типа резервного копирования характерны следующие свойства:

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

• Компактность копий, поскольку они содержат только данные без сохранения структуры БД;

• Кроме основных данных, копия может содержать связанные с ними файлы конфигураций, журналов и т.п.;

• Уровень детализации резервного копирования и восстановления – от уровня таблиц (с учетом механизма хранения данных) до уровня каталогов и файлов; 

• Перенос резервных копий на другие хосты возможен только при идентичности аппаратных платформ хостов;

• Создание копий может происходить только при остановке сервера MySQL или включенной блокировке на изменение данных;

• Выбор инструментов для работы и восстановления зависит от имеющегося механизма хранения данных:

• Для InnoDB и других типов таблиц используется mysqlbackup;

• Для NDB восстановление происходит с помощью ndb_restore;

• Для МойISAM можно использовать команды уровня файловой системы – scp, cp, rsync,принимает.

Логический тип может применяться для БД небольшого размера при необходимости корректировки значений данных или структуры таблиц. Также он подойдет при переносе хранилища на другие хосты с разными аппаратными платформами.    

Для логического типа резервного копирования характерны следующие свойства:

• Для возможности создания резервной копии сервер MySQL должен быть запущен и способен обрабатывать запросы;

• Скорость создания копий меньше, чем физически, из-за необходимости выполнения дополнительных операций преобразования данных в логический формат;

• Размер бекапов больше по сравнению с физическим методом;

• Уровень детализации копирования и восстановления – от уровня таблиц до уровня каталогов и файлов вне зависимости от механизма хранения данных; 

• Бекап не содержит каких-либо файлов, не являющихся частью БД;

• Кроссплатформенность – резервные копии не зависят от типа устройств и аппаратной платформы хостов;

• Инструментарий для работы и обновления: программа mysqldump вместе с оператором SELECT  INTO OUTFILE,клиент MySQL (SQL формат),клиент MySQLimport или оператор LOAD DATA (Текстовый формат с разделителями).

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

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

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

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

Клиентская программа mysqldump используется в качестве инструмента для создания резервных копий логического типа одной или нескольких БД MySQL. Утилита дополнительно способна генерировать исходные данные в форматах XML или текстовом CSV. Найти и скачать программу можно на официальном веб-сайте ее разработчиков. 

Полученный с помощью утилиты файл дампа в дальнейшем можно использовать для различных целей, в частности:

• Для восстановления утраченных данных;

• Для переноса БД на другой SQL-сервер;

• В качестве источника данных для проведения тестирования или настройки реплик;

• Для обнаружения несовместимостей при обновлении.

Среди преимуществ утилиты mysqldump перед другими инструментами можно выделить следующие кейсы:

• Открытый исходный код;

• высокая скорость работы для БД небольших объемов;

• Возможность импорта таблиц из одной БД в другую, минуя процессы импорта и экспорта;

• Широкий выбор полезных опций запуска команды (100+), что, в частности, позволяет выполнить тонкие настройки процесса создания резервных копий на уровне БД или отдельных таблиц;

• Высокий уровень автоматизации;

• Кроссплатформенность (Windows, macOS, ряд дистрибутивов Linux и другие менее известные ОС);

• Бесплатное использование в отличие, например, от горячая копия от MySQL Enterprise.

Среди недостатков программы можно выделить следующие:

• Блокировка таблиц при создании дампа (для формата InnoDB поможет использование опции –single-transaction);

• замедление скорости работы при обработке БД больших объемов (300+ Мб);

• Не поддерживает параллельный запуск (однопоточность);

• Создает дополнительную нагрузку на сервер. 

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

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

Приведем ряд примеров по применению программы mysqldump для создания резервных копий и других целей.

Пример 1

Создание копии одной БД:

mysql> mysqldump --databases mybase > mydump.sql

В случае только одной базы указанная команда может быть записана без использования опции –databases.Однако, в этом случае следует учитывать, что дамп не будет содержать операторов CREATE DATABASE или USE и потому, например, при его перезагрузке следует указать имя БД по умолчанию.

Пример 2

Создание копии нескольких баз:

mysql> mysqldump --databases mybase1 mybase2 mybase3 > mydump.sql

Пример 3

Создание копии всех имеющихся баз: 

mysql> mysqldump --all-databases > mydump.sql

Пример 4

Выгрузка отдельных таблиц:

mysql> mysqldump mybase table1 table5 table6 > mydump.sql

Пример 5

Создание дампа базы и его сжатие в архиве gz:

mysql> mysqldump mybase | gzip> mydump.sql

Пример 6

Создание дампа схемы БД. 

mysql> mysqldump --no-data mybase > mydump.sql

Пример 7

Сохранение вывода команды в отдельном файле. По умолчанию в стандартный поток вывода утилита выводит данные в виде SQL-операторов. Этот вывод можно записать в файл:

mysql> mysqldump -u ИМЯ ПОЛЬЗОВАТЕЛЯ -p > myfile 

Указанные параметры являются необязательными и имеют следующие значения:

-u –  имя пользователя БД (ИМЯ ПОЛЬЗОВАТЕЛЯ);

-p – пароль для входа (вводится по запросу).

Пример 8

Управление объектами, сохраняющими дамп сохраненных программ. Утилита имеет несколько опций(--events, --triggers, --routines, ) для управления объектами, которые могут содержать дамп. По умолчанию эти опции включены, поэтому при загрузке таблиц также выгружаются соответствующие объекты. Однако при желании можно запретить выгрузку тех или иных объектов. Для этого в команде следует использовать опцию, состоящую из оператора пропускать и названия соответствующего параметра.

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

mysql> mysqldump --skip-triggers mybase table1 table3 > newdump.sql

Пример 9 

Выгрузка только данных. В Пример 6 мы продемонстрировали, как вывести схему БД без данных. Теперь приведем пример вывода одних только данных. Для этого в команде следует запретить вывод операторов CREATE с помощью опции --no-create-info:

mysql> mysqldump --no-create-info mybase > newdump.sql

Пример 10

Выгрузка данных определенного диапазона. Иногда необходимо выгрузить данные только за определенный период времени. В этом нам поможет опция –where. Необходимым условием ее использования является наличие в таблице столбца с типом данных «timestamp/datetime».

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

mysql> mysqldump mybase table5 --where="date_column BETWEEN ‘2025-06-10 00:00:00’ and ‘2025-09-10 00:00:00’” > newdump.sql

Автоматизация через cron

Языковые средства командной оболочки bash и возможности даемона cron предоставляют возможность автоматизации создания бекапов на сервере. Приведем последовательность действий по реализации этой возможности с помощью несложного сценария.  

Создадим сценарий:

$ sudo nano example_backup.sh
К новому файлу добавим следующий код:
#!/bin/bash
#Имя пользователя БД MySQL
user=user_db 
#Пароль пользователя БД MySQL
password=1234567
#Каталог для сохранения копий
backup_folder=/home/username/backups/
#Определение количества дней хранения копий
keeping_days=12
#Команда создания дампа базы mybase 
mysqldump -u root --opt -R mybase > $backup_folder/copy-mybase-$(date +%Y%m%d-%H%M%S).sql
#Команда удаления копий со сроком хранения больше, чем keeping_days
find $backup_folder/copy-mybase* -mtime + $keeping_days -exec rm {}\;
#Выход из сценария
exit 0

Назначение каждой строки сценария разъясняется в соответствующих комментариях.

 

Сохраним внесенные изменения и выйдем из редактора.

Настроим задачу cron по созданию бекапов с помощью системных средств. Для этого откроем файл для записи заданий с именем crontab и внесем в него соответствующие изменения:

$ crontab -e

В конец файла добавим следующую строчку:

00 3 * * * bash example_backup.sh > /dev/null 2>&1

Здесь мы определили время запуска нашего сценария с именем example_backup.sh (в три часа ночи каждый день). Выбор времени не случаен, поскольку именно в эти часы суток нагрузка на сервер обычно минимальна, а значит, ежедневное исполнение сценария не будет его перегружать.

Сохраним внесенные изменения и закроем файл.

После этого можно не беспокоиться – бекапы будут автоматически создаваться вместе с выполнением операций по их ротации согласно времени хранения. Этот термин всегда можно откорректировать, изменив значение параметра keeping_days в файле сценария.

Другие инструменты

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

• MySQL Shell;

• XtraBackup;

• MyDumper;

• MySQLPump.

В отличие от mysqldump все они поддерживают параллельный запуск и соответственно могут работать со многими потоками данных. Это напрямую влияет на скорость формирования бекапов и выводит указанные инструменты на новый уровень. Для подтверждения этого тезиса, на сайте приведены результаты проведенного тестирования производительности mysqldump и указанных инструментов по созданию бекапов при запуске 1, 16, 32 и 64 потоков обработки данных. Тестирование проводилось на мультипроцессорной системе с 32 виртуальными процессорами. Результаты тестов приведены на Рисунку 1.

 

Рисунок 1. Результаты тестирования доступных средств создания бекапов

По результатам тестирования можно сделать следующие выводы:

• Самые эффективные результаты продемонстрировал инструмент MyDumper при использовании метода сжатия типа zstd (70 сек);

• Утилита mysqldump является наиболее медленным средством среди всех остальных (1670 сек);

• На скорость обработки данных влияют методы сжатия;

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

Восстановление из резервной копии

По хорошо известным причинам иногда бывает необходимо выполнять восстановление БД из ранее созданного дампа. Общая команда при этом будет выглядеть следующим образом:

mysql>mysql < mydump.SQL

Или так 

mysql> source mydump.sql

Этого будет достаточно в случае, если дамп создавался с опциями –databases или --all-databases, то есть, он содержит операторы CREATE DATABASE и USE и поэтому при загрузке файла указывать имя базы по умолчанию не требуется. Но если указанные операторы отсутствуют в файле дампа, последовательность действий будет другой.

В случае отсутствия базы сначала ее нужно создать. Это можно сделать с помощью следующей команды: 

mysql> mysqladmin create mybase

После этого, при перезагрузке дампа необходимо указать ее базы:

mysql> mysql mybase < mydump.sql

Только после выполнения указанных действий база будет восстановлена.

Нередки случаи ситуации, когда необходимо восстановить какую-либо из таблиц БД, например, после ее проверки средствами CHECK TABLE или mysqlcheck. Это можно сделать путем ее пересоздания с помощью дампа.

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

mysql> mysql mybase < dump_table1.sql

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

mysql> mysqldump mybase table1 > dump_table1.sql
mysql> mysql mybase < dump_table1.sql

Во многих случаях этого достаточно для восстановления таблицы и исправления ошибок.

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

mysql> REPAIR TABLE table1 

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

Безопасное хранение копий и другие вопросы организации системы бекапов

Организация системы резервного копирования для собственного или коммерческого хостинга обычно определяется рядом характеристик, к которым, в частности, относятся:

• Тип хостинга (виртуальный, VPS, выделенный сервер или облако);

• Место хранения бекапов (локальное В-хранилище, наружные SDD / HDD диски, облачные сервисы, удаленные серверы бекапов);

• Метод резервного копирования (дифференцированное, инкрементное, полное, снапшоты);

• Содержимое бекапов (файлы, БД, VM);

• Частота формирования копий (ежедневно, еженедельно, персональные настройки);

• Срок хранения бекапов (7, 14, 28 дней);

• Возможности по автоматизации процесса создания бекапов;

• возможность мониторинга процесса;

• Наличие шифрования данных.

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

Приведем здесь лишь некоторые из рекомендованных характеристик системы резервного копирования для продакшн и не только, которіе FREEhost.UA уже несколько лет используеn на практике для клиентов клиентов различных типов хостинга:

• Автоматическое создание бекапов для виртуального хостинга со сроком хранения 14 дней; место хранения – обособленные серверы;

• Бесплатный FTP-доступ к диску на обособленном сервере для типов хостинга VPS и физический сервер;

• Бесплатная помощь клиентам по настройке системы резервного копирования для любого типа хостинга;

• Дополнительная опция включения шифрования данных резервной копии (рекомендуется для облака и удаленных серверов);

• Применение стратегии 3-2-1;

• Периодическое тестирование восстановления файлов дампов;

• Ежедневный мониторинг успешности бекапов с помощью email, Telegramили Zabbix. 

Более подробно ознакомиться с рекомендациями от специалистов FREEhost.UA можно в статье на сайте компании.

Выводы

Построение надежной и безопасной системы бекапов требует разнопланового подхода к выбору методов и средств организации периодического резервного копирования на собственном хостинге или продакшене. И не в последнюю очередь качество такой системы зависит от уровня технологичности и лояльности специалистов избранной хостинг-компании. Если с этим все хорошо – успех гарантирован!

Подписывайтесь на наш телеграм-канал https://t.me/freehostua, чтобы быть в курсе новых полезных материалов

Смотрите наш канал Youtube на https://www.youtube.com/freehostua.

Мы в чем-то ошиблись, или что-то пропустили?

Напишите об этом в комментариях, мы с удовольствием ответим и обсудим Ваши замечания и предложения.

Дата: 15.09.2025 Автор: Александр Ровник #automation#backup#devops#mysql#ubuntu#zabbix

Голосование 1 2 3 4 5