• База знаний
  • /
  • Блог
  • /
  • Wiki
  • /
  • ONLINE CHAT
+380 (44) 364 05 71

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

Как восстановить таблицу базы данных MySQL

Оглавление

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

Основные причины повреждения таблиц с данными в СУБД MySQL

Несмотря на постоянное развитие и усовершенствование методов упорядочения и хранения данных, указанная СУБД все равно не может обеспечить достаточную надежность и отказоустойчивость баз данных, построенных на ее основе. Это связано не только с несовершенством алгоритмов работы и технологий, но и вполне объективными факторами, такими, например, как условия эксплуатации базы.

Перечислим наиболее распространенные причины:

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

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

Обзор существующих подсистем хранения данных

В связи с этим указанная подсистема не отвечает требованиям стандарта ACID (atomicity, consistency, isolation, durability), относительно надежности работы транзакционных систем и поэтому не может быть рекомендована для использования в высоконагруженных проектах, в которых объем данных составляет более 2 Гб и используются сложные запросы, связанные с модификацией данных. MyISAM относится к подсистемам, не позволяющим СУБД в автоматическом режиме инициировать запуск процесса восстановления таблиц в случае их повреждения. Для этого «вылечить» таблицу возможно только внешними утилитами, запуск которых должен быть инициирован пользователем. В связи с этим указанная подсистема не отвечает требованиям стандарта ACID (atomicity, consistency, isolation, durability), относительно надежности работы транзакционных систем и поэтому не может быть рекомендована для использования в высоконагруженных проектах, в которых объем данных составляет более 2 Гб и используются сложные запросы, связанные с модификацией данных. MyISAM относится к подсистемам, не позволяющим СУБД в автоматическом режиме инициировать запуск процесса восстановления таблиц в случае их повреждения. Для этого «вылечить» таблицу возможно только внешними утилитами, запуск которых должен быть инициирован пользователем.

Начиная с версии MySQL 8.0, СУБД по умолчанию использует другую, более совершенную подсистему хранения данных - InnoDB, в которой все таблицы хранятся в общем файле, что позволяет обеспечить поддержку транзакций и внешних ключей и, соответственно, гарантировать определенный уровень отказа устойчивости и надежности работы . Проекты MySQL, созданные на базе подсистемы InnoDB, способны инициировать самозапуск процесса восстановления поврежденных таблиц и, соответственно, в автоматическом режиме исправлять ошибки и восстанавливать таблицы без вмешательства пользователя.

Восстановление таблиц типа MyISAM

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

  • Остановить службу MySQL;
  • Копировать все данные в каталог резервного копирования;
  • Инициировать процессы проверки таблиц и их обновления.

Остановку MySQL-сервера можно выполнить с помощью следующей команды:

$ systemctl stop mysql

Остановка службы MySQL

Конечно, пользователь при этом должен иметь администраторские права, или воспользоваться командой sudo.

Сделаем резервную копию наших данных. В системах Ubuntu по умолчанию все данные хранятся в каталоге /var/lib/mysql/. Исходя из этого, введем в терминале:

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

Колпирование данных

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

mysql> use motoinfc_kiev;

Выбор базы данных

mysql> show tables;

просмотр базы данных

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

mysql> check table control_ip;

Проверка таблицы.

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

mysql> repair table control_ip;

Устранение повреждений таблицы.

На выходе также получаем отчет, где указанная примененная к таблице операция и результаты ее выполнения. То есть в данном случае все ОК.

Восстановление таблиц типа InnoDB

В случае если в таблицах используется подсистема типа InnoDB, то, как уже отмечалось, сама СУБД будет инициировать процесс восстановления и в автоматическом режиме исправлять ошибки. Достаточно только перезагрузить сервер MySQL. Однако когда Администратору все же необходимо перенастроить поврежденную таблицу, в документации MySQL предлагается применить метод «Дамп и перезагрузка». Алгоритм действий в этом случае должен быть следующим:

  • Возобновление доступа к поврежденной таблице;
  • Создание логической резервной копии таблицы с сохранением структуры и данных;
  • Удаление поврежденной таблицы;
  • Загрузка в базу восстановленной таблицы с данными из сохраненной копии.

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

$ systemctl restart mysql

Перезагрузка службы MySQL.

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

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

Запуск редактора nano для внесений правок в файл.

Внесем в файл изменения, как показано ниже. Сохраним изменения (ctrl+O) и выйдем из редактора (ctrl+X).

Внесение правок в mysqld.cnf

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

$ mysqldump -u alexander -p motoinfc_kiev control_ip > dumpcontrolip.sql

Разъяснить назначение параметров данного формата команды mysqldump:

  • u – указание на имя пользователя базы данных;
  • p – указание на пароль доступа к базе данных, который можно указать сразу после параметра или ввести затем по запросу системы;
  • alexander – логин пользователя базы данных;
  • motoinfc_kiev – название базы данных
  • control_ip – название таблицы, копия которой нам нужна;
  • dumpcontrolip.sql – файл для сохранения структуры и данных таблицы control_ip или дамп.

После подтверждения набранной команды система выведет запрос на ввод пароля доступа к базе, как показано ниже.

Создание дампа таблицы

После ввода пароля дамп будет создан в текущем каталоге. Чтобы убедиться в этом, выведем содержимое файла дампа в терминале:

$ cat dumpcontrolip.sql

Просмотр созданного дампа.

Просмотр созданного дампа.

Конечный экран вывода команды будет выглядеть следующим образом:

Просмотр созданного дампа.

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

Развертывание таблицы на сервере будет происходить следующим образом. Сначала с помощью команды DROP TABLE IF EXISTS control_ip, включающей файл дампа, будет удалена поврежденная таблица control_ip. Даже если таблица будет удалена ранее, ошибка не будет выведена из-за наличия параметра IF EXISTS в указанной команде. На втором этапе с помощью команды CREATE TABLE будет создана новая структура таблицы, что исключит возможность повторения ошибок. И уже на третьем этапе с помощью команды INSERT INTO будут загружены данные, хранившиеся в поврежденной таблице.

Теперь загрузим дамп в базу motoinfc_kiev. Для этого введем следующую команду:

$ mysql -u alexander -p motoinfc_kiev < dumpcontrolip.sql

Загрузка бампа в базу данных.

После ввода пароля команда будет успешно выполнена.

Усппешная загрузка дампа в бд.

Таким же образом мы можем восстановить любую поврежденную таблицу типа InnoDB.

Предлагаем Вам услуги ВВС хостинга в Украине и Польше. Во все тарифные планы включена базовая поддержка. Поэтому такие проблемы, как помощь в восстановлении таблиц баз данных, техническая поддержка FREEhost.UA берет на себя.

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

Смотрите наш канал Youtube на https://www.youtube.com/freehostua.

Мы в чем ошиблись, или что-то пропустили?

Напишите Об этом в комментариях, мы с удовольствием ответим и обсуждаем Ваши замечания и предложения.

Дата: 21.11.2022
Автор: Александр
Голосование

Авторам статьи важно Ваше мнение. Будем рады его обсудить с Вами:

comments powered by Disqus
navigate
go
exit
Спасибо, что выбираете FREEhost.UA