Posted By: Mar. 12, 2020
How to check and repair MySQL Databases
You will need know how to check and repair MySQL databases or tables when you troubleshoot your website as they may have become corrupt. The mysqlcheck command is a maintenance tool that can be used to check, repair, analyze and optimize multiple tables from the command line. One of the best features of using mysqlcheck is that you don’t need to stop the MySQL service to perform the database maintenance.
In this tutorial, we will show you how to check/repair MySQL databases and tables.
Note : It is recommended to take a backup of your databases before performing a database repair operation.
Basic Syntax of mysqlcheck
A basic syntax of mysqlcheck is shown below:
mysqlcheck [OPTION] DATABASENAME TABLENAME -u root -p
A brief explanation of each option that you can use with mysqlcheck as shown below:
-c : Used to check a table for errors
-C : Used to check a tables that are changed after last week.
-a : Used to analyze tables.
-A : Used to check all databases.
-g : Used to check tables for version-dependent changes.
-B, –databases : Used to specify multiple databases.
-F : Used to check tables that are not closed properly.
–fix-db-names : Used to fix the database name.
–fix-table-names : Used to fix the table name.
–e : Used to perform an extended check.
-r : Used to repair corrupt table.
Check a Specific Table in a MySQL Database
In some cases, you need to check a specific table in a specific database. In that case, you can use the following syntax:
mysqlcheck -c databasename tablename -u root -p
For example, checks authors table in books database by running the following command:
mysqlcheck -c books authors -u root -p
You should get the following output:
books.authors OK
Check All Tables in a MySQL Database
If you want to check all the tables in a specific database use the following syntax:
mysqlcheck -c databasename -u root -p
For example, check all tables in books database by running the following command:
mysqlcheck -c books -u root -p
You should get the following output:
Enter password: books.accountant OK books.authors OK books.writer OK
Check and Optimize All Tables and All MySQL Databases
You can check all tables and all databases using the following command:
mysqlcheck -c -u root -p --all-databases
Output:
Enter password: books.accountant OK books.authors OK books.writer OK guest.MyGuests OK movies.netflix OK mysql.columns_priv OK mysql.component OK mysql.db OK mysql.default_roles OK mysql.engine_cost OK mysql.func OK mysql.general_log OK mysql.global_grants OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.password_history OK mysql.plugin OK mysql.procs_priv OK mysql.proxies_priv OK mysql.role_edges OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK
You can optimize all tables and all databases using the following command:
mysqlcheck -o root -p --all-databases
Output:
Enter password: books.accountant note : Table does not support optimize, doing recreate + analyze instead status : OK books.authors note : Table does not support optimize, doing recreate + analyze instead status : OK books.writer note : Table does not support optimize, doing recreate + analyze instead status : OK guest.MyGuests note : Table does not support optimize, doing recreate + analyze instead status : OK movies.netflix note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.columns_priv note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.component note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.db note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.default_roles note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.engine_cost note : Table does not support optimize, doing recreate + analyze instead status : OK
In the above output, you should see “Table does not support optimize” which means the InnoDB table that doesn’t support this option.
Repair MySQL Databases
To repair accountant tables in books database run the following command:
mysqlcheck -r books accountant -u root -p
Output:
mysqlcheck -r books accountant -u root -p Enter password: books.accountant OK
To repair all tables in both books and movies database run the following command:
mysqlcheck -r --databases books movies -u root -p
Output:
Enter password: books.accountant OK books.authors OK books.writer OK movies.netflix OK
To check and repair all tables in all databases run the following command:
mysqlcheck --auto-repair --all-databases -u root -p
Output:
Enter password: books.accountant OK books.authors OK books.writer OK guest.MyGuests OK movies.netflix OK mysql.columns_priv OK mysql.component OK mysql.db OK mysql.default_roles OK mysql.engine_cost OK mysql.func OK mysql.general_log OK mysql.global_grants OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.password_history OK mysql.plugin OK mysql.procs_priv OK mysql.proxies_priv OK mysql.role_edges OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK
Important note: InnoDB storage engine does not support repair. So you will need to change MySQL storage engine from InnoDB to MyISAM.
Check, Repair and Optimize MySQL Database with PHPMyAdmin
You can also check, repair and optimize tables and databases using the PHPMyAdmin web interface.
You can follow the below steps to check, repair and optimize tables and databases:
1- Open the phpMyAdmin tool through a web browser as shown below:
2- Select the affected database in the left pane. You should see all the tables in the right pane in the following screen:
3- Click Check All to select all the tables. At the bottom of the window, choose Check Table from the menu. You should see a summary of the tables in the following screen:
4- To repair the table, Check All to select all the tables and choose Repair Table from the menu. You should see the following page:
Then you should get a confirmation that the command been executed successfully:
5- To optimize the table, Check All to select all the tables and choose Optimize Table from the menu. You should see the following page:
Then you should get a confirmation that the command been executed successfully:
Conclusion
In the above tutorial, we learned how to check and repair MySQL table using mysqlcheck command-line tool. We also learned how to check, repair and optimize database tables using the PHPMyAdmin web interface. I hope you can now easily fix your corrupted tables using this tool.
Программа mysqlcheck используется для проверки целостности (-c, -m, -C), восстановления (-r), анализа (-a) или оптимизации (-o) таблиц базы данных MySQL. Некоторые опции (например -e и -q) могут использоваться одновременно.
Не все опции поддерживаются различными движками MySQL. Опции -c, -r, -a и -o взаимоисключаемые, что означает, что будет применена последняя указанная опция.
Если не указано ничего, то будет применена опция -c. Альтернативами (синонимами) являются:
mysqlrepair: опция по умолчанию -r
mysqlanalyze: опция по умолчанию -a
mysqloptimize: опция по умолчанию -o
Использование:
mysqlcheck [OPTIONS] database [tables]
или:
mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
или
mysqlcheck -uлогин -pпароль [--auto-repair] [--check] [--optimize] [--all-databases] [имя_базы_данных]
Опции:
-A, --all-databases
Проверить все базы данных. Аналогична опции —databases, если указать все базы данных.
-1, --all-in-1
Вместо выполнения запросов для каждой таблицы в отдельности выполнить все запросы в одном отдельно для каждой таблицы. Имена таблиц будут представлены в виде списка имен, разделенных запятой.
-a, --analyze
Анализировать данные таблицы.
--auto-repair
Если проверенная таблица повреждена, автоматически восстановить ее. Исправления будут произведены после проверки всех таблиц, если были обнаружены повреждения.
-#, --debug=...
Выводит информацию журнала отладки. Часто используется следующий набор параметров: ‘d:t:o,filename’
--character-sets-dir=...
Директория, где находятся установки символов.
-c, --check
Проверить таблицу на наличие ошибок.
-C, --check-only-changed
Проверить только таблицы, измененные со времени последней проверки или некорректно закрытые.
--compress
Использовать сжатие данных в протоколе сервер/клиент.
-?, --help
Вывести данную вспомогательную информацию и выйти из программы.
-B, --databases
Проверить несколько баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных.
--default-character-set=...
Установить набор символов по умолчанию.
-F, --fast
Проверить только базы данных, которые не были закрыты должным образом.
-f, --force
Продолжать даже при получении ошибки SQL.
-e, --extended
При использовании данного параметра совместно с CHECK TABLE можно быть уверенным в целостности таблицы. Если же использовать этот параметр с REPAIR TABLE, запустится расширенное восстановление таблицы.
-h, --host=...
Хост базы данных.
-m, --medium-check
Быстрее, чем —extended-check, но находит только 99,99 процентов всех ошибок.
-o, --optimize
Оптимизировать таблицу.
-p, --password[=...]
Используемый пароль при подключении к серверу. Если пароль не указан, у пользователя запрашивается пароль с терминала.
-P, --port=...
Номер порта, используемого для подключения по TCP/IP.
--protocol=(TCP | SOCKET | PIPE | MEMORY)
Для указания протокола соединения, который надлежит использовать.
-q, --quick
При использовании данной опции совместно с CHECK TABLE предотвращается сканирование строк для корректировки неправильных связей. Это наиболее быстрый метод проверки. Если же использовать этот параметр с REPAIR TABLE, программа попытается восстановить только систему индексов. Это наиболее быстрый метод восстановления таблицы.
-r, --repair
Может исправить почти все, за исключением уникальных ключей, имеющих дубликаты.
-s, --silent
Выводить только сообщения об ошибках.
-S, --socket=...
Файл сокета, используемый для подсоединения.
--tables
Перекрывает опцию —databases (-B).
-u, --user=#
Имя пользователя MySQL, если этот пользователь в данное время не является активным.
-v, --verbose
Вывести информацию о различных этапах.
-V, --version
Вывести информацию о версии и выйти из программы.
When your mysql table gets corrupted, use mysqlcheck command to repair it.
Mysqlcheck command checks, repairs, optimizes and analyzes the tables.
1. Check a Specific Table in a Database
If your application gives an error message saying that a specific table is corrupted, execute the mysqlcheck command to check that one table.
The following example checks employee table in thegeekstuff database.
# mysqlcheck -c thegeekstuff employee -u root -p Enter password: thegeekstuff.employee OK
You should pass the username/password to the mysqlcheck command. If not, you’ll get the following error message.
# mysqlcheck -c thegeekstuff employee mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
Please note that myisamchk command that we discussed a while back works similar to the mysqlcheck command. However, the advantage of mysqlcheck command is that it can be executed when the mysql daemon is running. So, using mysqlcheck command you can check and repair corrupted table while the database is still running.
2. Check All Tables in a Database
To check all the tables in a particular database, don’t specify the table name. Just specify the database name.
The following example checks all the tables in the alfresco database.
# mysqlcheck -c alfresco -u root -p Enter password: alfresco.JBPM_ACTION OK alfresco.JBPM_BYTEARRAY OK alfresco.JBPM_BYTEBLOCK OK alfresco.JBPM_COMMENT OK alfresco.JBPM_DECISIONCONDITIONS OK alfresco.JBPM_DELEGATION OK alfresco.JBPM_EVENT OK ..
3. Check All Tables and All Databases
To check all the tables and all the databases use the “–all-databases” along with -c option as shown below.
# mysqlcheck -c -u root -p --all-databases Enter password: thegeekstuff.employee OK alfresco.JBPM_ACTION OK alfresco.JBPM_BYTEARRAY OK alfresco.JBPM_BYTEBLOCK OK .. .. mysql.help_category error : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it! mysql.help_keyword error : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it! ..
If you want to check all tables of few databases, specify the database names using “–databases”.
The following example checks all the tables in thegeekstuff and alfresco database.
# mysqlcheck -c -u root -p --databases thegeekstuff alfresco Enter password: thegeekstuff.employee OK alfresco.JBPM_ACTION OK alfresco.JBPM_BYTEARRAY OK alfresco.JBPM_BYTEBLOCK OK ..
4. Analyze Tables using Mysqlcheck
The following analyzes employee table that is located in thegeekstuff database.
# mysqlcheck -a thegeekstuff employee -u root -p Enter password: thegeekstuff.employee Table is already up to date
Internally mysqlcheck command uses “ANALYZE TABLE” command. While mysqlcheck is executing the analyze command the table is locked and available for other process only in the read mode.
5. Optimize Tables using Mysqlcheck
The following optimizes employee table that is located in thegeekstuff database.
# mysqlcheck -o thegeekstuff employee -u root -p Enter password: thegeekstuff.employee OK
Internally mysqlcheck command uses “OPTIMIZE TABLE” command. When you delete lot of rows from a table, optimizing it helps to get the unused space and defragment the data file. This might improve performance on huge tables that has gone through several updates.
6. Repair Tables using Mysqlcheck
The following repairs employee table that is located in thegeekstuff database.
# mysqlcheck -r thegeekstuff employee -u root -p Enter password: thegeekstuff.employee OK
Internally mysqlcheck command uses “REPAIR TABLE” command. This will repair and fix a corrupted MyISAM and archive tables.
7. Combine Check, Optimize, and Repair Tables
Instead of checking and repairing separately. You can combine check, optimize and repair functionality together using “–auto-repair” as shown below.
The following checks, optimizes and repairs all the corrupted table in thegeekstuff database.
# mysqlcheck -u root -p --auto-repair -c -o thegeekstuff
You an also check, optimize and repair all the tables across all your databases using the following command.
# mysqlcheck -u root -p --auto-repair -c -o --all-databases
If you want to know what the command is doing while it is checking, add the –debug-info as shown below. This is helpful while you are checking a huge table.
# mysqlcheck --debug-info -u root -p --auto-repair -c -o thegeekstuff employee Enter password: thegeekstuff.employee Table is already up to date User time 0.00, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 344, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 12, Involuntary context switches 9
8. Additional Useful Mysqlcheck Options
The following are some of the key options that you can use along with mysqlcheck.
- -A, –all-databases Consider all the databases
- -a, –analyze Analyze tables
- -1, –all-in-1 Use one query per database with tables listed in a comma separated way
- –auto-repair Repair the table automatically it if is corrupted
- -c, –check Check table errors
- -C, –check-only-changed Check tables that are changed since last check
- -g, –check-upgrade Check for version dependent changes in the tables
- -B, –databases Check more than one databases
- -F, –fast Check tables that are not closed properly
- –fix-db-names Fix DB names
- –fix-table-names Fix table names
- -f, –force Continue even when there is an error
- -e, –extended Perform extended check on a table. This will take a long time to execute.
- -m, –medium-check Faster than extended check option, but does most checks
- -o, –optimize Optimize tables
- -q, –quick Faster than medium check option
- -r, –repair Fix the table corruption
Время на прочтение
4 мин
Количество просмотров 25K
MyISAM — основная, вернее, одна из главных (наряду с InnoDB) систем хранения данных в СУБД MySQL. При этом MyISAM таблицы повреждаются очень просто — с этим проблем нет никаких. Сложнее все повреждения ликвидировать, хотя это тоже можно сделать довольно быстро. В этом материале объясняется, как можно решить проблему с использованием myisamchk для идентификации проблемы с MyISAM и ее исправления.
Общеизвестно, что при создании таблицы в MySQL, создаются три различных файла: *.frm — формат таблицы, *.MYD (MyData) — хранение данных, *.MYI (MyIndex) — индекс. Для крупных баз данных стоит использовать InnoDB, поскольку здесь есть некоторая схожесть с Oracle и соответствующая функциональность.
В качестве примера демонстрации ошибки будем использовать вот это:
undef error - DBD::mysql::db selectrow_array failed: Table 'attach_data' is
marked as crashed and should be repaired [for Statement "SELECT LENGTH(thedata)
FROM attach_data WHERE id = ?"] at Bugzilla/Attachment.pm line 344
Bugzilla::Attachment::datasize('Bugzilla::Attachment=HASH(0x9df119c)') calledПонятно, что таблица attach_data повреждена, и ее нужно исправить. Таблицу будем исправлять с использованием myisamchk.
1. Определяем все поврежденные таблицы, используя myisamchk
# myisamchk /var/lib/mysql/bugs/*.MYI >> /tmp/myisamchk_log.txt
myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 18361936
MyISAM-table 'attach_data.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'groups.MYI' is usable but should be fixed
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'profiles.MYI' is usable but should be fixedЕсли указать вывод myisamchk во временный файл, на дисплее будут показаны только имена поврежденных таблиц. А вот в файле /tmp/myisamchk_log.txt будет указано гораздо больше данных включая имена неповрежденных таблиц:
Checking MyISAM file: user_group_map.MYI
Data records: 182 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 12. Исправляем поврежденные таблицы, используя myisamchk
Для этого используем myisamchk, с опцией -r, как и показано ниже:
# myisamchk -r profiles.MYI
- recovering (with sort) MyISAM-table 'profiles.MYI'
Data records: 80
- Fixing index 1
- Fixing index 2Здесь может появиться сообщение об ошибке: clients are using or haven’t closed the table properly, в случае, если таблицы до сих пор используются каким-либо приложением или другими таблицами. Для того, чтобы избежать появления этой ошибки, стоит завершить mysqld, прежде, чем начать исправление таблиц. Здесь можно использовать FLUSH TABLES.
3. Запускаем проверку и исправлением для всей БД MySQL
# myisamchk --silent --force --fast --update-state /var/lib/mysql/bugs/*.MYI
myisamchk: MyISAM file /var/lib/mysql/bugs/groups.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/bugs/profiles.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly-s: выводим только ошибки. Можно использовать двойной -s -s, чтобы сделать режим максимально «тихим»;
-f: автоматический перезапуск myisamchk с опцией -r, есть обнаружены ошибки;
-F: проверка только таблиц, которые не были закрыты в нормальном режиме;
-U: отмечаем таблицы, как поврежденные, если обнаружены ошибки.
4. Использование дополнительной памяти для больших БД MySQL
В случае работы с большими БД восстановление может занять несколько часов. Если есть дополнительные ресурсы, их можно использовать для ускорения процесса:
# myisamchk --silent --force --fast --update-state
--key_buffer_size=512M --sort_buffer_size=512M
--read_buffer_size=4M --write_buffer_size=4M
/var/lib/mysql/bugs/*.MYI5. Использование myisamchk для получения данных о таблице
При необходимости можно получить довольно много данных.
# myisamchk -dvv profiles.MYI
MyISAM file: profiles.MYI
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2007-08-16 18:46:59
Status: open,changed,analyzed,optimized keys,sorted index pages
Auto increment key: 1 Last value: 88
Data records: 88 Deleted blocks: 0
Datafile parts: 118 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
Datafile length: 6292 Keyfile length: 6144
Max datafile length: 4294967294 Max keyfile length: 4398046510079
Recordlength: 2124
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 3 unique int24 1 1024 1024
2 5 765 unique char packed stripped 1 2048 4096
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 3 no zeros
3 5 765 no endspace6. Все опции myisamchk
Для того чтобы получить дополнительную информацию по команде, стоит использовать помощь:
# myisamchk —help
Общие опции:
-s: только вывод ошибок;
-v: вывод большего количества информации;
-V: вывод версии и выход;
-w: ждать, если таблица заблокирована.
Опции проверки:
-c: проверка таблиц на ошибки;
-е: очень «грубая» проверка. Стоит использовать только в крайнем случае, если в обычном режиме ошибки не обнаруживаются;
-F: быстрая проверка, проверяются только таблицы, которые не закрывались правильно;
-С: проверка только таблиц, которые изменились со времени последней поверки;
-f: автоматический перезапуск myisamchk с опцией -r, есть обнаружены ошибки;
-i: вывод статистики по проверенным таблицам;
-m: облегченный режим проверки, быстрее, чем обычный, находится 99,99% ошибок;
-U: обновление статуса: пометка таблиц как поврежденных, если обнаруживаются любые ошибки;
-T: не помечать таблицы как проверенные.
Опции исправления:
-B: бэкап файла .MYD, «filename-time.BAK»;
—correct-checksum;
-е: попытка исправления максимального числа строк в файле данных. Кроме того, эта команда находит «мусорные» строки. Не стоит использовать эту команду, если ситуация не безнадежна;
-f: перезапись старых временных файлов;
-r: исправляет почти все, кроме уникальных ключей, которые на самом деле не уникальны;
-n: принудительная сортировка, даже, если временный файл получается очень большим;
-о: использование старого метода восстановления;
-q: быстрое исправление без модификации файла данных;
-u: распаковка файла, запакованного myisampack.