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?
Image Source: Logo Wine
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?
Image Source: Globo.Tech
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
A fully-managed No-code Data Pipeline platform like Hevo Data, helps you integrate data from PostgreSQL and/or MySQL and load data from 100+ different sources to a destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources provides users with the flexibility to bring in data of different kinds, in a smooth fashion without having to code a single line.
Get Started with Hevo for Free
A few Salient Features of Hevo are as follows:
Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
Scalable Infrastructure: Hevo has in-built integrations for 100+ sources that can help you scale your data infrastructure as required.
24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
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
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.
| Option | Description |
| -c, –check | Check the tables for errors |
| -a, –analyze | Analyze the tables |
| -o, –optimize | Optimize the tables |
| -r, –repair | Perform a repair that can fix almost anything except unique keys that are not unique |
| –auto-repair | If a checked table is corrupted, automatically fix it |
| -A, –all-databases | Check all tables in all databases. This is the same as –databases with all databases selected |
| -B, –databases | Process all tables in the named databases. With this option, all name arguments are regarded as database names, not as table names |
| –tables | Overrides the –databases or -B option such that all name arguments following the option are regarded as table names |
| -g, –check-upgrade | Check tables for version-dependent changes. It may be used with –auto-repair to correct tables requiring version-dependent updates |
| –compress | Compress all information sent between client and server |
| –debug-info | Print debugging information, memory, and CPU statistics when the program exits |
| -e, -–extended | Check and repair tables |
| -q, –quick | The fastest method of checking |
| -?, –help | Display a help message and exit |
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:
| Command | Meaning |
| mysqlrepair | The default option is –repair |
| mysqlanalyze | The default option is –analyze |
| mysqloptimize | The default option is –optimize |
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.
With a background in marketing research and campaign management at Hevo Data and myHQ Workspaces, Divyansh specializes in data analysis for optimizing marketing strategies. He has experience writing articles on diverse topics such as data integration and infrastructure by collaborating with thought leaders in the industry.