Чем открыть ib logfile

Обновлено: 06.07.2024

web программирование, администрирование и всякая всячина, которая может оказаться полезной

Can't open and lock privilege tables: Table `*.host` doesn't exist

Часть таблиц (а именно - все таблицы InnoDB и некоторые MyISAM) были повреждены.

По умолчанию все базы хранятся в каталоге /var/lib/mysql. При этом для таблиц MyISAM создаются файлы в подкаталоге, имя которого совпадает с названием базы данных, а InnoDB, в отличии от MyISAM, сохраняет таблицы в виде нескольких файлов: ib_logfile0, ib_logfile1, ibdata1. А в каталоге с названием базы, сохраняются файлы с расширением *.FRM в которых находится информация о структуре таблицы.

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

1. Сохраняем весь каталог с данными (/var/lib/mysql) - шаг особенно важный, если бэкапы не настроены и последних резервных копий просто нет

2. Если сервер не работает, можно восстанавливать непосредственно на нём, а можно использовать отдельную машину (в том числе с ОС Windows - желательно, чтобы версии MySQL на сервере и на машине для восстановления совпадали. Желательно - т.е. в некоторых ситуациях восстановление может пройти успешно, несмотря на разные версии БД)

3. Создаем на сервере одноименную базу.

4. Останавливаем сервер:
/etc/init.d/mysql stop

5. Туда же копируем файлы innodb:
ib_logfile0
ib_logfile1
ibdata1

6. Для успешного восстановления следует задать правильный размер лог файлов. узнать его можно командой ls -l

-rw-rw—- 1 mysql mysql 5242880 Jan 15 01:32 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Jan 15 01:32 ib_logfile1

/usr/sbin/mysqld –-innodb_log_file_size=5242880 –-innodb_force_recovery=1

Вывод в консоль будет содержать примерно такую информацию:

InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
010115 01:33:36 InnoDB: Started; log sequence number 0 0
InnoDB: . innodb_force_recovery is set to 6 .
010115 01:33:36 [Note] /usr/sbin/mysqld: ready for connections.

8. проверяем, запустилась ли база.. есть ли данные в восстановленной таблице InnoDB
Если нет останавливаем сервер и запускаем с параметром –-innodb_force_recovery=2 (затем - 3,4,5,6)
Если запуск с 6-кой не помог - стандартными средствами восстановить не удастся.. возможно, помогут инструменты для InnoDB.

9. Если всё ОК - сливаем дамп

mysqldump -uroot -p database > database.sql

Где database — имя базы.

После чего, загружаем его на рабочий сервер..

Метки: mysql

Автор будет признателен, если Вы поделитесь ссылкой на статью, которая Вам помогла:
BB-код (для вставки на форум)

html-код (для вставки в ЖЖ, WP, blogger и на страницы сайта)

ссылка (для отправки по почте)

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

В общем, дело было так…

Вступление

В далеком царстве, в тридевятом государстве В одном из государственных административных учреждений, которое обслуживается нашим предприятием, стоял сервер. В качестве сервера использовался обычный настольный компьютер с не особо мощным двухядерным процессором, жестким диском на 320 Гб, без всяких там “ненужных” RAID-массивов, ничего “лишнего”, c установленной на нем cерверной Windows, кажется, 2003. И выполнял он некоторые серверные задачи, о которых я-то в силу своей должности не был особо осведомлен. Работал так практически без перерыва порядка нескольких лет.

И вот, в один прекрасный день, позвал меня с моей напарницей к себе начальник отдела, дал нам задание разработать систему для этого учреждения с использованием в качестве СУБД MySQL Server 5.1. Систему мы разработали, все ей были весьма довольны, и пришла пора ее внедрять. Без всяких проблем мы с одним из наших админов, придя в офис учреждения, установили на вышеупомянутый сервер MySQL 5.1, развернули на ней базу, установили клиентское приложение на рабочие машины, запустили процесс внедрения. Работники учреждения были весьма рады системе, но осваивали ее медленно ввиду загруженности работой, совещаниями, заседаниями, обращениями граждан и т.д. Потихоньку вводили информацию, но сильно “растягивали удовольствие”. Надо было уже потихоньку начинать задумываться о стратегии бэкапа/восстановления, но я все думал о том, что это и не совсем-то мое дело, ведь я-то не админ, а разработчик. Но и админам это было совершенно безразлично. Я же себя утешал лишь тем, что пока информации мало, вот будет побольше, и если никто не начнет делать бэкапы, начну уже я. Оглядываясь назад, теперь я вижу, как легко можно было предсказать последующие события.

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

