• Главная
  •  > 
  • Сеть и интернет
  •  > 
  • Резервное копирование mysql базы данных. Разбираемся с утилитами для бэкапа баз данных

Резервное копирование mysql базы данных. Разбираемся с утилитами для бэкапа баз данных

1 февраля 2012 в 00:33

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

  • MySQL

Резервное копирование базы данных - это такая штука, которую вечно приходится настраивать для уже работающих проектов прямо на «живых» production-серверах.
Подобная ситуация легко объяснима. В самом начале любой проект еще пуст и там просто нечего копировать. В фазе бурного развития головы немногочисленных разработчиков заняты исключительно прикручиванием фишек и рюшек, а также фиксом критических багов с дедлайном «позавчера». И только когда проект «взлетит», приходит осознание, что главная ценность системы - это накопленная база данных, и её сбой станет катастрофой.
Эта обзорная статья - для тех, чьи проекты уже достигли этой точки, но жареный петух ещё не клюнул.

1. Копирование файлов базы

Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки /var/lib/mysql/db/ . Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы, который может затянуться на часы.

В большинстве «живых» проектов регулярное выключение сервера БД на длительное время неприемлемо. Для решения этой проблемы применяется трюк, основанный на снэпшотах файловой системы. Снэпшот - это что-то вроде «фотографии» файловой системы на определенный момент времени, сделанный без реального копирования данных (и потому быстро). Аналогичным образом работает «ленивое копирование» объектов во многих современных языках программирования.
Общая схема действий такова: блокируются все таблицы, сбрасывается файловый кэш БД, делается снэпшот файловой системы, разблокируются таблицы. После этого файлы спокойно копируются из снэпшота, после чего он уничтожается. «Блокирующая» часть такого процесса занимает время порядка секунд, что уже терпимо. В качестве расплаты на какое-то время, пока «жив» снэпшот, снижается производительность файловых операций, что в первую очередь бьет по скорости операций записи в базу.

Некоторые файловые системы, например, ZFS, поддерживают снятие снэпшотов нативно. Если вы не пользуетесь ZFS, но на вашем сервере стоит менеджер томов LVM, вы также сможете скопировать базу MySQL через снэпшот . Наконец, под *nix можно воспользоваться драйвером снэпшотов R1Soft Hot Copy , но этот способ не заработает в контейнере openvz ().

Для баз MyISAM существует официальная бесплатная утилита mysqlhotcopy , которая «правильно» копирует файлы баз MyISAM без остановки сервера. Существует аналогичная утилита для InnoDB , но она платная, хотя и возможностей в ней больше.

Копирование файлов - самый быстрый способ перебросить базу данных целиком с одного сервера на другой.

2. Копирование через текстовые файлы

Для того, чтобы считать в бэкап данные из production-базы, необязательно дергать файлы. Можно выбрать данные запросом и сохранить их в текстовый файл. Для этого используется SQL-команда SELECT INTO OUTFILE и парная ей LOAD DATA INFILE . Выгрузка производится построчно (можно отобрать для сохранения только нужные строки, как в обычном SELECT). Структура таблиц нигде не указывается - об этом должен заботиться программист. Он также должен позаботиться о включении команд SELECT INTO OUTFILE в транзакцию, если это необходимо для обеспечения целостности данных. На практике SELECT INTO OUTFILE используется для частичного бэкапа очень больших таблиц, которые нельзя скопировать никаким другим образом.

В большинстве случаев намного более удобна созданная Игорем Романенко утилита mysqldump . Утилита mysqldump формирует файл, содержащий все SQL-команды, необходимые для полного восстановления БД на другом сервере. Отдельными опциями можно добиться совместимости этого файла с практически любой СУБД (не только MySQL), кроме того, существует возможность выгрузки данных в форматах CSV и XML. Для восстановления данных из таких форматов существует утилита mysqlimport .

Утилита mysqldump консольная. Существуют её надстройки и аналоги, позволяющие управлять бэкапом через веб-интерфейс, например, украинская тулза Sypex Dumper (их представитель есть на хабре).

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

3. Инкрементные бэкапы

Традиционно рекомендуют держать 10 бэкапов: по одному на каждый день недели, а также бэкапы двухнедельной, месячной и квартальной давности - это позволит достаточно глубоко откатиться в случае порчи каких-либо данных.
Храниться бэкапы должны точно не на том же диске, что и живая база, и не на том же сервере. На случай пожаров и прочих катаклизмов лучше всего арендовать пару юнитов в соседнем дата-центре.

Эти требования могут стать проблемой для больших баз. Прокачка бэкапа 100-гигабайтной базы по 100-мбитной сети займет часа три, на которые полностью забьет канал.
Частично решить эту проблему позволяют инкрементные бэкапы, когда полный бэкап делается, скажем, только по воскресеньям, а в остальные дни пишутся только данные, добавленные или измененные за прошедшие сутки. Сложность в том, как выявить эти самые «данные, изменившиеся за сутки».

Здесь практически вне конкуренции система Percona XtraBackup , которая содержит модифицированный движок InnoDB, анализирует двоичные логи MySQL и вытаскивает из них необходимую информацию. Почти такими же возможностями обладает платная InnoDB Hot Backup, упомянутая выше.

Общая проблема с любыми бэкапами в том, что они всегда отстают. В случае фатального сбоя основного сервера восстановить систему можно будет только с некоторым «откатом» по времени, что очень и очень разочарует её пользователей. Если в системе так или иначе были затронуты финансовые потоки, подобный «откат» может в прямом смысле влететь в копеечку.

4. Репликация

Избежать откатов призвана система репликации MySQL. Идея репликации основана на том, что кроме «главного» сервера («Мастера») постоянно работают ведомые сервера MySQL («слейвы»), которые получают инкрементные бэкапы с мастера в режиме реального времени. Таким образом, время отката уменьшается почти до сетевого лага. В случае краха Мастера можно оперативно назначить «новым Мастером» один из слейвов и перенаправить клиентов на него. Кроме того, слейвы могут обрабатывать запросы на чтение данных (SELECT-ы); это можно использовать для выполнения каких-то расчетов или снижения нагрузки на мастера. MySQL поддерживает репликацию «из коробки», процесс хорошо описан юзером

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

Виды бэкапов баз данных

Для начала разберемся с тем, какие вообще бывают бэкапы. Сервер баз данных не является обычным десктопным приложением, и, чтобы обеспечить выполнение всех свойств ACID (Atomic, Consistency, Isolated, Durable), используется ряд технологий, а поэтому создание и восстановление БД из архива имеет свои особенности. Существуют три различных подхода к резервному копированию данных, каждый из которых имеет свои плюсы и минусы.

