Стаття також доступна російською (перейти до перегляду).

Зміст:

• Причини виконання переналаштувань / відновлень таблиць БД
• Методи відновлення таблиць БД
• Практичне використання методів відновлення
• Підготовка БД
• Загальні дії
• Метод дампа та перезавантаження
• Методи REPAIR TABLE и ALTER TABLE
• Технічне рішення від дата-центру FREEhost.UA

Під час обслуговування баз даних MySQL може виникнути потреба у переналаштуванні або відновленні таблиць або індексів, що може бути викликано багатьма причинами – пошкодження таблиць, несумісність із іншими версіями БД тощо. Існує кілька механізмів для виконання операцій по відновленню роботи БД, котрі враховують налаштування таблиць та тип пошкодження, або необхідного перетворення. Розглянемо їх використання на конкретних прикладах.       

Причини виконання переналаштувань / відновлень таблиць БД

Усі причини, котрі призводять до повної або часткової непрацездатності таблиць БД умовно можна поділити на дві великі групи: 

• Викликані перенавантаженнями або помилками персоналу під час експлуатації СУБД;

• Пов’язані із помилками коду та питаннями сумісності різних версій СУБД.

Пошук причин та вибір методів відновлення багато в чому визначається типом механізму збереження, котрий використовується у визначеній таблиці. Останні версії MySQL підтримують біля десятка механізмів збереження: MyISAM, MERGE, InnoDB, BDB та інші. 

Перші версії СУБД за замовчуванням використовували MyISAM, але починаючи з версії 5.5 основним став тип InnoDB.

Деталізувати причину «непрацездатності» таблиці можна за допомогою використання таких інструментів, як CHECK TABLE, mysqlcheck та mysql_upgrade.    

Методи відновлення таблиць БД

Незалежно від причин виникнення збою у роботі таблиць методика їх «лікування» зводиться до застосування трьох основних методів:

• Метод дампа та перезавантаження;

• Використання оператору REPAIR TABLE;

• Використання оператору ALTER TABLE.

Метод дампа та перезавантаження може бути застосований при несумісності версій, наприклад, у випадку, якщо інша версія СУБД не здатна обробляти таблиці після оновлення або пониження версії. І тому перед тим слід спочатку застосувати метод дампа, а вже потім виконати оновлення. 

Оператор REPAIR TABLE використовується у випадку, якщо перевірка показала наявність пошкодження у таблиці або необхідність її оновлення. Його можна застосовувати лише до таблиць типу MyISAM, ARCHIVE та CSV, оскільки з іншими він «не працює», у чому нижче ми переконаємося самі. Доступ до оператора із командної строчки оболонки Linux можна отримати за допомогою опції –repair утиліти mysqlcheck. 

Оператор ALTER TABLE дозволяє змінити базові налаштування таблиці, зокрема, тип її механізму збереження. Це дозволяє уникнути багатьох проблем із сумісністю версій та мати різні типи таблиць у одній базі.     

Практичне використання методів відновлення 

Підготовка БД

Спочатку підготуємо БД для проведення експериментів. На нашому VPS-сервері із Unix-подібною ОС Debian 12 (Bookworm) розгорнутий сервер MySQL. Перевіримо його версію.

$ mysqladmin -u root -p version

Вихід команди:

mysqladmin  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Server version          8.0.35
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 1 hour 19 min 8 sec

Виходячи з версії програми (8.0.35), можна зробити висновок, що за замовчуванням створюються таблиці прогресивного типу InnoDB. 

Включимо автоматичний запуск MySQL-серверу при завантаженні системи:

$ sudo systemctl enable mysql

Запустимо сервер:

$ sudo systemctl start mysql

Перевіримо стан системної служби:

$ sudo systemctl status mysql

Вихід команди:

mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Mon 2025-08-04 17:56:47 EEST; 1h 26min ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
   Main PID: 3562465 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 5700)
     Memory: 390.8M
        CPU: 33.011s
     CGroup: /system.slice/mysql.service
             ??3562465 /usr/sbin/mysqld
Aug 04 17:56:46 dedicated systemd[1]: Starting mysql.service - MySQL Community Server...
Aug 04 17:56:47 dedicated systemd[1]: Started mysql.service - MySQL Community Server.

Отже, служба запущена.

Увійдемо у консоль СУБД як root користувач БД:

$ sudo mysql -u root -p

Створимо БД із ім’ям mytestbaza:

> CREATE DATABASE mytestbaza;

Зробимо створену нами базу активною:

USE mytestbaza;

Створимо таблицю БД із ім’ям goods (товари), котра має чотири поля:

CREATE TABLE goods (
        goods_id INT AUTO_INCREMENT PRIMARY KEY,
        goods_name VARCHAR(50) NOT NULL,
        categ VARCHAR(40),
        price DECIMAL(10, 2)
        );

 

Заповнюємо таблицю записами відповідно до категорій товарів за допомогою наступного SQL-запиту: 

INSERT INTO goods (goods_name, categ, price) VALUES
        ('Tablet', 'Electro', 1000.50),
        ('Lamborghini', 'Auto', 900000.00),
        ('Kettle', 'Dishes', 50.20),
        ('Gumshoes', 'Shoes', 30.70);

