scottlinux.com | Linux Blog

mysqlcheck is a simple command line app that can check mysql databases for corruption, make repairs, and also optimize your databases.

Here are three quick commands that are useful to check, optimize, and also repair your mysql databases!

1. Check Databases

$ mysqlcheck -cA -u root -p

(-c for check database, -A for all databases)

$ mysqlcheck -cA -u root -p
Enter password: 
company.contacts                                   OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log
Error    : You can't use locks with log tables.
status   : OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      OK
mysql.slow_log
Error    : You can't use locks with log tables.
status   : 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
mysql.user                                         OK
phpmyadmin.pma_bookmark                            OK
phpmyadmin.pma_column_info                         OK
phpmyadmin.pma_designer_coords                     OK
phpmyadmin.pma_history                             OK
phpmyadmin.pma_pdf_pages                           OK
phpmyadmin.pma_relation                            OK
phpmyadmin.pma_table_coords                        OK
phpmyadmin.pma_table_info                          OK
phpmyadmin.pma_tracking                            OK
phpmyadmin.pma_userconfig                          OK

2. Optimize Databases

$ mysqlcheck -oA -u root -p

(-o for optimize)

$ mysqlcheck -oA -u root -p
Enter password: 
company.contacts                                   Table is already up to date
mysql.columns_priv                                 Table is already up to date
mysql.db                                           OK
mysql.event                                        Table is already up to date
mysql.func                                         Table is already up to date
mysql.general_log
note     : The storage engine for the table doesn't support optimize
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         Table is already up to date
mysql.ndb_binlog_index                             Table is already up to date
mysql.plugin                                       Table is already up to date
mysql.proc                                         Table is already up to date
mysql.procs_priv                                   OK
mysql.servers                                      Table is already up to date
mysql.slow_log
note     : The storage engine for the table doesn't support optimize
mysql.tables_priv                                  OK
mysql.time_zone                                    Table is already up to date
mysql.time_zone_leap_second                        Table is already up to date
mysql.time_zone_name                               Table is already up to date
mysql.time_zone_transition                         Table is already up to date
mysql.time_zone_transition_type                    Table is already up to date
mysql.user                                         OK
phpmyadmin.pma_bookmark                            Table is already up to date
phpmyadmin.pma_column_info                         Table is already up to date
phpmyadmin.pma_designer_coords                     Table is already up to date
phpmyadmin.pma_history                             Table is already up to date
phpmyadmin.pma_pdf_pages                           Table is already up to date
phpmyadmin.pma_relation                            Table is already up to date
phpmyadmin.pma_table_coords                        Table is already up to date
phpmyadmin.pma_table_info                          Table is already up to date
phpmyadmin.pma_tracking                            Table is already up to date
phpmyadmin.pma_userconfig                          OK

3. Repair Corrupted Databases

$ mysqlcheck -rA -u root -p

(-r for repair)

$ mysqlcheck -rA -u root -p
Enter password: 
company.contacts                                   OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      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
mysql.user                                         OK
phpmyadmin.pma_bookmark                            OK
phpmyadmin.pma_column_info                         OK
phpmyadmin.pma_designer_coords                     OK
phpmyadmin.pma_history                             OK
phpmyadmin.pma_pdf_pages                           OK
phpmyadmin.pma_relation                            OK
phpmyadmin.pma_table_coords                        OK
phpmyadmin.pma_table_info                          OK
phpmyadmin.pma_tracking                            OK
phpmyadmin.pma_userconfig                          OK

Cool!