Резервне копіювання баз даних PostgreSQL і MySQL

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

У цьому мануалі детально розглянемо, як виконувати резервне копіювання баз даних PostgreSQL і MySQL, як зберігати бекапи на віддаленому сервері Linux за допомогою rsync або scp, а також як надсилати їх у хмарні сховища через rclone.

Резервне копіювання PostgreSQL

Для PostgreSQL використовується стандартна утиліта pg_dump, яка дозволяє створити повну копію бази даних у вигляді SQL-файлу.

Приклад створення бекапу:

pg_dump -U myuser -h localhost mydatabase > /backup/mydatabase_$(date +%F).sql

Пояснення параметрів:

-U myuser — ім'я користувача бази даних;
-h localhost — адреса сервера (якщо база локальна, можна опустити);
mydatabase — ім'я бази даних;
/backup/... — шлях, куди зберігається копія;
$(date +%F) — додає поточну дату до імені файлу.

Щоб заощадити місце, файл можна одразу стиснути:

pg_dump -U myuser mydatabase | gzip > /backup/mydatabase_$(date +%F).sql.gz

Для резервного копіювання всіх баз даних на сервері:

pg_dumpall -U myuser | gzip > /backup/postgres_all_$(date +%F).sql.gz

Резервне копіювання MySQL

Для MySQL і MariaDB використовується утиліта mysqldump.

Приклад створення бекапу однієї бази даних:

mysqldump -u myuser -p mydatabase > /backup/mydatabase_$(date +%F).sql

Під час виконання команди система запитає пароль користувача user.

Щоб створити архів усіх баз даних:

mysqldump -u myuser -p --all-databases | gzip > /backup/mysql_all_$(date +%F).sql.gz

Якщо потрібно включити збережені процедури та тригери, додайте параметри --routines --triggers.

Резервне копіювання на CloudBox

CloudBox від INTROSERV - хмарне сховище з підтримкою численних протоколів підключення (включаючи rsync, scp, rsync через SSH, WebDAV). Простота використання CloudBox значно полегшує завдання з завантаження, управління та отримання даних.

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

Крок 1. Отримання доступу до CloudBox

Замовте один з доступних тарифних планів CloudBox на нашому сайті — https://introserv.com/solutions/cloudbox/

Після оплати сервіс стає доступним протягом кількох хвилин. Після активації послуги ви отримаєте дані для підключення:

адресу сервера (box$$$$$.introserv.cloud)
ім'я користувача (box$$$$$) та пароль

Крок 2. Підключення та синхронізація за допомогою rsync або scp

Використання rsync (рекомендовано):

rsync -avz -e "ssh -p 22" /backup/ user@cloudbox-server:/path_to_cloudbackups/

Де:

user — ім'я користувача CloudBox;
cloudbox-server — адреса підключення;
/path_to_cloudbackups/ — шлях на CloudBox.

Для автоматичної роботи бажано налаштувати SSH-ключі.

1) Створіть публічний ключ:

ssh-keygen -t rsa

2) Скопіюйте вміст файлу публічного ключа, який був створений, наприклад, /root/.ssh/id_rsa.pub в буфер обміну.

3) Перейдіть у розділ "Profile" (наведіть курсор на іконку користувача праворуч вгорі), додайте вміст ключа в поле "Public keys" та збережіть налаштування.

Після чого rsync працюватиме без введення пароля, що зручно для cron-завдань.

Альтернатива — scp:

scp /backup/mysql_mydatabase.sql.gz user@cloudbox-server:/path_to_cloudbackups/

Крок 3. Підключення та синхронізація за допомогою rclone

Якщо ви хочете використовувати rclone для взаємодії з CloudBox, виконайте налаштування таким чином.

1) Встановіть rclone:

sudo apt install rclone

2) Запустіть конфігурацію:

rclone config

При налаштуванні виберіть:

n — для створення нового підключення;
Назву, наприклад cloudbox;
Тип сховища — виберіть sftp або ssh;
Введіть адресу сервера формату box$$$$$.introserv.cloud, ім'я користувача формату box$$$$$, порт (у даному випадку порт SSH — за замовчуванням 22), шлях до директорії;
Виконайте авторизацію (якщо потрібно).

Перевірте підключення:

rclone ls cloudbox:/path_to_cloudbackups/

Копіювання резервних даних:

rclone copy /backup/ cloudbox:/db-backups/ --progress

