Как восстановить поврежденные таблицы в MySQL

Введение

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

К числу распространенных причин повреждения таблиц относятся:

Сервер MySQL останавливается в процессе записи.
Внешнее приложение изменяет таблицу, которая одновременно изменяется сервером.
Устройство неожиданно выключается.
Компьютерное оборудование выходит из строя.
В коде MySQL где-то обнаружена программная ошибка.

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

Сначала остановите службу MySQL:

sudo systemctl stop mysql

Затем скопируйте все свои данные в новую папку для резервного копирования. В системах Ubuntu каталог данных по умолчанию — /var/lib/mysql/:

cp -r /var/lib/mysql /var/lib/mysql_bkp

После создания резервной копии вы готовы проверить, действительно ли таблица повреждена. Если таблица использует механизм хранения MyISAM, вы можете проверить наличие повреждений, перезапустив MySQL и выполнив команду CHECK TABLE из командной строки MySQL:

sudo systemctl start mysql

CHECK TABLE table_name;

В результате выполнения этой команды появится сообщение, указывающее на повреждение таблицы. Если таблица MyISAM действительно повреждена, её обычно можно восстановить, выполнив команду REPAIR TABLE:

REPAIR TABLE table_name;

Если восстановление прошло успешно, в результатах поиска появится сообщение примерно такого вида:

Output
+--------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status | OK |
+--------------------------+--------+----------+----------+

Если таблица по-прежнему повреждена, в документации MySQL предлагается несколько альтернативных методов восстановления поврежденных таблиц.

С другой стороны, если поврежденная таблица использует механизм хранения InnoDB, процесс восстановления будет иным. InnoDB является механизмом хранения по умолчанию в MySQL, начиная с версии 8.0, и имеет автоматическую проверку и восстановление повреждений. InnoDB проверяет наличие поврежденных страниц, выполняя контрольные суммы для каждой читаемой страницы, и если обнаруживает несоответствие в кодировке, автоматически останавливает сервер MySQL.

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

Учитывая это, перезапустите службу MySQL, чтобы проверить, появится ли после этого доступ к серверу:

sudo systemctl restart mysql

Если сервер недоступен или не работает, может быть полезно включить параметр InnoDB force_recovery. Это можно сделать, отредактировав файл mysqld.cnf. В системах Ubuntu и Debian этот файл обычно находится в каталоге etc/mysql. В системах Red Hat и Rocky этот файл обычно находится в каталоге /etc/my.cnf.d.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

В раздел [mysqld] добавьте следующую строку:

. . .
[mysqld]
. . .
innodb_force_recovery=1

Сохраните и закройте файл, а затем перезапустите службу MySQL. Если вам удастся получить доступ к поврежденной таблице, используйте утилиту mysqldump для сохранения данных таблицы в новый файл. Вы можете назвать этот файл как угодно, но здесь мы назовем его dump.sql:

mysqldump database_name table_name > out.sql

Затем удалите таблицу из базы данных. Чтобы избежать повторного открытия команды MySQL, можно использовать следующую команду:

mysql -u user -p --execute="DROP TABLE database_name.table_name"

После этого восстановите таблицу, используя созданный вами файл дампа:

mysql -u user -p < out.sql

Следует отметить, что механизм хранения InnoDB, как правило, более отказоустойчив, чем более старый механизм MyISAM. Таблицы, использующие InnoDB, всё ещё могут быть повреждены, но благодаря функциям самовосстановления риск повреждения и сбоя таблиц значительно ниже.