Mysql innodb проверка базы на ошибки

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.

Время на прочтение
5 мин

Количество просмотров 31K

Предположим, вы работаете с MySQL таблицами Innodb, и в один

прекрасный

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

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

Так вот, сейчас речь о ситуации вроде этой:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
080703 23:46:16 InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end 1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed

Что делать для восстановления таблицы? В принципе, типов повреждений может быть несколько, ниже мы рассмотрим один из наиболее распространенных моментов. А именно — когда повреждена страница в кластеризованном первичном ключе.

В примере рассматривается файл test.idb, где заменено несколько байтов, так что повреждение достаточно умеренное.

При этом операция CHECK TABLE в INNODB практически бесполезна. Для текущего поврежденного файла мы получаем:

mysql> check table test;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> check table test;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.test | check | status   | OK       |
+-----------+-------+----------+----------+
1 row in set (0.69 sec)

Первый запуск — проверка таблицы в обычном режиме, в этом случае innodb просто падает, если есть ошибка в контрольной сумме (даже, если мы выполняем CHECK). Во втором случае запускаем innodb_force_recovery=1. И даже здесь мы получаем в логах запись о несовпадении контрольной суммы, при этом CHECK TABLE говорит нам, что с таблицей все ок. Как видим, CHECK TABLE доверять можно далеко не всегда.

В примере «повреждение» совсем небольшое, поэтому, если запускаем innodb_force_recovery=1, получаем следующее:

