DB: Резервное копирование и восстановление базы данных

Написал скрипт, при помощи которого можно делать резервные копии баз данных #PgSQL / #MariaDB и отправлять их в удалённое хранилище через #Rsync.

Скрипт

Скрипт состоит из следующих компонентов:

• app.backup_db.conf - файл с настройками.
• app.backup_db.sh - приложение.
• cron.backup_db - задание для CRON.

Установка

• Установить пакеты:

1

apt install --yes sshpass

• Скачать и установить скрипт:

1

curl -sL 'https://raw.githubusercontent.com/pkgstore/bash-install/refs/heads/main/install.sh' | bash -s -- '/root/apps/backup' 'bash-backup-db' 'main'

MongoDB

• Запустить командную оболочку mongosh, создать пользователя backup с паролем PASSWORD и дать ему роль backup:

1

mongosh --username 'root' --authenticationDatabase 'admin' --eval 'use admin' --eval 'db.createUser({user: "backup", pwd: "PASSWORD", roles: [{role: "backup", db: "admin"}]})'

MariaDB

• Создать пользователя backup@127.0.0.1 с паролем PASSWORD и дать ему разрешения на все базы данных:

1

echo "create user 'backup'@'127.0.0.1' identified by 'PASSWORD'; grant all on *.* to 'backup'@'127.0.0.1'; flush privileges;" | mariadb --user='root' --password

PostgreSQL

• Создать пользователя backup и сделать его супер-пользователем:

1

sudo -u 'postgres' createuser --pwprompt 'backup' && sudo -u 'postgres' psql -c 'alter role backup superuser;'

Настройка

База данных

Общие параметры резервного копирования базы данных.

• DB_SRC - массив названий баз данных для резервного копирования. Название базы данных должно содержать имя DBMS, разделённое точкой (.).
  • mysql - MySQL / MariaDB.
  • pgsql - PostgreSQL.
• DB_DST - директория для хранения дампов баз данных.
• DB_HOST - хост для соединения с DBMS. По умолчанию: 127.0.0.1.
• DB_PORT - порт для соединения с DBMS. По умолчанию: 3306 (mysql) / 5432 (pgsql).
• DB_USER - имя пользователь для соединения с DBMS. По умолчанию: root (mysql) / postgres (pgsql).
• DB_PASS - пароль для соединения с DBMS.

MySQL

Индивидуальные параметры для резервного копирования базы дынных СУБД MySQL/MariaDB.

• MYSQL_ST - добавить опцию --single-transaction при создании резервной копии БД MySQL.
  • 0 - убрать опцию.
  • 1 - добавить опцию (по умолчанию).
• MYSQL_SLT - добавить опцию --skip-lock-tables при создании резервной копии БД MySQL.
  • 0 - убрать опцию.
  • 1 - добавить опцию (по умолчанию).

PostgreSQL

Индивидуальные параметры для резервного копирования базы дынных СУБД PostgreSQL.

• PGSQL_CLN - добавить опцию --clean при создании резервной копии БД PgSQL.
  • 0 - убрать опцию.
  • 1 - добавить опцию (по умолчанию).
• PGSQL_FMT - добавить опцию --format=FORMAT.
  • plain - сформировать текстовый SQL-скрипт. Это поведение по умолчанию.
  • custom - выгрузить данные в специальном архивном формате, пригодном для дальнейшего использования утилитой pg_restore. Наряду с форматом directory является наиболее гибким форматом, позволяющим вручную выбирать и сортировать восстанавливаемые объекты. Вывод в этом формате по умолчанию сжимается.
• PGSQL_IE - добавить опцию --if-exists при создании резервной копии БД PgSQL.
  • 0 - убрать опцию.
  • 1 - добавить опцию (по умолчанию).
• PGSQL_NO - добавить опцию --no-owner при создании резервной копии БД PgSQL.
  • 0 - убрать опцию.
  • 1 - добавить опцию (по умолчанию).
• PGSQL_NP - добавить опцию --no-privileges при создании резервной копии БД PgSQL.
  • 0 - убрать опцию.
  • 1 - добавить опцию (по умолчанию).
• PGSQL_QAI - добавить опцию --quote-all-identifiers при создании резервной копии БД PgSQL.
  • 0 - убрать опцию.
  • 1 - добавить опцию (по умолчанию).

Шифрование

Параметры шифрования резервной копии базы данных.

• ENC_ON - включение / отключение функции шифрования.
  • 0 - шифрование отключено.
  • 1 - шифрование включено.
• ENC_APP - приложение для шифрования.
  • gpg - для шифрования использовать OpenGPG.
  • ssl - для шифрования использовать OpenSSL.
• ENC_PASS - секретная фраза для шифрования.
• ENC_GPG_CIPHER - алгоритм симметричного шифрования GPG. По умолчанию: AES256.
• ENC_GPG_DIGEST - алгоритм хеша для преобразования секретной фразы при симметричном шифровании GPG. По умолчанию: SHA512.
• ENC_GPG_COUNT - указать, сколько циклов преобразования будет проходить секретная фраза для симметричного шифрования GPG. По умолчанию: 65536.
• ENC_SSL_CIPHER - алгоритм симметричного шифрования SSL. По умолчанию: aes-256-cfb.
• ENC_SSL_DIGEST - алгоритм хеша для преобразования секретной фразы при симметричном шифровании SSL. По умолчанию: sha512.
• ENC_SSL_COUNT - указать, сколько циклов преобразования будет проходить секретная фраза для симметричного шифрования SSL. По умолчанию: 65536.

