Это выдержка из восьмой главы книги Rodney Landrum: «SQL Server Tacklebox», в которой описывается, как DBA может устранить последствия повреждения данных. Будут продемонстрированы инструменты и сценарии, необходимые для своевременного поиска и устранения повреждений данных и предотвращения их попадания в резервные копии.
Чудовище Data Corruption может быть тихим и незаметным убийцей пользовательских данных. Оно может нанести удар сразу или ждать неделями, прежде чем проявится сущим кошмаром. Нет, я не говорю о разработчиках, речь пойдёт о повреждении базы данных.
Если Вы достаточно долго работаете администратором баз данных, то уже наверняка сталкивались с этим монстром по крайней мере в одной из его многочисленных форм. Часто такие повреждения происходят после сбоя электропитания, когда сервер вместо того, чтобы корректно завершить работу, просто умирает в процессе изменения данных. В результате этой, или какой‑либо другой неисправности оборудования, данные или индексы получают повреждения прямо на диске и SQL Server больше не может их использовать, пока целостность не будет восстановлена.
К счастью, есть несколько шагов, которые вы можете предпринять для защиты данных и, что не менее важно, своего места работы, в случае, если такое повреждение произойдёт. Само собой разумеется что прежде всего нужно позаботиться о хорошей стратегии резервного копирования, отсутствие которой равносильно игре в русскую рулетку в одиночку. Однако, тут будет продемонстрировано несколько других методов, основанных на разных командах DBCC. Кроме того, мы рассмотрим сценарий, который обеспечит обнаружение повреждений и сообщит о них по мере выявления, прежде чем они распространятся по вашей инфраструктуре. На основе этого можно рассчитывать что администратор базы данных сможет также ограничить ущерб, который причиняет ещё более свирепый «The Monster at the End of This Book».
PS: Если вам не повезло, и вы никогда не видели «The Monster at the End of This Book» (Jon Stone, иллюстратор Michael Smollin. «Golden Books») с очаровательным монстром Гровером с улицы Сезам в главной роли, то, во‑первых, я вам сочувствую, а во‑вторых, приношу свои извинения, потому что предыдущие ссылки мало что для вас значат. Я могу только порекомендовать вам немедленно ознакомиться с ним вместе с «The Zombie Survival Guide» (автор Max Brooks, Three Rivers Press) и добавить оба в обязательный список изучения для всех новых администраторов баз данных.
Причины Data Corruption
Есть много причин, из‑за которых база данных может повредиться. Чаще всего это происходит после сбоя оборудования. Обычно сбой происходит в дисковой подсистеме, которая отвечает за запись данных на диск. SQL Server ожидает что записываемые на диск данные останутся неизменными после передачи их интерфейсам операционной системы, а затем драйверу контроллера диска и непосредственно самому диску. Например, такого рода повреждение данных может быть вызвано отключением питания в середине транзакции.
Однако не только сбои дисковой подсистемы приводят к повреждению данных. Если вы обновите базу данных с SQL Server 2000 до SQL Server 2005 или выше, а затем проверите её с помощью скрипта, находящего повреждения, представленного в конце этой статьи, вы с удивлением обнаружите сообщения, которые можно истолковать как ошибки в файле базы данных. Однако, к счастью, это просто предупреждения об использовании пространства в фале, которое образовалось из‑за разницы версий базы, и решение проблемы будет в выполнении команды DBCC UPDATEUSAGE.
Какой бы ни была причина Data Corruption, администратор базы данных не должен долгое время находиться в неведении о целостности данных в базе. К сожалению, Чудовище Data Corruption часто умеет спрятаться и не высовывает свою страшную голову, пока сервер не обратится к поврежденными данными. К этому времени повреждение может попасть в файлы резервных копий, и, когда вы прибегнете к последнему из средств — восстановлению базы данных, восстановиться может уже имеющая повреждения база. Важность использования надежной стратегии регулярного резервного копирования невозможно переоценить. Кроме того, нужен скрипт или инструмент, который будет регулярно проверять базу данных и сообщать о любых проблемах и повреждениях, пока не стало слишком поздно. Именно такой сценарий будет представлен в этой статье.
Последствия Data Corruption
Как уже говорилось в этой статье, в большинстве случаев повреждения данных связаны с отказом оборудования. Это может быть контроллер жесткого диска или блок питания. Для выявления проблем, которые могут возникнуть из‑за подобных отказов, SQL Server использует функцию вычисления контрольной суммы страницы. Значение контрольной суммы вычисляется во время записи страниц на диск и проверяется при последующем чтении с диска. По существу, если значение контрольной суммы прочитанной страницы не соответствует тому, которое было рассчитано при записи, то SQL Server считает, что данные были изменены вне процесса ядра сервера баз данных. До SQL Server 2005, в качестве опции, можно было включить для базы данных обнаружение разорванных страниц (Torn Page Detection), которое выполняло аналогичные проверки.
Если SQL Server обнаружит повреждение, его реакция на проблему будет зависеть от масштаба ущерба. Если повреждение таково, что база данных недоступна для чтения, SQL Server не сможет инициализировать и загрузить эту базу. Почти во всех таких случаях потребуется полное восстановление базы данных из копии.
Если ущерб ограничен (затронуты только одна или две страницы данных), тогда SQL Server сможет открывать и читать базу данных. Это позволит использовать в качестве инструмента команду DBCC, для оценки и/или устранения ущерба. Имейте также в виду, что в рамках общей процедуры резервного копирования и восстановления у вас есть возможность выполнить восстановление на уровне страниц, если потребуется восстановить только одну или несколько страниц данных. Дополнительные сведения о восстановлении страниц из резервных копий базы данных можно найти по ссылке: Restore Pages (SQL Server)
Прежде чем двигаться дальше, стоит отметить, что несмотря на рекомендации оставлять режим проверки для всех экземпляров баз данных включенным (обнаружение разорванных страниц или контрольную сумму), это привносит накладные расходы, и возможно такую проверку отключить. Идея состоит в том, что, если вы доверяете своей дисковой подсистеме и системе электропитания, можно не включать проверку, если производительность является самой серьезной проблемой. Большинство дисковых подсистем сегодня имеют специальную батарейку, чтобы обеспечить успешное завершение операции записи.
Для SQL 2000 можно было использовать sp_dboption, которая позволяет включить или отключить обнаружение разорванных страниц. Для более поздних версий вы можете использовать команду ALTER DATABASE, чтобы включить torn page detection или checksum (невозможно включать оба режима одновременно), или можно указать none для отключения проверок.
Защита от Data Corruption
Помимо частого резервного копирования и проверки баз данных (что позволяет откатиться до того момента, когда баз ещё не была повреждена) хорошо подготовленный администратор базы данных будет иметь в своем распоряжении некоторые инструменты, которые позволяют определить место повреждения и восстановить любые поврежденные данные.
Однако, прежде чем ринуться с подобием мачете в заводь с чудовищами, я должен сообщить вам, что я ни в коем случае не являюсь экспертом в области повреждений баз данных. Я всего лишь обычный администратор базы данных, который с надеется что никогда не столкнется с повреждениями, но хочу быть настолько подготовленным, насколько это возможно, если это произойдет.
Таким образом, я собираюсь сосредоточиться на практических аспектах использования инструментов и скриптов, которые администратор базы данных может использовать для борьбы с Data Corruption, в основном оперируя семейством команд DBCC.
Я не буду слишком глубоко погружаться в недра механизма хранения SQL Server, где можно встретить всевозможные эзотерические термины, относящиеся к тому, как SQL Server физически распределяет или отображает данные в файле (GAM/ SGAM страницы, страницы PFS, цепочки IAM и многое другое). В качестве ссылки на подобный уровень детализации я не могу сделать ничего лучше, чем указать вам на работы Пола Рэндала, в его блоке в категории Corruption: https://www.sqlskills.com/blogs/paul/category/Corruption/
Он многое сделал для разработки команд DBCC, и является признанным экспертом в области повреждений данных и, безусловно, у него больше всего «кислорода в баллоне» для необходимого погружения.
DBCC CHECKDB
DBCC CHECKDB — это основная команда, которую администратор базы данных должен использовать для проверки и исправления ошибок согласованности в базах данных SQL Server. DBCC существует уже много лет и представлено в большинстве версий SQL Server. Можно встретить два варианта расшифровки этой аббревиатуры: Database Consistency Checks либо Database Console Commands, последняя из них более точна, поскольку DBCC включает команды, которые выходят за рамки простой проверки согласованности базы данных.
Однако для нас сейчас интересны только согласованность и целостность баз данных. DBCC CHECKDB на самом деле объединяет другие команды DBCC: DBCC CHECKCATALOG, DBCC CHECKALLOC и DBCC CHECKTABLE. Запуск DBCC CHECKDB включает в себя выполнение и этих команды, поэтому отпадает необходимость запускать их потом отдельно.
Чтобы продемонстрировать, как использовать эти инструменты для поиска и устранения повреждений данных, мне сначала нужно будет создать базу, а затем совершить «злодеяние» по повреждению в ней данных. Если мы начнем с нуля, это облегчит поиск и последующее повреждение страниц данных и/или индексов, поэтому давайте создадим совершенно новую, «незапятнанную» базу с метким названием «NEO». Как видно на рис. 1, в этой новой базе данных не создано ни одного объекта.
Чтобы убедиться, что файлы NEO ещё не повреждены, мы можем запустить команду DBCC CHECKDB, результат которой показан на рисунке 2.
Как и ожидалось, сообщений об ошибках согласованности или распределения не было, но все это очень скоро изменится… Я упомянул, что в конце этой книги появится монстр, и это не милый старый Гровер из «Улицы Сезам».
Пожалуйста, не идите на следующую страницу!
DBCC PAGE
Ага, вы все еще читаете, я вижу! Что ж, прежде чем мы выпустим монстра, я хочу показать вам еще одну очень важную команду DBCC, о которой вы, возможно, не знаете, а именно DBCC PAGE (sys.dm_db_page_info). Она «официально» не документирована, поскольку Microsoft её не поддерживает, но на самом деле я нашел кучу информации об этой команде из хорошо известных и уважаемых источников, таких как Пол Рэндал (Paul Randal), так что я больше не считаю ее недокументированной.
Синтаксис прост:
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
Однако выводимый командой результат может быть весьма пугающим для непосвященного администратора баз данных. Итак, прежде чем мы представим монстра, который портит базы данных, я хочу просмотреть с помощью DBCC PAGE на базу данных NEO. Команда выглядит следующим образом:
DBCC PAGE (NEO,1,1,3)
Первая цифра после имени базы «1» — это номер файла, вторая цифра «1» — это номер страницы, а последняя цифра «3» — это параметр подробности выводимой информации, принимает значения от 0 до 3. Значение «3» указывает, что мы хотим видеть не только заголовок страницы, но и детали. Не очень впечатляющий результат показаны на рисунке 3.
Причина того, что результаты оказались не очень информативными, заключается в том, что мы забыли включить важный флаг трассировки (3604). Если вы работаете с SQL Server и не знакомы с флагами трассировки, свяжитесь со мной, и мы поговорим об этом за кружкой пива. На самом деле, я не против, и буду рад новому товарищу и возможности проявить педантичность.
Однако сейчас я просто отмечу, что для просмотра вывода команды DBCC PAGE нам нужно запустить команду DBCC TRACEON. Конкретно:
DBCC TRACEON (3604)
На рис. 4 показаны результаты повторного выполнения DBCC PAGE с включенным флагом трассировки.
В нижней части листинга видно, что страницы 1:172 – 1:383 не распределены и все страницы имеют коэффициент заполнения 0%. Напомним, что эта база данных без таблиц или каких-либо других объектов, а потому и без вставленных куда-нибудь данных.
Итак, давайте теперь создадим простую таблицу и вставим в нее некоторый объём данных. Скрипт для этого показан в листинге 1. Он создает в базе данных NEO таблицу с именем ONE и вставляет в нее 1000 записей (на самом деле 999). Простые вещи, но важным моментом в контексте этого примера является то, что эта загрузка данных приведет к выделению дополнительных страниц в базе данных и заполнению их данными, и мы сможем полюбоваться на эти новые страницы.
USE [NEO]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ONE]')
AND type in (N'U'))
DROP TABLE [dbo].[ONE]
GO
CREATE TABLE [dbo].[ONE](
[NEOID] [int] NULL,
[NEOTEXT] [nchar](50) NULL
) ON [PRIMARY]
GO
BEGIN Tran T_Time
DECLARE @SQL_Alphabet varchar(26)
SET @SQL_Alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @rnd_seed int
@rnd_seed = 26
DECLARE @counter int = 1
WHILE @counter < 1000
BEGIN
Insert Into ONE
Values (
@counter,
(select SUBSTRING (@SQl_alphabet,
Cast(RAND() * @rnd_seed as int) + 1,
CAST(RAND() * @rnd_seed as int) + 1)
)
)
SET @counter = @counter + 1
END
Commit Tran T_TimeЛистинг 1. Создание и заполнение таблицы ONE.
На рис. 5 показан образец данных, которые были вставлены.
На рисунке 4 видно, что для пустой базы данных страницы 1:172 — 1:383 ещё не были распределены. Повторный запуск DBCC PAGE должен был показать, что для размещения данных были выделены дополнительные страницы, и они имеют разный процент заполнения. На рис. 6 показан новый результат.
Теперь мы видим, что страницы 1:184 – 1:189 распределены и заполнены на 100 процентов. Выбрав одну из новых страниц (1:184), содержащую только что загруженные данные, можно снова запустить DBCC PAGE для этой конкретной страницы и получить полную «корзину» информации, как показано на рис. 7.
На рисунке видно, например, что в результате возвращаются фактические значения для NEOID и NEOTEXT, 553 и UVWXYZ соответственно. Также там представлен шестнадцатеричный дамп, указывающий на конкретное место в файле данных, где хранится запись с NEOID = 533: 10006c00 29020000...
Если вы не эксперт в понимании шестнадцатеричных чисел, не бойтесь; я тоже такой. Однако, используя данную информацию, можно найти эту запись и изменить ее вне процесса SQL Server, что нанесет базе серьезный ущерб. Однако для этого «вредительства» мне понадобится мой верный шестнадцатеричный редактор, о котором я вскоре расскажу.
Повреждение страниц с данными
Мы знаем, что таблица ONE в базе данных NEO представляет собой кучу, поэтому любое внешнее повреждение будет непосредственно для страниц данных, а не в каком-либо из возможных некластерных индексов.
Случай с повреждением страниц индекса на самом деле не так драматичен, поскольку данные в индексе являются «дубликатами» данных с листового уровня, и поэтому восстановить ущерб относительно легко. Мы рассмотрим этот не смертельный вариант повреждений после того, как рассмотрим «вероломное вмешательство» и, надеюсь, успешное восстановление после повреждения данных в нашей являющейся кучей таблице.
Подлог шестнадцатеричного числа в данных
В мире существует множество редакторов в шестнадцатеричном формате, многие из них бесплатны или имеют бесплатный пробный период. Для этой статьи я загрузил пробную версию «Hex Editor Neo» разработанный HHD Software.
Шестнадцатеричный редактор позволяет администратору просто открывать и просматривать содержимое любого файла, в нашем случае файла данных базы. Хотя это интересное занятие, я бы рекомендовал его только для целей тестирования или обучения, так как это очень опасный инструмент в неопытных руках.
Здесь мы будем использовать шестнадцатеричный редактор для «обнуления» записи в файле данных базы, причём только на одной странице. Это приведет к повреждению, похожему на те, что возникают при аппаратных проблемах, из-за которой на диск записывается несогласованная информация, и при этом база данных не становится нечитаемой для SQL Server.
И хотя я не говорил об этом до сих пор…
Не делайте дальше ничего без предварительного резервного копирования базы данных!
Данные, которые я ухайдакаю (так говорят южане) путём обнуления, находятся на странице данных, показанной на рисунке 7, а именно на 1:184. Чтобы повредить данные на этой странице, сначала нужно остановить службу SQL Server, чтобы корневой файл данных не использовался. У меня это:
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataNEO.mdf
Затем я просто открываю «Hex Editor Neo» и нахожу местоположение нужной записи с помощью NEOID=553 и NEOTEXT ="UVWXYZ", которую мы ранее увидели с помощью DBCC PAGE.
Большинство шестнадцатеричных редакторов, включая «Hex Editor Neo», имеют возможность поиска значений в файле. Вспомним результаты вывода команды DBCC PAGE с информацией, которая размещена на странице 1:184, мы просто возьмём значение 10006c00 29020000 и задав его для поиска найдём запись где NEOID равно 553. Как вы можете увидеть на рисунке 8, запись в шестнадцатеричном редакторе выглядит почти идентично выводу команды DBCC PAGE.
Далее я собираюсь внести в данные одно небольшое изменение, обнулив букву «U», подменив 55 на 00. Вот и все. На рис. 9 показан результат подмены.
Затем я сохраню файл и закрою шестнадцатеричный редактор. Вы тоже должны так сделать, иначе файл данных останется используемым, и будет невозможно инициализировать базу данных после запуска SQL Server. Теперь, наконец, настало время выпустить монстра…
Противостояние с монстром Data Corruption
На первый взгляд все выглядит нормально. База NEO данных запущена и доступна, и в журнале событий не было сообщений об ошибках. В Management Studio я могу без проблем детализировать объекты базы данных, включая таблицу ONE. Однако если я попытаюсь запросить таблицу с помощью SELECT * FROM ONE, произойдет нечто пугающее, как показано в листинге 2.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x9a3e399c; actual: 0x9a14b99c).
It occurred during a read of page (1:184) in database ID 23 at offset 0x00000000170000 in file
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATANEO.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.Листинг 2: Последствия Data Corruption в таблице ONE.
Это какое-то шоу ужасов, которое администраторы баз данных не хотели бы увидеть. Очевидно, это очень серьезная ошибка и опасное повреждение. Такая ошибка будет выдаваться каждый раз, когда запись 553 будет попадать в результаты запроса, поэтому любой просмотр таблицы будет приводить к проявлению проблемы.
Такие повреждения нужно уметь быстро исправлять. К счастью, перед повреждением файла данных мы сделали резервную копию. Поэтому, если ничего не поможет, я могу воспользоваться этим файлом резервной копии для восстановления данных. При решении проблем с подобными повреждениями критически важно, чтобы у вас были исправные резервные копии. К сожалению, в реальности это повреждение может оставаться незамеченным в течение нескольких дней, а это будет означать, что ваши резервные копии также будут содержать повреждённые данные.
Если это так, то в какой-то момент вы можете столкнуться с самым худшим из возможных сценариев, а именно с потерей данных. Однако, прежде чем смириться с этим фатальным исходом, я собираюсь победить монстра и посмотреть, смогу ли я решить проблему с помощью DBCC CHECKDB.
У DBCC CHECKDB много разных параметров, здесь я коснусь лишь некоторых из них. DBCC CHECKDB за время своего существования много раз улучшалась и подвергалась значительным переработкам в релизе SQL Server 2005 и более поздних версиях. Одним из самых полезных усовершенствований для администратора баз данных, работающего над устранением проблем с повреждением в базе, является большое количество подробных и полезных сообщений об ошибках.
Итак, погнали, давайте посмотрим, насколько всё плохо и что с этим можно поделать. Для начала я выполню ограниченную проверку согласованности данных на физическом уровне базы данных. Сделать это можно с помощью следующей команды:
DBCC CHECKDB ('neo') WITH PHYSICAL_ONLY;
GOНа рис. 10 показаны её результаты, которые, как и ожидалось, не вдохновляют.
Наихудшим из узнанного является предпоследняя строка, которая говорит, что REPAIR_ALLOW_DATA_LOSS это минимальный уровень исправления обнаруженных ошибок. Это означает, что мы можем исправить ущерб, запустив DBCC CHECKDB с опцией REPAIR_ALLOW_DATA_LOSS, но, как понятно из имени параметра, это приведет к потере данных.
Есть два других уровня восстановления, которые для нас были бы предпочтительнее: REPAIR_FAST или REPAIR_REBUILD. Первый имеется только для обратной совместимости и не выполняет восстановление базы данных с 2005 года. Если бы минимальный вариант восстановления был REPAIR_REBUILD, это указывало бы на то, что повреждение можно было бы легко восстановить, поскольку оно затронуло восстановимую структуру, например, некластерный индекс. Такое повреждение можно исправить перестроением индекса и без опасности потери данных.
Только в крайнем случае рекомендуется использовать варианты восстановления DBCC CHECKDB которые могут привести к потере данных. Причем восстановление из резервной копии очевидно является предпочтительным выбором, если это приведёт данные в неповрежденное состояние. Это, конечно, будет возможным только если сама резервная копия не содержит поврежденных данных.
Однако сейчас я собираюсь действовать в соответствии с тем, что рекомендовано в выводе команды DBCC CHECKDB и запускать её с параметром, который определяет вариант быстрого восстановления: REPAIR_ALLOW_DATA_LOSS. Для работы этой опции база данных должна быть в однопользовательском режиме, поэтому синтаксис будет следующим:
ALTER DATABASE NEO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB ('neo', REPAIR_ALLOW_DATA_LOSS)
GOРезультаты выполнения DBCC CHECKDB команды показаны в листинге 3.
DBCC results for 'ONE'.
Repair: The page (1:184) has been deallocated from object ID 2121058592, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043301888 (type In-row data).
Msg 8928, Level 16, State 1, Line 1
Object ID 2121058592, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043301888 (type In-row data): Page (1:184) could not be processed. See other errors for details.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2121058592, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043301888 (type In-row data), page (1:184). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -4.
The error has been repaired.
There are 930 rows in 14 pages for object "ONE".Листинг 3: Ошибка устранена, но данные потеряны.
«Хорошей» новостью является то, что ошибки теперь исправлены. Плохая новость заключается в том, что монстр забрал с собой данные, повреждённая страница в файле данных теперь очищена. Попутно обратите внимание, что в выходных данных присутствует идентификатор объекта таблицы, в которой произошло повреждение, а также идентификатор индекса, который в данном случае равен 0, поскольку у таблицы нет индексов.
Итак, на данный ммент я знаю, что потерял данные, и это была только одна страница данных. Но неизвестно сколько именно данных было на этой странице. Простой SELECT показывает, что я потерял не только строку, которую я «подправил» (NEOID 553), но и еще 68 строк, вплоть до строки 621. Рисунок 11 демонстрирует факт потери этих строк.
Эти строки можно легко восстановлены, если есть актуальная резервная копия. У Вас же есть хорошая резервная копия, верно? Верно? Предполагая, что Вы её сделали, понадобится восстановить резервную копии в другую базу данных, например, пусть она называется NEO2. После этого можно синхронизировать две таблицы путем копирования отсутствующих строк. Синхронизация двух таблиц может быть выполнена с помощью простого оператора INSERT INTO, подобного показанному в листинге 4.
INSERT INTO NEO..ONE ( NEOID, NEOTEXT )
SELECT NEOID,
NEOTEXT
FROM NEO2..ONE
WHERE NEOID NOT IN ( SELECT NEOID
FROM NEO..ONE )Листинг 4: Синхронизация двух таблиц для восстановления потерянных строк данных.
В этом «контролируемом примере» исправление довольно простое. В других сценариях с гораздо более высоким уровнем повреждения может потребоваться прибегнуть к другим мерам для восполнения утраченных данных после восстановления с потерей данных. Эти средства почти всегда будут включать восстановление базы данных из резервной копии, поэтому я еще раз подчеркиваю важность надежной, проверенной и хорошо документированной политики резервного копирования базы данных.
Повреждение некластерных индексов
Ранее я уже говорил, что с повреждением некластерного индекса справиться намного проще, чем с повреждением страницы данных на листовом уровне, поскольку эти индексы являются дополнительными, избыточными копиями реальных данных и могут быть легко перестроены. Однако было бы интересно доказать эти утверждения. Я воспользуюсь той же техникой подмены данных в шестнадцатеричном редакторе, чтобы повредить некластерный индекс, а не данные таблицы, и посмотрю, как с этим удастся потом справиться.
Мы уже знаем, что в результатах, выводимых командой DBCC индикатором того, относится ли повреждение к индексу или таблице, является значение IndexID. Для нашей таблицы ONE, которая является кучей, в листинге 3 значение IndexID = 0, поскольку для таблицы не были ещё созданы индексы. Значение IndexID = 1 означает кластерный индекс, а значение от 2 до 250 указывает на возможные некластерные индексы.
Предположим, что я выполнил необходимое восстановление базы из резервной копии и по столбцу NEOID таблицы ONE создал некластерный индекс с именем: NEOID.
Во-первых, нам нужно узнать на каких страницах разместился новый индекс, который создан для таблицы ONE. Затем мы посмотрим на страницу некластерного индекса средствами DBCC PAGE, чтобы определить какие данные нужно подменить, имитируя повреждение индекса, и при этом не повредить страницы данных в куче.
Чтобы узнать размещение страниц индекса, можно воспользоваться другой недокументированной командой DBCC, которая называется DBCC IND. Вот синтаксис этой команды:
DBCC IND (DBID, TABLEID,-1)
Итак, чтобы применить эту команду для недавно проиндексированной таблицы ONE, выполним следующее:
DBCC ind(23, 2121058592, -1)
Результаты показывают несколько строк, у которых значения IndexID в основном 0, а также несколько строк с IndexID = 2, что указывает на страницы некластерного индекса. Нам нужно запомнить продемонстрированное на рис. 11 значение указателя страницы для случая, когда IndexID = 2. Выберем там страницу 180.
Теперь можно снова запустить DBCC PAGE, и мы получим нужную нам информацию о странице:
DBCC TRACEON (3604);
GO
DBCC PAGE (NEO,1,180,3)
GOРезультаты выглядят совсем иначе, чем при просмотре страницы данных. Я вижу возвращенное шестнадцатеричное значение (HEAP RID), представляющее каждую строку индекса для запрошенной страницы, как это показано на рисунке 12.
Я снова использовал шестнадцатеричный редактор, чтобы обнулить HEAP RID, и снова это повредило базу данных. Однако есть одно существенное отличие: на этот раз, когда я запускаю DBCC CHECKDB('neo') WITH PHYSICAL_ONLY, значение IndexID поврежденного объект указывается как «2», то есть это код код некластерного индекса.
Приняв к сведению эту информацию, мы можем предложить иные способы устранения повреждений, кроме восстановления из резервной копии или запуска DBCC CHECKDB с REPAIR_ALLOW_DATA_LOSS, что потенциально приводит к потере данных.
Мы можем просто удалить и повторно создать некластерный индекс, используя код из листинга 5.
USE [NEO]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ONE]')
AND name = N'NEO_ID_NC')
DROP INDEX [NEO_ID_NC] ON [dbo].[ONE] WITH ( ONLINE = OFF )
GO
USE [NEO]
GO
CREATE NONCLUSTERED INDEX [NEO_ID_NC] ON [dbo].[ONE]
([NEOID] ASC)
WITH (SORT_IN_TEMPDB = ON, ONLINE = ON, ALLOW_PAGE_LOCKS = OFF)
GOЛистинг 5: Удаление и повторное создание поврежденного некластерного индекса.
Теперь, после того, как мы немного углубились в способы поиска и исправления некоторых проблем из-за повреждения данных, давайте обратимся к процессу обнаружения повреждений.
В поисках монстра Data Corruption
Нам нужен наилучший способ узнавать о повреждения в базах данных прежде чем они попадут в резервные копии и превратятся в более серьезную проблему. Как же этого добиться?
Один из вариантов — настроить регулярные проверки целостности с помощью планов обслуживания. Такое решение будет простым, поддерживаемым Майкрософт, прозрачным, полезным и, безусловно, лучше, чем отсутствие проверок целостности вообще. Тем не менее, мне нравится уровень контроля и гибкости, которые можно получить, создавая свои скрипты для выполнения тех же функций, что и планы обслуживания. Поэтому я поделюсь с вами сценарием, который я использую для перебора всех баз данных, включая системные базы данных, и получения отчета об ошибках, возвращаемых командой DBCC CHECKDB.
С помощью этого скрипта и тривиального способа извлечения во временную таблицу вывода результатов DBCC CHECKDB, вы не позволите монстру незаметно проникнуть в вашу инфраструктуру данных. И вы сможете действовать обдуманно, когда будете решать проблему сразу после обнаружения.
Скрипт в листинге 6 пробегает по всем базам данных экземпляра SQL Server, фиксирует ошибки и отправляет вам сообщение о числе обнаруженных ошибок, чтобы вы могли более подробно исследовать проблему при её обнаружении.
CREATE TABLE #CheckDBTemp (
Error INT
, [Level] INT
, [State] INT
, MessageText NVARCHAR(1000)
, RepairLevel NVARCHAR(1000)
, [Status] INT
, [DBID] INT
, ObjectID INT
, IndexID INT
, PartitionID BIGINT
, AllocUnitID BIGINT
, [File] INT
, Page INT
, Slot INT
, RefFile INT
, RefPage INT
, RefSlot INT
, Allocation INT
)
-- Needed variables
DECLARE @TSQL NVARCHAR(1000)
DECLARE @dbName NVARCHAR(100)
DECLARE @dbErrorList NVARCHAR(1000)
DECLARE @dbID INT
DECLARE @ErrorCount INT
DECLARE @EmailSubject NVARCHAR(255)
DECLARE @ProfileName VARCHAR(100)
DECLARE @EmailRecipient VARCHAR(255)
-- Init variables
SET @dbID = 0
SET @dbErrorList = ''
SET @EmailSubject = 'Integrity Check Failure on ' + CAST(COALESCE(@@SERVERNAME, 'Server Name Not Available') AS NVARCHAR)
SET @ProfileName = 'Notifications'
SET @EmailRecipient = 'rlandrum13@cox.net'
-- CYCLE THROUGH DATABASES
WHILE(@@ROWCOUNT > 0)
BEGIN
IF( @dbID > 0 )
BEGIN
SET @TSQL = 'DBCC CHECKDB(''' + @dbName + ''') WITH TABLERESULTS, PHYSICAL_ONLY, NO_INFOMSGS'
INSERT INTO #CheckDBTemp
EXEC(@TSQL)
SELECT @ErrorCount = COUNT(*) FROM #CheckDBTemp
IF( @ErrorCount > 0 )
BEGIN
SET @dbErrorList = @dbErrorList + CHAR(10) + CHAR(13) + 'Issue found on database : ' + @dbName
SET @dbErrorList = @dbErrorList + CHAR(10) + CHAR(13) + (Select Top 1 MessageText from #CheckDBTemp)
END
TRUNCATE TABLE #CheckDBTemp
END
IF SUBSTRING(CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')),1,1) = '8'
BEGIN
SELECT TOP 1 @dbName = name, @dbID = dbid
FROM sysdatabases WHERE dbid > @dbID
AND name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'Online'
ORDER by dbid
END
ELSE
BEGIN
SELECT TOP 1 @dbName = name, @dbID = database_ID
FROM sys.databases WHERE database_ID > @dbID
AND name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'Online'
ORDER by database_ID
END
END
-- If errors were found
IF( @dbErrorList <> '' )
BEGIN
IF SUBSTRING(CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')),1,1) = '8'
BEGIN
EXEC master..xp_sendmail @recipients = @EmailRecipient, @subject = @EmailSubject, @message = @dbErrorList
END
ELSE
BEGIN
EXEC msdb..sp_send_dbmail @profile_name = @ProfileName, @recipients = @EmailRecipient, @subject = @EmailSubject, @body = @dbErrorList, @importance = 'High'
END
END
DROP TABLE #CheckDBTempЛистинг 6: Сценарий для поиска и сообщения о повреждении базы данных.
Вы могли заметить, что в коде используется параметр DBCC CHECKDB, который я ранее не упоминал, а именно WITH TABLERESULTS. Как следует из названия, результаты выполнения команды возвращаются в виде таблицы. Этот параметр не упоминается в электронной документации, но очень полезен для автоматизации проверки ошибок с помощью SQL Agent Jobs или ваших собственных скриптов.
Этот скрипт можно легко изменить, чтобы он отсылал сообщение электронной почты о том, что все базы данных, кроме базы данных NEO, не имеют ошибок. Это может несколько смягчить шок, если будет видно, например, что из 20 баз данных повреждена будет только одна. Я знаю, что это могло бы мне немного помочь… В любом случае, когда будет обнаружено повреждение, вы получите сообщение, похожее на то, что изображено на рисунке 14. И это сообщение действительно будет тем монстром, который разбудит вас посреди ночи в холодном поту.
В этом письме я показываю ObjectID, IndexID и другую информацию о поврежденной странице, а также вывожу имя базы данных. Этого должно быть достаточно, чтобы продолжить расследование с помощью «новых» инструментов: DBCC PAGE, DBCC INDID и DBCC CHECKDB в разных вариантах восстановления. Или это должно стать тревожным сигналом ктому факту, что вам, возможно, придется восстанавливать данные из резервной копии, не содержащей повреждения.
Резюме
В этой статье мы повредили данные в базе данных и бегло рассмотрели несколько недокументированных параметров DBCC, которые помогали восстановить поврежденные данные. Здесь мы затронули тему только поверхностно, упрощённо показав, как редактировать на странице шестнадцатеричные значения, а также немного научились понимать как сопоставлять результаты различных команд DBCC при устранении неполадок, связанных с повреждением данных.
Я не устану напоминать, что наличие продуманного плана резервного копирования является самой важной задачей для администратора баз данных. Хотя в этой статье я не описывал подробно резервное копирование и восстановление (только по этой теме можно написать целую книгу), но сама тема статьи представляет вескую причину иметь хорошие резервные копии как часть мер по обеспечению высокой доступности и защиты данных в рамках аварийного плана восстановления. Поврежденная база данных действительно угрожает катастрофой и может привести к длительному простою. Наверняка Вам не хочется когда‑нибудь прийти к своему начальнику, или начальнику вашего начальника, и сказать что вы потеряли данные безвозвратно. Если вам предстоит такое, лучше сразу актуализировать свое резюме, при условии, что диск, где оно хранилось, также не поврежден.
Часто у DBA возникает паника после обнаружения в базе данных Data Corruption любого уровня. Если нет проверенных резервных копий и проигнорированы основные рекомендации, которые помогают устранению неполадок, у администратора нет безопасного места, где он мог бы спрятаться, когда монстр впадает в ярость. Все, что вы сможете сделать, это починить базу с возможностью потери данных на сотнях страниц, а затем забиться в уголок своего рабочего места, которое скоро опустеет.
Если у вас есть проверенные резервные копии и вы умеете устранять повреждения без потери данных, то ваше рабочее место однажды может переместиться в офис руководителя с тонированными стёклами во всю стену, из которых открывается отличный вид на мир, где нет монстров.
Приглашаем разобраться в важных темах – устранения ошибок MS SQL и восстановления базы данных. Получите представление о разных способах устранения проблемы, которые следует использовать при различных обстоятельствах.
Восстановление баз данных
Специалисты пользуются несколькими способами восстановления баз данных (БД). Наиболее простой и удобный – воспользоваться программой (SSMS) SQL Server Management Studio.
Как восстановить
Узнать, где находится SQL Server Management Studio, довольно легко. Microsoft Windows Server 2012 R2 располагается в стандартном перечне программных продуктов. В Microsoft Windows Server 2008 R2 следует зайти в меню Пуск и отыскать Microsoft Windows Server 2012. Там смотреть Microsoft SQL Server Management Studio.
Далее следует ввести тип сервера с именем, а чтобы подтвердить подлинность – информацию, требуемую для прохождения авторизации. Нажать Соединить (Connect).
В левом углу из обозревателя (Object Explorer) раскрыть Базы данных (Server Objects). Из представленного перечня отобрать базу, подлежащую восстановлению либо ту, данные которой будут восстанавливаться. На выбранном файле кликнуть мышкой и в выпавшем перечне выбрать Задачи (Tasks), затем Восстановить (Restore), потом База данных… (Databases …).
Проделанные шаги дадут старт процессу Restore Database, а значит требуемая база данных начнет восстанавливаться. Следует сделать выбор источника для Restore Database.
Чтобы возобновить базу данных, при запуске мастер восстановления сделает попытку автоматом определить очередность файлов резервных копий. В том случае, когда базу данных нужно загружать из определенной папки либо устройства, понадобится:
- Переключить соответствующую кнопку на Устройство (From device).
- Прописать, откуда восстановится БД.
- Выбрать инфобазу, в которую произведется загрузка данных (Destination for restore). Ею может выступать любая БД, которая регистрировалась на SQL Server (в том числе и база, с которой создавалась резервная копия).
В программе реализована возможность указания времени, необходимого для восстановления БД. Для этого необходимо просто кликнуть по кнопке Временная шкала… (Timeline). Если существует скопированный журнал транзакций или checkpoint в нем, то требуемый промежуток времени может быть указан с высокой точностью (вплоть до секунды).
Если требуется провести копирование БД, то во вкладке Файлы (Files) нужно будет прописать путь к файлам выбранной инфобазы.
Настройка дополнительных параметров
Также в программе реализована возможность настройки дополнительных параметров. Выставляя тот или иной флажок, можно регулировать необходимые действия. Следует выбрать соответствующий флажок, чтобы в процессе восстановления БД:
- Которая опубликована не на сервере, где она создавалась, сохранились настройки репликации, поможет отметка «Сохранить параметры репликации). Он важен, если при резервном копировании была реплицирована БД;
- была проведена перезапись файлов БД с именем, которое указывалось в качестве базы назначения – нужно поставить отметку «Перезаписать существующую базу данных»;
- сузить доступность к базе всем, кто не sysadmin, db_owner, dbcreator – нужно поставить флажок «Ограничение доступа к восстановленной базе данных»;
- старту должен предшествовать перевод БД в режим одного пользователя, а по его завершению вернуть в пользование для множества пользователей – поставить отметку «Закрыть существующие соединения»;
- чтобы провести требуемое резервное копирование завершающего фрагмента журнала транзакций, следует поставить отметку «Создание резервной копии заключительного фрагмента журнала перед восстановлением». Если в окошке Временная шкала резервного копирования (Backup Timeline) для временной точки требуется эта резервная копия, то отметка будет поставлена системой, без возможности снятия;
- чтобы после завершения восстановления каждой резервной копии уточнялась необходимость продолжения процесса – следует поставить отметку «Выдавать приглашение перед восстановлением каждой резервной копии» (Prompt before restoring each backup). Достаточно полезен, т.к. после того, как восстановлено определенное количество резервных копий можно остановить дальнейшую цепочку восстановительных процессов.
Настроив все важные параметры следует нажать ОК. Тем самым запустится процесс. Соответствующее уведомление сообщит об его окончании.
Восстановление базы в новое место
Чтобы перенести базу данных MSSQL Server по другому пути каталога либо сделать ее копию, следует знать, как восстановить БД в новую папку. Полезно знать как ее переименовывать. Для этого можно воспользоваться вышеупомянутой программой SSMS и T-SQL.
Подготовка к восстановлению базы данных
Перед стартом процесса восстановления нужно соблюдать ряд требований:
- Когда осуществляется процесс восстановления базы, доступ к ней может быть только у системного администратора. Для остальных пользователей доступ должен быть ограничен.
- Перед восстановлением нужно сделать резервную копию активного журнала транзакций.
- Чтобы восстановить зашифрованную базу необходим доступ к сертификату либо ассиметричному ключу, который применялся в качестве ее шифратора. Не имея доступа к ним, восстановление зашифрованной БД становится невозможным. Потому, такой сертификат следует хранить, пока может понадобиться резервное копирование.
После того, как база данных версии SQL Server 2005 (9.x) либо более поздней, восстановится, произойдет автоматическое обновление, и она станет доступной.
Если присутствуют полнотекстовые индексы
В том случае, когда в БД SQL Server 2005 (9.x) присутствуют полнотекстовые индексы, в момент ее обновления произойдет импорт, сброс либо перестроение. Результат зависит от того, какое значение проставлено в свойствах сервера upgrade_option.
При обновлении такие индексы станут недоступны, если upgrade_option имеет значения:
- 2 в режиме импорта;
- 0 в режиме перестроения.
Продолжительность поцессов импорта и перестроения зависит от того, какой объем занимают данные. Импорт может длиться пару часов, а процесс перестроения – гораздо дольше (может продолжаться в 10 раз дольше).
В том случае, когда выбран процесс Импорт, а доступ к полнотекстовому каталогу отсутствует, то произойдет перестроение одноименных индексов, которые связаны с ним. Для изменения свойств upgrade_option необходимо воспользоваться процедурой sp_fulltext_service.
Соблюдение правил безопасности
Чтобы обезопасить себя, крайне не рекомендуется проводить присоединение либо восстановление БД, которые были получены из ненадежных или вовсе неизвестных источников. Они могут содержать вредоносные коды, способные:
- запускать выполнение инструкций T-SQL, не предусмотренных системой;
- вызывать ошибки в результате изменения схемы либо самой структуры БД
Если БД получена из источников, не внушающих доверия, то перед началом ее использования необходимо:
- протестировать по инструкции DBCC CHECKDB;
- исследовать исходный и иные коды БД, изучить процедуры.
Инструкции RESTORE
На ход реализации этих инструкций влияет факт существования восстанавливаемой базы. Если база:
- присутствует, то разрешения получают пользователи sysadmin, dbcreator, dbo (владелец БД) по умолчанию;
- отсутствует, то пользователям потребуются разрешения CREATE DATABASE.
Разрешения на реализацию таких инструкций выдаются в соответствии с ролями. В соответствии с ними сервер всегда имеет доступ к данным о членстве. Разрешение RESTORE отсутствует у пользователей с ролями db_owner. Причина в том, что членство может быть проверено лишь в тех случаях, когда к базе данных всегда есть доступ и она не повреждена. А это иногда не соблюдается в процессе выполнения инструкций RESTORES.
Пошаговая инструкция восстановления БД в новую папку в SSMS
- Открыть SSMS и произвести подключение к SQL Server Database Engine.
- Щелкнуть мышкой по имени сервера, чтобы развернулось его дерево.
- Кликнуть мышкой на Базы данных, потом – по Восстановить базу данных.
- В разделе Источник выбрать Общие, чтобы определить соответственное расположение и источник копий, подлежащих восстановлению. Пользователю предлагается выбрать нужный вариант (Базы данных либо Устройства). Особенности:
- При выборе Базы данных открывается перечень БД, где можно выбрать нужную. В нем представлены лишь те базы, у которых резервные копии создавались по журналу msdb. Стоит отметить, что для БД на целевом сервере, резервные копии которых поступили с иных серверов, подобный журнал будет отсутствовать. В таких ситуациях следует выбирать вариант Устройство. Это позволит руками прописать файл, а в случае необходимости – обозначить устройство для выполнения восстановления.
- Устройство можно выбрать, воспользовавшись кнопкой обзора (…). В результате появится окошко Выбор устройств резервного копирования. Перейти в окошко Тип носителя резервной копии, в котором из списка выбрать необходимый тип устройства. Если требуется добавить ряд устройств, это можно сделать с помощью кнопки Добавить в окошке Носитель резервной копии. Когда все необходимые устройства добавлены, необходимо вновь перейти на страницу Общие. Для этого следует нажать ОК в списке Носитель резервной копии. Обратившись к списку Источник: Устройство: База данных обозначить название БД, куда будет производиться восстановление. Пользователь может воспользоваться данным списком только при выборе Устройства. Можно выбирать лишь те БД, у которых на отобранном устройстве имеются резервные копии.
- Название новой базы для проведения восстановления автоматом сформируется в поле База данных в разделе Назначение. При желании оно может быть изменено. Для этого желаемое название вводится в окошке База данных.
- Далее перейти к Восстановить до. Пользователь может оставить значение До последней выбранной резервной копии (по умолчанию) либо кликнуть по Временной шкале. При выбре второго варианта всплывет соответствующее окошко Временная шкала …. В нем нужно указывать точное время.
- Необходимые резервные копии для восстановления можно выбрать в соответствующей сетке. В ней отражены все наборы, доступные в выбранном месте. Система сама предложит план восстановления отобранных копий, который будет использован по умолчанию. Он может быть переопределен, если в сетке изменить отобранные элементы.
- Для указания другого места расположения файлов базы, необходимо выбрать страницу Файлы после чего нажать на Переместить все файлы в папку. Следует указать вновь выбранное место расположения папок файлов данных и журнала.
- Если возникла необходимость – провести настройку параметров, как было рассказано выше.
Чтобы начать процесс, в котором будет восстанавливаться БД в новую папку с возможностью переименовывать ее, можно воспользоваться инструкциями Transact-SQL.
Как просмотреть отчет
Стандартный отчет «События резервного копирования и восстановления» позволяет получить сведения о том, когда проводилось:
- Резервное копирование определенной БД;
- операции восстановления базы MS SQL из них.
Данный отчет включает данные, касающиеся создания резервных копий:
- время, затраченное на это в среднем (Average Time Taken For Backup Operations);
- операции, которые прошли успешно (Successful Backup Operations);
- ошибки, которые были допущены (Backup Operation Errors);
- удачно прошедших восстановлений баз (Successful Restore Operations).
Чтобы он начал формироваться, следует в Обозревателе объектов выбрать нужную БД и щелкнуть по ней мышкой. Выбрать в меню Отчеты, а затем – Стандартный отчет. После этого кликнуть на События резервного копирования и восстановления.
Чтобы просмотреть информацию из сформированного отчета следует выбрать нужную группировку и раскрыть данные по ней.
Для восстановления поврежденной БД можно воспользоваться еще одним инструментом.
Как исправить ошибки в MS SQL с помощью Recovery Toolbox for SQL Server
Для восстановления поврежденной базы данных можно обратиться к помощи Recovery Toolbox for SQL Server. Для исправления ошибки (Error), следует воспользоваться пошаговой инструкцией восстановления данных из файла *.mdf, который был поврежден. Для этого необходимо:
- Скачать Recovery Toolbox for SQL Server.
- Установить программу следуя инструкциям и запустить ее.
- Из списка файлов выбрать файл *.mdf, который был поврежден.
- Осуществить предварительный просмотр тех данных, которые в процессе выполнения программы могут быть подвергнуты извлечению из базы MS SQL сервер, которая подверглась повреждению.
- Выбрать наиболее приемлемый способ, которым будут экспортироваться данные:
- сохранением на диск в качестве SQL-скрипта;
- выполнением SQL-скрипта в самой БД.
- Произвести выборку информации, требующей восстановления и сохранения.
- Начать восстановление нажатием Start recovery.
Данная программа создавалась, чтобы облегчить процесс восстановления поврежденных БД. Специально разработанная, оптимизированная для восстановления SQL Server, утилита поможет устранить ошибки и внести правки в разные типы повреждений *.mdf файлов и базы данных MS SQL Server.
Как становится понятно, для исправления ошибок и восстановления БД необходимо уметь пользоваться различными инструментами. Читайте, изучайте материалы по данной теме. Если возникнут вопросы – обязательно задавайте.
Также приглашаем на специальный курс по MS SQL в Otus.
Posted By: Mar. 12, 2020
How to check and repair MySQL Databases
You will need know how to check and repair MySQL databases or tables when you troubleshoot your website as they may have become corrupt. The mysqlcheck command is a maintenance tool that can be used to check, repair, analyze and optimize multiple tables from the command line. One of the best features of using mysqlcheck is that you don’t need to stop the MySQL service to perform the database maintenance.
In this tutorial, we will show you how to check/repair MySQL databases and tables.
Note : It is recommended to take a backup of your databases before performing a database repair operation.
Basic Syntax of mysqlcheck
A basic syntax of mysqlcheck is shown below:
mysqlcheck [OPTION] DATABASENAME TABLENAME -u root -p
A brief explanation of each option that you can use with mysqlcheck as shown below:
-c : Used to check a table for errors
-C : Used to check a tables that are changed after last week.
-a : Used to analyze tables.
-A : Used to check all databases.
-g : Used to check tables for version-dependent changes.
-B, –databases : Used to specify multiple databases.
-F : Used to check tables that are not closed properly.
–fix-db-names : Used to fix the database name.
–fix-table-names : Used to fix the table name.
–e : Used to perform an extended check.
-r : Used to repair corrupt table.
Check a Specific Table in a MySQL Database
In some cases, you need to check a specific table in a specific database. In that case, you can use the following syntax:
mysqlcheck -c databasename tablename -u root -p
For example, checks authors table in books database by running the following command:
mysqlcheck -c books authors -u root -p
You should get the following output:
books.authors OK
Check All Tables in a MySQL Database
If you want to check all the tables in a specific database use the following syntax:
mysqlcheck -c databasename -u root -p
For example, check all tables in books database by running the following command:
mysqlcheck -c books -u root -p
You should get the following output:
Enter password: books.accountant OK books.authors OK books.writer OK
Check and Optimize All Tables and All MySQL Databases
You can check all tables and all databases using the following command:
mysqlcheck -c -u root -p --all-databases
Output:
Enter password: books.accountant OK books.authors OK books.writer OK guest.MyGuests OK movies.netflix OK mysql.columns_priv OK mysql.component OK mysql.db OK mysql.default_roles OK mysql.engine_cost OK mysql.func OK mysql.general_log OK mysql.global_grants OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.password_history OK mysql.plugin OK mysql.procs_priv OK mysql.proxies_priv OK mysql.role_edges OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK
You can optimize all tables and all databases using the following command:
mysqlcheck -o root -p --all-databases
Output:
Enter password: books.accountant note : Table does not support optimize, doing recreate + analyze instead status : OK books.authors note : Table does not support optimize, doing recreate + analyze instead status : OK books.writer note : Table does not support optimize, doing recreate + analyze instead status : OK guest.MyGuests note : Table does not support optimize, doing recreate + analyze instead status : OK movies.netflix note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.columns_priv note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.component note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.db note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.default_roles note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.engine_cost note : Table does not support optimize, doing recreate + analyze instead status : OK
In the above output, you should see “Table does not support optimize” which means the InnoDB table that doesn’t support this option.
Repair MySQL Databases
To repair accountant tables in books database run the following command:
mysqlcheck -r books accountant -u root -p
Output:
mysqlcheck -r books accountant -u root -p Enter password: books.accountant OK
To repair all tables in both books and movies database run the following command:
mysqlcheck -r --databases books movies -u root -p
Output:
Enter password: books.accountant OK books.authors OK books.writer OK movies.netflix OK
To check and repair all tables in all databases run the following command:
mysqlcheck --auto-repair --all-databases -u root -p
Output:
Enter password: books.accountant OK books.authors OK books.writer OK guest.MyGuests OK movies.netflix OK mysql.columns_priv OK mysql.component OK mysql.db OK mysql.default_roles OK mysql.engine_cost OK mysql.func OK mysql.general_log OK mysql.global_grants OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.password_history OK mysql.plugin OK mysql.procs_priv OK mysql.proxies_priv OK mysql.role_edges OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK
Important note: InnoDB storage engine does not support repair. So you will need to change MySQL storage engine from InnoDB to MyISAM.
Check, Repair and Optimize MySQL Database with PHPMyAdmin
You can also check, repair and optimize tables and databases using the PHPMyAdmin web interface.
You can follow the below steps to check, repair and optimize tables and databases:
1- Open the phpMyAdmin tool through a web browser as shown below:
2- Select the affected database in the left pane. You should see all the tables in the right pane in the following screen:
3- Click Check All to select all the tables. At the bottom of the window, choose Check Table from the menu. You should see a summary of the tables in the following screen:
4- To repair the table, Check All to select all the tables and choose Repair Table from the menu. You should see the following page:
Then you should get a confirmation that the command been executed successfully:
5- To optimize the table, Check All to select all the tables and choose Optimize Table from the menu. You should see the following page:
Then you should get a confirmation that the command been executed successfully:
Conclusion
In the above tutorial, we learned how to check and repair MySQL table using mysqlcheck command-line tool. We also learned how to check, repair and optimize database tables using the PHPMyAdmin web interface. I hope you can now easily fix your corrupted tables using this tool.
Одной из ярких причин возникновения ошибок в базе данных может послужить неправильная остановка сервера MySQL. Как это обычно бывает, сбой в электропитании сервера, либо иные причины, повлекшие за собой банальное отключение машины, либо перезагрузку. Иногда, и нередко подобного рода сбои могут повлечь за собой проблемы, которые решаются лишь путем восстановления данных из бэкапа, и это к вопросу для чего нужно делать бэкапы. Наличие ошибок в базе данных может проявиться не сразу, однако если они есть, то вы их рано или поздно обязательно заметите. Проблемы, как правило, проявляются в виде ошибок после запросов к базе, либо база начинает уходить в раздумье на не свойственное для этого время.
Давайте посмотрим, что можно предпринять первым делом, чтобы попытаться исправить ситуацию. Утилита mysqlcheck как правило устанавливается по умолчанию с сервером MySQL может быть использована для проверки баз данных на ошибки. Рассмотрим пример ее использования.
Использование утилиты mysqlcheck
# mysqlcheck -u root -p --optimize --auto-repair --all-databases
Назначение параметров:
--optimize – Оптимизируем все базы. --auto-repair – Ремонтируем все базы. --all-databases – Проверяем ошибки у всех баз.
Если база данных больших размеров, а вам нужно срочно поправить определенную таблицу, то вы можете запустить проверку определенной таблицы в базе данных.
Проверка отдельной таблицы в базе данных:
# mysqlcheck -r имя_базы имя_таблицы_в_базе -u root -p
Исправление таблиц MyISAM.
Так же существует утилита myisamchk, отличается от предыдущей утилиты тем, что перед её использованием необходимо останавливать сервер баз данных, в то время как mysqlcheck может использоваться при работающем сервере. Рассмотрим пример использования утилиты myisamchk.
Останавливаем сервер MySQL
# service mysqld stop
Анализируем базу данных на наличие ошибок
# myisamchk -s /var/db/mysql/наша_база/*.MYI
Обратите внимание, что путь к базам данных у вас может отличаться, этот момент вам следует так же уточнить. Если есть ошибки, то появиться сообщение подобного вида:
myisamchk: warning: 2 client is using or hasn't closed the table properly MyISAM-table '/var/db/mysql/наша_база/ary_mem.MYI' is usable but should be fixed
Исправляем ошибки с помощью myisamchk
# myisamchk -r /var/db/mysql/наша_база/ary_mem.MYI
Исправляем ошибки по всем таблицам в базе (рекурсивно)
# myisamchk -r /var/db/mysql/наша_база/*.MYI
Процесс исправления ошибок будет сопровождаться подобным выводом сообщений в окне консоли:
- Fixing index 1 - Fixing index 2 - Fixing index 3
После того как ошибки исправлены, можно запустить сервер MySQL, чтобы убедиться что всё в порядке.
Запуск сервера MySQL
# service mysqld start
Хотелось бы отметить один момент, что команды остановки и запуска сервера MySQL, а так же пути расположения баз данных на вашем сервере могут отличаться, поэтому вам так же следует эти моменты учесть.
Ошибки SQL являются неотъемлемой частью работы с базами данных. Важно понимать их причины и способы решения, чтобы успешно разрабатывать и поддерживать приложения. В этой статье мы рассмотрим различные виды ошибок SQL, их возможные причины, а также методы решения таких проблем. Мы также предоставим ответы на часто задаваемые вопросы ошибках SQL и предложим полезные советы для профессионалов в области баз данных.
Основные виды ошибок SQL:
- Синтаксические ошибки
- Ошибки времени выполнения
- Логические ошибки
Оглавление
- 0.1 Синтаксические ошибки
- 0.2 Ошибки времени выполнения
- 0.3 Логические ошибки
- 1 Как исправить ошибки SQL: шаг за шагом
- 1.1 Определение типа ошибки
- 1.2 Анализ причин ошибки
- 1.3 Применение соответствующего решения
- 2 Часто задаваемые вопросы
- 2.1 Как наиболее эффективно найти и исправить ошибки SQL?
- 2.2 Влияет ли версия базы данных на возникновение ошибок SQL?
- 2.3 Как предотвратить ошибки SQL при разработке приложений?
Синтаксические ошибки
a. Неправильное использование ключевых слов (H4 Heading) b. Ошибки в структуре запроса (H4 Heading) c. Проблемы с кавычками и скобками (H4 Heading)
Ошибки времени выполнения
a. Ошибки доступа к данным (H4 Heading) b. Ошибки ограничений целостности (H4 Heading) c. Проблемы с производительностью (H4 Heading)
Логические ошибки
a. Неправильный выбор операторов (H4 Heading) b. Ошибки в вычислениях (H4 Heading) c. Проблемы с агрегацией данных (H4 Heading)
Чтобы разобраться подробнее – приходите на бесплатный курс
- Определение типа ошибки (H3 Heading)
- Анализ причин ошибки (H3 Heading)
- Применение соответствующего решения (H3 Heading)
Определение типа ошибки
- Используйте сообщения об ошибках
- Отслеживайте контекст запроса
Анализ причин ошибки
- Проверьте синтаксис
- Проверьте права доступа
- Убедитесь, что данные корректны
Применение соответствующего решения
- Исправьте синтаксические ошибки
- Решите проблемы с данными
- Оптимизируйте запросы
Часто задаваемые вопросы
- Как наиболее эффективно найти и исправить ошибки SQL?
- Какие инструменты могут помочь в диагностике и исправлении ошибок SQL?
- Влияет ли версия базы данных на возникновение ошибок SQL?
- Как предотвратить ошибки SQL при разработке приложений?
Чтобы разобраться подробнее – приходите на бесплатный курс
Как наиболее эффективно найти и исправить ошибки SQL?
- Используйте подходящие инструменты и ресурсы для отладки
- Разбивайте сложные запросы на простые
- Протестируйте запросы с разными наборами данных
Какие инструменты могут помочь в диагностике и исправлении ошибок SQL?
- Редакторы кода с поддержкой SQL, такие как Visual Studio Code или Sublime Text
- Среды разработки баз данных, такие как SQL Server Management Studio или MySQL Workbench
- Специализированные инструменты для анализа и оптимизации запросов, такие как SQL Sentry Plan Explorer или EverSQL
Влияет ли версия базы данных на возникновение ошибок SQL?
Да, версия базы данных может влиять на возникновение ошибок SQL из-за различий в поддерживаемых функциях, синтаксисе и стандартах. Важно использовать актуальную версию базы данных и знать о возможных ограничениях или различиях между версиями.
Как предотвратить ошибки SQL при разработке приложений?
- Используйте хорошие практики проектирования баз данных и написания запросов
- Тестируйте ваш код на разных этапах разработки
- Внедряйте контроль версий и процессы код-ревью для обеспечения качества кода
- Обучайте разработчиков основам SQL и принципам работы с базами данных
Заключение:
Ошибки SQL являются неизбежным аспектом работы с базами данных, но с правильными знаниями и инструментами их можно успешно диагностировать и исправлять. Надеемся, что эта статья помогла вам лучше понять различные типы ошибок SQL, их причины и способы решения. Следуйте нашим рекомендациям и советам, чтобы свести к минимуму вероятность возникновения ошибок SQL и обеспечить надежную и эффективную работу ваших приложений с базами данных.