При логическом, или SQL, бэкапе (pg_dump, mysqldump, SQLCMD) создается мгновенный снимок содержимого базы с учетом транзакционной целостности и сохраняется в виде файла с SQL-командами (можно выбрать всю базу или отдельные таблицы), при помощи которого можно воссоздать базу данных на другом сервере. На это требуется время (особенно для больших баз) для сохранения и восстановления, поэтому очень часто эту операцию выполнять нельзя и ее производят во время минимальной нагрузки (например, ночью). При восстановлении администратору необходимо будет выполнить несколько команд, чтобы подготовить все необходимое (создать пустую базу данных, учетные записи и прочее).

Физический бэкап (уровня файловой системы) - копирование файлов, которые СУБД использует для хранения данных в базе данных. Но при простом копировании игнорируются блокировки и транзакции, которые, скорее всего, будут неправильно сохранены и нарушены. При попытке присоединить этот файл он будет в несогласованном состоянии и приведет к ошибкам. Чтобы получить актуальный бэкап, базу данных нужно остановить (можно уменьшить время простоя, использовав два раза rsync - вначале на работающей, потом на остановленной). Недостаток этого метода очевиден - нельзя восстановить определенные данные, только всю базу данных. При старте БД, восстановленной из архива файловой системы, нужно будет провести проверку на целостность. Здесь используются разные вспомогательные технологии. Например, в PostgreSQL логи упреждающей журнализации WAL (Write Ahead Logs) и специальная функция (Point in Time Recovery - PITR), позволяющая вернуться к определенному состоянию базы. С их помощью легко реализуется третий сценарий, когда бэкап уровня файловой системы объединяется с резервной копией WAL-файлов. Вначале восстанавливаем файлы резервной копии файловой системы, а затем при помощи WAL база приводится к актуальному состоянию. Это чуть более сложный подход для администрирования, но зато нет проблем с целостностью БД и восстановлением баз до определенного времени.

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

Barman

Лицензия: GNU GPL

Поддерживаемые СУБД: PostgreSQL

PostgreSQL поддерживает возможности физического и логического бэкапа, добавляя к ним еще один уровень WAL (см. врезку), который можно назвать непрерывным копированием. Но управлять при помощи штатных инструментов несколькими серверами не очень удобно даже админу со стажем, а в случае сбоя счет идет на секунды.

Barman (backup and recovery manager) - внутренняя разработка компании 2ndQuadrant, предоставляющей услуги на базе PostgreSQL. Предназначен для физического бэкапа PostgreSQL (логический не поддерживает), архивирования WAL и быстрого восстановления после сбоев. Поддерживаются удаленный бэкап и восстановление нескольких серверов, функции point-in-time-recovery (PITR), управление WAL. Для копирования и подачи команд на удаленный узел используется SSH, синхронизация и бэкап при помощи rsync позволяет сократить трафик. Также Barman интегрируется со стандартными утилитами bzip2, gzip, tar и подобными. В принципе, можно использовать любую программу сжатия и архивирования, интеграция не займет много времени. Реализованы различные сервисные и диагностические функции, позволяющие контролировать состояние сервисов и регулировать полосу пропускания. Поддерживаются Pre/Post-скрипты.

Barman написан на Python, управление политиками резервного копирования производится при помощи понятного INI-файла barman.conf, который может находиться в /etc или домашнем каталоге пользователя. В поставке идет готовый шаблон с подробными комментариями внутри. Работает только на *nix-системах. Для установки в RHEL, CentOS и Scientific Linux следует подключить EPEL - репозиторий, в котором содержатся дополнительные пакеты. В распоряжении пользователей Debian/Ubuntu официальный репозиторий:

$ sudo apt-get install barman

В репозитории не всегда последняя версия, для ее установки придется обратиться к исходным текстам. Зависимостей немного, и разобраться в процессе несложно.

Sypex Dumper

Лицензия: BSD

Поддерживаемые СУБД: MySQL

Вместе с MySQL поставляются утилиты mysqldump, mysqlhotcopy, позволяющие легко создать дамп базы данных, они хорошо документированы, и в интернете можно найти большое количество готовых примеров и фронтендов. Последние позволяют новичку быстро приступить к работе. Sypex Dumper представляет собой PHP-скрипт, позволяющий легко создать и восстановить копию базы данных MySQL. Создавался для работы с большими базами данных, работает очень быстро, понятен и удобен в использовании. Умеет работать с объектами MySQL - представлениями, процедурами, функциями, триггерами и событиями.

Еще один плюс, в отличие от других инструментов, при экспорте производящих перекодирование в UTF-8, - в Dumper экспорт производится в родной кодировке. Результирующий файл занимает меньше места, а сам процесс происходит быстрее. В одном дампе могут быть объекты с разными кодировками. Причем легко импорт/экспорт произвести в несколько этапов, останавливая процесс во время нагрузки. При возобновлении процедура начнется с места остановки. При восстановлении поддерживается четыре варианта:

  • CREATE + INSERT - стандартный режим восстановления;
  • TRUNCATE + INSERT - меньше времени на создание таблиц;
  • REPLACE - восстанавливаем в рабочей базе старые данные, не затирая новые;
  • INSERT IGNORE - добавляем в базу удаленные или новые данные, не трогая существующие.

Поддерживается сжатие копии (gzip или bzip2), автоудаление старых бэкапов, реализован просмотр содержимого дамп-файла, восстановление только структуры таблиц. Имеются и сервисные функции по управлению БД (создание, удаление, проверка, восстановление БД, оптимизация, очистка таблиц, работа с индексами и другое), а также файл-менеджер, позволяющий копировать файлы на сервер.


Управление производится при помощи веб-браузера, интерфейс с использование AJAX локализован из коробки и создает впечатление работы с настольным приложением. Также возможно запускать задания из консоли и по расписанию (через cron).

Для работы Dumper понадобится классический L|WAMP-сервер, установка обычная для всех приложений, написанных на PHP (копируем файлы и устанавливаем права), и не будет сложной даже для новичка. Проект предоставляет подробную документацию и видеоуроки, демонстрирующие работу с Sypex Dumper.

Есть две редакции: Sypex Dumper (бесплатно) и Pro (10 долларов). Вторая имеет больше возможностей, все отличия приведены на сайте.

SQL Backup And FTP

Лицензия:

Поддерживаемые СУБД: MS SQL Server

