Generate CHECK TABLE statements for all MySQL database tables on a server

DD=`cat /etc/my.cnf | sed "s/#.*//g;" | grep datadir | tr '=' ' ' | gawk '{print $2;}'` && ( cd $DD ; find . -mindepth 2 | grep -v db\.opt | sed 's/\.\///g; s/\....$//g; s/\//./;' | sort | uniq | tr '/' '.' | gawk '{print "CHECK TABLE","`"$1"`",";";}' )
This command will generate "CHECK TABLE `db_name.table_name` ;" statements for all tables present in databases on a MySQL server, which can be piped into the mysql command. (Can also be altered to perform OPTIMIZE and REPAIR functions.) Tested on MySQL 4.x and 5.x systems in a Linux environment under bash.
Sample Output
CHECK TABLE `mysql.columns_priv` ;
CHECK TABLE `mysql.db` ;
CHECK TABLE `mysql.func` ;
CHECK TABLE `mysql.help_category` ;
CHECK TABLE `mysql.help_keyword` ;
CHECK TABLE `mysql.help_relation` ;
CHECK TABLE `mysql.help_topic` ;
CHECK TABLE `mysql.host` ;
CHECK TABLE `mysql.tables_priv` ;
CHECK TABLE `mysql.time_zone` ;
CHECK TABLE `mysql.time_zone_leap_second` ;
CHECK TABLE `mysql.time_zone_name` ;
CHECK TABLE `mysql.time_zone_transition` ;
CHECK TABLE `mysql.time_zone_transition_type` ;
CHECK TABLE `mysql.user` ;
CHECK TABLE `mysql.user_info` ;

-1
By: atcroft
2009-07-25 03:42:31

What Others Think

What the ... Please stop tormenting that poor animal (Yes, I mean the cat: http://sial.org/howto/shell/useless-cat/). sed "s/#.*//g" /etc/my.cnf | ... Does the job. Also why grep, awk can do that: sed "s/#.*//g" /etc/my.cnf | gawk -F"=" '/datadir/{print $2}' Btw., got rid of the tr, too.. Hell, lets do it all in sed: sed -n "s/#.*//g;/datadir/s/datadir=//p" /etc/my.cnf And find has regular expression support: find . -mindepth 2 -name "*db.opt*" And sort -u is the same as 'sort | uniq' You really seem to like your semicolons. Awk and sed only need them to separate multiple statements.
0x89 · 592 weeks and 4 days ago
Psychology assignment writing services are essential and they have become very popular for those seeking psychology essay writing help since most of them seek Psychology Research Paper Services.
florahwilliams · 14 weeks and 3 days ago
Thanks for the team for the best commands, they are working for me. http://aostv.xyz/
nayak · 13 weeks and 5 days ago
This command is very usefull thanks for this. https://blackfridayvpn.net/safervpn-black-friday/
samajkapoor · 8 weeks and 4 days ago
Thanks for this post. safervpn
samajkapoor · 8 weeks and 4 days ago
I can say from myself that there were written about the task on psychology so now there are a lot of these tasks for real specialists and you need to be able and know where to deal with this case. For example, without much difficulty and problems now you can even get advice online. Such a psychologist helped me. So be sure to take a look and get acquainted with calmerry therapy , I am very glad that I was able to find just great material on this case. Good luck, I hope I was able to help.
Manicopus · 2 weeks ago

What do you think?

Any thoughts on this command? Does it work on your machine? Can you do the same thing with only 14 characters?

You must be signed in to comment.

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again. That way others can gain from your CLI wisdom and you from theirs too. All commands can be commented on, discussed and voted up or down.

Share Your Commands



Stay in the loop…

Follow the Tweets.

Every new command is wrapped in a tweet and posted to Twitter. Following the stream is a great way of staying abreast of the latest commands. For the more discerning, there are Twitter accounts for commands that get a minimum of 3 and 10 votes - that way only the great commands get tweeted.

» http://twitter.com/commandlinefu
» http://twitter.com/commandlinefu3
» http://twitter.com/commandlinefu10

Subscribe to the feeds.

Use your favourite RSS aggregator to stay in touch with the latest commands. There are feeds mirroring the 3 Twitter streams as well as for virtually every other subset (users, tags, functions,…):

Subscribe to the feed for: