6.5.8 Как восстановить MySQL с поврежденным InnoDB

MySQL Restore Banner

Иногда MySQL может перестать работать из-за повреждения структуры данных InnoDB. В таких случаях стандартные инструменты, такие как REPAIR TABLE, обычно оказываются бесполезны. Если сервер не запускается или базы данных становятся недоступными, единственным решением остается форсированное восстановление и пересоздание оригинальной структуры данных.

Общий алгоритм действий при этом выглядит следующим образом:

  1. Перевод MySQL в аварийный режим, чтобы получить доступ к данным.
  2. Создание резервной копии всех баз, включая технические.
  3. Полное удаление/комментирование поврежденных данных.
  4. Переинициализация MySQL с чистым каталогом.
  5. Восстановление данных из резервной копии.

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

Пошаговая инструкция

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

Важно: innodb_force_recovery не предназначен для стабильной работы MySQL. Используйте его только для аварийного запуска с целью выполнения восстановления стабильной работы MySQL без этого режима.

1. Переведите MySQL в аварийный режим. Для этого сначала нужно найти файл конфигурации MySQL на Вашей системе:

Для Linux 
sudo nano /etc/mysql/my.cnf

или

sudo nano /etc/my.cnf

В образах Ubuntu+ISPManager4 файл конфигурации MySQL чаще всего находится по адресу /etc/mysql/mysql.conf.d/mysqld.cnf.

Для Windows

Откройте C:\ProgramData\MySQL\MySQL Server X.Y\my.ini в любом текстовом редакторе.

Добавьте в конце секции [mysqld] параметр innodb_force_recovery=6:

[mysqld]
innodb_force_recovery=6

Дополнительно: уровни восстановления могут варьироваться от 1 до 6. Уровень 6 — самый агрессивный и может привести к потере данных, ведь БД при нем уже работает в режиме read_only.

Сохраните файл и закройте редактор.

2. Перезапустите MySQL, чтобы изменения вступили в силу.

Для Linux (SysVinit или Upstart) 
sudo service mysql restart
Для Linux (systemd) 
sudo systemctl restart mysql
Для Windows

Запустите MySQL через services.msc или выполните в командной строке:

net start mysql

3. Теперь создайте полный дамп всех баз данных:

mysqldump -u root -p --all-databases --single-transaction --routines --triggers > backup.sql

Объяснения параметров:

  • --single-transaction позволяет делать дамп без блокировки таблиц.
  • --routines --triggers сохраняет хранимые процедуры и триггеры.
Если дамп невозможен из-за ошибок

Попробуйте --force:

mysqldump -u root -p --all-databases --single-transaction --routines --triggers --force > backup.sql

4. После успешного создания дампа остановите сервер MySQL:

sudo service mysql stop

5. Снова откройте конфигурационный файл my.cnf и отключите режим восстановления удалив строку:

innodb_force_recovery=6

Сохраните изменения.

6. Переименуйте старый каталог данных, чтобы сохранить его копию:

sudo mv /var/lib/mysql /var/lib/mysql_old

Создайте новый каталог и установите нужные права:

sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql

Совет: в некоторых случаях MySQL может работать на нестандартных пользователях, иметь нестандартные права доступа и так далее. Просто скопируйте все эти параметры со старой переименованной папки /var/lib/mysql_old.

7. Инициализируйте новый каталог данных:

Для MySQL 8 и выше 
sudo mysqld --initialize
Для MySQL 5.7 
sudo mysqld --initialize
Для MySQL 5.6 и ниже 
sudo mysql_install_db

Важно: при инициализации новой среды MySQL система предоставит Вам временный пароль root MySQL, который обязательно стоит записать:

MySQL Восстановление, временный пароль

А после чего – желательно сразу же сменить на новый. Сделать это можно так:

  • Войти в MySQL с временным паролем:
mysql -u root -p
  • Выполнив команду, где вместо NewStrongPassword указать пароль:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword';
EXIT;

8. Теперь запустите MySQL заново:

sudo service mysql start

Если MySQL не стартует, проверьте логи:

sudo journalctl -u mysql --no-pager | tail -30

или

sudo cat /var/log/mysql/error.log | tail -n 30

9. Загрузите данные обратно с помощью импорта дампа:

mysql -u root -p < backup.sql

10. Проверьте, корректно ли восстановлены данные с помощью подобного запроса к одной из Ваших баз:

SHOW DATABASES;
USE your_db;
SHOW TABLES;

После чего проверьте также работу сайта, особенно с упором на чтение и запись БД.

Если после тестов и на протяжении пары дней все в порядке – Вы успешно восстановили структуру данных MySQL. Теперь старые файлы (/var/lib/mysql_old) можно при желании удалить для освобождения занятого на диске пространства.