Основные боевые действия

И вот, наступил другой “прекрасный” день, сервер упал. Причем, не программно, а аппаратно – “полетел” жесткий диск. Далее жесткий диск был сдан в фирму, занимающуюся восстановлением данных, и значительная часть информации с него была восстановлена. Однако, админы при восстановлении самого сервера, восстановили только всю информацию с диска D, про нашу MySQL никто и не вспомнил (по “счастливой случайности” она оказалась поставлена на диск C). Все это время я себе спокойно занимался другими проектами, про эти события и знать не знал.

Первым делом ищу папку Program Files, куда ставится по умолчанию MySQL. Не нахожу, но зато нахожу папку с файлами данных MySQL, в ней папка с именем той самой нашей базы. Небольшой заряд оптимизма немного разогнал тучи моего отчаяния, но… глядя на файлы *.MYD, в которых по идее должны бы быть записи таблиц, вижу, что размер их слишком мал для такого количества информации, которое было введено. Бинарные логи также не были включены, и этот вариант автоматически отпадал. Начинаю думать над этим вопросом и вспоминаю, что можно взглянуть на девелоперскую базу на нашем тестовом сервере, что я и сделал. Там я увидел, что большинство таблиц – причем, самых важных – работают на движке InnoDB, а значит, что их записи хранятся отдельно в файле ibdata* (в моем случае, только ibdata1). Выхожу на уровень выше и вижу этот заветный файл. Рядом также два лога движка InnoDB: ib_logfile0 и ib_logfile1. Открыв один из логов для просмотра в текстовом редакторе среди “кракозябр” я также увидел и куски самой информации, и тут я понял, что восстановить базу можно, по крайней мере, теоретически.

Надо было выбрать машину, где произвести попытку восстановления самой базы. Т.к. во время разработки я пользуюсь тестовым сервером, доступ к которому на уровне файловой системы мне без боя никто не даст, я решаю установить MySQL Server 5.1 и MySQL GUI Tools прямо на свою девелоперскую машину, работающую под Windows XP. Итак, все стоит, СУБД запущено, и подумав, как бы подступиться к задаче, я решаю сначала сделать экспорт скрипта создания БД с тестового сервера, и запускаю его потом на своей машине. Теперь у меня есть пустая база данных с полностью идентичной структурой и со всеми хранимыми процедурами. Останавливаю сервис MySQL, и копирую все содержимое из папки с именем нашей базы с упавшего сервера в аналогичную папку свежеустановленной локально у себя MySQL (папка с именем базы, вложенная в папку data, расположенную по пути для хранения файлов с данными указанному во время установки MySQL). При настройке локальной MySQL Server я решил указать для файлов данных InnoDB отдельную папку, куда далее скидываю восстановленный файл ibdata1. После этого перекидываю файлы InnoDB-логов (ib_logfile0 и ib_logfile1) прямо в свою папку data, где они должны быть по умолчанию.

Очередная попытка запустить MySQL снова не увенчалась успехом. Не имея реально боевого опыта в администрировании СУБД (за исключением опыта, полученного на курсах, но, опять же, по другим СУБД) я решил пойти путем эксперимента, хотя сейчас понимаю, что он совсем не был необходим. Удаляю файлы логов InnoDB, запускаю сервис MySQL и вижу, что он создает сами файлы InnoDB-логов с такими же названиями, но с размером в 20Мб.