MS SQL Server - одно из популярных решений, а потому встречается достаточно часто. Задание резервного копирования создается при помощи среды SQL Server Management Studio, собственно Transact-SQL и командлетов модуля SQL PowerShell (Backup-SqlDatabase). На сайте MS можно найти просто огромное количество документации, которая позволяет разобраться с процессом. Документация хотя и полная, но очень специфическая, а информация в интернете часто противоречит друг другу. Новичку действительно вначале потребуется потренироваться, «набив руку», поэтому, даже несмотря на все сказанное, сторонним разработчикам есть где развернуться. К тому же бесплатная версия SQL Server Express не может похвастаться встроенными инструментами для резервного копирования. Для более ранних версий MS SQL (до 2008) можно найти бесплатные утилиты, например SQL Server backup , но в большинстве подобные проекты уже коммерциализировались, хотя и предлагают всю функциональность часто за символическую сумму.


Например, разработка SQL Backup And FTP и One-Click SQL Restore соответствует принципу «настроил и забыл». Обладая очень простым и понятным интерфейсом, они позволяют создавать копии баз данных MS SQL Server (включая Express) и Azure, сохранять зашифрованные и сжатые файлы на FTP и облачных сервисах (Dropbox, Box, Google Drive, MS SkyDrive или Amazon S3), результат можно тут же просмотреть. Возможен запуск процесса как вручную, так и по расписанию, отправка сообщения о результате задания по email, запуск пользовательских скриптов.

Поддерживаются все варианты бэкапа: полный, дифференциальный, журнал транзакций, копирование папки с файлами и многое другое. Старые резервные копии удаляются автоматически. Для подключения к виртуальному узлу используется SQL Management Studio, хотя здесь могут быть нюансы и это будет работать не во всех таких конфигурациях. Для загрузки предлагается пять версий - от бесплатной Free до навороченной Prof Lifetime (на момент написания этих строк стоила всего 149 долларов). Функционала Free вполне достаточно для небольших сетей, в которых установлено один-два SQL-сервера, все основные функции активны. Ограничено количество резервных БД, возможность отправки файлов на Google Drive и SkyDrive и шифрование файлов. Интерфейс хотя и не локализован, но очень прост и понятен даже новичку. Нужно лишь подключиться к SQL-серверу, после чего будет выведен список баз данных, следует отметить нужные, настроить доступ к удаленным ресурсам и указать время выполнения задания. И все это в одном окне.

Но есть одно «но». Сама программа не предназначена для восстановления архивов. Для этого предлагается отдельная бесплатная утилита One-Click SQL Restore, понимающая и формат, созданный командой BACKUP DATABASE. Админу необходимо лишь указать архив и сервер, на который восстановить данные, и нажать одну кнопку. Но в более сложных сценариях придется использовать RESTORE.


Особенности бэкапа MS SQL Server

Создание резервной копии и восстановление СУБД имеет свои отличия, которые нужно учитывать, особенно их много при переносе архива на другой сервер. Для примера разберем некоторые нюансы MS SQL Server. Для архивирования при помощи Transact-SQL следует использовать команду BACKUP DATABASE (есть и разностная DIFFERENTIAL) и журнал транзакций BACKUP LOG.

Если резервная копия разворачивается на другом сервере, нужно убедиться, что присутствуют те же самые логические диски. Как вариант - можно вручную прописать правильные пути для файлов базы данных, используя опцию WITH MOVE команды RESTORE DATABASE.

Простая ситуация - бэкап и перенос баз на другие версии SQL Server. Эта операция поддерживается, но в случае SQL Server будет работать, если версия сервера, на которой разворачивается копия, такая же или новее, чем та, на которой она создавалась. Причем есть ограничение: новее не более чем на две версии. После восстановления БД будет находиться в режиме совместимости с той версией, с которой осуществлялся переход, то есть новые функции будут недоступны. Это легко поправить, изменив COMPATIBILITY_LEVEL. Можно это сделать при помощи GUI или SQL.

ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110;

Определить, на какой версии была создана копия, можно, просмотрев заголовок файла архива. Чтобы не экспериментировать, при переходе на новую версию SQL Server следует запустить бесплатную утилиту Microsoft Upgrade Advisor.

Iperius

Лицензия: коммерческая, есть версия Free

Поддерживаемые СУБД: Oracle 9–11, XE, MySQL, MariaDB, PostgreSQL и MS SQL Server

Когда приходится управлять несколькими типами СУБД, без комбайнов не обойтись. Выбор большой. Например, Iperius - легкая, очень простая в использовании и одновременная мощная программа для резервного копирования файлов, имеющая функцию горячего резервирования баз данных без прерывания в работе или блокирования. Обеспечивает полный или инкрементальный бэкап. Может создавать полные образы дисков для автоматической переустановки всей системы. Поддерживает резервное копирование на NAS, USB-устройства, стример, FTP/FTPS, Google Drive, Dropbox и SkyDrive. Поддерживает сжатие zip без ограничения в размере файлов и AES256-шифрование, запуск внешних скриптов и программ. Включает весьма функциональный планировщик заданий, возможно параллельное или последовательное выполнение нескольких заданий, результат отправляется на email. Поддерживаются многочисленные фильтры, переменные для персонализации путей и настроек.

Возможность закачки по FTP позволяет легко обновлять информацию на нескольких веб-сайтах. Открытые файлы резервируются при помощи технологии VSS (теневого копирования томов), что позволяет производить горячий бэкап не только файлов СУБД, но и других приложений. Для Oracle также задействуется средство организации резервного копирования и восстановления данных RMAN (Recovery Manager). Чтобы не перегружать канал, есть возможность настройки полосы пропускания. Управление резервированием и восстановлением производится при помощи локальной и веб-консоли. Все функции на виду, поэтому для настройки задания потребуется лишь понимание процесса, в документацию заглядывать даже не придется. Просто следуем подсказкам мастера. Также можно отметить менеджер учетных записей, что очень удобно при большом количестве систем.

Базовые функции предлагаются бесплатно, но возможность резервирования БД заложена только в версиях Advanced DB и Full. Поддерживается установка от XP до Windows Server 2012.

Handy Backup

Лицензия: коммерческая

Поддерживаемые СУБД: Oracle, MySQL, IBM DB2 (7–9.5) и MS SQL Server

Одна из самых мощных систем управления реляционными базами данных - IBM DB2, имеющая уникальные функции по масштабированию и поддерживающая множество платформ. Поставляется в нескольких редакциях, которые построены на одной базе и отличаются функционально. Архитектура баз данных DB2 позволяет управлять практически всеми типами данных: документами, XML, медиафайлами и так далее. Особо популярна бесплатная DB2 Express-C. Бэкап очень прост:

Db2 backup db sample

Или снапшот, использующий функцию Advanced Copy Services (ACS):

Db2 backup db sample use snapshot

