Как восстановить базу данных сайта из бэкапа в phpMyAdmin

PROИТ: SQL Server Как восстановить базу данных из bak файла бэкапа (restore DB from backup)

Задача
: восстановить
базу данных из файла бэкапа
. Шаг за шагом с иллюстрациями рассмотрим как это сделать на примере СУБД Microsoft SQL Server 2014. Кроме восстановления посмотрим также как сделать линковку
(связь) пользователя базы данных и логина СУБД (будет использовать SQL скрипт sp_change_users_login). Итак, залогиньтесь в СУБД SQL Server и в дереве в контекстном меню пункта Databases
выберите пункт «Restore Database…
«:

В параметрах выберем «Device
» и перейдем по кнопке «Обзор
» (кнопка с многоточием):

В открывшемся окне нажимаем «Add
» и находим файл бэкапа базы данных:

Нажимаем ОК:

Перед началом рекомендуется проверить корректность файла, нажав кнопку «Verify Backup Media
«:

Далее перейдем в левом меню на раздел «Files
» и проверим пути для сохранения восстановленной базы данных. При необходимости меняем их (не обязательно):

Также можно перейти в раздел «Options
» и сделать настройки, если требуется (не обязательно):

Нажимаем ОК и дожидаемся сообщения об успешном восстановлении базы:

Если база данных содержала информацию о пользователях, то необходимо проверить их связь с логинами. Для этого в дереве находим вновь воссозданную БД, раскрываем пункт «Security
» — «Users
» и выбираем какого-либо существующего пользователя и в контекстном меню переходим на пункт «Properties
«:

В открывшемся окне свойств пользователя мы видим, что пользователь существует только в контексте это базы (т.е. не связан ни с каким логином «SQL user without login»):

Это не позволит Вам подключиться к базе данных под данным пользователем

. Поэтому создадим логин для этого пользователя (если логин уже существует, пропустите этот шаг). В дереве объектов СУБД перейдем на уровень выше (выше, чем текущая БД) в пункт «Security
» и в контекстном меню пункта «Logins
» выберем «New Login
…»:

В открывшемся окне заполняем: — login name
— имя логина (я всегда делаю его одноименным с пользователем базы) — выбираем параметр «SQL Server authentication
» и задаем пароль — снимаем «галку» «Enforce password policy
» — default database
— прописываем нужную базу данных:

Далее можно перейти в левом меню в раздел «User Mapping
«, где увидим, что можно закрепить за созданным логином некого пользователя в базе. Если у Вас база данных не содержит никаких пользователей, то задайте эти параметры, как показано на рисунке (в противном случае пропустите этот шаг) и будет автоматически создан пользователь БД связанный с данным логином:

Если же Вы задали вышеуказанный параметр, а пользователь уже существует, то Вы получите следующее сообщение об ошибке:Microsoft SQL Server Management Studio
Create failed for User ‘ShortURLDBUser’.  (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
User, group, or role ‘ShortURLDBUser’ already exists in the current database. (Microsoft SQL Server, Error: 15023)

При этом логин будет создан, но не связан с пользователем. Чтобы связать логин СУБД с пользователем конкретной базы данных
выполним следующий SQL-скрипт:

use ShortUrlDB go
EXEC
 sp_change_users_login 'Update_One'
, 'ShortUrlDBUser'
, 'ShortUrlDBUser'
go

Теперь, если снова перейти в свойства пользователя БД, то мы видим, что пользователь связан с только что созданным логином.

(с) Ella S.

Восстановление отдельных страниц в базе данных / Habr

Предисловие

Статья Gail Shaw «Help, my database is corrupt. Now what?», перевод которой я запостил на прошлой неделе, вызвала, вроде бы, определенный интерес, но она, увы, не содержала «практики». Да, там написано как можно спасти данные, но нет никаких примеров. Изначально я хотел сделать еще один перевод все того же автора, но, подумав, решил написать пост «от себя», как бы «по мотивам». Причины, побудившие меня поступить так, я опишу в конце поста, в примечаниях.

Восстановление баз данных в SQL Server

Как уже было сказано в предыдущей статье, в том случае, если повреждены страницы кластерного индекса или кучи, то данные, содержащиеся на этих страницах, потеряны и единственным вариантом для их восстановления является непосредственно восстановление базы данных. SQL Server предоставляет множество возможностей для восстановления баз данных. Во-первых, это восстановление базы данных целиком — оно может занимать довольно много времени (зависит от размера БД и скорости жестких дисков). Во-вторых, восстановление отдельных файловых групп, либо файлов, если ваша БД состоит из нескольких файловых групп (или, соответствено, файлов). В этом случае, есть возможность восстановления только поврежденных частей БД, не затрагивая остальных. Эти два вида восстановления БД используются довольно часто и затрагиваться в дальнейшем не будут. В-третьих, в SQL Server 2005 появилась возможность восстановления отдельных страниц БД — в этом случае из бэкапа будут восстановлены только указанные страницы. Такое восстановление будет особенно актуально, если DBCC CHECKDB найдет несколько поврежденных страниц в какой-нибудь огромной таблице, «лежащей» в здоровенном файле. За счет того, что восстанавливаться будет не весь файл, и даже не вся таблица, а только несколько страниц — время простоя может быть значительно сокращено.

Требования и ограничения

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

Самое главное, что нужно помнить — для восстановления отдельных страниц, база данных должна использовать полную (full) модель восстановления, либо модель восстановления с неполным протоколированием (bulk-logged). Если ваши базы находятся в простой (simple) модели восстановления — дальше вы можете уже и не читать. Второе требование — ваши полные бэкапы и бэкапы журнала транзакций должны образовывать неразрывную цепочку журналов (log chain). Если вы никогда не выполняете команду BACKUP LOG WITH TRUNCATE_ONLY (NO_LOG) и не переключаетесь в простую модель восстановления для того, чтобы уменьшить журнал транзакций, и у вас есть ВСЕ резервные копии журнала транзакций с момента последней полной резервной копии не содержащей поврежденных страниц (включая эту самую полную резервную копию) — за цепочку журналов можно не волноваться. В модели восстановления с неполным протоколированием, теоретически, восстановление отдельных страниц должно работать нормально в том случае, если соблюдаются условия описанные выше, и восстанавливаемые страницы не изменялись операциями, выполняемыми с минимальным протоколированием.

Редакции SQL Server

Восстановление страниц возможно в любой редакции SQL Server, но для редакций Enterprise Edition и Developer Edition возможно восстановление поврежденных страниц on-line, т.е. к базе данных, во время восстановления, можно обращаться (и более того, обращаться можно даже к той таблице, к которой относятся восстанавливаемые в данный момент страницы, если сами эти страницы не будут «затрагиваться» — в противном случае, запрос завершится ошибкой). Для редакций «ниже» Enterprise Edition, восстановление страниц проходит в режиме off-line и база данных, на время восстановления, становится недоступной.

Тип поврежденной страницы

В том случае если повреждены страницы индекса, либо данных, их восстановление возможно в режиме online в редакции Enterprise Edition. Страницы, приндалежащие критически важным системным таблицам могут быть восстановлены, но база данных, при восстановлении, будет недоступна в любой редакции SQL Server. «Карты размещения» не могут быть восстановлены «отдельно». Если повреждены GAM, SGAM, PFS, ML, DIFF-страницы, необходимо восстанавливать базу данных целиком. Единственным исключением являются IAM-страницы. Хотя они и относятся к «картам размещения», но они описывают только одну таблицу, а не всю базу данных, и их восстановление возможно. Загрузочная страница базы данных (9-я страница в 1-м файле БД) и страница заголовка файла (0-я страница в каждом файле) не могут быть восстановлены «отдельно», при их повреждении придется восстанавливать БД целиком.

Собственно, восстановление

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

Портим БД

Для экспериментов я буду использовать базу данных AdventureWorks, которая поставляется вместе с SQL Server. Если вы не устанавливали ее, при желании, можно скачать здесь. Перевожу ее в модель восстановления full:

ALTER DATABASE AdventureWorks SET RECOVERY FULL

убеждаюсь, что ошибок в ней еще нет:

DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

и создаю полный бэкап:

BACKUP DATABASE AdventureWorks   TO DISK = 'D:tmpaw_backupsaw_full_ok1.bak'

В этой базе данных я создаю таблицу crash.

CREATE TABLE crash (txt varchar(1000))

Поле типа varchar мы и будем портить, для того, чтобы проверить что произойдет, если вдруг SQL Server обнаружит в нем не те данные, которые он сам туда записал. Прежде чем что-то испортить, надо это чем-то заполнить. Я забиваю в созданную таблицу левые данные.

SET NOCOUNT ON  DECLARE @i INT  SET @i = 1  WHILE @i BEGIN  INSERT INTO crash  SELECT REPLICATE('a', 1000)  SET @i = @i + 1  END  SET NOCOUNT OFF  

Теперь делаю резервную копию журнала транзакций:

BACKUP LOG AdventureWorks   TO DISK = 'D:tmpaw_backupsaw_log_ok1.trn'

Теперь немного изменим данные: Итак, все готово. Отсоединяем БД и открываем mdf-файл FAR’ом (или чем вам удобнее), ищем в нем строку «zzzzzzz» и заменяем несколько ‘z’ на произвольные символы: Теперь, когда БД испорчена, подсоединяем ее. И, да, я помню, что в предыдущей статье было четко сказано, что отсоединять/присоединять БД не стоит. Но в нашем случае это абсолютно «безопасно» — база данных в «suspect» не упадет.

Ищем ошибки

Итак, испорченная БД успешно вернулась в строй. Снова запустим проверку целостности:

DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

В результате то, чего мы ждали (обязательно запоминайте номера поврежденных страниц!
):Msg 8928, Level 16, State 1, Line 1
Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data): Page (1:20455) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data), page (1:20455). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'crash' (object ID 1883153754).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'AdventureWorks'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorks).