Синхронізація з видаленням відсутніх локально файлів:

rclone sync /backup/ cloudbox:/db-backups/ --progress

Якщо потрібно — зашифруйте файли перед передачею:

rclone copy /backup/ cloudbox:/db-backups/ --progress --crypt

Крок 4. Приклад автоматизації скрипту з CloudBox

Створіть скрипт /usr/local/bin/db_backup_cloudbox.sh, наприклад:

#!/bin/bash DATE=$(date +%F) BACKUP_DIR="/backup/$DATE" mkdir -p $BACKUP_DIR # PostgreSQL pg_dump -U myuser mydatabase | gzip > $BACKUP_DIR/postgres_mydatabase.sql.gz # MySQL mysqldump -u myuser -p mydatabase | gzip > $BACKUP_DIR/mysql_mydatabase.sql.gz # Upload to CloudBox rsync -avz -e "ssh -p 22" $BACKUP_DIR/ user@cloudbox-server:/path_to_cloudbackups/$DATE/ # Upload to CloudBox (rclone option, uncomment if used) # rclone copy $BACKUP_DIR/ cloudbox:/db-backups/$DATE/ --progress # Remove backups older than 7 days find /backup/ -type f -mtime +7 -delete

Потім додайте cron-завдання (у прикладі скрипт запускається щодня, один раз на добу, о 2 годині ночі):

0 2 * * * /usr/local/bin/db_backup_cloudbox.sh

Що важливо враховувати:

Переконайтеся, що з'єднання з CloudBox стабільне та пропускна здатність достатня.
Налаштуйте моніторинг: переконайтеся, що файли дійсно копіюються.
Періодично перевіряйте відновлення з CloudBox-копій.
При використанні конфіденційних даних — розгляньте шифрування резервних копій.

Копіювання на віддалений сервер Linux по SSH

Якщо ви хочете зберігати резервні копії не лише локально, а й на іншому сервері, можна використовувати rsync або scp.

Використання rsync (переважний варіант, оскільки копіює лише зміни):

rsync -avz -e "ssh -p 22" /backup/ [email protected]:/remote_backups/

Пояснення параметрів:

user — ім'я користувача на віддаленому сервері;
remote.server — IP-адреса або доменне ім'я сервера;
/remote_backups/ — шлях для зберігання резервних копій.

Для автоматичної роботи бажано налаштувати ключі SSH.

Генерування ключів:

ssh-keygen -t rsa

Копіювання ключа на віддалений сервер:

ssh-copy-id [email protected]

Після цього rsync працюватиме автоматично без запиту пароля, що зручно для cron-завдань.

Альтернатива — використання утиліти scp:

scp /backup/mysql_mydb.sql.gz [email protected]:/remote_backups/

Копіювання в стороннє хмарне сховище через rclone

Якщо потрібно зберігати резервні копії у хмарі (Google Drive, Dropbox, Amazon S3 та інші), зручно використовувати rclone — універсальну утиліту для синхронізації даних із різними хмарними сервісами.

Встановлення:

sudo apt install rclone

Налаштування:

rclone config

Далі дотримуйтесь інструкцій майстра:

Виберіть n для створення нового підключення;
Введіть ім'я, наприклад remote;
Виберіть тип сховища (наприклад s3, drive, dropbox);
Введіть ключі доступу або авторизуйтеся через браузер.

Перевірити підключення:

rclone ls remote:path

Приклад копіювання резервних копій у хмару:

rclone copy /backup/ remote:db-backups/ --progress

Команда синхронізації (видаляє старі файли, яких немає локально):

rclone sync /backup/ remote:db-backups/ --progress

Також можна зашифрувати файли перед відправленням:

rclone copy /backup/ remote:db-backups/ --progress --crypt

Безпека та контроль

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

gpg -c /backup/mysql_mydatabase.sql.gz

Стежте за правами доступу до каталогу /backup і паролями від баз даних.

Відновлення бази з резервної копії

PostgreSQL:

gunzip < backup.sql.gz | psql -U myuser -d mydatabase

MySQL:

gunzip < backup.sql.gz | mysql -u myuser -p mydatabase

Висновок

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

Використовуючи комбінацію стандартних інструментів pg_dump, mysqldump, rsync, rclone і cron, можна побудувати надійну систему резервного копіювання без стороннього платного ПЗ. Це рішення легко автоматизується і підходить для будь-яких Linux-систем.