Задача: есть 30-40 клиентских проектов на разных системах (CMS, фреймворки), с разными базами (mysql в основном, но есть и mongodb и elasticsearch), хотим организовать бэкап с одновременным хранением истории версий. Размещены кто где – есть VPS, есть shared хостинги (как нормальные с ssh, так и экзотика типа хостинга в Швейцарии с древним софтом).

Основные требования, которые мы ставили:

• универсальность, решение не должно быть привязано к конкретной CMS а также к специфическим настройкам хостинга;
• контроль целостности дампов;
• защита от копирования чужих дампов по клиентскому доступу;
• быстрое развертывание на новых проектах

UPD по итогам первых комметов:
Да, мы знаем про такой вариант, но он не подходит. Причины ниже.
ssh -l user remoteserver "mysqldump -u user --password='dbpass' database" > /gitlocalpath/localfile.sql.gz





Почему не код приведенный выше?

1. Есть сайты без ssh, на shared hosting
2. Есть сайты с БД в 1,5Гб. Во первых мы должны передавать по сети такой объем без компрессии — зачем? Плюс обрыв соединения.
3. Есть сайты вообще не с mysql, а например mongodb или elasticsearch.
4. Можно настроить снова-таки зоопарк разных решений под каждый хостинг, но как их админить и сколько времени на настройку нового? (сейчас настройка бэкапа для нового проекта занимает несколько минут)

Если подытожить то образно говоря мы на клиенте запаковали версией скрипта любые данные в архив, зашифровали и отправили. И знаем что сервер это принял и положил в git.

Универсальность также требует, возможность использовать репозиторий любому количеству клиентов, вне зависимости от того где размещены сайты — на хостинге, или собственном сервере. А значит удаленный репозиторий и доступ в него по ssh отпадает. Централизованный сбор баз через серверный mysqldump — тоже отпадает, т.к. не везде есть удаленный доступ к mysql. В качестве универсального решения подходит только клиент-серверная реализация с FTP связью.

Но при этом нужно избежать добавления неполных кусков базы в репозиторий при разрыве соединения и не давать возможности навредить остальным клиентам путем мониторинга директории загрузки и скачивания чужих баз.

Контроль целостности — это просто. Пакуем БД в .gz архив и на серверной стороне проверяем распаковку gzip -t. Если тест прошел — можно обрабатывать и добавлять, если тест провален — файл надо оставить в папке загрузки, т.к. может быть он просто недокачан.

Первая версия системы

Первая версия системы не поддерживала шифрования, а просто переносила файл в директорию за пределами FTP-доступа как только прекращалась запись, и вполне может использоваться в небольших SOHO проектах, т.к. там нет специальных требований и ничего кроме mysqldump и gzip не требуется. Но такая упрощенная защита не годится для широкого применения. И разработка достаточно надежной защиты от копирования потребовала немало интересных часов на обдумывание и тестовые реализации.

Варианты, которые мы рассматривали:

• мгновенное перемещение загруженных файлов за пределы FTP-доступа;
• отдельные папки загрузки под каждого клиента;
• случайно генерируемые папки загрузки;
• шифрование gpg;
• шифрование openssl.

Всё, кроме шифрования openssl, было либо слишком сложно с явными точками отказа, либо добавляло требования к клиентской стороне и противоречило требованию универсальности. Поэтому мы остановились на openssl.

Сохранение базы клиента в git, как это работает в целом

База данных клиента автоматически загружается к нам на сервер и добавляется в репозиторий, обеспечивая тем самым дополнительную резервную копию, легкий доступ к ней, и наглядность внесенных данных с момента последнего бэкапа.

База клиента шифруется одноразовым паролем плюс с определенного url’а, указанного в конфигах, забирается initialization vector, пароль шифруется открытым ключом сервера, зашифрованные файлы передаются на наш сервер, где база распаковывается и добавляется в git. Перехват какого-либо из файлов не дает возможности злоумышленнику расшифровать файл. Даже получив оба файла, их невозможно расшифровать без закрытого ключа сервера и без упомянутого выше IV.

Нет ограничений по клиентскому ПО кроме наличия openssl и любого скрипта, который сможет выполнить операции по дампу и шифрованию, с последующей передачей по FTP. У нас сейчас часть клиентов на sh, а часть на php скриптах

Технические подробности реализации

Система состоит из двух частей.

Клиентская должна забрать initialization vector с определенного url’a, указанного в конфиге, и сгенерировать две случайные переменные, ARCH_NAME — для имени архива и файла с одноразовым паролем, и PASSWD — этот самый одноразовый пароль.