Но нужно помнить, что в случае снимков мы не можем восстанавливать (db2 recover db) отдельные таблицы. Есть и возможности по автоматическому бэкапу, и многое другое. Продукты хорошо документированы, хотя в русскоязычном интернете руководства встречаются редко. Также далеко не во всех специальных решениях можно найти поддержку DB2.

Например, Handy Backup позволяет выполнять бэкап нескольких типов серверов баз данных и сохранять файлы практически на любой носитель (жесткий диск, CD/DVD, облачное и сетевое хранилище, FTP/S, WebDAV и другие). Возможен бэкап баз данных через ODBC (только таблицы). Это одно из немногих решений, поддерживающих DB2, и к тому же имеет логотип «Ready for IBM DB2 Data Server Software». Вся процедура выполняется при помощи обычного мастера, в котором необходимо лишь выбрать нужный пункт и сформировать задачу. Сам процесс настройки настолько прост, что разобраться сможет и новичок. Можно создать несколько заданий, которые будут запускаться по расписанию. Результат фиксируется в журнале и отправляется по email. Во время работы задания остановка сервиса не требуется. Архив автоматически сжимается и шифруется, что гарантирует его безопасность.

Работу с DB2 поддерживают две версии Handy Backup - Office Expert (локальный) и Server Network (сетевой). Работает на компьютерах под управлением Win8/7/Vista/XP или 2012/2008/2003. Сам процесс развертывания несложен для любого админа.

Как вы знаете, все важные данные ваших сайтов, их настройки, статьи, комментарии и другая информация хранятся в базе данных. Потеря этой информации может иметь очень тяжелые последствия для проекта. Поэтому важно своевременно делать резервные копии базы данных mysql. Также эти копии могут быть очень полезными при переносе проекта на другой сервер.

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

Все что вам нужно для резервного копирования mysql - это доступ к серверу с операционной системой Linux, на котором установлен сервер баз данных, а также имя базы данных и параметры доступа к ней.

Для экспорта информации из базы данных в формате SQL можно использовать утилиту mysqldump. Вот ее синтаксис:

$ mysqldump опции имя_базы [имя_таблицы] > файл.sql

По умолчанию утилита будет выводить все в стандартный вывод, поэтому нам нужно перенаправить эти данные в файл, что мы и делаем с помощью оператора ">". Опции указывают параметры аутентификации и работы, а имя базы и таблицы - данные которые нужно экспортировать. Теперь рассмотрим кратко опции, которые будем использовать:

  • -A - копировать все таблицы из всех баз данных;
  • -i - записывать дополнительную информацию в комментариях;
  • -c - использовать имена колонок для инструкции INSERT;
  • -a - включать все возможные опции в инструкцию CREATE TABLE;
  • -k - отключает первичные ключи на время копирования;
  • -e - использовать многострочный вариант инструкции INSERT;
  • -f - продолжить даже после ошибки;
  • -h - имя хоста, на котором расположен сервер баз данных, по умолчанию localhost;
  • -n - не писать инструкции для создания базы данных;
  • -t - не писать инструкции для создания таблиц;
  • -d - не записывать данные таблиц, а только их структуру;
  • -p - пароль базы данных;
  • -P - порт сервера баз данных;
  • -Q - брать все имена таблиц, баз данных, полей в кавычки;
  • -X - использовать синтаксис XML вместо SQL;
  • -u - пользователь, от имени которого нужно подключаться к базе данных.

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

mysqldump -u имя_пользователя -p имя_базы > data-dump.sql

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

head -n 5 data-dump.sql

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

mysqldump -h хост -P порт -u имя_пользователя -p имя_базы > data-dump.sql

Копирование таблицы mysql может быть выполнено простым добавлением имени таблицы в конец строки:

mysqldump -u имя_пользователя -p имя_базы имя_таблицы > data-dump.sql

Также, чтобы выполнять автоматическое резервное копирование базы mysql может понадобиться сразу задать пароль, для этого указывайте его сразу после опции -p, без пробела:

mysqldump -u имя_пользователя -pпароль имя_базы > data-dump.sql

Мы можем делать бэкап вручную время от времени, но это не совсем удобно, поскольку есть другие важные дела. Поэтому используем планировщик cron, чтобы автоматизировать процесс. Тут есть два способа более простой, и более сложный, но точный. Допустим, нам нужно создавать резервную копию каждый день, тогда просто создайте скрипт в папке /etc/cron.daily/ со следующим содержимым:

sudo vi /etc/cron.daily/mysql-backup

!/bin/bash
/usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql

Папку /backups/mysql-dump.sql нужно заменить на свою папку для резервных копий. Осталось дать скрипту права на выполнение:

chmod ugo+x /etc/cron.daily/mysql-backup

Добавьте в открывшейся файл такую строку и сохраните изменения:

30 2 * * * /usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql

Команда будет выполняться каждый день, в 2:30, это удобно, поскольку ночью обычно меньше нагрузка на сервер. Как вы поняли, первое число - это минуты, второе - часы, третье день, дальше неделя и месяц. Звездочка значит, что этот параметр не имеет значения.

Восстановление из резервной копии

Восстановить резервную копию mysql или mariadb из существующего SQL файла тоже очень просто. Поскольку использовался синтаксис sql мы просто можем выполнить все команды с помощью стандартного клиента mysql.

Сначала нужно создать новую базу данных. Для этого авторизуйтесь на mysql сервере с правами суперпльзователя:

mysql -u root -p

Затем создайте новую базу данных, например, с именем new_database, если база данных уже существует, то этого делать не нужно:

mysql> CREATE DATABASE new_database;

mysql -u пользователь -p база_данных < data-dump.sql

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

Выводы

Теперь вы знаете как выполняется копирование базы данных mysql, а также как восстановить скопированную информацию. Мы рассмотрели все возможные опции mysqldump чтобы вы могли настроить утилиту так, как вам нужно. Резервное копирование базы данных mysql это очень важный момент и в определенной ситуации может сохранить много времени, поэтому обязательно настройте у себя на сервере!

Можно выделить 5 основных причин потери данных:

  1. Программные ошибки — Возникновение условий, приводящих к аварийному завершению системы. Поскольку такие ошибки основываются на дефектах программной логики, система баз данных не может выполнить восстановление в подобных ситуациях. Поэтому восстановление должен проводить сам программист, выполнив обработку таких исключений.
  2. Ошибки администратора (человеческий фактор) — Случаи, в которых пользователь с большими полномочиями может неумышленно (или умышленно) разрушить данные. Необходимо постараться создать такой режим работы, который сделает подобную ситуацию маловероятной, однако совсем исключать такую возможность нельзя.
  3. Выход из строя компьютера (сбой системы) — Возникает в результате ошибок в оборудовании и программном обеспечении. В этом случае содержимое оперативной памяти компьютера может быть потерянно. В качестве защиты, можно рекомендовать использование резервного сервера, зеркальное отображение баз данных и пр.
  4. Отказ дискового накопителя — Физическое разрушение жесткого диска. Рекомендуется использование технологий RAID для хранения файлов баз данных, кроме того необходимо, чтобы файлы резервных копий хранились на дисковом носителе, отличным от устройства, на котором располагаются файлы баз данных.
  5. Катастрофы (пожар, наводнение, землетрясение) или кража — Обойти эту ситуацию станет возможным, если устройство, содержащее необходимую для восстановления данных информацию, будет храниться отдельно от основного оборудования и не будет потеряно в результате катастроф или краж.

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

