Резервне копіювання PostgreSQL та MySQL: pg_dump і mysqldump

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

У цьому матеріалі ми розглянемо, як створювати та відновлювати резервні копії баз даних PostgreSQL і MySQL за допомогою стандартних утиліт командного рядка — pg_dump та mysqldump.

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

1.1. Що таке pg_dump

pg_dump — стандартна утиліта PostgreSQL для створення логічного бекапу бази даних. Вона дозволяє зберегти структуру (DDL) та дані (DML) у текстовий SQL-файл, який потім можна використати для відновлення.

1.2. Синтаксис команди

pg_dump -U <user> -h <host> -p <port> -d <database> -F p -f <path_to_file.sql>

1.3. Приклад резервного копіювання

pg_dump -U myuser -h localhost -d mydatabase -F p -f /backups/mydatabase_$(date +%F).sql

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

• -U myuser — ім'я користувача PostgreSQL;
• -h localhost — хост сервера (можна вказати IP);
• -d mydatabase — назва бази даних;
• -F p — формат виводу (plain — звичайний SQL);
• -f — шлях до файлу, куди буде записано бекап;
• $(date +%F) — додає до імені файлу поточну дату (наприклад, 2025-11-03).

1.4. Стиснення резервної копії

Щоб зменшити розмір файлу, можна використати стиснення через gzip:

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

2. Відновлення бази даних PostgreSQL

2.1. Використання psql

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

psql -U myuser -d mydatabase -f /backups/mydatabase_2025-11-03.sql

Якщо резервна копія стиснена:

gunzip -c /backups/mydatabase_2025-11-03.sql.gz | psql -U myuser -d mydatabase

2.2. Якщо база ще не створена

Спочатку створіть нову базу:

createdb -U myuser newdatabase

psql -U myuser -d newdatabase -f /backups/mydatabase_2025-11-03.sql

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

3.1. Що таке mysqldump

mysqldump — стандартна утиліта для створення дампу MySQL. Вона зберігає SQL-команди для створення таблиць і вставки даних.

3.2. Синтаксис команди

mysqldump -u <user> -p -h <host> <database> > <path_to_file.sql>

3.3. Приклад резервного копіювання

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

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

• -u myuser — ім'я користувача;
• -p — запитає пароль при виконанні;
• mydatabase — назва бази даних;
• > — перенаправлення виводу у файл.

3.4. Стиснення дампу

mysqldump -u myuser -p mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz

4. Відновлення бази даних MySQL

4.1. Відновлення з SQL-файлу

mysql -u myuser -p mydatabase < /backups/mydatabase_2025-11-03.sql

4.2. Якщо база даних відсутня

Створіть нову базу перед відновленням:

mysql -u myuser -p -e "CREATE DATABASE newdatabase;"

mysql -u myuser -p newdatabase < /backups/mydatabase_2025-11-03.sql

4.3. Відновлення зі стисненого дампу

gunzip -c /backups/mydatabase_2025-11-03.sql.gz | mysql -u myuser -p mydatabase

5. Рекомендації щодо автоматизації та безпеки

5.1. Автоматизація бекапів за допомогою Cron

Для регулярного резервного копіювання без ручного втручання налаштуйте автоматизацію через cron.

Базова настройка cron:

# Open cron editor
crontab -e

Додайте рядок, наприклад:

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

Ця команда запускатиме скрипт щодня о 2:00 ночі.

Розширений підхід з використанням скрипту. Створіть /usr/local/bin/db_backup.sh:

#!/bin/bash
DATE=$(date +%F)
BACKUP_DIR="/backup/$DATE"
mkdir -p $BACKUP_DIR
# PostgreSQL backup
pg_dump -U myuser mydatabase | gzip > $BACKUP_DIR/postgres_mydatabase.sql.gz
# MySQL backup
mysqldump -u myuser -p mydatabase | gzip > $BACKUP_DIR/mysql_mydatabase.sql.gz
# Remove backups older than 7 days
find /backup/ -type f -mtime +7 -delete

Зробіть скрипт виконуваним:

chmod +x /usr/local/bin/db_backup.sh

і додайте в cron:

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

5.2. Найкращі практики безпеки та зберігання

• Зберігання поза сервером: зберігайте бекапи у хмарних сховищах (AWS S3, Google Cloud) або на зовнішніх дисках.
• Контроль доступу: обмежте доступ до папки /backups, оскільки файли можуть містити конфіденційні дані.

chmod 700 /backup

chmod 600 /backup/*.sql.gz

• Перевірка цілісності: виконуйте тестове відновлення щомісяця для підтвердження працездатності бекапів.
• Конфіденційність: файли бекапів зберігають вміст бази даних — поводьтеся з ними як з конфіденційною інформацією.

Висновок

Використання pg_dump і mysqldump — це простий, надійний і універсальний спосіб резервного копіювання баз даних PostgreSQL і MySQL. Ці інструменти підходять як для ручного копіювання, так і для автоматизації процесу з мінімальними зусиллями.

Регулярні резервні копії — запорука стабільності та безпеки вашого проєкту.