Виберемо наявні у таблиці записи, щоб перевірити чи доступні введені нам дані:

SELECT * FROM goods;

Можна переконатися, що дані присутні. Отже, таблиця БД підготовлена і тому можна переходити до етапу тестування.

Загальні дії 

У випадку появи ознак будь-яких проблем із даними або сервером, в першу чергу слід зупинити роботу служби: 

$ sudo systemctl stop mysql

Після цього необхідно скопіювати дані в каталог для резервного копіювання. У нашому випадку це каталог mysql_1copy: 

$ cp -r /var/lib/mysql /var/lib/mysql_1copy

Після виконаних дій можна переходити до етапу виявлення причини збою та її усунення. Для цього спочатку запустимо сервер:

$ sudo systemctl start mysql

Тепер спробуємо його перезавантажити. Використання цього прийому дає шанс відновити безперешкодний доступ до таблиці.  

$ sudo systemctl restart mysql

У випадку, якщо сервер повністю або частково недоступний можна скористатися можливостями самовідновлення серверу при перезавантаженні шляхом включення опції force_recovery для таблиць типу InnoDB. Для цього слід відкрити файл конфігурації mysqld.cnf та внести відповідні зміни. Продемонструємо це.

Введемо у командній строчці:

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

Слід додати до файлу наступну строчку:

[mysqld]
. . .
innodb_force_recovery=1

Після внесення змін збережемо файл та вийдемо з редактору. Після виконання перезавантаження серверу маємо шанс отримати гарний результат.

Якщо ж сервер доступний і проблема з ним не пов’язана, необхідно скористатися одним з методів, наведених вище в залежності від вихідних даних поточної ситуації, але перед тим слід провести первинну перевірку бази за допомогою одного з інструментів, наприклад, CHECK TABLE. 

Для цього необхідно увійти у консоль управління сервером зазначеним вище способом та активувати створену нами базу mytestbaza. Після цього ввести у консолі:

> CHECK TABLE goods;

Можна переконатися, що операція перевірки таблиці була виконана і її статус ОК, тобто, все гаразд. У випадку наявності якихось проблем на консоль були б виведені відповідні повідомлення.

Метод дампа та перезавантаження

Якщо проблеми з базою почалися одразу після її оновлення, вочевидь, слід застосувати метод дампа та перезавантаження. Сутність його полягає у попередньому створенні дампа даних та його перезавантаження у базу зі створенням нової структури таблиці. Для таблиць типу InnoDB, як у нашому випадку, слід застосувати команду mysqldump. Продемонструємо це.

Введемо у консолі:

> mysqldump mytestbaza goods > mydump_goods.sql
> mysql mytestbaza < mydump_goods.sql
Тут mydump_goods.sql – файл дампа для goods.

В результаті ми отримали оновлену / переналаштовану структуру із завантаженими даними. 

Якщо виникла потреба переналаштувати всі таблиці визначеної БД, слід ввести у консолі:

> mysqldump mytestbaza > mydump_goods.sql
> mysql mytestbaza < mydump_goods.sql

Методи REPAIR TABLE та ALTER TABLE

Метод repair слід застосовувати для виправлення пошкодженої таблиці. Але, як вже зазначалося, метод діє далеко не для всіх типів таблиць. Щоб перевірити це спробуємо його застосувати до нашої таблиці, котра повинна мати тип InnoDB. З’ясувати, який тип має таблиця можна за допомогою наступної команди:

> SHOW CREATE TABLE goods;

Вихід команди:

goods | CREATE TABLE `goods` (
  `goods_id` int NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(50) NOT NULL,
  `categ` varchar(40) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

Отже, дійсно, це InnoDB, як і повинно бути, виходячи із зазначеного в документації MySQL.      

Тепер спробуємо застосувати до неї команду виправлення помилок:

> REPAIR TABLE goods;

Відповідь: The storage engine for the table doesn't support repair. Тобто, наявний тип збереження не підтримує відновлення вказаним методом. 

Спробуємо змінити тип збереження і знову застосувати repair. Для цього нам знадобиться оператор ALTER TABLE.

Введемо у консолі:

> ALTER TABLE goods ENGINE = MyISAM;

Відповідь: Query OK, 4 rows affected (0.18 sec)

Знову застосуємо оператор виправлення помилок:

<pre> REPAIR TABLE goods;

Можна переконатися, що оператор був успішно виконаний. Результат операції: ОК.

Таким чином, зміна методу збереження для таблиці допомогла нам успішно застосувати оператор виправлення помилок, що іноді може знадобитися на практиці

Підписуйтесь на наш телеграм-канал https://t.me/freehostua, щоб бути в курсі нових корисних матеріалів.

Дивіться наш канал Youtube на https://www.youtube.com/freehostua.

Ми у чомусь помилилися, чи щось пропустили?

Напишіть про це у коментарях, ми з задоволенням відповімо та обговоримо Ваші зауваження та пропозиції.

Читайте также: Як відновити пошкоджену таблицю MySQL.

Дата: 12.08.2025 Автор: Олександр Ровник #backup#linux#mysql

Голосування 1 2 3 4 5