2. Типы резервного копирования

Существует 2 режима создания резервных копий:

  1. Статическое резервное копирование — режим, при котором в процессе создания копии только одна активная сессия, поддерживаемая системой, является той сессией, которая создает резервную копию. Другие пользовательские процессы во время выполнения копирования недопустимы.
  2. Динамическое резервное копирование — режим, при котором копирование баз данных может выполняться без остановки сервера баз данных, удаления пользователей или даже закрытия файлов. Пользователи могут и не знать, что выполняется процесс резервного копирования.

MS SQL Server поддерживает оба режима создания резервных копий.

3. Модели восстановления баз данных

Выбор модели восстановления базы данных определяет объем данных, который может быть потерян во время разрушения базы данных, а также скорость использования, размер резервной копии протокола транзакций и период времени, необходимый для резервного копирования протокола. MS SQL Server поддерживает три модели восстановления:

  1. Полная модель восстановления — модель, при которой все операции записываются в протокол транзакций. Поэтому эта модель предоставляет полную защиту против сбоев внешних устройств.
    • Преимущества:
      1. Есть возможность восстановить базу данных с последней подтвержденной транзакции, которая была сохранена в файле протокола.
      2. Возможно восстановить данные на любой момент времени.
      3. Возможно восстановить данные на отметку в протоколе. Отметки в протоколе соответствуют заданной транзакции и добавляются только если эта транзакция подтверждается.
      4. Протоколируются все операции, связанные с изменением индекса. В этом случае пересоздание индекса выполняется быстрее, потому что не надо создавать их заново.
    • Недостатки:
      1. Соответствующий протокол транзакций может быть очень большим по объему, и файлы на диске, содержащие этот протокол, могут увеличиваться в размере очень быстро. В связи с этим возможно заполнение протокола транзакций.
      2. Протокол транзакций должен быть защищен от сбоев внешних устройств. По этой причине использование технологии RAID для защиты протокола транзакций строго рекомендуется.
      3. Требуется значительно больше времени на резервное копирование.
    • Заключение:
      Данная модель восстановления рекомендована для производственных баз данных, если позволяет аппаратная часть сервера баз данных.
  2. Модель восстановления с неполным протоколированием — То же, что и полная модель восстановления, за тем исключением, что не ведется протоколирование массовых или bulk-операций . А резервные копии протокола транзакций содержат в этом случае результат такой операции.
    • Преимущества:
      1. Как и с полной моделью восстановления, есть возможность восстановить базу данных с последней подтвержденной транзакции, которая была сохранена в файле протокола.
      2. Возможно восстановить данные на любой момент времени, если не выполнялось объемных операций.
      3. Возможно восстановить данные на отметку в протоколе, если не было объемных операций.
      4. Объемные операции выполняются намного быстрее, чем под полной моделью восстановления, так как они не протоколируются.
      5. Для резервной копии протокола требуется гораздо меньше памяти, чем в случае полного восстановления.
    • Недостатки:
      1. Те же, что и при полной модели восстановления.
      2. Не протоколируется операции с изменением индекса. При восстановлении, индексы потребуется создать заново.
      3. Восстановление с резервной копии протокола дольше, нежели при полной модели восстановления.
      4. Нет возможности восстановить данные на момент времени или на отметку в протоколе в случае объемных операций.
    • Заключение:
      Модель восстановления с неполным протоколированием используется для производственных баз данных в тех случаях, когда периодически происходят крупномасштабные или объемные bulk-операции.
  3. Простая модель восстановления — В простой модели восстановления протокол транзакций усекается, если появляется точка восстановления. Но это не означает, что вообще не существует протоколирования, содержимое протокола используется во время создания контрольной точки, где все транзакции протокола подтверждены или для них выполнен откат.
    • Преимущества:
      1. Производительность всех объемных операций очень высокая.
      2. Низкие требования к объему памяти протокола.
    • Недостатки:
      1. Данные возможно восстановить только на момент последнего резервного копирования, а значит недопустимы восстановления на конкретный момент времени или на отметку в протоколе. Все изменения с последнего резервного копирования должны быть восстановлены вручную.
    • Заключение:
      Рекомендуется использовать простую модель восстановления для производственных баз, только в тех случаях, когда сервер баз данных не обладает достаточным объемом памяти или когда аппаратная часть сервера баз данных ниже рекомендуемой.

4. Методы резервного копирования