Смотрю размер “своих” файлов с логами с упавшего сервера и вижу, что он существенно отличается и составляет 81Мб. Что-ж… останавливаю сервер, и захожу через MySQL Administrator в настройки переменных запуска (Startup Variables), вкладка InnoDB. Задаю размер log-файлов равным 81 Мб и закидываю обратно “свои” лог-файлы. MySQL запустился, но данных в интересующих меня таблицах я не увидел (хотя в таблицах, работавших на движке MyISAM, они уже были восстановлены). Немного пошарив по сети, я вычитал, что нужно запустить MySQL в режиме восстановления, для чего нужно задать значение стартовой переменно InnoDB_force_recovery равным 6. Попробовал сделать это через MySQL Administrator (ясное дело, что можно задавать параметры через GUI, а можно и редактировать файлы my.ini или my.cnf [в зависимости от версии MySQL и выбранной ОС] вручную), и, то ли я что-то не доделал, то ли что-то недосмотрел, но нужного результата не увидел. MySQL запустился как обычно, но про восстановление не сказал ни слова. “Что-ж,” – подумал я – “попробуем тогда через консоль, и еще раз явно пропишем параметр innodb_force_recovery”.

Запускаю коммандную строку Windows и ввожу:

> mysqld --console --innodb_force_recovery=6

На что в ответ получаю:

110727 10:31:52 [Note] Plugin 'FEDERATED' is disabled.
110727 10:31:52 InnoDB: Initializing buffer pool, size = 40.0M
110727 10:31:52 InnoDB: Completed initialization of buffer pool
110727 10:31:52 InnoDB: Operating system error number 32 in a file operation.
InnoDB: The error means that another program is using InnoDB's files.
InnoDB: This might be a backup or antivirus software or another instance
InnoDB: of MySQL. Please close it to get rid of this error.

> mysqld --console --innodb_force_recovery=6

Пока что я не понял, в чем было дело, почему запускалось сразу два процесса. В том числе и потому, что радость охватила меня, и подключившись к базе данных через Toad for MySQL я увидел, что данные восстановленны! Эта радость отодвинула все вопросы на второй план.

Осознание победы и контрольный выстрел

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

> mysqldump --routines -u "user" -p db_name > [path\]db_name.sql

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

Затем уже на рабочем сервере устанавливаю сам MySQL, настраиваю его, создаю пустую базу и скидываю в нее дамп:

> mysql -u "user" –p db_name < [path\]db_name.sql

В настройках клиентского приложения менять ничего не пришлось, т.к. ip-адрес и сетевое имя сервера остались теми же, что и были до падения. Зашли в систему, проверили, и – о, чудо! – вся информация на месте. Тут и сказочке конец… но остановимся на “морали сей басни".

Выводы и заключение

  1. Стратегия бэкапа/восстановления должна определяться уже на стадии разработки;
  2. В вопросах бэкапа/восстановления не надо надеяться на админов, если можешь сделать это сам; и тем более, если для тебя не составит особого труда автоматизировать этот процесс (что вполне можно сделать на примитивном уровне штатными средствами);
  3. Организация на предприятии и четкое регламентирование обязанностей каждого отдельного сотрудника в каждом конкретном проекте очень важны, когда мы имеем дело с IT; и современные стандарты разделения труда на IT-предприятиях очень и очень актуальны и выросли не на пустом месте;
  4. И все-же, если база полетела и нет бэкапа – это еще не повод сразу отчаиваться и опускать руки.
  1. Подставляем в папку с данными файлы данных и описаний таблиц (файлы *.frm, *.MYI и *.MYD, все в папке с именем БД — т.е. копируем всю эту папку целиком);
  2. В папку c файлами данных InnoDB (по умолчанию, скорее всего, это папка data, где хранятся и бинарные логи, и папки с именами БД; в моем случае я сделал для данных InnoDB отдельную папку) скидываем все файлы ibdata*. Здесь, наверное, надо отметить, что размер файлов должен соответствовать указанному в настройках. Если размеры разнятся, то можно поступить так: удалить созданный сервером файл (или файлы) вручную, и создать новый (или, соответственно, новые) с размером равным размеру имеющегося, затем запустить сервер и остановить его, и вместо созданных им файлов подставить свои. Облегчить эту операцию – чтобы не пришлось играть с настройками вручную – может MySQL Administrator. Или можно сделать еще проще – элементарно подмениь все файлы (или файл, если он один), а в файле настроек my.ini/my.cfg просто изменить размер – по идее должно сработать.
  3. Скидываем логи InnoDB (или подменяем, если они уже есть) туда, где они должны быть (по умолчанию, это та же папка data, где хранятся файлы с данными и описаниями, но это, опять же, может быть изменено в настройках);
  4. Задаем размер логов InnoDB равным размеру наших логов (если вручную – то в байтах, если в MySQL Administrator – в Мб);
  5. Запускаем MySQL daemon
    > mysqld --console
    внимательно смотрим лог, должен быть запущен сервис и выполнено восстановление;
  6. Если восстановление не происходит, тогда в конфигурации или параметром ставим innodb_force_recovery=1 и пробуем запустить. Если не проходит, ставим innodb_force_recovery=2 и пробуем запустить. И т.д. до 6.
  7. И, наконец, смотрим на месте ли наши данные, и если на месте – делаем дамп базы, который потом уже разворачиваем на рабочий сервер.

