Как востановить работу innoDB после падения?

Question

[Виктор Таран]

Стандартная ситуация клиент обратился с проблемой на сервере, по факту оказалось 100% диска заняты
В результате этого логи innoDB отказались работать как следствие отказ работы БД
После чистки сервера базы естественно октазались запускаться, сообщив о "111"
После mv файлов mysql так же отказался стартовать.
Сразу пришлось писать
innodb_force_recovere = 2
при этом он на нем запустился вполне себе, но дальше дело не пошло ошибка в таблицах InnoDB
Но они заблокированы для записи
ну и mysqlchck тоже не помогает.

spoiler

You may download the Percona Server operations manual by visiting
www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
2019-02-12T15:27:56.984998Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 37080)
2019-02-12T15:27:56.985152Z 0 [Warning] Changed limits: table_open_cache: 2392 (requested 18432)
2019-02-12T15:27:57.126444Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2019-02-12T15:27:57.126917Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-25) starting as process 27262 ...
2019-02-12T15:27:57.132309Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
2019-02-12T15:27:57.132406Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-02-12T15:27:57.132431Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-02-12T15:27:57.132448Z 0 [Note] InnoDB: Uses event mutexes
2019-02-12T15:27:57.132452Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-02-12T15:27:57.132473Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2019-02-12T15:27:57.132475Z 0 [Note] InnoDB: Using Linux native AIO
2019-02-12T15:27:57.132708Z 0 [Note] InnoDB: Number of pools: 1
2019-02-12T15:27:57.132810Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-02-12T15:27:57.154244Z 0 [Note] InnoDB: Initializing buffer pool, total size = 18G, instances = 8, chunk size = 128M
2019-02-12T15:27:57.352031Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-02-12T15:27:57.382065Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-02-12T15:27:57.395066Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2019-02-12T15:27:57.405972Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-02-12T15:27:57.433060Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=3] log sequence number 108677477433 is in the future! Current system log sequence number 21193390167.
2019-02-12T15:27:57.433095Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to dev.mysql.com/doc/refman/5.7/en/forcing-innodb-rec... for information about forcing recovery.
2019-02-12T15:27:57.433267Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=2] log sequence number 132140291279 is in the future! Current system log sequence number 21193390167.
2019-02-12T15:27:57.433286Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to dev.mysql.com/doc/refman/5.7/en/forcing-innodb-rec... for information about forcing recovery.
2019-02-12T15:27:57.433469Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=4] log sequence number 21194400206

Answer 1

[SubGANs]

Стопните все апачики и пхп-фпм, если они есть, чтобы с базой ничего не работало. Остановите сам мускль и заархивируйте на всякий случай /var/lib/mysql, чтобы если что-то пойдет не так можно было вернуться к исходному состоянию.
Потом запускайте бд с innodb_force_recovery от 3 до 6 и делайте дамп, начните с 3, если дамп не сделается, то повышайте.
Если совсем все плохо и дамп не делается даже на 6 уровне, то надо сливать потаблично. Если и так не сливается, часто всякие большие таблицы логов, то можно у них слить чисто схему таблицы --no-data ключ.
Потом все останавливаете, удаляйте файлы ibdata, ib_log*, и все файлы с расширением .ibd, стартуете мускль в нормальном режиме и загружаете дампы.

Answer 2

[Владимир]

При включенном innodb_force_recovery - неважно какой режим - база доступна только для чтения, чтобы вы смогли сдампить что доступно а потом пересоздать всю папку /var/lib/mysql и залить дамп в чистую БД
P.S. читайте документацию https://dev.mysql.com/doc/refman/5.7/en/forcing-in... там все написано.

Comments

[Виктор Таран]

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

[Дмитрий Шицков]

Виктор Таран, а какая ошибка при дампе?

[Виктор Таран]

значит появилось немного статистики.
и так начинаю чекать отдельно бд
Две из бд в дампе не могут создать ничего кроме заголовка и отваливаются с

mysqldump: Error: 'Lost connection to MySQL server during query' when trying to dump tablespace

При том что примечательно падают саме мальенкие базы, а сайт с 130К товаров чувстсвует себя на ура и подумав 30 секунд делает дамп на 5ГБ
та же байда и при майсикуэльчек, тоже чек останавливается из за краша бд, Насколько я понимаю когда доходит до определенных таблиц бд тупо падает, именно по этому она и не может старнатуть в нормальном режиме, а в сейв мод она принудительно остается запущеной и через пару секунд опять доступна.

[Владимир]

Ну тут только делать резервную копию файлов из /var/lib/mysql (в том виде что есть) и увеличивать innodb_force_recovery пытаясь сделать дамп. занчения 4 и выше могут повредить содержимое поэтому и нужен бекап.

[Виктор Таран]

Владимир, Делал не работает.
По сути вытащил все базы, дропал таблички битые, дабы их было не много и повезло что это были штатные, одна для импорта с 1с вторая сессии, в общем структуру с других сайтов забрал а данные сдохли, но они тут и не нужны.
А вот последняя БД с не проблема при show tables она крашет mysql да так что kill -9
А вот каким-то образом mysqlckeck проходит большую часть бд и крашется уже там.
Вот и пытаюсь понять какую табличку нужно ... ( починить) ;)
По сути сейчас не могу даже вывести нормально список таблиц в бд

spoiler

dbhikvisionpro.b_workflow_move OK
dbhikvisionpro.b_workflow_preview OK
dbhikvisionpro.b_workflow_status OK
dbhikvisionpro.b_workflow_status2group OK
dbhikvisionpro.b_xml_tree OK
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '

show tables
сразу крашет.
есть идеи как достать список таблиц?

[Владимир]

Виктор Таран, если у вас основной движок innodb то прямо на файловой системе у вас внутри /var/lib/mysql/$DBname/ лежат файлы на каждую таблицу с ее именем по 3шт .MYD .MYI .frm при условии что innodb_file_per_table=1

[Виктор Таран]

MYI - не от слова MyISAM ?

[Владимир]

Виктор Таран, вы правы - я перепутал, для иннодб там аналогично долждны быть файлы .frm и .ibd (при innodb_file_per_table=1)

[Виктор Таран]

короче сделал вот так

TABLE=$(ls  -la | grep ibd | grep -Eo b_.+[^.ibd]) && echo "$TABLE"

потом перенаправил вот так

mysqldump -uroot dbhikvisionpro b_adv_banner_2_group > /tmp/111/b_adv_banner_2_group.sql  || echo "b_adv_banner_2_group $?"
mysqldump -uroot dbhikvisionpro b_adv_banner_2_page > /tmp/111/b_adv_banner_2_page.sql  || echo "b_adv_banner_2_page $?"
mysqldump -uroot dbhikvisionpro b_adv_banner_2_site > /tmp/111/b_adv_banner_2_site.sql   || echo "b_adv_banner_2_site $?"
.......

В результате получил 76 метров таблиц и

b_app_passwor 6
b_catalog_discount_con 6
b_catalog_loa 6
b_clouds_file_uploa 6
b_consent_fiel 6
b_form_crm_fiel 6
b_form_fiel 6
b_im_comman 6
b_lists_fiel 6
b_mail_filter_con 6
b_mail_message_u 6
b_report_visual_report_dashboar 6
b_rest_stat_metho 6
b_sale_basket_archive_packe 6
b_sale_loc_search_wor 6
b_sale_order_archive_packe 6
b_sale_order_roun 6
b_sender_message_fiel 6
b_sender_posting_rea 6
b_sender_posting_unsu 6
b_short_ur 6
b_user_fiel 6
b_user_profile_recor 6
b_xml_tree_import_1c 2

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

[Владимир]