MS SQL Server предоставляет 4 различных метода резервного копирования:

  1. Полное копирование базы данных (Full) —При полном резервном копировании создается резервная копия всей базы данных целиком, она включает в себя схему всех таблиц, соответствующие файловые структуры, а также содержит все данные из этой базы на момент завершения резервного копирования. Это осуществляется благодаря тому, что полное копирование захватывает состояние базы данных на момент начала копирования. А затем, если копирование выполняется динамически, система записывает любые действия, которые имеют место в процессе создания копии.
    • Преимущества:
      Быстрая скорость восстановление базы данных.
    • Недостатки:
      Полное резервное копирование требует больше времени и требует больше пространства для хранения, чем другие методы копирования.
    • Заключение:
      Для небольших баз данных, которые можно скопировать быстро, лучше всего применять полное резервное копирование. Но для больших баз требуется кроме полных резервных копий, создавать также и дифференцированные (разностные) копии баз данных.
  2. Дифференцированное (разностное) резервное копирование (Differential) — В этом случае создается копия только частей баз данных, которые менялись с момента последнего полного копирования баз данных. Эта полная резервная копия называется основой для разностной копии. Как и в случае полного резервного копирования, любые действия, имеющие место во время создания копии, также копируются.
    • Преимущества:
      Этот тип резервного копирования минимизирует время, требуемое для копирования, т. к. количество копируемых данных значительно меньше, чем при полном копировании.
    • Недостатки:
      Для восстановления необходимо загрузить сначала полную копию баз данных, а затем разностную, что занимает больше времени. И, соответственно, нет смысла применять разностное копирование без полного.
    • Заключение:
      Используется на больших базах данных в связке с полным резервным копированием.
  3. Резервное копирование протокола транзакций (Transaction log) — Данный вид копирования применяется при полной модели восстановления (или при неполном протоколировании) баз данных, и учитывает только изменения, записанные в протокол транзакций. Поэтому такая форма резервного копирования не основывается на физических страницах баз данных, а только на логических операциях.
    • Преимущества:
      1. Самая быстрая скорость создания копии.
      2. В отличии от дифференцированных резервных копий, где возможно восстановить базу данных только на момент последнего копирования, позволяет восстановить базу данных на конкретный момент времени.
      3. Правильное «закрытие» протокола транзакций перед началом новой порции действий с этим протоколом. Одна из наиболее общих ошибок системы возникает, когда переполняется протокол транзакций. Если память, используемая для протокола транзакций, заполняется на 100%, то система должна остановить все выполняющие транзакции, пока память не будет освобождена. Эта проблема может быть устранена только при частом выполнении резервного копирования протокола транзакций: каждый раз происходит «закрытие» порции существующего протокола и сохранение на другом внешнем устройстве. Эта порция протокола становится повторно используемой, следовательно, система восстанавливает дисковое пространство.
    • Недостатки:
      Более долгий процесс восстановления, чем при дифференцированном резервном копировании, где требуется полная копия базы данных и последняя разностная копия, в данном случае потребуется полная копия и все существующие копии протоколов транзакций.
    • Заключение:
      Рекомендуется применять на производственных базах данных в связке с полным резервным копированием.
  4. Резервное копирование файлов или файловых групп — Данный метод позволяет копировать указанные файлы баз данных вместо копирования всей базы данных. Отдельные файлы могут быть восстановлены из копии, позволяя выполнить восстановление после сбоя, который повлиял лишь на небольшое подмножество файлов баз данных.
    • Заключение :
      Копирование файлов базы данных рекомендуется выполнять только когда база данных является очень большой, и нет достаточно времени для полного копирования базы данных.

5. Какие базы данных и как часто копировать?

  1. База данных master является наиболее важной базой данных системы, потому что она содержит информацию обо всех базах данных в этой системе. Поэтому резервное копирование базы данных master должно происходить на регулярной основе. Кроме того, рекомендуется создавать копию каждый раз, когда выполняются действия, приводящие к модификации базы данных master . Вот некоторые из них:
    • Выполнение операторов и хранимых процедур;
    • Создание, изменение и удаление базы данных;
    • Изменения протокола транзакций.
  2. Следует выполнять резервное копирование всех производственных баз данных на регулярной основе. Дополнительно, необходимо делать резервную копию после того как с базами данных были выполнены следующие изменения:
    • После создания базы данных;
    • После создания индексов;
      • Microsoft SQL Server 2008 R2

    3. Базы данных:

    • Общий объем баз данных: ~ 95 Гб
    • Объем базы данных master : ~ 5 Мб
    • Объем базы данных DB 1 : ~ 23 Гб
    • Модель восстановления базы данных DB 1 : Полная
    • Прирост базы данных DB 1 в день: ~ 200 Мб

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

    • Время создания полной резервной копии: ~ 5 мин.
    • Время создания копии протокола транзакций: ~ 4 сек.
    • Размер полной резервной копии (с сжатием): ~ 1,6 Гб
    • Размер копии протокола транзакций: ~ 20 Мб

Необходимое дисковое пространство для плана резервного копирования DB 1 :

    • Хранение полных резервных копий (1 месяц): ~ 50 Гб
    • Хранение копий протокола транзакций (1 месяц): ~ 10 Гб
    • Хранение полных копий от 1-ого числа каждого месяца (1 год): ~ 20 Гб
    • Итого размер дискового пространства: ~ 80 Гб
    • Итого размер дискового пространства в % от размера базы: ~ 350 %

Время восстановления базы данных DB1 :

    • Время восстановления полной резервной копии: ~ 5 мин.
    • Время восстановления копии протокола транзакций: ~ 5 сек.

Помогла ли Вам данная статья?

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

    Неисправности аппаратного обеспечения

  • Некорректное использование инструкций UPDATE и DELETE

    Ошибки программного обеспечения

    Аварийные ситуации, например, пожар или затопление

Чтобы избежать потери данных, можно реализовать для базы данных стратегию восстановления. Стратегию восстановления необходимо спланировать, реализовать и протестировать с учетом возможных неисправностей, с которыми можно встретиться в процессе работы системы, и необходимого уровня защиты данных. В витринах данных, то есть в случаях, когда данные можно восстановить из других систем, вероятно, нет необходимости создавать резервные копии каждой отдельной транзакции. Возможно, будет достаточно выполнять полное резервное копирование данных с регулярными временными интервалами. И наоборот, для базы данных, в которой хранятся транзакции интернет-магазина, возможно, будет необходимо сохранять резервные копии каждой отдельной транзакции. СУБД SQL Server предоставляет полный комплекс функций для реализации именно того вида резервного копирования, который вам необходим. В данной лекции рассматриваются наиболее широко используемые в Microsoft SQL Server стратегии для защиты данных.

Полное резервное копирование базы данных

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

Полная резервная копия базы данных содержит все данные и метаинформацию базы данных, которые необходимы для восстановления базы данных полностью, включая полнотекстовые каталоги. При восстановлении базы данных из полной резервной копии восстанавливаются все файлы базы данных, причем данные извлекаются в непротиворечивом состоянии на тот момент времени, в который выполнялось резервное копирование. Пока выполняется резервное копирование, база данных работает в рабочем режиме, и пользователь может выполнять транзакции, изменяя данные обычным путем. Термин "непротиворечивое состояние" означает, что все транзакции, которые были зафиксированы в процессе выполнения резервного копирования базы данных, применяются, а все транзакции, которые не были завершены, подвергаются откату. Для ситуаций, которые могли бы привести к нарушению непротиворечивости данных вследствие выполнения транзакций, изменяющих данные в процессе выполнения резервного копирования, в SQL Server есть особый процесс, который позволяет гарантировать непротиворечивость данных. Этот процесс выполняет запись на устройство резервного копирования как страниц данных, так и журнала транзакций.

Совет . Полнотекстовые каталоги были введены в базы данных, чтобы добавить в SQL Server функции полнотекстового индексирования. Полнотекстовое индексирование позволяет быстрее и с большей точностью осуществлять поиск данных в базе данных. Дополнительную информацию о полнотекстовом индексировании см. в Электронной документации по SQL Server в разделе "Полнотекстовые индексы".