Затем делается дамп базы. Для удобного просмотра изменений, mysqldump надо делать с опцией --skip-extended-insert. Тогда генерируется отдельный оператор insert для каждой строки данных. Без неё все строки таблицы заносятся одним, очень-очень длинным оператором insert, что, естественно, создаёт определённые трудности для, сравнения отличий между двумя dump-ами.

Дамп пакуем в архив, архив ARCH_NAME.tar.gz шифруется паролем PASSWD + полученным с урла IV, при помощи openssl в ARCH_NAME.enc.file, файл с паролем шифруется открытым ключом сервера в ARCH_NAME.otp.key, все это передается на сервер по FTP и удаляется локально.

Серверная с помощью incron проверяет, что попало в папку загрузки

• если .otp.key, то сразу перемещаем за пределы директории загрузки и расшифровываем приватным ключом. Если уже есть соответствующий .enc.file, то запускаем алгоритм распаковки и добавления в локальный репозиторий с последующей выгрузкой в удаленный репозиторий. Если еще нет, то оставляем на 6 часов. Нет никакой причины оставлять именно на 6 часов, это время можно поменять;
• если .enc.file, то пробуем расшифровать и если не можем — оставляем на 6 часов в директории загрузки на тот случай, если он еще докачается;
• если любое другое расширение, то удаляем файл и отправляем уведомление на почтовый ящик администратора (указывается в конфиге), добавляя в него часть журнала FTP-сервера.

Все действия журналируются (см. примеры логов на github). Удаленный bare-репозиторий проекта подключен в наш redmine, что позволяет в удобном виде просматривать список БД и изменений в них.



Таким образом мы получили простую, защищенную систему хранения резервных копий баз данных клиентов, с версионностью и удобным визуальным отображением внутри Redmine. Вместо Redmine можно использовать любое другое GUI решение для Git.

Инструкция по установке и настройке

Сервер

На сервере должен быть настроен git, и FTP-сервер.

Серверный скрипт: https://github.com/evergreen-it-dev/mysql-2-git/blob/master/server/db2repo-server.sh
Серверный конфиг: https://github.com/evergreen-it-dev/mysql-2-git/blob/master/server/db2repo-server.conf.example

1. Для начала нужно создать структуру директорий на сервере
Например

foo
|- tmp
|- complete
|- repo

tmp — домашняя директория фтп-пользователей, сюда загружаются все файлы
complete — недоступная для фтп-пользователей директория, куда перемещаются из tmp файлы ключей и распакованные архивы
repo — собственно директория локального репозитория с деревом файлов

2. Выложить initialization vector в HEX-формате куда-нибудь в веб-доступ, если вам так будет удобно. Нам — удобно, потому что его легко менять и система продолжает работать. В планах есть генерация уникального разового IV для каждого архива. Но если хотите — его можно жестко задать в скриптах.

3. После того как создана структура директорий, надо установить (или настроить, если он уже установлен) incron (смотрите руководство для своей операционной системы) и настроить в нем мониторинг tmp вот такой записью
/foo/tmp IN_CLOSE_WRITE /path/to/db2repo-server.sh $#
Таким образом как только открытый для записи файл в /foo/tmp был закрыт, выполнится db2repo-server.sh. Обратите внимание на $# — это имя файла с которым произошло наблюдаемое событие. Очень нужная штука в процессе распаковки и добавления в репозиторий!

4. Ну и напоследок – указать параметры в db2repo-server.conf и убедится, что в db2repo-server.sh указан правильный путь к конфигу напротив source.

Параметры которые надо указать в db2repo-server.conf
TEMP_DIR — это tmp из описанной выше структуры
UPLOADED_DIR — это complete из описанной выше структуры
REPO_DIR — это repo из описанной выше структуры
BARE_REPO — удаленный репозиторий в формате user@server:repo.git
PRIVATE_KEY — путь к приватному ключу id_rsa для распаковки архива с одноразовым паролем
MAIN_LOG — главный логфайл сервера, хранится постоянно
TEMP_LOG — временный логфайл процесса распаковки и добавления в репозиторий, используется для отправки на почту
INCRON_FILE_NAME — не трогать, смотреть документацию по incron если интересно что это
FTP_LOG — путь к лог файлу фтп, используется для отправки уведомлений, если в temp_dir загрузили что-то странное
MAIL_ADDR — почта для отправки уведомлений

5. chmod +x db2repo-server.sh и готово.

