Как исправить таблицу mysql

Introduction

MySQL offers several different ways to analyze and repair databases. Errors and anomalies appear for numerous reasons, some of which include:

  • A MySQL update or downgrade.
  • Rapid changes in indexes or data.
  • Table inconsistencies.

When checking for corrupted data and repairing tables, the steps taken to fix the errors depend on how you connect to the database, the database server state, the type of error, and the database engine used.

This article shows you multiple ways to troubleshoot and repair a MySQL database.

How to Repair a MySQL Database

Prerequisites

  • Terminal or command line with root privileges.
  • MySQL version 8.0 installed and configured.
  • A corrupt database or database table.

How to Check MySQL Table for Errors

Running diagnostics is the first step to troubleshooting for issues. There are two ways to check a MySQL table for errors:

  • Through a MySQL CHECK TABLE query.
  • Using the terminal program mysqlcheck.

Check a Table with CHECK TABLE

The CHECK TABLE query works in MySQL while the service is running. The general use is:

CHECK TABLE <table name> [, table name, table name] [option] [option];

The simplest use is one table without any options:

CHECK TABLE <table name>;

The output of the CHECK TABLE query shows an informative table about the check results:

Output of the CHECK TABLE MySQL query

The available options when checking tables are:

  • FOR UPGRADE — discovers version inconsistencies. For example, MySQL 8.0 does not support two-digit years, so a table containing such values is marked for upgrade.
  • QUICK — does not check rows for incorrect linking. This is helpful in avoiding timeouts.
  • FAST — performs a check on tables that are not closed properly.
  • CHANGED — only checks tables with changes since the last check or that have not closed properly.
  • MEDIUM — checks table rows to verify the validity of deleted links and performs a checksum for the rows.
  • EXTENDED — does a check for complete consistency.

Without any options, CHECK TABLE performs a MEDIUM check on MyISAM tables and views. The FOR UPGRADE and QUICK perform checks on InnoDB engine tables and views while other options are ignored.

You can also combine the options for a more detailed check. For example, to do a quick check to find out whether a table closed adequately, run:

CHECK TABLE <table name> FAST QUICK;

Any options that do not apply to the tables are ignored.

Check a Table With mysqlcheck

The mysqlcheck command is the command-line version of CHECK TABLE. The program runs in the terminal while the MySQL service is operating. To perform the check:

1. As the root user, navigate to the directory where the databases are stored:

sudo su
cd /var/lib/mysql

2. Check the whole database with:

mysqlcheck <database name>

Terminal output of the mysqlcheck command

Alternatively, check a specific table within the database by providing the table name as well:

mysqlcheck <database name> <table name>

Terminal output of the mysqlcheck command on a table

There are several methods how to repair a database in MySQL. However, keep in mind that these methods are not a quick fix and can result in data loss. If the database tables are corrupt often, identify the reason for the occurrence.

Before making any changes and starting repairs, create a copy of the directory:

cp -rfv /var/lib/mysql /var/lib/mysql_copy

Alternatively, make a backup version of the database before trying any of the repair solutions. Follow our guide: How to Back Up & Restore a MySQL Database.

Below you will find three options how to repair a MySQL database.

Repairing MySQL Database with REPAIR TABLE Query

After scanning and locating the database and the tables with problems, the quickest way to fix the issue is with the REPAIR TABLE query:

Repairing a MySQL database table using REPAIR TABLE

If the server shuts down during the repair, rerun the REPAIR TABLE operation when the server starts again. Make sure to do this before performing any other operations on the same table. In normal conditions, a MyISAM table will not result in data loss from using this method.

Repairing MySQL Database with mysqlcheck

The mysqlcheck command is an alternative to repairing database tables in the terminal.

1. First, navigate to the MySQL database folder as the root user:

cd /var/lib/mysql

2. Add the -r option to the mysqlcheck command to repair the database table:

mysqlcheck -r <database> <table name>

Repairing a MySQL table with mysqlcheck

The mysqlcheck -r command is the terminal version of the REPAIR TABLE query.

Repairing MySQL Database with ALTER TABLE

Use the ALTER TABLE query to rebuild the table using the same storage engine. For example, if you have an InnoDB table, run:

ALTER TABLE <table name> ENGINE = InnoDB;

To rebuild a MyISAM table, use:

ALTER TABLE <table name> ENGINE = MyISAM;

If you are unsure which storage engine the table uses, run the following query to find out:

SHOW CREATE TABLE <table name>;

Checking database table engine using SHOW CREATE TABLE

Running Engine-Specific Diagnostics and Repairs to MySQL Database

Depending on the database engine used, MySQL provides engine-specific approaches for analyzing and repairing a database table.

MyISAM tables are prone to corruptions. However, most of the time the errors are easy to fix.

