- Checking Version Compatibility
- Checking Data Consistency
- Upgrades
- General Table Errors
> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City |
| Country |
| CountryLanguage |
+-----------------+
> check table City\G
*************************** 1. row ***************************
Table: world.City
Op: check
Msg_type: status
Msg_text: OK
This is a good task to stay updated on so you are aware of possible errors. One possible problem is that this tool really focuses on MyISAM and not InnoDB. If you use it for InnoDB, The "Check table" command really only applies when you add the QUICK option (or no options). The FAST, CHANGED, MEDIUM and EXTENDED options are all ignored for InnoDB. Now, if you are asking yourself, what about InnoDB? Why would MySQL ignore data consistency in the InnoDB engine? Take a deep breath and relax, InnoDB is ACID complaint, ACID is "an acronym standing for atomicity, consistency, isolation, and durability." So do not disregard checking InnoDB tables because it can still provide you some insight or confirmation on your tables. Keep in mind that if an InnoDB table was to be corrupted the server will shut down to protect the data. You just get more bang for your buck with MyISAM tables and this tool.
Hopefully you get a response of "OK" or "Table is already up to date" otherwise you need to run a repair table to fix the table.
So what are the options available to us so you can do this often and easily.
The documentation link below will also provide you with several community driven automatic options. You can script it the process and easily show tables then apply check tables to all of your results. It just appears easier to me though to use the tools provided for you.
$ mysqlcheck -u root -p --databases world --fast
Enter password:
world.City OK
world.Country OK
world.CountryLanguage OK
$mysqlcheck -u root -p --databases world --fast --check-only-changed
Enter password:
world.City OK
world.Country OK
world.CountryLanguage OK
Now this is simple and direct but it also lends itself to another question, What about the password?
Should you create a user with no password that is allowed to check tables just so you do not have to put a password into your script or cron job? You want to prevent having the password sitting around in .mysql_history files as well. So again take advantage of the tools available for you. MySQL 5.6 introduced the MySQL Configuration Utility. I have an example of how to set it up in a previous blog post:
http://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html
mysqlcheck --login-path=local --databases world --fast --check-only-changed
world.City OK
world.Country OK
world.CountryLanguage OK
$ mysqlcheck --help will provide a full list of options available to you.
Now, you can check all of your tables, keep your passwords out of the crontab file and/or scripts.
Documentation:
