Лекция 3. Резервное копирование и восстановление БД. Журналы транзакций

**1.1. Компоненты резервного копирования базы данных.** Следующей ключевой функцией администрирования многопользовательских баз данных является проведение мероприятий по резервному копированию (backup) и восстановлению (restore) баз данных. Данные функции неразрывно связаны с обеспечением безопасности баз данных и сопутствуют жизненному циклу любой базы данных. В контексте этой лекции процедура резервного копирования и восстановления баз данных будет рассмотрена теоретически, а также в контексте инструкций языков tSQL, PostgreSQL, а также для СУБД mongoDB. ***Резервное копирование базы данных*** – это процедура сохранения копии данных на носителе, не являющимся основным местом их хранения. Цель данной процедуры – получить возможность восстановления данных в случае их потери на основном сервере. Технически, для правильного проведения процедур резервного копирования, помимо скриптов, речь о которых пойдет ниже, необходимо иметь фундаментальное представление о структуре файлов баз данных, видах резервных копий и сопровождающей процесс документации. Структура необходимых знаний схематично показана на рис. 1.  Сразу отметим, что файловая структура баз данных и возможные типы резервных копий могут существенно различаться, в зависимости от выбранной СУБД. Принцип функционирования файловой структуры MS SQL Server 2018 будет подробно разобран на практических занятиях, сопровождающих эту лекцию. Ниже будут приведены типы резервного копирования для СУБД MS SQL Server 2018. Типы резервных копий СУБД PostgreSQL и mongoDB будут кратко рассмотрены в части лекции, посвященной скриптам резервного копирования. Для СУБД MS SQL Server 2018 выделяют 3 основных типа резервного копирования баз данных: полная резервная копия базы данных, разностная резервная копия базы данных и резервная копия журналов транзакций. Также, отдельно отметим возможность частичного резервного копирования файлов баз данных. На рис. 2 показано соотношение типов копий по объему данных.  На рисунке самый большой круг (синий), это ***полная резервная копия*** базы данных. Она отображает состояние всей базы данных на момент копирования. Как правило, такая копия весьма внушительна по размеру и процесс ее создания занимает значительное время, создавая сильную нагрузку на сервер баз данных. Копия содержит все данные заданной базы данных и журналы транзакций, которые будут сопровождать процесс восстановления данных в случае необходимости. Второй по размеру круг на рис. 2 (коричневый) – это ***разностная (дифференцированная)*** резервная копия базы данных. Копия этого типа зависит от предварительно созданной полной резервной копии базы данных. Разностная копия содержит в себе только те данные, которые были изменены в базе с момента последнего создания полной резервной копии базы данных. Дело в том, что, как было сказано выше, процесс создания полной резервной копии зачастую бывает весьма объемным, длительным по времени и может сильно нагрузить сервер. Отсюда невозможно регулярно (например, каждый день) создавать полные резервные копии и создаются они, как правило, с промежутком в неделю. Чтобы не потерять массивы данных, которые добавляются или изменяются в базе данных в промежутке между снятием полных копий, применяется разностное копирование. Наконец последний, самый маленький круг на рис. 2 (черный), это ***резервная копия журналов*** транзакций. В эту самую маленькую по объему резервную копию базы данных входят все записи журнала транзакций, которые не вошли во все предыдущие резервные копии журналов. **1.2. Структура журнала транзакции и восстановление баз данных.** Журнал транзакций – один из ключевых элементов СУБД. В этот файл записываются все транзакции, которые были осуществлены в соответствующей СУБД. Именно благодаря этому файлу, который ведется СУБД в автоматическом режиме, сама СУБД сможет определить, когда, где и какие изменения были осуществлены пользователями в базе данных. Это является критически важным при многих системных процедурах СУБД, не исключая процедуры восстановления баз данных. Упрощенная типовая структура журнала транзакций показана на рис. 3.  Идентификатор транзакции – уникальный номер, выдаваемый каждой транзакции СУБД, позволяющий однозначно отличить одну транзакцию от всех остальных. Указатель назад – номер этапа транзакции, на котором осуществляется указанное в данной записи действие. Указатель назад в положении 0 говорит о начале транзакции на этой этапе. Указатель вперед – номер следующего этапа транзакции. Указатель вперед в положении 0 говорит об окончании транзакции на этом этапе. Время – системное время в тот момент, когда было осуществлено действие в рамках транзакции. Тип операции – одна из инструкций, входящих в состав транзакции (BEGIN, INSERT, UPDATE и так далее). Объект – объект базы данных, в котором были внесены изменения транзакцией. Исходный образ – значение до изменения транзакцией. Конечный образ – значение после изменения транзакцией. В табл. 1 показан образец журнала для группы произвольных транзакций.  Логика процесса восстановления данных для случая, описанного в тексте конспекта Лекции 2 показана на рис. 4.  Продавец магазина последовательно, с помощью транзакции заносит данные в таблицы КЛИЕНТ и ПРОДАВЕЦ. При попытке добавить новую строку в таблицу ЗАКАЗ происходит крах системы (перебой питания, переполнение физической или оперативной памяти и так далее). После восстановления системы, процессор восстановления с помощью записей журнала транзакций откатывает изменения, внесенные некорректно законченной транзакцией, возвращая таблицы КЛИЕНТ и ПРОДАВЕЦ в состояние «до транзакции». Транзакцию можно повторить снова, уже с положительным результатом. Ниже приведены основные правила взаимодействия администратора баз данных с журналами транзакций. 1. Правильным решением всегда будет размещать копию журнала транзакций на твердотелый, ленточный или иной быстрый и надежный отдельный накопитель. 2. Ввиду их небольшого размера, резервные копии журналов следует делать по возможности чаще, и точно не реже чем через час. 3. После создания очередной полной резервной копии базы данных, все ранее сохраненные копии журнала транзакций (как и разностные копии) можно и следует удалить. 4. Постоянный мониторинг состояния и наличия свободного места на диске, на котором происходит журналирование и создание резервных копий журнала транзакций. В случае возникновения проблем с журналом транзакций, СУБД потеряет возможность осуществлять дальнейшие транзакции. 5. Добавление в скрипты резервного копирования журналов транзакций функции усечения (SHRINK) для того, чтобы избежать их переполнения (действие опционально). ***1.3. Документация и скрипты резервного копирования.*** Сам процесс резервного копирования в организациях регламентируется соответствующей нормативной документацией. В контексте нашей лекции мы рассмотрим документацию, связанную с анализом параметров сервера баз данных и его окружения, а также план резервного копирования. Исследование параметров функционирования базы данных позволяет определить эффективное время процедуры резервного копирования баз данных, а также усредненную длительность этого процесса и нагрузку на сетевое оборудования. Пример документа «Планируемые характеристики объекта резервного копирования» показан в табл. 2.  Перед составлением плана резервного копирования, в результате коллективной работы пользователей и администраторов баз данных составляется набор бизнес-требований к процедуре резервного копирования баз данных. В этих требованиях, в произвольной форме отмечаются базы данных, которые подлежат резервному копированию, а также периодичность и время снятия резервных копий. Приведем пример фрагмента таких бизнес-требований. 1. Полная копия базы данных Adventure Works должна создаваться один раз в неделю. 2. Разностная копия базы данных Adventure Works делается каждый день. 3. Копии журнала транзакций базы данных Adventure Works делаются каждый час. 4. Копия системной базы данных master делается раз в неделю. 5. Копия системной базы данных msdb делается раз в неделю. 6. Копии с высокой и длительной нагрузкой на сервер баз данных (в первую очередь полная копия базы данных Adventure Works) должны выполняться в нерабочее время офиса организации. Сам документ План резервного копирования регламентирует день, время и частоту регулярных процедур резервного копирования и является базой для автоматизации этого рутинного процесса. Пример документа показан в табл. 3.  В поле описание добавлены дополнительные заметки, которые необходимо учитывать при осуществлении настройки процедуры копирования. В приведенном примере указаны следующие дополнительные указания – необходимость применения функции SHRINK для всех журналов транзакций, необходимость удаления старых резервных копий, регулярная проверка целостности базы данных и согласованности данных. На основании плана резервного копирования настраиваются агенты автоматизации СУБД, после чего обслуживание резервных копий проходит в полуавтоматическом режиме. О принципах автоматизации функций администратора баз данных будет рассказано в одной из последующих лекций курса. Типовые скрипты резервного копирования и восстановления данных для языка tSQL приведены в Рабочей тетради студента по дисциплине «Проектирование и администрирование хранилищ и баз данных, Часть 2» и будут разобраны в ходе курса в рамках соответствующей практической работы. Ниже будут разобраны базовые скрипты резервного копирования и восстановления для СУБД PostgreSQL и MongoDB. Базовым методом резервного копирования PostgreSQL является применение процедуры pg_dump. В ходе своего выполнения, данная процедура соединяется с целевой базой данных и открывает большую транзакцию с настройкой Repeatable Read (см. глава 3), которая считывает все данные с целевой базы данных. Благодаря функции Repeatable Read, на действия транзакции, осуществляющей копирование не сможет повлиять ни одна другая транзакция, параллельно происходящая в базе данных, и итоговый массив данных гарантированно будет согласованным. Результатом работы процедуры pg_dump по умолчанию является простой текстовый документ со всеми данными целевой базы данных. Приведем базовую команду процедуры pg_dump: [linuxpc ~] $ pg_dump -U /пользователь БД/ /копируемая БД/ > /путь к файлу и название файла.sql/ Флаг -U указывает на пользователя, от имени которого будет осуществляться резервное копирование. Если СУБД не требует такого рода проксирования, этот флаг можно опустить. Перечислим иные важные флаги для процедуры pg_dump. -d, --dbname = /название базы данных/, явное указание целевой базы данных. -h, --host = /название хоста/, имя хоста на котором находится целевой сервер баз данных. -p, --port = /номер порта/, указание на порт доступа к БД на стороне хоста. -W, --password, указание на то, что при использовании пользователя необходимо указать его пароль. Также, с помощью флагов можно добиться более гибкой настройки массива копируемых данных. -a: копируются только данные, структура данных не копируется; -s: копируется только структура данных, без данных; -n: копируется только указанная схема данных; -N: копируется все, кроме указанных схем данных; -t: копируются только указанные таблицы; -T: копируется все, кроме указанных таблиц. В случае возникновения необходимости скопировать не какую-то отдельную базу данных, а все содержимое сервера целиком используется процедура pg_dumpall. [linuxpc ~] $ pg_dumpall > /путь к файлу и название файла.sql/ Восстановление базы данных из резервной копии происходит с использованием процедуры pg_restore. При восстановлении следует обратить внимание на то, что процедура может добавлять данные даже в существующие базы данных (возможна ошибка, но технически данные будут добавлены). [linuxpc ~] $ pg_restore -d /целевая база данных/ /путь к файлу и название файла/ Процедура pg_dump хорошо показывает себя на маленьких и средних массивах данных, хранимых в базах. В случае больших массивов данных будет целесообразным рассмотреть возможность настройки репликации базы данных. Об этом будет рассказано в последующих лекциях. Базовым методом резервного копирования документальной СУБД MongoDB является применение процедуры mongo_dump. Приведем базовую команду процедуры mongo_dump: mongodump --host /имя хоста целевой базы данных/ --port /номер порта доступа к серверу/ --username /имя пользователя/ --password /пароль пользователя/ --out /путь к файлу резервной копии/ Для масштабирования резервной копии можно применять флаги --db /имя базы данных для резервной копии/ и --collection /имя коллекции для резервной копии/. Восстановление базы данных из резервной копии происходит с использованием процедуры mongorestore. mongorestore --host /имя хоста/ --port /номер порта доступа к серверу/ --username /имя пользователя/ --password /пароль пользователя/ --out /путь к файлу резервной копии/ ***1.4. Вопросы для самостоятельного изучения по итогам лекции.*** 1. С помощью какой инструкции SQL вызвать просмотр журнала транзакций MS SQL Server? 2. Как настроить инструкцию SQL, чтобы увидеть журнал транзакций, максимально приближенный к образцу на рисунке 17? 3. Составьте таблицу и скрипты резервного копирования для вашего проекта из Курсовой работы по дисциплине Проектирование и администрирование хранилищ и баз данных. 3.5. Тестовые задания для самопроверки. 1. Для получения списка файлов данных и журналов транзакций, входящих в набор резервных копий используется следующий оператор Transact-SQL: А) RESTORE labelOnly FROM Б) RESTORE headerOnly FROM В) RESTORE data FROM Г) RESTORE fileListOnly FROM 2. При применении модели восстановления SIMPLE для восстановления БД минимум необходимо: А) только разностные копии Б) соответствующая полная копия БД и при необходимости разностные копии В) соответствующая полная копия и вся цепочка копий журналов транзакций Г) полный набор копий (полная, разностная, журнал транзакций) 3. Какая из предложенных инструкций установит полную модель восстановления для конкретной БД. А) ALTER DATABASE dbname SET RECOVERY FULL Б) RECOVERY MODEL FULL TO DATABASE dbname В) ALTER DATABASE dbname RECOVERY AS FULL Г) ALTER TABLE dbname SET RECOVERY FULL 4. Какие модели восстановления поддерживает компонент Database Engine? А) простая, полная, с неполным протоколированием Б) простая, сложная, комбинированная В) простая, полная, комбинированная Г) простая, полная, простая с протоколированием 5. Что из перечисленного нельзя реализовать с помощью журнала транзакций? А) восстановление отдельных транзакций Б) восстановление всех незавершенных транзакций В) накат файла, файловой группы Г) все перечисленное реализуемо 6. Усечение журнала транзакций происходит с использованием команды: А) DBCC LOW Б) DBCC SMALL В) DBCC SHRINK Г) DBCC CLEAR 7. Резервная копия, содержащая данные, накопленные «между» полным копированием, называется А) дифференцированная Б) диверсифицированная В) дискриминированная 8. Резервная копия, каждый раз подлежащая ручной настройке со стороны администратора, называется А) файловая копия Б) полная копия В) копия журнала транзакций 9. Последовательность восстановления БД MS SQL Server «по умолчанию»: А) полная копия – файловая копия Б) полная копия – дифференцированная копия В) файловая копия – копия журнала транзакций 10 Обязательно исполнение условия копирования журнала транзакций: А) копирование с периодичностью в 10 минут Б) проверка файла после создания резервной копии В) копирование на отдельный носитель