Скорость резервного копирования определяется скоростью используемых устройств ввода/вывода (тех устройств ввода вывода, которые используются для сбора и хранения информации). Чтобы добиться наилучшей производительности, SQL Server считывает файлы последовательно. Если ваши устройства ввода/вывода способны одновременно обрабатывать данные ввода/вывода резервного копирования и данные ввода/вывода, поступающие в результате обычного использования системы, то создание резервной копии окажет на производительность системы незначительное воздействие. Тем не менее, лучше выполнять полное резервное копирование базы данных при отсутствии пиковых нагрузок.

В следующем разделе мы рассмотрим варианты реализации этой стратегии резервного копирования.

Простая модель восстановления

Следует заранее уведомить SQL Server о том, какой тип резервного копирования вы намерены использовать, поэтому надо сконфигурировать базу данных так, чтобы настройки соответствовали выбранному вами типу резервного копирования. Такая настройка выполняется посредством выбора значения параметра "модель восстановления базы данных". Модель восстановления базы данных, которая используется по умолчанию, является производным от модели восстановления модели базы данных, определенной при ее создании. Чтобы реализовать стратегию резервного копирования, которая будет включать только полные резервные копии, следует выбрать простую модель восстановления (SIMPLE).

Выбираем модель восстановления SIMPLE

    В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Management Studio (Все программы, Microsoft SQL Server 2005, Среда SQL Server Management Studio).

    В диалоговом окне Connect To Server (Соединение с сервером) нажмите кнопку Connect (Соединить).

    В панели инструментов Standard (Стандартная) нажмите кнопку New Query (Новый запрос), чтобы открыть окно New Query (Новый запрос).

    Чтобы задать модель восстановления, можно использовать инструкцию ALTER DATABASE. Введите текст следующей инструкции и нажмите кнопку Execute (Выполнить).

ALTER DATABASE AdventureWorks

SET RECOVERY SIMPLE;

Дополнительная информация .В этой лекции рассматривается, главным образом, создание резервных копий и восстановление с помощью инструкций SQL. В лекциях 6-7 будет рассмотрено выполнение многих из этих же процедур не через инструкции T-SQL, а с помощью интерфейса пользователя SQL Server Management Studio.

Проверяем настройки модели аварийного восстановления

    Чтобы просмотреть заданную для базы данных модель восстановления, можно использовать функцию DATABASEPROPERTYEX, которая извлекает параметры текущей базы даты или свойства указанной базы данных. Выполните инструкцию, приведенную ниже, чтобы извлечь информацию о модели восстановления базы данных AdventureWorks.

SELECT DATABASEPROPERTYEX("AdventureWorks","Recovery")

    Убедитесь, что в результатах запроса указана модель восстановления SIMPLE.

    Закройте окно среды SQL Server Management Studio.

Устройства резервного копирования

До начала выполнения операций резервного копирования необходимо определить, где будут храниться резервные копии. Место хранения резервных копий называется устройством резервного копирования. Каждое устройство резервного копирования может хранить несколько резервных копий разных типов. Существует два разных вида устройств резервного копирования:

    Ленточные устройства .Могут использоваться для хранения резервных копий на лентах. Ленточные устройства должны быть установлены локально. Резервная копия может занимать несколько лент, а на одной ленте могут находиться одновременно резервные копии SQL Server и Windows.

    Дисковые устройства .Файлы на локальном или удаленном диске или дисковом накопителе. К этим файлам обращаются, указывая путь к файлу, в котором хранится резервная копия. Для обращения к удаленным хранилищам следует использовать путь в формате UNC.

Примечание . В этой книге будет рассмотрено только резервное копирование на дисковые устройства. Резервное копирование файлов SQL Server на ленточные устройства в настоящее время используется не очень часто. Если резервные копии SQL Server сохраняются на лентах, то они обычно создаются при помощи программ сторонних разработчиков, которые предлагают дополнительные функции, например, использование удаленного ленточного хранилища. В качестве альтернативы ленточное устройство может использоваться для дополнительного страхования сохранности данных, резервная копия которых уже сохранена на дисковом устройстве.

Устройства резервного копирования идентифицируются по имени устройства. В качестве имени устройства может использоваться имя логического или физического устройства. Имя физического дискового устройства представляет собой путь к файлу резервной копии, например, "\\BACKUPSERVER\Backups\adv\AdventureWorks.bak". Этот путь можно включить непосредственно в инструкцию резервного копирования. Имя логического устройства представляет собой имя, указывающее на имя физического устройства резервного копирования и хранящееся в SQL Server. Когда в инструкции резервного копирования используется имя логического устройства, SQL Server осуществляет поиск соответствующего физического устройства в системном каталоге и выполняет резервное копирование, сохраняя резервную копию в указанной папке.

Чтобы добавить в системный каталог логическое устройство, можно использовать хранимую процедуру sp_addumpdevice. В следующем примере определяется логическое устройство с именем Adv_FullDb_Dev.

EXEC sp addumpdevice "disk", "AdvFullDbDev", "T:\BACKUPS\AdvFullDbDev.bak";

Совет . Обязательно измените путь к файлу, чтобы он соответствовал вашему компьютеру. Т:/, то измените эту часть пути к файлу в инструкции так, чтобы он соответствовал букве диска на вашем компьютере. Кроме того, убедитесь в том, что все папки, заданные в этом пути, существуют на вашем компьютере.

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

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

Совет . Сокращение RAID происходит от выражения "redundant array of independent disks" (избыточный массив независимых дисков). Такие массивы представляют собой системы дисков с несколькими приводами, которые используются для повышения доступности и емкости хранилища.

Выполнение полного резервного копирования базы данных

После того, как вы установили модель резервного копирования на SIMPLE и решили, на каком устройстве резервного копирования сохранять резервные копии, можно приступить к выполнению резервного копирования. Полное резервное копирование базы данных выполняется с помощью довольно простой инструкции BACKUP DATABASE. В простейшей форме нужно только указать системе, резервную копию какой базы данных создать и на каком устройстве ее сохранить. Чтобы создать резервную копию базы данных AdventureWorks на предварительно выбранном логическом устройстве, используется следующая инструкция T-SQL:

TO Adv_FullDb_Dev;

Если надо выполнить полное резервное копирование базы данных на физическое устройство, необходимо указать тип устройства и место размещения резервной копии в инструкцииBACKUP DATABASE. Чтобы создать резервную копию базы данных в папке t:\adv.bak, используйте следующую инструкцию:

BACKUP DATABASE AdventureWorks

TO DISK="t:\adv.bak";