mysql> CREATE TABLE `test2` (
    ->   `c` char(255) DEFAULT NULL,
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2 select * from test;
Query OK, 229376 rows affected (0.91 sec)
Records: 229376  Duplicates: 0  Warnings: 0

Теперь мы получили все данные в таблице MyISAM, так что все, что остается сделать — дропнуть старую таблицу, и конвертировать новую в innodb после рестарта без опции innodb_force_recovery. Если старая таблица будет нужна в дальнейшем, ее можно просто переименовать. Вторая альтернатива — сделать дамп с MySQLDump и загрузить таблицу обратно. В принципе, это почти одно и то же. MyISAM используется по причине, описанной ниже.

Почему бы просто не воспользоваться OPTIMIZE TABLE? Все потому, что работа в режимер innodb_force_recovery проводится в режиме чтения для операций с данными, поэтому нельзя вставлять или стирать данные (при этом можно создавать или удалять таблицы Innodb):

mysql> optimize table test;
+-----------+----------+----------+----------------------------------+
| Table     | Op       | Msg_type | Msg_text                         |
+-----------+----------+----------+----------------------------------+
| test.test | optimize | error    | Got error -1 from storage engine |
| test.test | optimize | status   | Operation failed                 |
+-----------+----------+----------+----------------------------------+
2 rows in set, 2 warnings (0.09 sec)

Это было просто, правда?

После этого можно пойти еще дальше, и отредактировать наш файл test.ibd, полностью удалив один из заголовков страницы. Теперь CHECK TABLE будет падать даже при использовании innodb_force_recovery=1

080704 0:22:53 InnoDB: Assertion failure in thread 1158060352 in file btr/btr0btr.c line 3235
InnoDB: Failing assertion: page_get_n_recs(page) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even

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

Получаем такую ошибку:

mysql> insert into test2 select * from test;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Попытки использования автоматических процессов восстановления поврежденных данных не приводят к положительному результату. Поэтому стоит использовать серию команд с LIMIT для режима ручного восстановления:

mysql> insert ignore into test2 select * from test limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> insert ignore into test2 select * from test limit 20;
Query OK, 10 rows affected (0.00 sec)
Records: 20  Duplicates: 10  Warnings: 0
mysql> insert ignore into test2 select * from test limit 100;
Query OK, 80 rows affected (0.00 sec)
Records: 100  Duplicates: 20  Warnings: 0
mysql> insert ignore into test2 select * from test limit 200;
Query OK, 100 rows affected (1.47 sec)
Records: 200  Duplicates: 100  Warnings: 0
mysql> insert ignore into test2 select * from test limit 300;
ERROR 2013 (HY000): Lost connection to MySQL server during query

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

Теперь мы обнаружили поврежденные данные в таблице, при этом стоит использовать max PK, и проверить иные значения:

mysql> select max(id) from test2;
+---------+
| max(id) |
+---------+
|     220 |
+---------+
1 row in set (0.00 sec)
mysql> insert ignore into test2 select * from test where id>250;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> insert ignore into test2 select * from test where id>300;
Query OK, 573140 rows affected (7.79 sec)
Records: 573140  Duplicates: 0  Warnings: 0

Так, мы пробуем пропустить 30 строк, но это оказывается недостаточным. Пропускаем 80 строк, и теперь все хорошо. Используя «двоичный поиск» мы можем понять, сколько строк нужно пропустить, для восстановления максимального количества поврежденных данных. Размер строки при этом может помочь. Так, у нас есть 280 байт на строку, поэтому мы получаем около 50 строк на страницу. И здесь 30 строк недостаточно — если таблица страниц повреждена, нужно пропустить минимум всю страницу. Если повреждена страница на более высоком уровне BTREE, нужно пропустить больше страниц, для использования этого метода восстановления.

В некоторых случаях, например, когда повреждена root page для кластеризованного индекса, этот метод не будет нормально работать. В этом случае стоит использовать Innodb Recovery Toolkit.

P.S. Принимаем заявки на статьи

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.

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:

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>

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

mysqlcheck <database name> <table name>

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:

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>

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>;

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>

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;

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

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.

mysqlcheck offers an efficient way to perform maintenance of tables and databases in MySQL. It can check tables, repair them if found corrupt, optimize them and perform combined operations of checking, repairing, and optimizing altogether. mysqlcheck command-line utility in MySQL is intended to be used when the mysqld server is running. It can execute CHECK TABLE, REPAIR TABLE, ANALYZE TABLE and OPTIMIZE TABLE statements in the most convenient way for the user.

In this blog, we explore the different ways of using mysqlcheck command line for checking, repairing, and optimizing tables and databases in MySQL. Start verifying and taking corrective actions for your tables and databases using this guide. 

Table of Contents

• What is MySQL?
  • Business Benefits of Using MySQL
• What is mysqlcheck Client?
  • mysqlcheck Command
• Using mysqlcheck for MySQL Database Tables
  • Part 1: Creating a MySQL Database Back-Up
  • Part 2: Running mysqlcheck Command
    • Running mysqlcheck to Analyze a Table in MySQL Database
    • Running mysqlcheck to Repair Tables in a MySQL Database
    • Running mysqlcheck to Optimize Tables in a MySQL Database
  • Part 3: Using mysqlcheck in Compound Commands
    • Running mysqlcheck to Optimize and Repair Tables in a MySQL Database
  • Part 4: mysqlcheck Command Modifications
• Conclusion

What is MySQL?

MySQL is the world’s most popular Open Source Relational Database Management System (RDBMS) used by all types of Small and Medium-Size Businesses (SMBs) and large enterprises. MySQL was initially developed, marketed, and supported by MySQL AB, a Swedish company but later got acquired by Sun Microsoft Systems (currently known as Oracle Corporation). Just like other typical databases, MySQL can store user/business/customer information in the form of rows and columns in a table. It provides referential integrity between rows and columns of various tables and processes user requests using SQL. 

MySQL holds a highly regarded name in businesses working with databases and Cloud-based Data Warehousing solutions. It’s scalable, reliable, and user-friendly. It also works cross-platform which means that users can run MySQL on Linux and Windows, and restore backups from the other platform. 

Business Benefits of Using MySQL

MySQL is popular all over the world and is used by leading tech giants owing to the following reasons:

Easy to Install and Deploy

Businesses can set up and run SQL queries on their data using MySQL in minutes. MySQL enables them to deliver new applications faster than proprietary databases.

High Speed

It’s a no-brainer that if you are working with large datasets, you wouldn’t want to spend an extensive amount of time working with datasets and tables. Unlike other databases, MySQL is comparatively faster and can query information from large datasets helping in business intelligence activities.

Read more on the top 10 MySQL ETL tools for your business here. 

Industry Standards

Whether you are a developer who is required for rapid development of software or a freelancer who seeks to work with databases, MySQL has been in use for over 20 years and you can be sure of using MySQL as a fully integrated transaction-safe, ACID-compliant database.

Reliability and High Availability

MySQL has a well-established reputation for reliability among its 5 million user base. In addition to reliability, MySQL Cluster gives 99.999 percent availability.

Multiple Platform Support

MySQL can be used on 20 platforms including Linux, Solaris, AIX, HP-UX, Windows, and Mac OS X. This provides organizations with complete flexibility in delivering a solution on the platform of their choice.

What is mysqlcheck Client?

mysqlcheck is a table maintenance program to check, repair, optimize, or analyze multiple tables from the command line. It works with InnoDB, MyISAM, and ARCHIVE tables and provides three levels of checking:

• Table-specific
• Database-specific
• All databases

mysqlcheck table client for MySQL consists of four SQL statements to perform the table maintenance action:

• CHECK TABLE, 
• REPAIR TABLE, 
• ANALYZE TABLE, and 
• OPTIMIZE TABLE

While performing table repair or analysis, it is important to bear in mind that table maintenance operations like mysqlcheck can become time-consuming processes especially when you have a large number of entries in your tables. If you use the –databases or –all-databases option to process all tables in one or more databases, a mysqlcheck call might take you a long time to complete. 

Note: For performing mysqlcheck operation, you must run the mysqld server. This means that you do not have to stop the server to perform table maintenance. mysqlcheck is different from myisamchk and aria_chk utilities that don’t require the server to be running.

mysqlcheck Command

There are three ways to use mysqlcheck command-line tool:

./client/mysqlcheck [OPTIONS] database [tables]
./client/mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
./client/mysqlcheck [OPTIONS] --all-databases

Using mysqlcheck for MySQL Database Tables

Important Note: Before we proceed with table check, repair, or analysis operations, it’s best advised to create a backup of your table(s), for some circumstances might lead to a potential data loss. 

As a result, we’ve split this tutorial into four sections for your convenience:

• Part 1: Creating a MySQL Database Back-Up
• Part 2: Running mysqlcheck Command
• Part 3: Using mysqlcheck in Compound Commands
• Part 4: mysqlcheck Command Modifications

Part 1: Creating a MySQL Database Back-Up

To create a backup of all your existing MySQL databases, follow these steps:

Step 1: Log in to your MySQL server using Secure Shell (SSH).

Step 2: Stop MySQL server using the appropriate command based on your Linux distribution:

For CentOS and Fedora, type:

service mysqld stop

For Debian and Ubuntu, type:

service mysql stop

Step 3: Input the following command to copy all of your databases to a directory name based on the current time.

cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)

Step 4: Restart MySQL server with the command appropriate for your Linux distribution:

For CentOS and Fedora, type:

service mysqld start

For Debian and Ubuntu, type:

service mysql start

Part 2: Running mysqlcheck Command

To use the mysqlcheck table command and check tables, follow these steps:

Step 1: As an administrator, change your directory to MySQL Data Directory.

cd /var/lib/mysql

Step 2: Now type in the mysqlcheck command to check for an existing table in a database. In our example, we are checking for a table called “email” under the database “customers”.

$ mysqlcheck -c customers email
customers.email                         OK

Notice our result. If a table passes the check, mysqlcheck displays OK for the table.

Not only this, mysqlcheck command can be used to CHECK (-c, -m, -C), REPAIR (-r), ANALYZE (-a), or OPTIMIZE (-o) table within your databases. The -c, -r, -a, and -o options work exclusively.

Some of the options (like -e (–extended) or -q (–quick)) can also be used at the same time but not all options are supported by all storage engines.

Running mysqlcheck to Analyze a Table in MySQL Database

As an example, the following command analyzes the “email” table within our “customers” database:

$ mysqlcheck -a customers email
customers.email                         OK

Running mysqlcheck to Repair Tables in a MySQL Database

The following command repairs all tables in the “customers” and “leads” databases:

$ mysqlcheck -r --databases customers leads

Note: If you see a note stating: The storage engine for the table doesn’t support repair it means that you are doing REPAIR on an InnoDB.

Running mysqlcheck to Optimize Tables in a MySQL Database

The following mysqlcheck database command optimizes all tables in all your MySQL databases.

$ mysqlcheck -o --all-databases

For user reference, this is a table showcasing the most used options for the mysqlcheck database command.

A more extensive list of other mysqlcheck database command options can be found on this page.

Likewise, these mysqlcheck table/database options can be combined together to perform a joint operation. Have a look at how this can be performed in the following section.

Part 3: Using mysqlcheck in Compound Commands

The mysqlcheck command-line utility can be extended for giving compound commands. For instance, let’s assume a case where there is a need to repair and optimize the “email” table from our previously stated “customer” database. 

mysqlcheck table command options like  -c (check), -r (repair), -a (analyze), and -o (optimize) options work exclusively and can be used concurrently in the same mysqlcheck command.

Running mysqlcheck to Optimize and Repair Tables in a MySQL Database

The following mysqlcheck command checks, optimizes, and auto-repairs all corrupted tables in the “customer” database. The auto repair option automatically fixes a checked table if found corrupted.

$ mysqlcheck --auto-repair -o customers

And the following mysqlcheck command optimizes and auto-repairs all tables in all your MySQL databases.

$ mysqlcheck --auto-repair -o --all-databases

Part 4: mysqlcheck Command Modifications

The command mysqlcheck can be altered, changing its default behavior from checking tables (–check) to repairing or optimizing tables. This can be done by changing the binary “check” and replacing it with “repair”, or “analyze”, or “optimize”.

These are the commands you get after substitution:

All these commands when invoked would perform the same operation as mysqlcheck -[option] when used. 

If you would like to learn about MySQL database export command-line utility, see our blog on MySQL Export Database Command Line: 3 Easy Methods. If you would like to know more about MySQL Analytics tools, visit our other informative blog here- MySQL Analytics Tools: A Quick Guide.

Conclusion

mysqlcheck table/database commands are ideal for automated optimizations of MySQL databases and tables. The more your database expands and the number of tables increases, the more likely it is that your tables/databases will encounter mistakes from time to time. In those circumstances, mysqlcheck can be a lifesaver.

Managing growing databases and the number of associated processes like business analytics isn’t an easy job. We find numerous business teams trapped and trying to figure out how to examine their data in a timely and efficient manner. One way of doing this is migrating your data to a Cloud-based Data Warehouse. But what if we told you that you can manage all your source connections from a single place? 

Our tool Hevo with its strong integration with 100+ Sources & BI tools such as MySQL, PostgreSQL, MS SQL Server, MySQL compatible Aurora, Google Cloud SQL for MySQL allows you to not only export data from sources & load data in the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. 

Visit our Website to Explore Hevo

Hevo lets you migrate your data from your favorite applications to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also check Hevo’s pricing plans on this page. 

Share your most frequently used MySQL commands, as well as those for your tables and databases. Suggest to us any more MySQL topics you’d like us to cover in the comments below. We’d appreciate hearing from you.