When working with InnoDB databases, corruptions appear when the checksum values don’t match. Errors typically happen due to a power outage or some other hardware issue.

Checking and Repairing MyISAM Tables with myisamchk

MySQL provides a specific checker for MyISAM tables.

1. Shut down the MySQL server. Open the terminal and run:

systemctl mysql stop

2. As the root user, navigate to the location of the database:

cd /var/lib/mysql/<database name>

3. To check a specific table, run:

myisamchk <table name>

Checking a MyISAM table using myisamchk

4. Repair a table by running:

myisamchk --recover <table name>

5. Restart the MySQL server:

systemctl restart mysql

6. Lastly, check the table in MySQL after recovery:

sudo mysql -u <user> -p <password>
USE <database name>;
SELECT * FROM <table name> LIMIT 4;

Checking a MySQL table after recovery

Running the InnoDB Recovery Process

MySQL documentation suggests running the InnoDB recovery process for InnoDB databases.

1. Locate and open the global configuration file using your favorite text editor. For Ubuntu and Debian systems, the location is:

cd /etc/mysql/my.cnf

Note: For CentOS and Fedora, the location is:

cd /etc/my.cnf

2. Add the following option to the my.cnf file:

[mysqld]
innodb_force_recovery=4

By default, the forced recovery option is set to 0 (off) and goes up to 6. Every new level includes the recovery features of the lower numbers.

3. Save and close the file.

4. Restart the MySQL server to ensure the new configuration file applies:

systemctl restart mysql

5. In the home folder, export all the databases to a backup file with the mysqldump command:

sudo mysqldump --all-databases --add-drop-database --add-drop-table --routines > <file name>.sql

6. Start MySQL client and drop all the affected database tables. To drop a database table, try:

DROP TABLE IF EXISTS <table name>;

If the table does not drop, try removing the database table manually. Log in as the root user and navigate to the directory where the databases are:

sudo su
cd /var/lib/mysql/<database name>

Remove the database table:

rm -r <table name>

7. Stop the MySQL server:

systemctl stop mysql

8. In the my.cnf file, comment out the innodb_force_recovery option added in step 2 to disable the recovery mode. Then, save the file and close.

9. Start the MySQL server up again:

systemctl start mysql

10. Restore the database from the backup .sql file in the MySQL shell. Log into the MySQL client:

sudo mysql -u <username> -p <password>

11. Run the following query to import the databases:

USE <database name>;
# Turning off auto commit is optional and speeds things up for larger databases
SET autocommit=0; 
SOURCE <file name>.sql;

12. Lastly, test the database to confirm everything works correctly. If not, restore the database from backup and try an alternate method.

Conclusion

This tutorial outlines several different methods to repair a MySQL database. Since database corruptions are bound to happen, MySQL provides multiple ways to discover and fix errors in a database, some of which are outlined in this article.

However, since recovery sometimes results in data loss, the best way to protect your information is to create regular backups and follow the best database security practices.

Как известно, любые повреждения происходят в результате каких-либо внешних факторов. Состояние внутренней структуры таблиц баз данных (БД), в данном случае таблиц MySQL, всецело определяет, насколько надёжным будет использование самой БД. На высоконагруженных серверах БД повреждение таблиц отнюдь не редкость. Администраторам, да и самим пользователям порой приходится прибегать к починке структуры таблиц своих БД. О том, по каким причинам повреждаются таблицы и какие существуют методы решения данной проблемы на примере MySQL и будет изложено в данной статье.

Содержание

  1. Причины повреждения таблиц
  2. Проверка таблиц на предмет повреждений
  3. Восстановление MyISAM-таблиц
  4. Восстановление InnoDB-таблиц
  5. Заключение

Причины повреждения таблиц

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

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

В любом случае, первое, что нужно сделать — это остановить работу MySQL-сервера, сделать резервные копии файлов таблиц, которые по умолчанию находятся в каталоге /var/lib/mysql:

$ cp -r /var/lib/mysql ~/backups/mysql_back

Для выполнения этой команды могут потребоваться привилегии суперпользователя или пользователя, имеющего доступ к каталогу /var/lib/mysql. Только после этого можно приступить к разбору ситуации и проверить, действительно ли таблицы повреждены. Для этого можно воспользоваться командной консолью MySQL. Но предварительно необходимо снова запустить MySQL-сервер. И авторизоваться на нём (от имени пользователя-администратора) с помощью команды:

$ mysql -u username -p

Здесь username – имя пользователя-администратора в системе MySQL.

Проверка таблиц на предмет повреждений

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

mysql> use db_name;

Здесь db_name – имя требуемой базы данных, таблицы которой нужно проверить. Для получения списка всех имеющихся на сервере БД можно выполнить следующий запрос:

mysql> show databases;

Теперь можно выполнить, собственно, саму проверку:

mysql> check table db_table;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| db_name.db_table   | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.00 sec)

Как нетрудно догадаться, «db_table» здесь — это имя требуемой таблицы. В данном случае вывод в столбце «Msg-text» говорит о том, что с таблицей всё в порядке.

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

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

mysql> repair table db_table;

Теперь, если починка завершилась нормально, будет получен следующий вывод:

+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| db_name.db_table  | repair | status   | OK       |
+-------------------+--------+----------+----------+

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

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

В случае, если повреждённые таблицы обслуживались движком InnoDB, то придётся использовать более сложный метод восстановления. Дело в том, что движок InnoDB сам способен следить за исправностью таблиц и устранять повреждения. Однако, если он по каким-либо причинам не справляется с этой задачей, то сервер MySQL останавливается.

Метод восстановления для InnoDB-таблиц рекомендуется разработчиками MySQL и суть его в следующем:

  • необходимо сначала восстановить доступ к повреждённой таблице, поскольку сервер в этом случае останавливается;
  • выполнить резервную копию в виде дампа повреждённых таблиц, используя утилиту mysqldump, которая сохраняет структуру таблицы и данные;
  • удалить повреждённую таблицу;
  • выполнить загрузку дампа таблицы, но уже во вновь созданную таблицу БД, что будет сделано утилитой mysqldump автоматически.

Для восстановления доступа к таблицам можно сначала попытаться перезагрузить сервер MySQL. Если это не помогло, то можно запустить его с опцией innodb_force_recovery. Для этого необходимо отредактировать конфигурационный файл /etc/mysql/mysql.conf.d/mysqld.cnf, например, используя текстовый редактор nano:

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

Необходимо сделать изменения в разделе [mysqld], добавив в него строку:

. . .
[mysqld]
. . .
innodb_force_recovery=1

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

$ mysqldump -u username -p db_name db_table > ~/backups/back.dump

Дамп back.dump будет сохранён в подкаталоге backups домашнего каталога текущего пользователя.

Теперь необходимо удалить из БД повреждённую таблицу. Для этого после авторизации в командной консоли MySQL (как описано в главе «Причины повреждения таблиц»), нужно выполнить следующий запрос:

mysql> DROP TABLE db_name.db_table;

Если БД была предварительно выбрана командой use, то в запросе следует указать только таблицу:

mysql> DROP TABLE db_table;

Теперь можно выйти из командной консоли MySQL командой exit. И выполнить загрузку дампа таблицы в БД:

$ mysql -u user -p < /home/username/backups/back.dump

В данном случае вместо «/home/username/backups/back.dump» следует использовать  местоположение, куда ранее был сохранён дамп.

Заключение

В заключение следует отметить, что представленные способы восстановления являются пожалуй единственными вариантами, когда восстановление можно произвести «малыми силами» и в сжатые сроки. Если же повреждения слишком масштабные и сильные, то необходимо использовать альтернативные, более сложные способы восстановления.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

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

Некоторые распространенные причины поврежденных таблиц:

Сервер MySQL останавливается в середине записи.
Внешняя программа изменяет таблицу, которая одновременно изменяется сервером.
Машина неожиданно выключилась.
Аппаратное обеспечение компьютера выходит из строя.
Где-то в программном коде MySQL есть программная ошибка.
Если вы подозреваете, что одна из ваших таблиц была повреждена, вы должны сделать резервную копию вашего каталога данных, прежде чем устранять неполадки или пытаться исправить таблицу. Это поможет минимизировать риск потери данных.

Сначала остановите службу MySQL:

sudo systemctl stop mysql

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

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

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

CHECK TABLE table_name;

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

REPAIR TABLE table_name;
Предполагая, что ремонт был успешным, вы увидите сообщение, подобное следующему:

Output
+———————————+———+———-+—————+
| Table                           | Op     | Msg_type | Msg_text |
+———————————+———+———-+—————+
| database_name.table_name | repair | status   | OK           |
+———————————+———+———-+—————+

Однако, если таблица все еще повреждена, документация MySQL предлагает несколько альтернативных методов восстановления поврежденных таблиц.

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

Редко возникает необходимость исправления таблиц InnoDB, поскольку InnoDB имеет механизм восстановления после сбоя, который может решить большинство проблем при перезапуске сервера. Однако, если вы столкнулись с ситуацией, когда вам нужно перестроить поврежденную таблицу InnoDB, в документации MySQL рекомендуется использовать метод «Сброс и перезагрузка». Это включает в себя восстановление доступа к поврежденной таблице, использование mysqldump утилиты для создания логической резервной копии таблицы, которая сохранит структуру таблицы и данные в ней, а затем перезагрузку таблицы обратно в базу данных.

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