6. Если вы хотите централизовано собирать бэкапа клиентских баз данных, создайте скрипт db-collector.sh, который по очереди вызывает скрипты на клиентских серверах, пример файла:

#!/bin/bash
echo "Project1"
sshpass -p 'superpass'  ssh -p 3389  user@google.com '/path/to/script/db2repo-client.sh'
sleep 60
echo "Project2 - mysql"
ssh user@gov.ua '/path/to/script/db-mysql-2repo-client.sh'
sleep 60
echo "Project2 - elastic"
ssh user@gov.ua '/path/to/script/db-elstcsrch-2repo-client.sh'
sleep 60
echo "Project3 via php script"
wget  -qO --no-check-certificate https://site.us/dmpr/index.php?key=secretpass &>/dev/null
sleep 60
....

Клиент

На клиентском сервере нужно сделать дамп базы, зашифровать openssl и отправить на ftp, соответственно на клиентской стороне должны быть инструменты для этого, чтобы клиентский скрипт выполнялся.

Для хостингов без ssh-доступа мы разработали php-вариант скрипта, напишем в следующей части статьи.

Клиентский скрипт: https://github.com/evergreen-it-dev/mysql-2-git/blob/master/client/db2repo-client.sh
Клиентский конфиг: https://github.com/evergreen-it-dev/mysql-2-git/blob/master/client/db2repo-client.conf.example

1. Переименовать db2repo-client.conf.example в db2repo-client.conf и разместить на клиентском сервере.

2. В файле db2repo-client.conf заполнить параметры:

DB_CONFIG — путь к конфигурационному файлу сайта (зачем это надо — см. ниже)
SOURCE_DIR — папка где будет происходить таинство дампа и шифрования
DUMP_NAME — имя файла дампа БД, предлагаемый шаблон содержит: имя сайта — тип базы данных (mysql\mongo\elastic\etc) — имя базы — тип сайта (прод\препрод\тест\и т.д.)
MIN_DUMP_SIZE — минимальный размер файла дампа в Мб, если получившийся дамп будет меньше этого размера, то процесс прервется и вы получите уведомление на почту
BACKUP_NAME — ЧПУ имя этого бекапа, используется в теме уведомления о слишком маленьком размере файла дампа
PUB_KEY — путь к файлу открытого ключа бекап-сервера
IV_URL — ссылка на страницу с initialization vector в HEX-формате, если у вас не используется — просто удалите эту переменную
IV — переменная использующаяся при шифровании, если вы не будете использовать IV_URL, то просто укажите initialization vector в HEX-формате здесь
MAIL_TO — почтовый адрес для алерта о слишком маленьком размере файла дампа
TEMP_LOG — временный лог процесса (я знаю что эта переменная практически не используется в клиентском скрипте и переехала туда ХЗ откуда, но возможно я добавлю больше журналирования, поэтому пусть будет)
FTP_USER, FTP_PASS, FTP_HOST — параметры FTP

Переменные ниже вполне очевидны сами по себе, но могут вызвать недоумение их предлагаемые в conf.example значения.

Дело в том, что в скриптах бекапа баз принципиально стараемся не использовать жестко заданные пароли. Намного надежнее получать их из конфигов сайта или /etc/mysql/debian.cnf. Поэтому вы можете пойти нашим путем и подобрать парсер параметров, или просто вбить имя базы, пользователя и пароль в клиентский скрипт db2repo-client.conf и удалить переменную DB_CONFIG
DB_NAME
DB_USER
DB_PASS
А где же DB_HOST? Его нету, потому что в большинстве случаев он не нужен, а если понадобится — его легко добавить прямо в клиентский скрипт db2repo-client.sh. Возможно в будущем добавим эту переменную в конфиг.

3. После того как вы указали все переменные в db2repo-client.conf, надо в файле db2repo-client.sh прописать путь к файлу конфига во второй строчке, напротив source

4. И в принципе — все, можно делать chmod +x db2repo-client.sh и запускать

В результате, на бекап-сервере в папке /foo/tmp/ должны появиться два файла: зашифрованный архив с базой, и зашифрованный ключом пароль к архиву. Правда, если вы уже настроили incron и db2repo-server, то появятся они там очень ненадолго, и сразу же будут перемещены\распакованы в /foo/complete/, а еще через пару секунд, единственным признаком завершившегося процесса, будет файл базы в /foo/repo/

Как писалось выше, для хостингов без ssh мы разработали php-скрипт бекапа совместимого с серверным скриптом – опишем его в следующей части саги.



Пользуйтесь, если это также вам нужно! И буду рад вашим комментариям и идеям.