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

Содержание:

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

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

Причины выполнения перенастроек / восстановления таблиц БД

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

• Вызванные перегрузками или ошибками персонала при эксплуатации СУБД.

• Связанные с ошибками кода и вопросами совместимости разных версий СУБД.

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

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

Уточнить причину «неработоспособности» таблицы можно с помощью таких инструментов, как CHECK TABLE, mysqlcheck и mysql_upgrade

Методы восстановления таблиц БД

Независимо от причины сбоя методика «лечения» таблиц сводится к трем основным подходам:

1. Метод дампа и перезагрузки;

2. Использование оператора REPAIR TABLE;

3. Использование оператора ALTER TABLE.

Метод дампа и перезагрузки применяется при несовместимости версий, например, если другая версия СУБД не может обрабатывать таблицы после обновления или понижения версии. В этом случае сначала создаётся дамп, а уже потом выполняется обновление.

Оператор REPAIR TABLE используется, если проверка показала повреждение таблицы или необходимость ее обновления. Применим только для таблиц типов MyISAM, ARCHIVE и CSV. В Linux доступ к нему можно получить через опцию --repair утилиты mysqlcheck.

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

Практическое применение методов

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

На VPS с Debian 12 (Bookworm) развернут сервер MySQL. Проверяем версию:

bash
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

Создадим БД с именем my test baza:

> 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

Если сервер полностью или частично недоступен, можно воспользоваться возможностями самовосстановления сервера при перезапуске, включив опцию innodb_force_recovery для таблиц типа InnoDB. Для этого нужно открыть файл конфигурации mysqld.cnf и внести соответствующие изменения.

В командной строке вводим:

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

В файл нужно добавить следующую строку:

[mysqld]
. . .
innodb_force_recovery=1

После внесения изменений сохраняем файл и выходим из редактора. Затем перезагружаем сервер — есть шанс, что это позволит восстановить его работу.

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

Для этого заходим в консоль управления сервером указанным выше способом, активируем созданную нами базу mytestbaza и вводим в консоли:

> CHECK TABLE goods;

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

Метод дампа и перезагрузки

Если проблемы с базой начались сразу после её обновления, целесообразно применить метод дампа и перезагрузки. Его суть заключается в предварительном создании дампа данных и их последующей загрузке в базу с созданием новой структуры таблицы. Для таблиц типа 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;

Ответ: Движок хранения таблицы не поддерживает операцию восстановления. То есть имеющийся тип сохранения не поддерживает восстановление указанным методом. 

Попробуем изменить тип сохранения и снова применить repair. Для этого нам пригодится оператор ALTER TABLE

Введем в консоли

> ALTER TABLE goods ENGINE = MyISAM;

Ответ: Query OK, 4 rows affected (0.18 sec)

Снова применим оператор исправления ошибок:

> 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