sudo systemctl restart mysql

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

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

В [mysqld]разделе добавьте следующую строку:


[mysqld]

innodb_force_recovery=1

Сохраните и закройте файл, а затем попробуйте перезапустить службу MySQL снова. Если вы можете успешно получить доступ к поврежденной таблице, используйте mysqldump утилиту для выгрузки данных таблицы в новый файл. Вы можете назвать этот файл как угодно, но здесь мы назовем его out.sql:

mysqldump database_name table_name > out.sql

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

mysql -u user -p --execute="DROP TABLE database_name.table_name"

После этого восстановите таблицу с помощью только что созданного файла дампа:

mysql -u user -p < out.sql

Обратите внимание, что механизм хранения InnoDB, как правило, более отказоустойчив, чем старый механизм MyISAM. Таблицы, использующие InnoDB, все еще могут быть повреждены, но благодаря функциям автоматического восстановления риск повреждения и сбоев таблиц значительно ниже.

9 апреля, 2019 12:02 пп
3 415 views
| Комментариев нет

MariaDB, mySQL

Этот мануал является частью серии статей по устранению неполадок и диагностике настроек MySQL. Здесь мы рассмотрим некоторые общие проблемы, с которыми сталкиваются многие пользователи MySQL, и предоставим рекомендации по устранению конкретных ошибок. Также здесь вы найдете ссылки на другие мануалы и официальную документацию по MySQL, которые могут быть полезны в некоторых случаях.

Читайте также:

  • Доступ к логам ошибок MySQL
  • Устранение неполадок в запросах MySQL
  • Управление удаленным доступом к MySQL
  • Устранение сбоев в MySQL

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

Вот некоторые общие причины повреждения таблиц:

  • Сервер MySQL остановился, не завершив операцию записи.
  • Таблица изменяется внешней программой и сервером одновременно.
  • Машина внезапно остановилась.
  • Произошла поломка аппаратного обеспечения.
  • В коде MySQL появился баг.

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

Сначала остановите сервис MySQL:

sudo systemctl stop mysql

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

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

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

CHECK TABLE table_name;

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

REPAIR TABLE table_name;

Если восстановление данных прошло успешно, вы увидите такое сообщение:

+--------------------------+--------+----------+----------+
| Table                    | Op     | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status   | OK       |
+--------------------------+--------+----------+----------+

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

Если поврежденная таблица использует движок хранения InnoDB, процесс ее восстановления будет другим. InnoDB – это движок хранения MySQL по умолчанию начиная с версии 5.5, он включает в себя автоматическую проверку и исправление ошибок. InnoDB проверяет наличие поврежденных страниц, отслеживая контрольные суммы на каждой странице. Если он находит несоответствие контрольных сумм, он автоматически останавливает сервер MySQL.

Необходимость исправления таблиц InnoDB возникает редко, поскольку InnoDB имеет встроенный механизм восстановления после сбоя, который может решить большинство проблем при перезапуске сервера. Однако если вам все же нужно восстановить поврежденную таблицу InnoDB самостоятельно, документация MySQL рекомендует использовать метод сброса и перезагрузки. Этот метод подразумевает восстановление доступа к поврежденной таблице с помощью утилиты mysqldump, которая создает логическую резервную копию таблицы, сохраняя структуру и данные, а затем перезагружает таблицу обратно в базу данных.

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

sudo systemctl restart mysql

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

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

В раздел [mysqld] добавьте следующую строку:

. . .
[mysqld]
. . .
innodb_force_recovery=1

Сохраните и закройте файл, а затем попробуйте перезапустить MySQL снова. Если вы можете получить доступ к поврежденной таблице, используйте утилиту mysqldump, чтобы выгрузить данные из таблицы в новый файл. Вы можете назвать этот файл как угодно (здесь мы назовем его out.sql):

mysqldump database_name table_name > out.sql

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

mysql -u user -p --execute="DROP TABLE database_name.table_name"

После этого восстановите таблицу с помощью только что созданного дампа:

mysql -u user -p < out.sql

Обратите внимание, механизм хранения InnoDB, как правило, более отказоустойчив, чем старый механизм MyISAM. Таблицы, использующие InnoDB, все еще могут быть повреждены, но благодаря функциям автоматического восстановления риск повреждения и сбоев таких таблиц значительно ниже.

Читайте также: Устранение ошибок сокетов в MySQL

Tags: MySQL

Понравилась статья? Поделить с друзьями:

Не пропустите также:

  • Ошибка сканера 6 hp laserjet m1120 mfp как исправить
  • Как найти количество газообразного вещества
  • Как найти абсолютное отклонение в балансе
  • Как найти картинку без надписи
  • Как с помощью телескопа найти планеты

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии