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

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

В этом материале мы рассмотрим, как создать и восстановить резервные копии баз данных 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. Эти инструменты подходят как для ручного копирования, так и для автоматизации процесса с минимальными усилиями.

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