Синхронизация

Параметры синхронизации резервной копии базы данных с удалённым хранилищем (используется rsync).

• SYNC_ON - включение / отключение функции синхронизации.
  • 0 - синхронизация отключена.
  • 1 - синхронизация включена.
• SYNC_HOST - IP-адрес удалённого хранилища для соединения по SSH.
• SYNC_PORT - порт удалённого хранилища для соединения по SSH. По умолчанию: 22.
• SYNC_USER - имя пользователя удалённого хранилища для соединения по SSH. По умолчанию: root.
• SYNC_PASS - пароль пользователя удалённого хранилища для соединения по SSH.
• SYNC_DST - директория удалённого хранилища для синхронизации дампов базы данных.
• SYNC_DEL - параметр добавляет опцию --delete, которая удаляет посторонние файлы на удалённом хранилище.
  • 0 - опция отключена.
  • 1 - опция включена.
• SYNC_RSF - параметр добавляет опцию --remove-source-files, которая удаляет исходные файлы после синхронизации с удалённым хранилищем.
  • 0 - опция отключена.
  • 1 - опция включена.
• SYNC_PED - параметр добавляет опцию --prune-empty-dirs, которая исключает из процесса синхронизации пустые директории.
  • 0 - опция отключена.
  • 1 - опция включена.
• SYNC_CVS - параметр добавляет опцию --cvs-exclude, которая исключает из процесса синхронизации нежелательные элементы, например: CWRCS, SCCS, CVS, CVS.adm, RCSLOG, cvslog.*, tags, TAGS, .make.state, .nse_depinfo, *~, #*, .#*, ,*, _$*, *$, *.old, *.bak, *.BAK, *.orig, *.rej, .del-*, *.a, *.olb, *.o, *.obj, *.so, *.exe, *.Z, *.elc, *.ln, core, .svn/, .git/, .bzr/.
  • 0 - опция отключена.
  • 1 - опция включена.

Дополнительно

Дополнительные параметры скрипта.

• FS_DAYS - дни, по прошествии которых, старые файлы будут удалены. По умолчанию: 30.

Приложение

Приложение забирает параметры из файла настроек и обрабатывает значения.

Задание

Задание запускает скрипт каждый день в 22:15 (после рабочего дня).

Восстановление

Набор команд, описывающий алгоритм восстановления резервной копии базы данных.

GPG

• Расшифровать архив базы данных DB_NAME.xz.gpg при помощи секретной фразы SECRET:

1

f='DB_NAME.xz.gpg'; p='SECRET'; gpg --batch --passphrase "${p}" --output "${f%.*}" --decrypt "${f}"

OpenSSL

• Расшифровать архив базы данных DB_NAME.xz.ssl при помощи секретной фразы SECRET, дайджеста (-md) sha512 и количества итераций (-iter) 65536:

1

f='DB_NAME.xz.ssl'; p='SECRET'; openssl enc -aes-256-cfb -in "${f}" -out "${f%.*}" -pass "pass:${p}" -d -md 'sha512' -iter '65536' -salt -pbkdf2

MongoDB

• Восстановить базу данных DB_NAME из архива DB_NAME.xz:

1

mongorestore --username='root' --authenticationDatabase='admin' --archive='DB_NAME.xz' --nsInclude='DB_NAME.*' --oplogReplay --drop

MariaDB

• Удалить базу данных DB_NAME:

1

echo 'drop database if exists DB_NAME;' | mariadb --user='root' --password

• Создать базу данных DB_NAME с кодировкой utf8mb4 и сопоставлением utf8mb4_unicode_ci:

1

echo "create database if not exists DB_NAME character set 'utf8mb4' collate 'utf8mb4_unicode_ci';" | mariadb --user='root' --password

• Дать все права на базу данных DB_NAME пользователю DB_USER:

1

echo "grant all privileges on DB_NAME.* to 'DB_USER'@'127.0.0.1'; flush privileges;" | mariadb --user='root' --password

• Импортировать данные в новую базу данных DB_NAME из файла DB_NAME.xz:

1

d='DB_NAME'; f="${d}.sql"; xz -d "${f}.xz" && mariadb --user='root' --password --database="${d}" < "${f}"

PostgreSQL

• Удалить базу данных DB_NAME:

1

sudo -u 'postgres' dropdb --if-exists 'DB_NAME'

• Создать базу данных DB_NAME с владельцем DB_USER:

1

sudo -u 'postgres' createdb --owner='DB_USER' 'DB_NAME'

• Восстановить базу данных DB_NAME под пользователем DB_USER из файла DB_NAME.xz:

1

u='DB_USER'; d='DB_NAME'; f="${d}.sql"; xz -d "${f}.xz" && psql --host='127.0.0.1' --port='5432' --username="${u}" --password --dbname="${d}" --file="${f}" --no-psqlrc --single-transaction