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

Зміст:

• Типи резервного копіювання MySQL/a>
• Використання mysqldump для резервного копіювання
• Автоматизація через cron
• Інші інструменти
• Відновлення з резервної копії
• Безпечне зберігання копій та інші питання організації системи бекапів
• Висновки

Всі сучасні веб-проекти використовують бази даних (БД), котрі зберігаються на хостингу та потребують пильної уваги обслуговуючого персоналу. Це пов'язано із їх вразливістю перед багатьма чинниками – людський фактор, вірус, злом, технічний збій тощо. У цій ситуації єдиним надійним способом не втратити важливу інформацію може бути лише її періодичне копіювання на незалежний носій. Така операція отримала назву створення бекапів або резервне копіювання. Розглянемо деякі теоретичні аспекти використання БД MySQL, а також  питання організації автоматичного резервного копіювання на власному хостингу.         

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

Формування резервних копій баз даних можливо кількома способами в залежності від ряду чинників – об’єму бази даних, рівня вимог до швидкості її відновлення, наявних ресурсів для зберігання тощо.

Наведемо основні типи резервного копіювання, відповідно до документації MySQL

• Фізичний;

• Логічний;

• Повне та інкрементне резервне копіювання

Фізичний тип застосовується у випадку значного об’єму БД та підвищених вимог до швидкості її відновлення. Резервні копії, зроблені на фізичному рівні містять необроблені копії файлів та каталогів із усіма даними БД.     

Для фізичного типу резервного копіювання характерні наступні властивості

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

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

• Окрім основних даних, копія може містити пов’язані з ними файли конфігурацій, журналів тощо;

• Рівень деталізації резервного копіювання та відновлення – від рівня таблиць (із врахуванням механізму збереження даних) до рівня каталогів та файлів;  

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

• Створення копій може відбуватися лише при зупинці роботи сервера MySQL або включеному блокуванні на зміну даних;

• Вибір інструментів для роботи та відновлення залежить від наявного механізму збереження даних: 

• Для InnoDB та інших типів таблиць використовується mysqlbackup; 

• Для NDB відновлення відбувається за допомогою ndb_restore; 

• Для MyISAM можна використовувати команди рівня файлової системи – scp, cp, rsync,tar.

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

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

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

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

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

• Рівень деталізації копіювання та відновлення – від рівня таблиць до рівня каталогів та файлів незалежно від механізму збереження даних;  

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

• Кросплатформність – резервні копії не залежать від типу пристроїв та апаратної платформи хостів;

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

Повне та інкрементне резервне копіювання можуть виконуватися одним із наведеним вище способів. Вказані типи різняться між собою за об’ємами збережених даних та способу їх формування.

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

Інкрементний (миттєво) тип застосовується з метою зафіксувати лише зміни, внесені до бази за визначений проміжок часу. Він спирається на записи системного журналу на сервері, у котрому фіксуються всі зміни, котрі відбулися у БД. При відновленні бази відновлюються лише зміни, внесені до неї у визначений період часу. Вказаний спосіб зазвичай застосовується одразу після проведення повного відновлення даних з метою актуалізації даних, про що вже згадувалося вище.            

Використання mysqldump для резервного копіювання

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

Отриманий за допомогою утиліти файл дампу надалі можна використовувати для різних цілей, зокрема, таких

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

• Для переносу БД на інший SQL-сервер;

• У якості джерела даних для проведення тестування або налаштування реплік;

• Для виявлення несумісностей при оновленні

Серед переваг утиліти mysqldump перед іншими інструментами можна виділити наступні кейси

• Відкритий вихідний код;

• Висока швидкість роботи для БД невеликих об’ємів;

• Можливість імпорту таблиць із однієї БД в іншу, минаючи процеси імпорту та експорту;

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

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

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

• Безкоштовне використання на відміну, наприклад, від hotcopy від 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 USERNAME -p > myfile

Вказані параметри є необов’язкові та мають наступні значення:

-u – ім’я користувача БД (USERNAME);

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

Приклад 8

Управління об’єктами, котрі зберігає дамп збережених програм. Утиліта має декілька опцій (--events, --triggers, --routines, ) для управління об’єктами, котрі може містити дамп. За замовчуванням ці опції включені, тому при вивантаженні таблиць також вивантажуються відповідні об’єкти. Однак, при бажанні можна заборонити вивантаження тих або інших об’єктів. Для цього у команді слід використати опцію, котра складається з оператору skip та назви відповідного параметру.

Наприклад, для заборони вивантаження тригерів відповідна команда буде виглядати наступним чином:

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, виділений сервер або хмара);

• Місце зберігання бекапів (локальне NAS-сховище, зовнішні SDD / HDD диски, хмарні сервіси, віддалені сервери бекапів);

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

• Вміст бекапів (файли, БД, VM); 

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

• Термін зберігання бекапів (7, 14, 28 днів);

• Можливості по автоматизації процесу створення бекапів;

• Можливість моніторингу процесу;

• Наявність шифрування даних.

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

Наведемо тут лише деякі з рекомендованих характеристик системи резервного копіювання для продакшн і не тільки, які FREEhost.UA використовує на практиці для клієнтів різного типу хостингу: 

• Автоматичне створення бекапів для віртуального хостингу із терміном зберігання 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