Виктор Таран, Обычно там сдампилась только часть таблицы, кокето колво строчек до первой нечитаемой, если в таблицах есть праймари кей по id или еще както то можно из дампа посмотреть на чем он остановился, и попробывать сделать селект пропустив 1н id.
Вот еще полезная статья по манипуляциям с innodb tablespace www.chriscalender.com/recovering-an-innodb-table-f...

[Виктор Таран]

с таблицами разобраться это я олень [^.ibd]
это любое из сочетаний, так что
b_app_passwor - дало ошибку из за отсутствия символа "d"
А вот последняя которая вернула 2, вот она отвечает даже вот так

mysql> use dbhikvisionpro
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

^[[ADatabase changed
mysql> drop tables b_xml_tree_import_1c;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: dbhikvisionpro

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server

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

[Владимир]

Если у вас поврежден тайблспейс - то скорее всего никак, делайте дамп всех таблиц кроме этой.
В текущей ситуации вам все равно нужно делать дампы всего,стоп базы, rm -rf /var/lib/mysql/*; mysql_install_db, старт базы, заливка дампов.

[Виктор Таран]

В общем этапы действий следующие, если кому понадобится то я опишу.
1. Запускаем в сейв мод минимальном для старта.
2. Делаем дамп всех баз данных смотрим какая база останавливает дамп и вываливает в ошибку сервера.
3. Дампим все целые базы
4. Базы которые в состояние дать show tables делаем дамб и смотрм какая таблица валит дамп, обходим эту таблицу или эксклудом или дампим все таблицы отдельно
5. Если база не в состояние дать даже список таблиц в бд то забираем только таблицы, каждую по отдельности, для этого заходим в папку где хранятся сами файлы и выбираем

cd /var/lib/mysql/ваша бд/
TABLE=$(ls  -la | grep ibd | grep -Eo b_.+[^.]) && echo "$TABLE" | cut -d '.' -f 1

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

Все что нам остается передать их в массив и смотреть какой код они отдают при дампе.

mkdir /tmp/111
mysqldump -uroot dbhikvisionpro b_vote > /tmp/111/b_vote.sql  2>&1 || echo "b_vote $?"
mysqldump -uroot dbhikvisionpro b_vote_question > /tmp/111/b_vote_question.sql  2>&1 || echo "b_vote_question $?"
mysqldump -uroot dbhikvisionpro b_vote_user > /tmp/111/b_vote_user.sql  2>&1 || echo "b_vote_user $?"

на выходе имеем таблицы в папке, каждая своим файлом ( так удобней смотреть)
исключаем те что крашат базу.
В моем случае пришлось гасить mysql через kill -9 ps aux | grep mysql
kill -9 номер пида
После того как все плохие таблицы были исключены собираем все в кучу, вместо > делаем >> и на выход даем один файл, в результате получаем обычный дамп. на самом деле вариантов много через эксклуд и перечисление таблиц и тд и тп, но мне проще работать с массивами в баше.
в результате получаем дамп, переносим его на отдельный сервер проверяем работает ли все, ну и после этого уже разбираемся в индивидуальном порядке с крашнутыми таблицами.
в моем случае бэкапов вообще небыло так что даже стандартные таблицы можно было востановить только от стоковых таблиц движка. дабы мне этого хватило.
ибо было убито 4 таблицы 2 востановил стоковыми ( хранение сессий и тд)
2 можно было убить использовались при импорте 1с и креатятся каждый раз.

mysqldump -uroot dbhikvisionpro b_abtest >> /tmp/alltables.sql
mysqldump -uroot dbhikvisionpro b_admin_notify >> /tmp/alltables.sql
mysqldump -uroot dbhikvisionpro b_admin_notify_lang >> /tmp/alltables.sql
mysqldump -uroot dbhikvisionpro b_adv_banner_2_country >> /tmp/alltables.sql
....

[Виктор Таран]

Владимир Владимир отдельное спасибо

[Владимир]

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