Время на прочтение
5 мин
Количество просмотров 30K
Предположим, вы работаете с MySQL таблицами Innodb, и в один
прекрасный
не самый хороший момент подводит глючное железо, драйвер, бажит ядро, отключается электричество или случается одна из редких ошибок в среде MySQL. На выходе получаем повреждение некоторых страниц в табличной области Innodb.
В одной из предыдущих статей, в комментариях, нас спрашивали, что можно сделать в такой ситуации. Мы постараемся ответить максимально лаконично и по делу.
Так вот, сейчас речь о ситуации вроде этой:
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
080703 23:46:16 InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end 1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed
Что делать для восстановления таблицы? В принципе, типов повреждений может быть несколько, ниже мы рассмотрим один из наиболее распространенных моментов. А именно — когда повреждена страница в кластеризованном первичном ключе.
В примере рассматривается файл test.idb, где заменено несколько байтов, так что повреждение достаточно умеренное.
При этом операция CHECK TABLE в INNODB практически бесполезна. Для текущего поврежденного файла мы получаем:
mysql> check table test;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> check table test;
+-----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.test | check | status | OK |
+-----------+-------+----------+----------+
1 row in set (0.69 sec)
Первый запуск — проверка таблицы в обычном режиме, в этом случае innodb просто падает, если есть ошибка в контрольной сумме (даже, если мы выполняем CHECK). Во втором случае запускаем innodb_force_recovery=1. И даже здесь мы получаем в логах запись о несовпадении контрольной суммы, при этом CHECK TABLE говорит нам, что с таблицей все ок. Как видим, CHECK TABLE доверять можно далеко не всегда.
В примере «повреждение» совсем небольшое, поэтому, если запускаем innodb_force_recovery=1, получаем следующее:
mysql> CREATE TABLE `test2` (
-> `c` char(255) DEFAULT NULL,
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2 select * from test;
Query OK, 229376 rows affected (0.91 sec)
Records: 229376 Duplicates: 0 Warnings: 0
Теперь мы получили все данные в таблице MyISAM, так что все, что остается сделать — дропнуть старую таблицу, и конвертировать новую в innodb после рестарта без опции innodb_force_recovery. Если старая таблица будет нужна в дальнейшем, ее можно просто переименовать. Вторая альтернатива — сделать дамп с MySQLDump и загрузить таблицу обратно. В принципе, это почти одно и то же. MyISAM используется по причине, описанной ниже.
Почему бы просто не воспользоваться OPTIMIZE TABLE? Все потому, что работа в режимер innodb_force_recovery проводится в режиме чтения для операций с данными, поэтому нельзя вставлять или стирать данные (при этом можно создавать или удалять таблицы Innodb):
mysql> optimize table test;
+-----------+----------+----------+----------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+----------+----------+----------------------------------+
| test.test | optimize | error | Got error -1 from storage engine |
| test.test | optimize | status | Operation failed |
+-----------+----------+----------+----------------------------------+
2 rows in set, 2 warnings (0.09 sec)
Это было просто, правда?
После этого можно пойти еще дальше, и отредактировать наш файл test.ibd, полностью удалив один из заголовков страницы. Теперь CHECK TABLE будет падать даже при использовании innodb_force_recovery=1
080704 0:22:53 InnoDB: Assertion failure in thread 1158060352 in file btr/btr0btr.c line 3235
InnoDB: Failing assertion: page_get_n_recs(page) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
Если мы видим нечто подобное, то innodb_force_recovery нам не поможет, поскольку работать с этим можно тлько в случаях повреждения данных в различных системных местах. Но в нашем случае это не помогает.
Получаем такую ошибку:
mysql> insert into test2 select * from test;
ERROR 2013 (HY000): Lost connection to MySQL server during query
Попытки использования автоматических процессов восстановления поврежденных данных не приводят к положительному результату. Поэтому стоит использовать серию команд с LIMIT для режима ручного восстановления:
mysql> insert ignore into test2 select * from test limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert ignore into test2 select * from test limit 20;
Query OK, 10 rows affected (0.00 sec)
Records: 20 Duplicates: 10 Warnings: 0
mysql> insert ignore into test2 select * from test limit 100;
Query OK, 80 rows affected (0.00 sec)
Records: 100 Duplicates: 20 Warnings: 0
mysql> insert ignore into test2 select * from test limit 200;
Query OK, 100 rows affected (1.47 sec)
Records: 200 Duplicates: 100 Warnings: 0
mysql> insert ignore into test2 select * from test limit 300;
ERROR 2013 (HY000): Lost connection to MySQL server during query
Здесь строки из таблицы переводятся в новую таблицу, пока мы не добираемся до строки, которая и вызывает падение MySQL. Мы можем ожидать этого в строке между 200 и 300, так что стоит использовать серию схожих действий для решения проблемы.
Теперь мы обнаружили поврежденные данные в таблице, при этом стоит использовать max PK, и проверить иные значения:
mysql> select max(id) from test2;
+---------+
| max(id) |
+---------+
| 220 |
+---------+
1 row in set (0.00 sec)
mysql> insert ignore into test2 select * from test where id>250;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> insert ignore into test2 select * from test where id>300;
Query OK, 573140 rows affected (7.79 sec)
Records: 573140 Duplicates: 0 Warnings: 0
Так, мы пробуем пропустить 30 строк, но это оказывается недостаточным. Пропускаем 80 строк, и теперь все хорошо. Используя «двоичный поиск» мы можем понять, сколько строк нужно пропустить, для восстановления максимального количества поврежденных данных. Размер строки при этом может помочь. Так, у нас есть 280 байт на строку, поэтому мы получаем около 50 строк на страницу. И здесь 30 строк недостаточно — если таблица страниц повреждена, нужно пропустить минимум всю страницу. Если повреждена страница на более высоком уровне BTREE, нужно пропустить больше страниц, для использования этого метода восстановления.
В некоторых случаях, например, когда повреждена root page для кластеризованного индекса, этот метод не будет нормально работать. В этом случае стоит использовать Innodb Recovery Toolkit.
P.S. Принимаем заявки на статьи 
Таблицы InnoDB наиболее популярны на сегодняшний день т.к они поддерживают транзакции, они надежны в отличие от MyISAM и InnoDB поддерживает одновременные записывание в ту же таблицу.
Внутренний механизм восстановления InnoDB является довольно хорошим. Если вы имеете сбой базы данных, InnoDB будет пытаться исправить все, запустив лог-файл можно посмотреть последнюю метку и время когда это случилось. В большинстве случаев — вас ждет успех и весь процесс является прозрачным.
К сожалению, если не удается InnoDB восстановить себя, -entire- база данных не запустяться. MySQL завершится с сообщением об ошибке и вся ваша база данных будет находиться в автономном режиме. Вы можете попробовать перезапустить базу данных снова и снова, но если процесс ремонта не удается — база данных будет падать и будет переставать запускаться.
Это одна из причин, почему вы всегда должны запустить настройки master/master при использовании InnoDB т.к есть всегда избыточный мастер, если не удается запустить 1-й.
Если у вас есть коррумпированные таблицы InnoDB, которые мешают вашей базы данных работать хорошо, вы должны следовать моим инструкциям. В своей теме «Исправляем поврежденные INNODB таблицы» я все покажу как можно легко исправить данную проблему и восстановить в рабочее состояние все базы данных.
Шаг 1.
Добавьте следующую строку в ваш конфигурационный файл /etc/my.cnf:
# vim /etc/my.cnf
и вставляем следующую строчку:
[mysqld] innodb_force_recovery = 4
Примечание: Если MySQL не запускается, продолжать увеличивать количество innodb_force_recovery, пока вы не получите innodb_force_recovery = 8. Хотя, в основном, доходит до 6:
- Mode 1 — не «отваливается» MySQL, когда он видит коррумпированные страницы.
- Mode 2 — не запускает фоновые операции.
- Mode 3 — Не пытается откатить транзакции.
- Mode 4 — не рассчитывает статистику или не применяет сохраненные/буферизированные изменения.
- Mode 5 — Не смотрите на log-и отката при запуске.
- Mode 6 — Не прокрутки вперед от повтора логов (ib_logfiles) во время пуска.
Так например, если ваш сервер MySQL запускается в режиме 3, но не режим 2, это может быть предположение что «авария» возникла с процессом отката транзакций. Кроме того, следует знать, что режимы 4-6 mysql будет работать в режиме только для чтения.
Я использую 4. Помогает в 99%.
Вы также можете запустить следующую команду чтобы добавить данную опцию в файл /etc/my.cnf автоматически (изменить цифру в поле «mode=»):
# mode=4; sed -i "/^[mysqld]/{N;s/$/ninnodb_force_recovery=$mode/}" /etc/my.cnf
Затем, после восстановления поврежденных баз, нужно убрать опцию (вернуть обратно в режим по умолчанию), то вы можете удалить innodb_force_recovery строку с помощью следующей команды:
# sed -i '/innodb_force_recovery/d' /etc/my.cnf
О sed, я надеюсь расскажу еще, но попозже.
Шаг 2.
Перезагрузите MySQL:
# service mysql restart
или
# service mysqld restart
Можно и так:
# /usr/local/bin/mysqld_safe &
База данных теперь будет запускаться, но с параметром innodb_force_recovery,все INSERT-ы и UPDATE-ы будут игнорироваться.
Шаг 3.
Стоит позаботится о бекапах всех таблиц (создать дамп):
# mysqldump --force --compress --triggers --routines --create-options -uUSER_NAME -pUN_PASSWORD --all-databases > /usr/alldb.sql
Шаг 4.
Выключите сервер базы данных и удалите каталог данных. Запустите «mysql_install_db» для создания таблиц MySQL по умолчанию.
# rm -fdr /usr/local/var
Перезапускаем:
# mysqladmin -uUSER_NAME -pUN_PASSWORD shutdown
Шаг 5.
Снимите опцию «innodb_force_recovery» в /etc/my.cnf файл и перезапустите сервер баз данных:
# /usr/local/bin/mysqld_safe &
Шаг 6.
Восстановить все из резервной копии.
Повторное создание каталогов базы данных и так же установим основные таблицы в MySQL:
# mkdir /usr/local/var # chown -R mysql:mysql /usr/local/var # /usr/local/bin/mysql_install_db # chown -R mysql:mysql /usr/local/var
Импортировать все данные обратно (следующая команда может занять много времени, чтобы завершить все и восстановить все данные):
# mysql -uroot --compress < /usr/alldb.sql
И, наконец, — обновим привилегии MySQL (потому что мы также обновили таблицы MySQL):
# /usr/local/bin/mysqladmin -uroot flush-privileges
Примечание: Для получения наилучших результатов, добавьте «port=8819» (или любой другой из случайных чисел) в /etc/my.cnf перед перезапуском MySQL, а затем запустить «mysqldump» с параметром «—port = 8819».
Если все что выше, не помогло, то стоит попытаться использовать Mysqlcheck:
Восстановить все базы:
# mysqlcheck --all-databases -r
Проанализировать все базы:
# mysqlcheck --all-databases -a
Оптимизировать все базы:
# mysqlcheck --all-databases -o
Восстанавливаем одну базу:
# mysqlcheck -ro database_name
Восстанавливаем один столбец в базе:
# mysqlcheck -ro database_name table_name
Статья «Исправляем поврежденные INNODB таблицы» подошла к завершению. Если есть еще какие-либо соображения по восстановлению поврежденных INNODB таблиц, поделитесь.
Источник: http://kb.odin.com/ru/6586
Проблема
- MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’
- При работе mysqldump и mysqlcheck появляется сообщение о несуществующей таблице (для проверки используйте учетную запись администратора MySQL):
mysqlcheck -uadmin -p****** db_example db_example.BackupTasks error : Can't find file: 'BackupTasks.MYD' (errno: 2)
- Невозможно выполнить запрос таблицы с оператором «SELECT»:
mysql> select * from db_example.misc; ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'
- Таблица не может быть восстановлена, так как ядро InnoDB не поддерживает восстановление.
mysql> repair table misc; +-------------------------+--------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------+--------+----------+---------------------------------------------------------+ | psa.APSApplicationItems | repair | note | The storage engine for the table doesn't support repair | +-------------------------+--------+----------+---------------------------------------------------------+
Причина
Повреждения InnoDB часто связаны с неисправностью оборудования. Сохранение поврежденных страниц происходит в результате сбоев питания или повреждений памяти. Также эта проблема может возникать, если вы храните базы данных InnoDB в сетевом хранилище (NAS).
Решение
Существует несколько способов восстановить MySQL:
I. Принудительное восстановление InnoDB
Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:
/etc/init.d/mysqld stop mkdir /root/mysql_backup cp -r /var/lib/mysql/* /root/mysql_backup/
Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.
/etc/my.cnf [mysqld] innodb_force_recovery = 4
ПРИМЕЧАНИЕ. Вы можете увеличить эту опцию до 5 или 6 — пока не получите оптимальный дамп.
Запустите службу mysqld:
/etc/init.d/mysqld start
Создайте дамп всех баз данных:
mysqldump -uadmin -p****** -A > /root/dumpall.sql
Если при создании дампа возникла следующая ошибка:
Incorrect information in file: ‘xxxxxxxx.frm’ when using LOCK TABLES»`
увеличьте значение innodb_force_recovery и повторите попытку. Если вы не можете создать дамп баз данных, попробуйте использовать способ II (скопировать содержимое таблицы) или III (восстановить из резервной копии).
Остановите mysqld и удалите поврежденные данные:
/etc/init.d/mysqld stop rm -rf /var/lib/mysql/*
Удалите опцию innodb_force_recovery из файла /etc/my.cnf и запустите mysqld:
/etc/init.d/mysqld start
В результате этого будет восстановлена главная база данных «mysql» и движок баз данных InnoDB.
Восстановите базы данных из дампа:
mysql -uadmin -p****** > dumpall.sql
II. Копирование содержимого таблицы
Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:
/etc/init.d/mysqld stop mkdir /root/mysql_backup cp -r /var/lib/mysql/* /root/mysql_backup/
Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.
/etc/my.cnf [mysqld] innodb_force_recovery = 1
Попробуйте создать копию:
CREATE TABLE <новая таблица> LIKE <поврежденная таблица>; INSERT INTO <новая таблица> SELECT * FROM <поврежденная таблица>;
Если получилось, удалите поврежденную таблицу и присвойте ее имя новой.
DROP TABLE <поврежденная таблица>; RENAME TABLE <новая таблица> TO <поврежденная таблица>;
III. Восстановление таблицы InnoDB
Восстановление таблиц InnoDB необходимо в случае возникновения следующей ошибки
mysql> USE databasename; mysql> SELECT * FROM table1; ERROR 1146 (42S02): TABLE 'databasename.table1' doesn't exist mysql>
Или при попытке сделать дамп через mysqldump
[red@hellsrv ~]$ mysqldump -uroot -p databasename > databasename.sql Enter password: mysqldump: Got error: 1146: Table 'databasename.table1' doesn't exist when using LOCK TABLES [red@hellsrv ~]$
ВниманиеДо начала любых действий рекомендуем создать резервную копию файлов базы
Создать резервную копию через mysqldump не получится (из-за ошибки). Потребуется копирование файлов базы на уровне файловой системы:
service mysqld stop cp -R /var/lib/mysql/databasename /home/USERNAME/backup
Для того чтобы восстановить таблицы InnoDB, нам нужно узнать:
- узнать структуру таблиц
- иметь файлы с данными (имеется ввиду файлы на уровне файловой системы)
Таблица InnoDB на уровне файловой системы состоит из двух фалов:
- файл .frm хранит в себе структуру таблицы;
- файл .ibd собственно данные
План восстановления:
- выяснить структуру поврежденной таблицы;
- создать новую базу;
- создать в новой базе таблицу нужной структуры;
- скопировать данные в новую таблицу из старой;
- если данные окажутся поврежденными, можно попробовать восстановить их используя утилиту innochecksum
Применяем утилиту чтения структуры таблицы:
mysqlfrm --diagnostic table1.frm CREATE TABLE `table1` ( `id` int(10) unsigned NOT NULL comment 'ID', `title` varchar(128) NOT NULL comment 'Title', PRIMARY KEY `PRIMARY` (`id`) ) ENGINE=InnoDB;
Также желательно узнать кодировку старой базы:
mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databasename'; +----------------------------+------------------------+ | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +----------------------------+------------------------+ | cp1251 | cp1251_general_ci | +----------------------------+------------------------+ 1 ROW IN SET (0.00 sec)
Создаем новую базу:
mysql> CREATE DATABASE helldb CHARACTER SET cp1251 DEFAULT COLLATE cp1251_general_ci; Query OK, 1 ROW affected (0.00 sec)
Создаем таблицу по выводу утилиты чтения структуры поврежденной таблицы:
mysql> USE databasename; mysql> CREATE TABLE `table1` ( `id` int(10) unsigned NOT NULL comment 'ID', `title` varchar(128) NOT NULL comment 'Title', PRIMARY KEY `PRIMARY` (`id`) ) ENGINE=InnoDB;
Далее копируем данные:
- Очищаем автоматически созданный файл
mysql> ALTER TABLE tables1 DISCARD TABLESPACE; Query OK, 0 ROWS affected (0.04 sec)
- Копируем файл с данными с поврежденной таблицы
cp /home/USERNAME/tables1.ibd tables1.ibd chown mysql:mysql tables1.ibd
- Импортируем данные
mysql> ALTER TABLE tables1 IMPORT TABLESPACE; Query OK, 0 ROWS affected, 1 warning (0.50 sec)
- Проверяем корректность чтения данных
mysql> SELECT * FROM tables1 LIMIT 10; +-----+-----------+ | id | title | +-----+-----------+ | 1 | Title 1 | | 2 | Title 2 | | 3 | Title 3 | | 4 | Title 4 | +-----+-----------+ 4 ROWS IN SET (0.00 sec)
Далее можно импортировать восстановленную таблицу или базу целиком.
IV. Восстановление из резервной копии
Если приведенные выше инструкции не помогли, остается только восстановить базы данных из резервных копий.
Похожее
Повреждения InnoDB часто связаны с неисправностью оборудования. Сохранение поврежденных страниц происходит в результате сбоев питания или повреждений памяти. Также эта проблема может возникать, если вы храните базы данных InnoDB в сетевом хранилище (NAS).
Проблема
MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’
При работе mysqldump и mysqlcheck появляется сообщение о несуществующей таблице (для проверки используйте учетную запись администратора MySQL):
# mysqlcheck -uadmin -p****** db_example
db_example.BackupTasks
error : Can't find file: 'BackupTasks.MYD' (errno: 2)
Невозможно выполнить запрос таблицы с оператором «SELECT»:
mysql> select * from db_example.misc;
ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'
Таблица не может быть восстановлена, так как ядро InnoDB не поддерживает восстановление.
mysql> repair table misc;
+-------------------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+--------+----------+---------------------------------------------------------+
| psa.APSApplicationItems | repair | note | The storage engine for the table doesn't support repair |
+-------------------------+--------+----------+---------------------------------------------------------+
Решение
Существует несколько способов восстановить MySQL:
I. Принудительное восстановление InnoDB
Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:
# /etc/init.d/mysqld stop
# mkdir /root/mysql_backup
# cp -r /var/lib/mysql/* /root/mysql_backup/
Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.
[mysqld]
innodb_force_recovery = 4
ПРИМЕЧАНИЕ. Вы можете увеличить эту опцию до 5 или 6 — пока не получите оптимальный дамп.
Запустите службу mysqld:
# /etc/init.d/mysqld start
Создайте дамп всех баз данных:
# mysqldump -uadmin -p****** -A > /root/dumpall.sql
Если при создании дампа возникла следующая ошибка:
Incorrect information in file: ‘xxxxxxxx.frm’ when using LOCK TABLES»`
увеличьте значение innodb_force_recovery и повторите попытку. Если вы не можете создать дамп баз данных, попробуйте использовать способ II (скопировать содержимое таблицы) или III (восстановить из резервной копии).
Остановите mysqld и удалите поврежденные данные:
# /etc/init.d/mysqld stop
# rm -rf /var/lib/mysql/*
Удалите опцию innodb_force_recovery из файла /etc/my.cnf и запустите mysqld:
# /etc/init.d/mysqld start
В результате этого будет восстановлена главная база данных «mysql» и движок баз данных InnoDB.
Восстановите базы данных из дампа:
# mysql -uadmin -p****** > dumpall.sql
II. Копирование содержимого таблицы
Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:
# /etc/init.d/mysqld stop
# mkdir /root/mysql_backup
# cp -r /var/lib/mysql/* /root/mysql_backup/
Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.
[mysqld]
innodb_force_recovery = 1
Попробуйте создать копию:
CREATE TABLE <новая таблица> LIKE <поврежденная таблица>;
INSERT INTO <новая таблица> SELECT * FROM <поврежденная таблица>;
Если получилось, удалите поврежденную таблицу и присвойте ее имя новой.
DROP TABLE <поврежденная таблица>;
RENAME TABLE <новая таблица> TO <поврежденная таблица>;
III. Восстановление таблицы InnoDB
Восстановление таблиц InnoDB необходимо в случае возникновения следующей ошибки
mysql> USE databasename;
mysql> SELECT * FROM table1;
ERROR 1146 (42S02): TABLE 'databasename.table1' doesn't exist
mysql>
Или при попытке сделать дамп через mysqldump
# mysqldump -uroot -p databasename > databasename.sql
Enter password:
mysqldump: Got error: 1146: Table 'databasename.table1' doesn't exist when using LOCK TABLES
Внимание! До начала любых действий рекомендуем создать резервную копию файлов базы!
Создать резервную копию через mysqldump не получится (из-за ошибки). Потребуется копирование файлов базы на уровне файловой системы:
# service mysqld stop
# cp -R /var/lib/mysql/databasename /home/USERNAME/backup
Для того чтобы восстановить таблицы InnoDB, нам нужно узнать:
узнать структуру таблиц
иметь файлы с данными (имеется ввиду файлы на уровне файловой системы)
Таблица InnoDB на уровне файловой системы состоит из двух фалов:
файл .frm хранит в себе структуру таблицы;
файл .ibd собственно данные
План восстановления:
выяснить структуру поврежденной таблицы;
создать новую базу;
создать в новой базе таблицу нужной структуры;
скопировать данные в новую таблицу из старой;
если данные окажутся поврежденными, можно попробовать восстановить их используя утилиту innochecksum
Применяем утилиту чтения структуры таблицы:
# mysqlfrm --diagnostic table1.frm
CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL comment 'ID',
`title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;
Также желательно узнать кодировку старой базы:
mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databasename';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| cp1251 | cp1251_general_ci |
+----------------------------+------------------------+
1 ROW IN SET (0.00 sec)
Создаем новую базу:
mysql> CREATE DATABASE databasename CHARACTER SET cp1251 DEFAULT COLLATE cp1251_general_ci;
Query OK, 1 ROW affected (0.00 sec)
Создаем таблицу по выводу утилиты чтения структуры поврежденной таблицы:
mysql> USE databasename;
mysql> CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL comment 'ID',
`title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;
Далее копируем данные. Очищаем автоматически созданный файл:
mysql> ALTER TABLE tables1 DISCARD TABLESPACE;
Query OK, 0 ROWS affected (0.04 sec)
Копируем файл с данными с поврежденной таблицы:
# cp /home/USERNAME/tables1.ibd tables1.ibd
# chown mysql:mysql tables1.ibd
Импортируем данные:
mysql> ALTER TABLE tables1 IMPORT TABLESPACE;
Query OK, 0 ROWS affected, 1 warning (0.50 sec)
Проверяем корректность чтения данных:
mysql> SELECT * FROM tables1 LIMIT 10;
+-----+-----------+
| id | title |
+-----+-----------+
| 1 | Title 1 |
| 2 | Title 2 |
| 3 | Title 3 |
| 4 | Title 4 |
+-----+-----------+
4 ROWS IN SET (0.00 sec)
Далее можно импортировать восстановленную таблицу или базу целиком.
IV. Восстановление из резервной копии
Если приведенные выше инструкции не помогли, остается только восстановить базы данных из резервных копий.
Источник
Восстановление работоспособности после падения mysql, разберем специфику восстановления низкоуровневых подсистем InnoDB.
Почему падает бд
Почему mysql не стартует, для этого есть множество причин, в данный момент бы будем разбирать только реальные проблемы работы бд а не программные настройки.
- Ошибка жёстких дисков, в следствие это невозможность считать файл.
- Ошибка записи была вызвана невозможность сохранить данные, по русски кончилось место в следствие этого произошла логическая ошибка БД.
Убедимся что проблема действительно есть
df -h
[root@centos-75-64-minimal ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/md2 437G 98G 317G 100% / devtmpfs 32G 0 32G 0% /dev tmpfs 32G 0 32G 0% /dev/shm tmpfs 32G 1.2M 32G 1% /run tmpfs 32G 0 32G 0% /sys/fs/cgroup /dev/md1 488M 143M 320M 31% /boot tmpfs 6.3G 0 6.3G 0% /run/user/600 tmpfs 6.3G 0 6.3G 0% /run/user/0
Место на диске кончилось, и БД не в состояние больше писать, отчистим место и попробуем перезапустить mysql, если же mysql не запускается то первым делом останавливаем mysql, в зависимости от ОС команда моет отличаться.
service mysqld restart
service mysql restart
/etc/init.d/mysqld restart
В нашем случае база не запустилась, по этому полностью останавливаем mysql
service mysqld stop
service mysql stop
/etc/init.d/mysqld stop
И убеждаемся что демон полностью остановлен и все процессы тоже, должен остаться только один процесс
[root@centos-75-64-minimal ~]# ps aux | grep mysql root 1903 0.0 0.0 112708 972 pts/3 S+ 19:46 0:00 grep --color=auto mysq
Если mysql все еще есть, то убиваем процесс
kill -9 "номер процесса"
Запускаем заново mysql
service mysqld start
service mysql start
/etc/init.d/mysqld start
Демон не запускается
service mysql start Redirecting to /bin/systemctl start mysql.service Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
В какие логи смотреть
Смотрим логи
journalctl -xe
PageUp и PageDown
Обратите внимание что это не лог mysql и сюда попадает множество другой информации, так что возможно вы не увидите вашу ошибку, поскольку она уже ушла дальше по логу.
Сам демон mysql может сообщить полезную информацию
service mysqld status
service mysql status
/etc/init.d/mysqld status
Redirecting to /bin/systemctl status mysql.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: failed (Result: start-limit) since Mon 2019-02-18 18:20:59 MSK; 18min ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 25723 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQL D_OPTS (code=exited, status=1/FAILURE) Process: 25699 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 14443 (code=killed, signal=KILL) Feb 18 18:20:59 centos-75-64-minimal systemd[1]: Failed to start MySQL Server. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: Unit mysqld.service entered failed state. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: mysqld.service failed. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: mysqld.service holdoff time over, scheduling ...rt. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: start request repeated too quickly for mysqld...ice Feb 18 18:20:59 centos-75-64-minimal systemd[1]: Failed to start MySQL Server. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: Unit mysqld.service entered failed state. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: mysqld.service failed. Hint: Some lines were ellipsized, use -l to show in full. [root@centos-75-64-minimal mysql]# service mysql status Redirecting to /bin/systemctl status mysql.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: failed (Result: start-limit) since Mon 2019-02-18 18:20:59 MSK; 18min ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 25723 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE) Process: 25699 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 14443 (code=killed, signal=KILL) Feb 18 18:20:59 centos-75-64-minimal systemd[1]: Failed to start MySQL Server. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: Unit mysqld.service entered failed state. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: mysqld.service failed. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: mysqld.service holdoff time over, scheduling restart. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: start request repeated too quickly for mysqld.service Feb 18 18:20:59 centos-75-64-minimal systemd[1]: Failed to start MySQL Server. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: Unit mysqld.service entered failed state. Feb 18 18:20:59 centos-75-64-minimal systemd[1]: mysqld.service failed.
Как мы видим в данном случае информативности оно не добавило, просто mysql не желает стартовать.
Можно более подробно посмотреть в error логе mysql
tail -f /var/log/mysql/error.log
Нужно убедиться что логирование включено, для этого в my.cnf должна быть директива
log-error = путь до файла лога
Так же она может быть написана в другом конфиге подключаемом через include, для быстрого поиска воспользоватсья данной командой:
find /etc/mysql/ -type f -exec grep -l "log-error" {} ;
2019-02-12T17:10:41.002325Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 80192) 2019-02-12T17:10:41.002565Z 0 [Warning] Changed limits: table_open_cache: 2392 (requested 18432) 2019-02-12T17:10:41.147060Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set. 2019-02-12T17:10:41.148006Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-25) starting as process 3314 .. 2019-02-12T17:10:41.153630Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html 2019-02-12T17:10:41.153694Z 0 [Note] InnoDB: PUNCH HOLE support available 2019-02-12T17:10:41.153702Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2019-02-12T17:10:41.153705Z 0 [Note] InnoDB: Uses event mutexes 2019-02-12T17:10:41.153708Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2019-02-12T17:10:41.153711Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7 2019-02-12T17:10:41.153715Z 0 [Note] InnoDB: Using Linux native AIO 2019-02-12T17:10:41.153902Z 0 [Note] InnoDB: Number of pools: 1 2019-02-12T17:10:41.153973Z 0 [Note] InnoDB: Using CPU crc32 instructions 2019-02-12T17:10:41.174468Z 0 [Note] InnoDB: Initializing buffer pool, total size = 18G, instances = 8, chunk size = 128M 2019-02-12T17:10:41.371813Z 0 [Note] InnoDB: Completed initialization of buffer pool 2019-02-12T17:10:41.401599Z 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-12T17:10:41.414873Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite 2019-02-12T17:10:41.425080Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2019-02-12T17:10:41.444212Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 21193390186 2019-02-12T17:10:41.444235Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 21193394424 2019-02-12T17:10:41.444432Z 0 [Note] InnoDB: Database was not shutdown normally! 2019-02-12T17:10:41.444436Z 0 [Note] InnoDB: Starting crash recovery. 2019-02-12T17:10:41.546953Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 31457280 bytes 2019-02-12T17:10:41.576387Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=3] log sequence number 108677477433 is in the future! Current system log sequence number 21193394506. 2019-02-12T17:10:41.576432Z 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 http://dev.mysql.com/doc/refman/5.7/en/ forcing-innodb-recovery.html for information about forcing recovery. 2019-02-12T17:10:41.576647Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=2] log sequence number 132140291279 is in the future! Current system log sequence number 21193394506. 2019-02-12T17:10:41.576670Z 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 http://dev.mysql.com/doc/refman/5.7/en/ forcing-innodb-recovery.html for information about forcing recovery. 2019-02-12T17:10:41.576922Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=4] log sequence number 21194400206 is in the future! Current system log sequence number 21193394506. 2019-02-12T17:10:41.576948Z 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 http://dev.mysql.com/doc/refman/5.7/en/ forcing-innodb-recovery.html for information about forcing recovery. 2019-02-12T17:10:41.577209Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=11] log sequence number 132140749179 is in the future! Current system log sequence number 21193394506. 2019-02-12T17:10:41.577226Z 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 http://dev.mysql.com/doc/refman/5.7/en/ forcing-innodb-recovery.html for information about forcing recovery. 2019-02-12T17:10:41.577858Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=6] log sequence number 132140702919 is in the future! Current system log sequence number 21193394506. 2019-02-12T17:10:41.577877Z 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 http://dev.mysql.com/doc/refman/5.7/en/ forcing-innodb-recovery.html for information about forcing recovery. 2019-02-12T17:10:41.578094Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=330] log sequence number 132140702919 is in the future! Current system log sequence number 21193394506. 2019-02-12T17:10:41.578112Z 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 http://dev.mysql.com/doc/refman/5.7/en/ forcing-innodb-recovery.html for information about forcing recovery. 2019-02-12T17:10:41.578328Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=790] log sequence number 21193512754 is in the future! Current system log sequence number 21193394506. 2019-02-12T17:10:41.578344Z 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 http://dev.mysql.com/doc/refman/5.7/en/ forcing-innodb-recovery.html for information about forcing recovery. 2019-02-12T17:10:41.578555Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=45] log sequence number 21194412806 is in the future! Current system log sequence number 21193394506.
Что происходит
Была повреждена одна или более таблиц бд, особенно это относится к InnoDB
Как правило если исправить эту ошибку то работоспособность БД восстановится.
В противном случае нам прийдётся делать дамп всех БД и переустанавливать mysql
Востанавливаем базы данных
Когда innodb вообще не запускается, нам прийдутся зайти в защитный режим, для этого добавим в /etc/my.cnf
innodb_force_recovery = 1
Место положение файла зависит от ОС
И попробуем заново запустить mysql.
Если старт не происходит, увеличиваем цифру с шагом в единицу и пытаемся запустить бд.
innodb_force_recovery = 2
Чем больше цифра, тем больше будет ограничений при старте, так что имеет смысл постепенно повышать значение, подобрав минимально подходящую, максимальное значение innodb_force_recovery = 8
В моем случае старт пошёл на =2, но одна из бд падала при обращение к ней и заработала только на 6.
Во многих мануалах встречается так же рекомендация по смене порта, и тут все просто, если обратиться к битой таблице то может упасть вся бд, а если с сайта обращение к ней то, некому будет мешать восстанавливать данные. Так что мы меняем порт, тем самым делаем невозможным подключиться к БД сайтам.
Внимание! Обязательно сделайте резервную копию БД перед началом работы. Остановите mysql и полностью скопируйте папку /var/lib/mysql, любые изменения в текущей бд могут привести к потере даных!
Делаем дамп всех БД, обычно рекомендуют сделать mysqldump -u root -p —all-databases —skip-lock-tables > alldb.sql с вариациями, но нам нужно понять кто не корректно и где дамптися так что я делаю по другому.
В начале посмотрим можем ли мы прочитать названия БД
mysql -uroot -p******** -e'show databases;'
[root@centos-75-64-minimal mysql]# mysql -uroot -p******s -e’show databases;' ±--------± | Database | ±--------± | information_schema | | dbami-com | | dbhikvisionpro | | performance_schema | | sitemanager | | sys | ±--------±
У нас есть список БД и мы можем сделать дамп каждой по отдельности.
Создадим директорию для бэкапов, я делаю это не в /tmp поскольку может понадобиться перезагрузиться а в зависимости от способа монтирования, она может перетереться при загрузке
mkdir -p /var/backup/mysql
Возьмём список бд в цикл и будем дампить каждую из них по отдельности, в купе с этим возвращать код завершения, за одно уберём системные БД из списка.
for i in `mysql -uroot -p******** -e'show databases;' | grep -v information_schema | grep -v performance_schema | grep -v Database`; do mysqldump -uroot -p******** $i > /var/backup/mysql/$i.sql || echo "$i $?";done
Если дамп прошёл успешно, то можно запускать
mysqlcheck --auto-repair -o --all-databases
Если и он справился удачно то можно попробовать удалить innodb_force_recovery из my.cnf и перезагрузить mysql
Но мы будем разбирать менее оптимистичный сценарий.
[root@centos-75-64-minimal mysql]# for i in `mysql -uroot -e’show databases;' | grep -v information_schema | grep -v performance_schema | grep -v Database`; do mysqldump -uroot -no-create-info $i > /var/backup/mysql/$i.sql || echo «$i $?«;done mysqldump: Error: 'Lost connection to MySQL server during query' when trying to dump tablespaces mysqldump: Couldn’t execute 'SHOW VARIABLES LIKE 'ndbinfo_version'': MySQL server has gone away (2006) dbhikvisionpro 2 mysqldump: Got error: 2002: Can’t connect to local MySQL server through socket '/var/lib/mysqld/mysqld.sock' (111) when trying to connect sitemanager 2 mysqldump: Got error: 2002: Can’t connect to local MySQL server through socket '/var/lib/mysqld/mysqld.sock' (111) when trying to connect
Как мы видим что-то пошло не так, при обращение к одной из бд произошла ошибка, и она привела к падению всего mysql, дальше дамп был не возможен. В данном случае mysql пришлось снимать kill −9 поскольку другим способом демон не желал ни работать ни корректно останавливаться.
Посмотрим что нам удалось выгрузить:
root@centos-75-64-minimal mysql]# ls -la total 597616 drwxr-xr-x 2 root root 4096 Feb 18 19:43 . drwxr-xr-x 3 root root 4096 Feb 18 19:37 .. -rw-r—r— 1 root root 611926379 Feb 18 19:43 dbami-com.sql -rw-r—r— 1 root root 1563 Feb 18 19:43 dbhikvisionpro.sql -rw-r—r— 1 root root 0 Feb 18 19:43 sitemanager.sql
Как мы видим, дамп корректно был завершена только для одной dbami-com.sql (не нулевой размер, dbhikvisionpro, это или часть некорректного дампа, или в нашем случае, ошибка была в первой же таблице по этому дамп содержал только общий заголовок и не имел внутри вообще никакой информации. Все последующие БД не имели размера поскольку sql уже не отвечал.
[root@centos-75-64-minimal mysql]# cat dbhikvisionpro.sql — MySQL dump 10.13 Distrib 5.7.23–25, for Linux (x86_64) - — Host: localhost Database: dbhikvisionpro — -------------------------- — Server version 5.7.23–25 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */; /*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME='rocksdb_bulk_load'', 'SELECT 0') */; /*!50717 PREPARE s FROM @rocksdb_get_is_supported */; /*!50717 EXECUTE s */; /*!50717 DEALLOCATE PREPARE s */; /*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */; /*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */; /*!50717 EXECUTE s */; /*!50717 DEALLOCATE PREPARE s */;
Проверим догадку, так ли это, зайдем в mysql и попробуем посмотреть список таблиц.
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 Databases changed~ Mysql> show tables; mysql> show tables; No connection. Trying to reconnect… ERROR 2002 (HY000): Can’t connect to local MySQL server through socket '/var/lib/mysqld/mysqld.sock' (111) ERROR:
SQL не могла вернуть даже список таблиц, мало того от этого запроса упала вся БД и не отвечала до полной отчистки памяти, принудительно через
ps aux | grep mysql
и
kill -9 ***
Убедившись что mysql полностью остановлен мы заново его запускаем.
Следующий вопрос, как сделать дамб базы данных не имея возможности посмотреть список таблиц, ответ достаточно очевиден нам нужно сделать этот список самим.
cd /var/lib/mysql/ваша бд/
Получаем список таблиц
Внимание! Такой способ возможен только при условии что innodb_file_per_table=1
ls *.ibd | grep -v "FTS"| cut -d '.' -f 1
[root@centos-75-64-minimal dbhikvisionpro]# ls *.ibd | grep -v «FTS»| cut -d '.' -f 1 b_abtest b_admin_notify b_admin_notify_lang b_adv_banner_2_country b_adv_banner_2_day b_adv_banner_2_group b_adv_banner_2_page b_adv_banner_2_site b_adv_banner_2_stat_adv b_adv_banner_2_weekday b_adv_banner b_adv_contract_2_page b_adv_contract_2_site b_adv_contract_2_type b_adv_contract_2_user b_adv_contract_2_weekday b_adv_contract b_adv_type b_agent b_app_password b_b24connector_buttons b_bitrixcloud_option b_blog_category b_blog_comment b_blog_group b_blog b_blog_image b_blog_post_category ...
Имея список таблиц сделаем дамп каждой таблицы по отдельности и и будем смотреть что у нас с ними.
Создаем папку для дампа таблиц
mkdir /tmp/dbhikvisionpro
И делаем дамп таблиц. Поскольку мы делаем дамп каждой таблицы по отдельности, мы можем следить за кодом завершения каждой из них.
for i in `ls *.ibd | grep -v "FTS"| cut -d '.' -f 1`; do mysqldump -uroot dbhikvisionpro $i > /tmp/dbhikvisionpro/$i.sql || echo "$i $?";done
# for i in `ls *.ibd | grep -v «FTS»| cut -d '.' -f 1`; do mysqldump -uroot dbhikvisionpro $i > /tmp/dbhikvisionpro/$i.sql || echo «$i $?«;done mysqldump: Error: 'Lost connection to MySQL server during query' when trying to dump tablespaces mysqldump: Couldn’t execute 'SHOW VARIABLES LIKE 'ndbinfo_version'': MySQL server has gone away (2006) b_xml_tree_import_1c 2 mysqldump: Got error: 2002: Can’t connect to local MySQL server through socket '/var/lib/mysqld/mysqld.sock' (111) when trying to connect
И видим что при обращение к таблице b_xml_tree_import_1c , код ответа 2, и опять упала бд, все последующие так же не читаются.
Все что нам остается, это пропустить данную таблицы и делать дамп дальше, постепенно исключая убитые таблицы, как правило это 1-2 таблицы, в моем случае это била таблица импорта из 1с и таблица сессий, обе не важные так что я их удалил, а структуру таблицы взял стандартную для дижка.
Делаем исключение битых таблиц grep -v исключает таблицу
for i in `ls *.ibd | grep -v "FTS"| grep -v "b_xml_tree_import_1c" | grep -v "b_session" | cut -d '.' -f 1`; do mysqldump -uroot dbhikvisionpro $i > /tmp/dbhikvisionpro/$i.sql || echo "$i $?";done
drwxrwxrwt. 18 root 4096 Feb 18 21:36. -rw-r-r- 1 root 7459 Feb 18 21:35 b_abtest.sql -rw-r-r- 1 root 3166 Feb 18 21:35 b_admin_notify_lang.sql -rw-r-r- 1 root 3318 Feb 18 21:35 b_admin_notify.sql -rw-r-r- 1 root 3301 Feb 18 21:35 b_adv_banner_2_country.sql -rw-r-r- 1 root 3140 Feb 18 21:35 b_adv_banner_2_day.sql -rw-r-r- 1 root 3028 Feb 18 21:35 b_adv_banner_2_group.sql -rw-r-r- 1 root 3163 Feb 18 21:35 b_adv_banner_2_page.sql -rw-r-r- 1 root 3031 Feb 18 21:35 b_adv_banner_2_site.sql -rw-r-r- 1 root 3055 Feb 18 21:35 b_adv_banner_2_stat_adv.sql -rw-r-r- 1 root 3109 Feb 18 21:35 b_adv_banner_2_weekday.sql -rw-r-r- 1 root 5759 Feb 18 21:35 b_adv_banner.sql -rw-r-r- 1 root 3183 Feb 18 21:35 b_adv_contract_2_page.sql -rw-r-r- 1 root 3102 Feb 18 21:35 b_adv_contract_2_site.sql -rw-r-r- 1 root 3110 Feb 18 21:35 b_adv_contract_2_type.sql -rw-r-r- 1 root 3160 Feb 18 21:36 b_adv_contract_2_user.sql -rw-r-r- 1 root 5984 Feb 18 21:36 b_adv_contract_2_weekday.sql -rw-r-r- 1 root 4099 Feb 18 21:36 b_adv_contract.sql -rw-r-r- 1 root 3274 Feb 18 21:36 b_adv_type.sql -rw-r-r- 1 root 11749 Feb 18 21:36 b_agent.sql -rw-r-r- 1 root 3515 Feb 18 21:36 b_app_password.sql -rw-r-r- 1 root 3153 Feb 18 21:36 b_b24connector_buttons.sql -rw-r-r- 1 root 3424 Feb 18 21:36 b_bitrixcloud_option.sql -rw-r-r- 1 root 3063 Feb 18 21:36 b_blog_category.sql -rw-r-r- 1 root 3934 Feb 18 21:36 b_blog_comment.sql -rw-r-r- 1 root 3054 Feb 18 21:36 b_blog_group.sql -rw-r-r- 1 root 3402 Feb 18 21:36 b_blog_image.sql -rw-r-r- 1 root 3175 Feb 18 21:36 b_blog_post_category.sql -rw-r-r- 1 root 3200 Feb 18 21:36 b_blog_post_param.sql -rw-r-r- 1 root 5096 Feb 18 21:36 b_blog_post.sql -rw-r-r- 1 root 3155 Feb 18 21:36 b_blog_site_path.sql -rw-r-r- 1 root 3178 Feb 18 21:36 b_blog_socnet_rights.sql -rw-r-r- 1 root 2987 Feb 18 21:36 b_blog_socnet.sql -rw-r-r- 1 root 4513 Feb 18 21:36 b_blog.sql -rw-r-r- 1 root 3343 Feb 18 21:36 b_blog_trackback.sql -rw-r-r- 1 root 3054 Feb 18 21:36 b_blog_user2blog.sql ……
Теперь у нас есть все таблицы и мы можем смотреть каждую по отдельности. Убедившись что все с ними хорошо, поэтому нам больше не нужны таблицы по отдельности, нам нужен один файл.
for i in `ls *.ibd | grep -v "FTS"| grep -v "b_xml_tree_import_1c" | grep -v "b_session" | cut -d '.' -f 1`; do mysqldump -uroot dbhikvisionpro $i >> /tmp/dbhikvisionpro/dbhikvisiononpro.sql || echo "$i $?";done
В результате получаем полноценный дамб за исключением двух таблиц. Как вариант в mysqldump есть —ignore-table=DATABASE.table1 но там нужно писать баш скрипт и многие жалуются что он не работает корректно, в моем же случае цикл у меня уже был сделал и не принципиально как он будет делаться.
Все что нам остаётся удалить проблемные БД, если не получается их удалить то проверяем диск на вероятные сбои как физические так и логические, если же все хорошо но база не удаляется, остаётся только полностью переустановить mysql.
Заново инициализируем mysql
Поскольку все БД мы уже получили, нам нужно восстановить работоспособность mysql, DROP TABLES не помогает, в связи с этим, мы удалим физически все БД и инициализируем чистый mysql
Остановим mysql
systemctl stop mysqld
Удалим полностью папку mysql
rm -rf /var/lib/mysqld
Инициализируем новую БД
mysqld --initialize-insecure --basedir=/usr --datadir=/var/lib/mysql
Создаем сокет
mkfifo /var/lib/mysqld/mysqld.sock
Права
chown -R mysql /var/lib/mysqld/mysqld.sock
Нам нужно установить root пароль, устанавливаем environment
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
Заходим в mysql без пароля
systemctl start mysqld mysql -u root
Обновляем пароль root
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
-> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit
новый вариант, просто делаем и тот и тот
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Останавливаем sql и возвращаемся в нормальный режим
systemctl stop mysqld systemctl unset-environment MYSQLD_OPTS systemctl start mysqld
Проверяем
mysql -u root -p