Как уже говорилось, на любом устройстве резервного копирования может храниться больше одной резервной копии. В аргументе инструкции BACKUP DATABASE можно указать, следует ли SQL Server перезаписать существующую резервную копию на этом устройстве или дописать ее к существующим резервным копиям. Для этого используются ключевые слова INIT илиNOINIT. Если указать INIT, то перед запуском резервного копирования устройство резервного копирования форматируется, при этом удаляются все резервные копии, которые имеются на этом устройстве. NOINIT, которое используется по умолчанию, если не указано иное, разрешает SQL Server дописать резервную копию на существующее устройство резервного копирования с сохранением всех существующих на нем резервных копий. Эти опции устанавливаются при помощи блока WITH в конце инструкции BACKUP DATABASE.

Если нужно создать такую же резервную копию, как в предыдущем примере, но при этом указать SQL Server сначала очистить устройство, используйте следующую инструкцию:

BACKUP DATABASE AdventureWorks

TO DISK="t:\adv.bak"

Как видите, выполнение полного резервного копирования базы данных не отличается сложностью. В следующем разделе вы увидите, что полная резервная копия - это основной тип резервного копирования, на котором строятся все остальные типы резервного копирования. Остальные типы резервного копирования зависят от наличия полной резервной копии, потому что им необходима восстановленная база данных в качестве исходной точки. Эти типы резервного копирования, в том числе, разностное резервное копирование, сохраняют изменения, которые были внесены в базу данных после создания полной резервной копии. Таким образом, мы видим, что полные резервные копии важны не только в стратегии восстановления, при которой выполняется только полное резервное копирование, но и в других стратегиях резервного копирования, о которых речь пойдет ниже.

Разностное резервное копирование

Главное преимущество полного резервного копирования базы данных заключается в том, что в полной резервной копии содержатся все данные, которые необходимы для восстановления базы данных полностью. Но это преимущество может одновременно быть и недостатком. Представьте себе базу данных, для которой каждую ночь создается резервная копия. Если придется восстанавливать базу данных, в любом случае придется использовать резервную копию, созданную прошлой ночью, и в результате будут потеряны результаты работы за целый день. Один из способов уменьшить потенциальный промежуток времени, который может быть не учтен резервным копированием является более частое выполнение полного резервного копирования базы данных. Но это само по себе является проблематичным. Поскольку все данные и фрагменты журнала транзакций записываются на устройство резервного копирования, выполнение резервного копирования может отнимать слишком много времени. Кроме того, для хранения всех этих резервных копий необходимо много дискового пространства h° , а полное резервное копирование может снизить производительность базы данных в результате потребности в большом объеме операций ввода/вывода. Не лучше ли будет один раз выполнить резервное копирование ночью, а затем выполнять резервное копирование только тех данных, которые были изменены в течение дня? Такие функциональные возможности предоставляет разностный тип резервного копирования.

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

Рис. 4.1. Стратегия резервного копирования с использованием разностных резервных копий

Выполнение разностного резервного копирования

Выполнение разностного резервного копирования мало чем отличается от выполнения полного резервного копирования. Единственное отличие заключается в том, что в блоке WITHобъявляются инструкции по создании разностной резервной копии. Синтаксис инструкции BACKUP DATABASE для выполнения резервного копирования базы данных Adventure Works на физическое устройство с перезаписью других существующих резервных копий на этом устройстве будет таким:

BACKUP DATABASE AdventureWorks

TO DISK="t:\adv_diff.bak"

WITH INIT,DIFFERENTIAL;

Если вы хотите использовать логическое устройство, то следует сначала создать его, как и в случае полного резервного копирования.

EXEC sp_addumpdevice "disk", "Adv_Diff_Dev",

"T:\BACKUPS\AdvDiffDev.bak";

BACKUP DATABASE AdventureWorks

TO Adv_Diff_Dev

WITH INIT,DIFFERENTIAL;

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

Резервное копирование журнала транзакций

Комбинируя полную и разностную копии базы данных, можно создать снимок состояния данных и восстановить их. Но в некоторых ситуациях желательно также иметь резервные копии всех событий, которые произошли с базой данных, вплоть до записей о каждой выполненной инструкции. Таким образом, можно было бы восстановить базу данных до любого необходимого состояния. Резервное копирование журнала транзакций как раз и предоставляет такую возможность. Как ясно из названия, метод резервного копирования журнала транзакций создает резервные копии всех записей журнала транзакций, которые произошли в базе данных. Основные преимущества резервного копирования журнала транзакций следующие:

    Резервное копирование журнала транзакций позволяет восстановить данные на определенный момент времени.

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

Как и в случае с разностными резервными копиями, для восстановления базы данных из резервных копий журнала транзакций в стратегии восстановления необходима базовая полная резервная копия базы данных. Стратегия резервного копирования с использованием резервных копий журнала транзакций показана на рис. 4.2 . Полные резервные копии базы данных выполняются в часы наименьшей загрузки оборудования, а резервные копии журнала транзакций выполняются в определенное время в течение дня. Резервные копии журнала транзакций содержат все транзакции, которые произошли с момента создания последней резервной копии журнала транзакций. Следовательно, чтобы восстановить базу данных с использованием резервных копий журнала транзакций, необходимы полная резервная копия базы данных и все резервные копии журнала транзакций с момента создания полной резервной копии. Как видите, важно, чтобы все резервные копии были доступны. Если полная резервная копия базы данных или одна из резервных копий журнала транзакций будут утеряны, то восстановление данных в желательном объеме будет невозможно.

Рис. 4.2. Стратегия резервного копирования с использованием резервных копий журнала транзакций

Комбинирование разностных резервных копий и резервных копий журнала транзакций

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

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

Рис. 4.3. Комбинированная стратегия резервного копирования

Например, чтобы восстановить данные до резервной копии журнала транзакций Т3, следует восстановить данные полной резервной копии F1, разностной резервной копии D1 и резервной копии журнала транзакций Т3.

Интервал времени между созданием резервных копий журнала транзакций зависит от:

    Количества и размера транзакций, выполняемых в базе данных. При этой стратегии резервного копирования SQL Server должен хранить все транзакции до тех пор, пока не будет сохранена резервная копия журнала транзакций. Следовательно, в файл журнала транзакций должны поместиться все транзакции, которые были выполнены за период времени между двумя последовательными резервными копированиями журнала транзакций. Если журнал заполняется слишком быстро, следует уменьшить временные интервалы между созданием резервных копий журнала транзакций или увеличить размер файла журнала транзакций.

    Приемлемого объема потери данных. Как уже упоминалось выше, можно восстановить данные вплоть до последней транзакции, если файлы данных утрачены. Но если журнал транзакций будет поврежден или утрачен, то можно будет восстановить только данные на момент последнего резервного копирования журнала транзакций. Уменьшение временных промежутков между созданием резервных копий журнала транзакций снизит объем потерянных данных в подобной ситуации.