Резервное копирование БД: Руководство по MySQL и PostgreSQL

# Резервная копия одной базы данных
mysqldump -u root -p имя_базы > backup.sql

# С паролем (для скриптов)
mysqldump -u root -pПАРОЛЬ имя_базы > backup.sql

# Резервная копия всех баз данных
mysqldump -u root -p --all-databases > all_databases.sql

# Резервная копия конкретных таблиц
mysqldump -u root -p имя_базы таблица1 таблица2 > tables.sql

# Сжатая резервная копия
mysqldump -u root -p имя_базы | gzip > backup_$(date +%Y%m%d).sql.gz

# Согласованная резервная копия для больших баз данных
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  имя_базы > full_backup.sql

# Только структура (без данных)
mysqldump -u root -p --no-data имя_базы > schema.sql

# Только данные (без структуры)
mysqldump -u root -p --no-create-info имя_базы > data.sql

# Резервная копия с удалённого сервера
mysqldump -h remote_server -u user -p имя_базы > remote_backup.sql

# Восстановление из SQL-файла
mysql -u root -p имя_базы < backup.sql

# Восстановление из сжатой резервной копии
gunzip -c backup.sql.gz | mysql -u root -p имя_базы

# Создание новой базы и восстановление
mysql -u root -p -e "CREATE DATABASE new_database;"
mysql -u root -p new_database < backup.sql

# Восстановление всех баз данных
mysql -u root -p < all_databases.sql

# Создание ~/.my.cnf
nano ~/.my.cnf

[client]
user=root
password=ВАШ_ПАРОЛЬ
host=localhost

# Ограничение прав доступа к файлу
chmod 600 ~/.my.cnf

# Теперь можно использовать без ввода пароля
mysqldump имя_базы > backup.sql

# Резервная копия одной базы данных
pg_dump -U postgres имя_базы > backup.sql

# Сжатый пользовательский формат (рекомендуется)
pg_dump -U postgres -Fc имя_базы > backup.dump

# Формат каталога (для параллельного восстановления)
pg_dump -U postgres -Fd имя_базы -f backup_dir/

# Резервная копия всех баз данных (включая роли)
pg_dumpall -U postgres > all_pg.sql

# Сжатая резервная копия
pg_dump -U postgres имя_базы | gzip > backup_$(date +%Y%m%d).sql.gz

# Восстановление из SQL-формата
psql -U postgres имя_базы < backup.sql

# Восстановление пользовательского формата (.dump)
pg_restore -U postgres -d имя_базы backup.dump

# Параллельное восстановление (быстрее)
pg_restore -U postgres -d имя_базы -j 4 backup.dump

# Создание новой базы и восстановление
psql -U postgres -c "CREATE DATABASE new_database;"
pg_restore -U postgres -d new_database backup.dump

nano ~/.pgpass

# Формат: host:port:database:user:password
localhost:5432:*:postgres:ВАШ_ПАРОЛЬ

chmod 600 ~/.pgpass

#!/bin/bash

# Конфигурация
DB_USER="root"
DB_PASS="ВАШ_ПАРОЛЬ"
DB_NAME="имя_базы"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Создание директории для резервных копий
mkdir -p $BACKUP_DIR

# Создание резервной копии
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Удаление старых резервных копий
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Резервное копирование завершено: ${DB_NAME}_${DATE}.sql.gz"

# Сделать скрипт исполняемым
chmod +x /root/scripts/mysql-backup.sh

# Добавить в crontab (каждую ночь в 02:00)
crontab -e
0 2 * * * /root/scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

# Копирование на удалённый сервер с rsync
rsync -avz /var/backups/mysql/ user@remote-server:/backup/mysql/

# Отправка одного файла с scp
scp /var/backups/mysql/backup.sql.gz user@remote-server:/backup/

# Отправка в облачное хранилище с rclone (S3, Google Drive и т.д.)
rclone copy /var/backups/mysql/ remote:bucket-name/mysql/

# Проверка размера файла резервной копии
ls -lh /var/backups/mysql/

# Проверка путём восстановления в тестовую базу данных
mysql -u root -p -e "CREATE DATABASE test_restore;"
mysql -u root -p test_restore < backup.sql
mysql -u root -p -e "SHOW TABLES;" test_restore
mysql -u root -p -e "DROP DATABASE test_restore;"