{"id":107,"date":"2006-06-14T18:54:00","date_gmt":"2006-06-14T18:54:00","guid":{"rendered":"http:\/\/blog.trungson.com\/?p=107"},"modified":"2006-06-14T18:54:00","modified_gmt":"2006-06-14T18:54:00","slug":"shell-script-mysql-optimize-all-tables","status":"publish","type":"post","link":"http:\/\/blog.trungson.com\/?p=107","title":{"rendered":"Shell-script: MySQL optimize all tables"},"content":{"rendered":"<p>Using myisamchk sometime requires shutting down MySQL server and that&#8217;s not very easy in certain situation. This shell script accepts a database name from input and gets all table names and runs a SQL query against these tables. Currently, it only optimizes all the tables and could be expanded to do a lot more. <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/optimize-table.html\">OPTIMIZE<\/a > does a lot more, an all-in-one solution (for MyISAM tables: repair, sort index, update stats). Enjoy! <\/p>\n<p><textarea name=\"code\" class=\"xml\"><br \/>\n#!\/bin\/sh<\/p>\n<p># this shell script finds all the tables for a database and run a command against it<br \/>\n# @date 6\/14\/2006<br \/>\n# @author Son Nguyen<\/p>\n<p>DBNAME=$2<\/p>\n<p>printUsage() {<br \/>\n  echo &#8220;Usage: $0&#8243;<br \/>\n  echo &#8221; &#8211;optimize <tablename>&#8221;<br \/>\n  return<br \/>\n}<\/p>\n<p>doAllTables() {<br \/>\n  # get the table names<br \/>\n  TABLENAMES=`mysql -D $DBNAME -e &#8220;SHOW TABLES\\G;&#8221;|grep &#8216;Tables_in_&#8217;|sed -n &#8216;s\/.*Tables_in_.*: \\([_0-9A-Za-z]*\\).*\/\\1\/p&#8217;`<\/p>\n<p>  # loop through the tables and optimize them<br \/>\n  for TABLENAME in $TABLENAMES<br \/>\n  do<br \/>\n    mysql -D $DBNAME -e &#8220;OPTIMIZE TABLE $TABLENAME;&#8221;<br \/>\n  done<br \/>\n}<\/p>\n<p>if [ $# -eq 0 ] ; then<br \/>\n  printUsage<br \/>\n  exit 1<br \/>\nfi<\/p>\n<p>case $1 in<br \/>\n  &#8211;optimize) doAllTables;;<br \/>\n  &#8211;help) printUsage; exit 1;;<br \/>\n  *) printUsage; exit 1;;<br \/>\nesac<\/p>\n<p><\/textarea><\/p>\n<p>Sample Usage:<br \/>\n<textarea name=\"code\" class=\"xml\"><br \/>\nroot@s# .\/mysql_optimize.sh &#8211;optimize abc<br \/>\n+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;-+<br \/>\n| Table            | Op       | Msg_type | Msg_text |<br \/>\n+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;-+<br \/>\n| abc.def          | optimize | status   | OK       |<br \/>\n+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;-+<br \/>\n<\/textarea><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Using myisamchk sometime requires shutting down MySQL server and that&#8217;s not very easy in certain situation. This shell script accepts a database name from input and gets all table names and runs a SQL query against these tables. Currently, it only optimizes all the tables and could be expanded to do a lot more. OPTIMIZE [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/posts\/107"}],"collection":[{"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=107"}],"version-history":[{"count":0,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/posts\/107\/revisions"}],"wp:attachment":[{"href":"http:\/\/blog.trungson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=107"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}