В данном случае повреждены сами данные, находящиеся в куче (index id = 0), поэтому SQL Server эти данные восстановить не сможет. Сейчас у нас есть три варианта:

  1. Смириться с потерей данных и выполнить DBCC CHECKDB(‘AdventureWorks’, REPAIR_ALLOW_DATA_LOSS)
  2. Сделать бэкап активной части журнала транзакций и восстановить БД целиком — в результате потери данных не будет, но это займет продолжительное время
  3. Сделать бэкап активной части журнала транзакций и восстановить только одну(!), поврежденную, страницу

Со вторым вариантом все должно быть понятно, а вот что произойдет если запустить DBCC CHECKDB или как восстанавливаются отдельные страницы — я покажу дальше.

Восстанавливаем поврежденную страницу

В первую очередь нам надо сделать бэкап заключительного фрагмента журнала транзакций (tail backup). При этом, если у вас Enterprise Edition, вы можете не добавлять параметр NORECOVERY, который переведет БД в состояние «restoring», поскольку эта редакция поддерживает on-line восстановление страниц. Более того, если у вас резервные копии журнала транзакций выполняются на регулярной основе, чтобы не нарушать цепочку журналов, в редакции Enterprise Edition, вы можете сделать COPY_ONLY бэкап. Я же иду по пути off-line восстановления и выполняю:

BACKUP LOG AdventureWorks   TO DISK = 'D:tmpaw_backupsaw_log_fail3.trn'  WITH NORECOVERY

Теперь, можно восстанавливать поврежденную страницу. В первую очередь, используем полный бэкап (aw_full_ok1.bak):