Ссылки

В дополнение

PhantomTLT в комментарии подсказывает (и, опираясь на его советы, я немного скорректировал памятку выше):

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

Далее стартуем MySQL и внимательно смотрим лог. Должен стартануть и выполнить восстановление.

Если сразу стартуете с innodb_force_recovery=6, то вы рискуете получить БД в inconsistent state. Т.е. целостность (согласованность) данных может быть нарушена.

Если вы дошли до innodb_force_recovery=6 и это не помогло, то очень грустно — нужно восстанавливать в полуручном режиме.

Затрахался я, восстанавливая одну базу mysql без должного бэк-апа. На русском инструкций, как это сделать, не нашёл, перелопатил кучу страниц и испробовал две шароварные программки (только в демо-режиме от них мало толку). Наконец, нашёл краткую и ёмкую инструкцию на английском от Dustin'а Davis'а, за что ему низкий поклон. Приведу её здесь, на случай, если кому пригодится (да и мне самому, мало ли что. ).

Дано: файлы базы данных ibdata1, ib_logfile0, ib_logfile1, а также папка с именем потерянной, но очень нужной вам базы с файлами *.frm внутри (то бишь, InnoDB).

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

1) Останавливаем службу MySQL (я, кстати сказать, это забыл сделать, но на конечный результат это не повлияло):

(Чтобы запустить её снова, когда всё закончим, в консоли скажем >net start mysql.)

2) помещаем сохранившиеся файлы (ib* и *.frm) в папку data, например, c:\Program Files\MySQL\data, впрочем, у меня ib_logfile* оказались в папке c:\Users\All Users\MySQL\data.

3) редактируем my.ini в папке mysql; переменную innodb_log_file_size устанавливаем равной размеру вашего ib_logfile* (по размеру они совпадают):

set-variable = innodb_log_file_size=170M

4) запускаем mysql в режиме восстановления:

>bin\mysqld --defaults-file=my.ini --standalone --console --innodb_force_recovery=6

(пути к файлам mysqld.exe и my.ini правите по необходимости)

Далее, если всё закончилось успешно, делаем дамп базы и восстанавливаем его на соответствующем сервере. Автор англоязычного поста советует SQLyog, а я воспользовался привычным и бесплатным phpMyAdmin.

Кстати, по ходу поисков нашлась аналогичная инструкция для работающих в linux'е, здесь.

P. S. Программы, которые мне встретились при поиске:
1. Stellar Phoenix Database Recovery For MySQL. Демо-версия позволяет просматривать содержимое базы данных (кроме больших текстовых полей), копировать в буфер или сохранять нельзя.
2. Recovery for MySQL. В демо-режиме смогла восстановить только структуру таблиц потерянной базы (в виде SQL-запросов: CREATE TABLE и т. д.).

читаю реализацию БД (точнее введение в это дело)
прямо сейчас - как раз про восстановление после сбоев )

А у меня к базе (one file per table) похерился словарь ibdata1. Бэкап был постарше на несколько дней и не имел всех изменений в структуре таблиц. Старый ibdata1 реанимировал основную часть таблиц на 100%, но не все, не смотря на то, что каждая табличка "не битая" лежала в отдельных файлах ibd + frm. Поднять смог только описательную часть из frm примерно так:

Немного помудохавшись пришлось перекинуть вручную подсматривая в Stellar, который распознал 100%. "Таблетка" есть под все софтины Stellar, кроме mysql. В общем, не ленитесь и делайте бэкапы чаще)


