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) можна за бажання видалити для звільнення зайнятого на диску простору