RESTORE DATABASE AdventureWorks  PAGE = '1:20455'  FROM DISK = 'D:tmpaw_backupsaw_full_ok1.bak'  WITH NORECOVERY

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

RESTORE LOG AdventureWorks  FROM DISK = 'D:tmpaw_backupsaw_log_ok1.trn'  WITH NORECOVERY

и

RESTORE LOG AdventureWorks  FROM DISK = 'D:tmpaw_backupsaw_log_fail3.trn'  WITH RECOVERY

Вроде бы все прошло успешно, запускаем DBCC CHECKDB и… Восстановление прошло успешно. Обратите внимание, что время простоя сокращается за счет того, что из полного бэкапа мы восстанавливаем не всю БД, а только поврежденные страницы (если бы я восстанавливал бэкап целиком — бэкап восстановился бы за 8,5 секунд, но, чем больше размер БД — тем больше будет разница во времени). Счастливчики с SQL Server Enterprise Edition, использующие on-line восстановление, так же сэкономят время на восстановлении из бэкапов лога, а при off-line восстановлении, увы, журналы будут обрабатываться целиком. Стоит так же добавить, что в SQL Server 2005, 2008, 2008 R2 восстановление отдельной страницы возможно только с помощью T-SQL, в Denali появилась возможность делать это через GUI.

А если все-таки DBCC CHECKDB?

На всякий случай я решил проверить что сделает SQL Server, если я запущу DBCC CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS. Все та же ошибка: Сначала переводим БД в режим SINGLE_USER:

ALTER DATABASE AdventureWorks SET SINGLE_USER

А затем, запускаем восстановление:

DBCC CHECKDB('AdventureWorks', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

В итоге:Repair: The page (1:20455) has been deallocated from object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data).

Ага, SQL Server удалил «испорченную» страницу. Переводим БД в режим MULTI_USER, чтобы она стала доступной для всех и проверяем что пропало: Учитывая, что размер страницы в SQL Server 8КБ, а для пользовательских данных доступно чуть меньше — то все закономерно, таблица «похудела» на 7 записей (в начале их было 99999). Поскольку на этой таблице не было кластерного индекса, данные могли храниться в произвольном порядке, т.е. мы даже не могли узнать какие данные будут потеряны.

Так почему, все-таки, не перевод?

Итак, почему это все-таки не перевод, а пост «по мотивам». Дело в том, что, в открытом доступе статьи «Page Restore» за авторством Gail Shaw нет. Есть такой раздел в книге SQL Server MVP Deep Dives vol.2, которая продается за довольно-таки ощутимые деньги (но, естественно, легко находится в интернетах) и я не уверен, что публиковать перевод — это эм… правильно что ли. В общем, я прочитал статью, взял на заметку основные моменты, а потом уже сам писал текст и, попутно, проводил эксперимент по восстановлению. Надеюсь, кому-нибудь этот опыт был полезен. И, господа, я искренне надеюсь, что если вы решите повторять этот эксперимент, то будете предельно осторожны (например, не будете эксперементировать с основной БД на production-сервере). Помните, что никакой ответственности за ваши действия я не несу.

128

20k


128

Восстановление MySQL баз данных ручными и «механическими» способами

Дата публикации:
2016-04-04

От автора:
добрый день, уважаемые. У вас что-то случилось? Опять «выкинули» не ту базу данных? Ну, это не смертельно, если знать все про восстановление MySQL. Сейчас мы расскажем вам все тонкости данного ритуала. Для этого нужен бубен, козявка из носа белохвостого тюленя… Это шутка! А все серьезное по этой теме будет изложено дальше.

Горе поправимо, если удалили базу

Без баз данных и систем управления ими (СУБД) в интернете никуда. Большая часть современных CMS и «самописных» движков, на которых развернуты сайты, используют MySQL. Поэтому ее можно смело назвать «всея интернетной» системой управления базами данных.

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

Быстрый способ восстановления

Чаще всего работа с БД в интернете происходит через phpMyAdmin, который является веб-интерфейсом для данной СУБД. Чтобы восстановить базу MySQL вручную:

Зайдите в phpMyAdmin и выберете нужную БД.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Перейдите по вкладке «Импорт», которая расположена в главном верхнем меню.

В разделе «Импортируемый файл» выберете источник резервной копии нужной базы.

Нажатие на кнопку «Ok».

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

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

Работа через MySQLdump

MySQLdump представляет собой веб-приложение, работающее на стороне сервера. Оно предназначено для восстановления баз MySQL из резервных копий, созданных с помощью приложения. Чтобы сильно «не зарываться», мы продемонстрируем создание простого бэкапа и восстановление из него БД. В качестве площадки для эксперимента используем самый популярный локальный сервер Рунета Denwer.

Для начала нужно скачать MySQLdump и поместить его по следующему адресу: F:Webserverusrlocalmysql-5.5bin

MySQLdump является консольным приложением, поэтому вся последующая работа с ним будет происходить через командную строку (cmd.exe). Теперь поэтапно:

Запускаем Denwer.

Через командную строку заходим на виртуальный диск (в примере – это диск Z).

Заходим в папку, где «лежит» MySQLdump.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

После этого запускаем утилиту на выполнение. Перед тем, как восстановить БД MySQL, в качестве примера создадим в папке bin резервную копию базы my_db1, и назовем бэкап «db1». Для этого мы используем команду mysqldump.

Теперь восстановим из созданной копии (db1) другую базу данных. Для этого используем команду mysql.

Код примеров использования обеих команд:

Если немного присмотреться к коду, то можно заметить, что обе команды имеют общий синтаксис. Единственное, что их различает – это названия источников данных и копий. А также знак направления движения данных («»).

uroot – это имя пользователя. Оно указывается сразу после параметра u (без пробела). Кроме этого синтаксис команд включает еще несколько параметров. Среди них: пароль, адрес удаленного хоста. С помощью команды mysqldump можно экспортировать БД в различные форматы, сжимать данные. Также это проверенный способ, как можно восстановить таблицу MySQL.

Утилита MySQLdump (с помощью одноименной команды) позволяет экспортировать базы в простом текстовом формате (.txt). А так как текст обладает высокой степенью сжатия, то это эффективный способ уменьшения объемов бэкапов, в ситуации, когда наблюдается «дефицит» серверного пространства.

Что можно сделать еще

Кроме рассмотренных двух основных методов для восстановления данных MySQL можно использовать еще несколько консольных утилит:

MySQL-консоль

Mysqlbinlog – для формирования бэкапов программа импортирует данные из серверных журналов бинарных логов. В них записываются все пользовательские и системные запросы (в бинарном коде), после выполнения которых были изменены данные баз.

Также для популярных CMS разработано множество специализированных плагинов. Например, для WordPress:

Keep Backup Daily.

UpdraftPlus Backup and Restoration.

Perfect Dashboard.

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

Как видите, восстановление MySQL не является такой уж невыполнимой задачей. Все утраченные БД и таблицы можно «вернуть» на место. При этом используются как «ручные» средства, так и специализированные расширения для CMS. Вам осталось выбрать лишь самое подходящее решение. Для более глубокого ознакомления с работой базы данных MySQL, рекомендую пройти Вам наш курс.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Хотите изучить MySQL?

Посмотрите курс по базе данных MySQL!

Смотреть

Защита от спама на WordPress
20 полезных плагинов календаря для WordPress

—>

Метки:
MySQL

Похожие статьи:

Комментарии Вконтакте:

Комментарии Facebook:

https://webformyself.com/vosstanovlenie-mysql-baz-dannyx-ruchnymi-i-mexanicheskimi-sposobami/
—>

Ссылка на основную публикацию
Похожие публикации