Случается так, что база данных(ibdata1, ib_logfile0) накрывается медным тазом, но к счастливейшему стечению обстоятельств остаются файлы frm и ibd и в настройках mysql выставлено innodb_file_per_table = 1, не стоит расстраиваться шанс восстановления данных мал, но он есть.

Успех зависит от того какой версии mysql вы использовали, если 5.6 и выше, то шансы растут, а если у вас еще есть структура таблиц, то там вообще шансы велики.

Для начала нам необходима структура базы данных, если у вас есть дамп то полдела сделано, нам нужна только структура. Для того что бы вынуть структуру из дампа есть такая утилита как awk и следующее выражение awk '/CREATE TABLE /,/ENGINE=/' dump.sql если конечно дамп сделан mysqldump то все получится, если другой утилитой, то придется подредактировать.

А теперь поговорим про ОС, где все эти манипуляции по восстановлению проводить, мой выбор пал на ubuntu 14.04, потому что там можно накатить любую версию mysql 5.5, 5.6, 5.7.

В общем ставим ubuntu, я ставил на VirtualBox с этого образа mini.iso.
Ставим openssh-server, apache, php, phpmyadmin
Если нужна версия mysql 5.7 нужно добавить репозиторий
И ключ командой apt-key adv --keyserver pgp.mit.edu --recv-keys 5072E1F5

И устанавливаем mysql apt-get install mysql-server-5.X вместо X — версия.
После установки не забудьте добавить в конфиг mysql innodb_file_per_table = 1 и перезапустить сервер /etc/init.d/mysql restart

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

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

Если у вас база данных без пароля для root, то выполняем следующие команды:
Если с паролем то добавляем параметры -p

После создания одноименных таблиц, останавливаем mysql /etc/init.d/mysql stop переходим в каталог где живут файлы mysql, по дефолту это /var/lib/mysql/, там находим нашу бд recover и заменяем все файлы frm на файлы из бд которую восстанавливаем.

/dbre/ — каталог где хранится бд которую восстанавливаем

Запускаем mysql /etc/init.d/mysql start И смотрим получилось ли у нас что, для этого заходим в mysql
Если покажет структуру таблицы, то ура, делаем dump mysqldump -uroot -d recover >

/struct.sql если напишет recover.table_name does't exist то беда.

А теперь внимание, в mysql 5.6+ появилась возможность импорта ibd файлов, что значительно упрощает весь процесс восстановления, поэтому в любому случае ставим mysql 5.6 или 5.7

В общем если у нас есть структура бд(как ее получить описал выше awk '/CREATE TABLE /,/ENGINE=/' dump.sql >

/struct.sql ), заливаем ее в нашу бд
Теперь надо отключить ibd файлы у каждой таблицы, для этого нужно вновь провести некоторые манипуляции
Создали sql файл, который массово отключит ibd для всех таблиц mysql -uroot recover <

Теперь самое интересное, копируем ibd файлы с базы которой восстанавливаем и подготавливаем sql файл импорта этих ibd файлов:
Скопировали idb файлы и создали sql файл с импортом для всех таблиц, попробуем применить его mysql -uroot recover <

Через phpmyadmin или mysql-workbench можно попробовать посмотреть, что мы там на восстанавливали и если все окей сделать нормальный dump mysqldump -uroot recover > recover.sql У меня так успешно восстановилось две базы данных, с другими двумя, они были версии mysql 5.5< поломались поля DATETIME

Еще есть утилита Percona data recovery tool for innodb, но тоже нужно знать структуру, как ей пользоваться описано в этой статье
Мне не понравилось решение с find для объединения файлов и я накатал свой небольшой скриптик, может кому понравится
Помещаем его в каталог pages-xxxxxxxx/FIL_PAGE_INDEX/ и запускаем, он объеденит все файлы из папок

Этой утилитой я тоже восстановил пару таблиц, в некоторых было все хорошо, в некоторых данные все же были повреждены.

Моя заметка скорее перевод вот этой, на авторство не претендую

И если кому вдруг интересно, я писал про восстановление БД wordpress, там структуру вытащить не получилось, поэтому я пошел более простым путем, я установил чистый wordpress и плагины, и сделал discard и import tablespace, и все отлично восстановилось.

Читайте также: