{"id":3116,"date":"2010-06-03T05:35:37","date_gmt":"2010-06-03T09:35:37","guid":{"rendered":"http:\/\/g33kinfo.com\/info\/?p=3116"},"modified":"2010-06-03T05:35:37","modified_gmt":"2010-06-03T09:35:37","slug":"optimize-all-mysql-tables-on-a-server","status":"publish","type":"post","link":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/","title":{"rendered":"Optimize All MySQL Tables on a Server"},"content":{"rendered":"

From ben90.com<\/a><\/p>\n

MySQL<\/p>\n

I would like to point out here quickly that I assume that you have root access in this article. One of my regular tasks is to optimize MySQL installations (primarily on shared cPanel servers). I use a number of utilities in order to identify areas in which I could optimize the MySQL installations, the main one being MySQLTuner. MySQLTuner is a nice little script which will give you quite a bit of information on your MySQL install and the databases running on it. It\u2019s probably worth saying here that tuning MySQL in a shared hosting environment is an on-going task and your config will need tweaking regularly!<\/p>\n

I assume that it is fairly common knowledge that it\u2019s worth optimizing your fragmented database tables. I\u2019m not going to go into the why\u2019s as that surpasses the scope of this post. The fact is that most users could do it quite easily and regularly, but they don\u2019t as it\u2019s just another thing to remember, I\u2019ll admit that even I\u2019m included in that statement on my personal sites. Just a quick note: you might find it interesting to read the OPTIMIZE TABLE entry in the MySQL Reference Manual if you don\u2019t understand what it does or just need to refresh <\/p>\n

So, more to the point, about 6 months or so ago, I decided that I would like to optimize all of the tables on one of the servers that I manage. With almost 10,000 tables, it wouldn\u2019t have be realistic to do it manually, so I got\/built the following script ( I cant remember if I wrote or found it ) and I have been using it to optimize all database tables on a few of the servers that I manage (on a weekly basis). The script is below. Essentially, it will just go through all of your MySQL tables and optimize them.
\n
\n#!\/bin\/bash<\/p>\n

########################################
\n# SCRIPT ACQUIRED FROM BEN90.COM #
\n########################################<\/p>\n

echo \"\"
\nif test $# -eq 2
\nthen # user and pass provided
\n MYSQL_LOGIN='-u $1 --password=$2'
\nelif test $# -eq 1
\nthen # user and no pass provided
\n MYSQL_LOGIN='-u $1'
\nelse # no credentials provided, use default
\n echo \"no username or pass provided. Using default options\";
\n echo \"\"
\n MYSQL_LOGIN='-u root'
\nfi<\/p>\n

for db in $(echo \"SHOW DATABASES;\" | \\
\n mysql $MYSQL_LOGIN | \\
\n grep -v -e \"Database\" -e \"information_schema\")
\ndo
\n TABLES=$(echo \"USE $db; SHOW TABLES;\" | \\
\n mysql $MYSQL_LOGIN | \\
\n grep -v Tables_in_)
\n echo \"Switching to database $db\"
\n for table in $TABLES
\n do
\n echo -n \" * Optimizing table $table ... \"
\n echo \"USE $db; OPTIMIZE TABLE $table\" | \\
\n mysql $MYSQL_LOGIN >\/dev\/null
\n echo \"done.\"
\n done
\ndone<\/p>\n

echo \"\"
\n<\/code>
\nThe first thing that you will need to do is save the script on your server. I have the script in with a few of my other management scripts just in \/root\/scripts\/ and have named it optimizetables.sh although you could name it anything you want and put it anywhere (be sensible though!).<\/p>\n

Once you have saved the script on your server, you will need to give it execute permissions, so cd to where you put the script and run
\n
\nchmod +x optimizetables.sh<\/code><\/p>\n

Now, it\u2019s time to run it. How you run it will really depend on your requirements. The script takes between 0 and 2 paramaters, you can use any of the following parameter sets. If you need to provide a username and password to access your MySQL install, you need this one
\n
\n.\/optimizetables.sh username password<\/code>
\nIf you just need a username to access it, use this one
\n.\/optimizetables.sh username<\/code>
\nThe script will default to just using the user root in this one, and this one should work in most cases.
\n.\/optimizetables.sh<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

From ben90.com MySQL I would like to point out here quickly that I assume that you have root access in this article. One of my regular tasks is to optimize MySQL installations (primarily on shared cPanel servers). I use a number of utilities in order to identify areas in which I could optimize the MySQL… <\/p>\n

Read More<\/i><\/a><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-3116","post","type-post","status-publish","format-standard","hentry","category-info"],"yoast_head":"\nOptimize All MySQL Tables on a Server - Linux Shtuff<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Optimize All MySQL Tables on a Server - Linux Shtuff\" \/>\n<meta property=\"og:description\" content=\"From ben90.com MySQL I would like to point out here quickly that I assume that you have root access in this article. One of my regular tasks is to optimize MySQL installations (primarily on shared cPanel servers). I use a number of utilities in order to identify areas in which I could optimize the MySQL... Read More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Linux Shtuff\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/fb.me\/g33kinf0\" \/>\n<meta property=\"article:author\" content=\"https:\/\/fb.me\/g33kinf0\" \/>\n<meta property=\"article:published_time\" content=\"2010-06-03T09:35:37+00:00\" \/>\n<meta name=\"author\" content=\"g33kadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/drsinger1111\" \/>\n<meta name=\"twitter:site\" content=\"@drsinger1111\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/\"},\"author\":{\"name\":\"g33kadmin\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"headline\":\"Optimize All MySQL Tables on a Server\",\"datePublished\":\"2010-06-03T09:35:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/\"},\"wordCount\":492,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"articleSection\":[\"General Info\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/\",\"name\":\"Optimize All MySQL Tables on a Server - Linux Shtuff\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#website\"},\"datePublished\":\"2010-06-03T09:35:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/optimize-all-mysql-tables-on-a-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Optimize All MySQL Tables on a Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#website\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/\",\"name\":\"Linux Shtuff\",\"description\":\"Because I have CRS Syndrome...\",\"publisher\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\",\"name\":\"g33kadmin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"contentUrl\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"width\":512,\"height\":512,\"caption\":\"g33kadmin\"},\"logo\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\"},\"description\":\"I am a g33k, Linux blogger, developer, student and Tech Writer for Liquidweb.com\\\/kb. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....\",\"sameAs\":[\"https:\\\/\\\/thelinuxreport.com\",\"https:\\\/\\\/fb.me\\\/g33kinf0\",\"https:\\\/\\\/x.com\\\/https:\\\/\\\/twitter.com\\\/drsinger1111\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Optimize All MySQL Tables on a Server - Linux Shtuff","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/","og_locale":"en_US","og_type":"article","og_title":"Optimize All MySQL Tables on a Server - Linux Shtuff","og_description":"From ben90.com MySQL I would like to point out here quickly that I assume that you have root access in this article. One of my regular tasks is to optimize MySQL installations (primarily on shared cPanel servers). I use a number of utilities in order to identify areas in which I could optimize the MySQL... Read More","og_url":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/","og_site_name":"Linux Shtuff","article_publisher":"https:\/\/fb.me\/g33kinf0","article_author":"https:\/\/fb.me\/g33kinf0","article_published_time":"2010-06-03T09:35:37+00:00","author":"g33kadmin","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/drsinger1111","twitter_site":"@drsinger1111","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/#article","isPartOf":{"@id":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/"},"author":{"name":"g33kadmin","@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"headline":"Optimize All MySQL Tables on a Server","datePublished":"2010-06-03T09:35:37+00:00","mainEntityOfPage":{"@id":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/"},"wordCount":492,"commentCount":0,"publisher":{"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"articleSection":["General Info"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/","url":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/","name":"Optimize All MySQL Tables on a Server - Linux Shtuff","isPartOf":{"@id":"https:\/\/g33kinfo.com\/info\/#website"},"datePublished":"2010-06-03T09:35:37+00:00","breadcrumb":{"@id":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/g33kinfo.com\/info\/optimize-all-mysql-tables-on-a-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/g33kinfo.com\/info\/"},{"@type":"ListItem","position":2,"name":"Optimize All MySQL Tables on a Server"}]},{"@type":"WebSite","@id":"https:\/\/g33kinfo.com\/info\/#website","url":"https:\/\/g33kinfo.com\/info\/","name":"Linux Shtuff","description":"Because I have CRS Syndrome...","publisher":{"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/g33kinfo.com\/info\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547","name":"g33kadmin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","url":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","contentUrl":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","width":512,"height":512,"caption":"g33kadmin"},"logo":{"@id":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif"},"description":"I am a g33k, Linux blogger, developer, student and Tech Writer for Liquidweb.com\/kb. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....","sameAs":["https:\/\/thelinuxreport.com","https:\/\/fb.me\/g33kinf0","https:\/\/x.com\/https:\/\/twitter.com\/drsinger1111"]}]}},"_links":{"self":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts\/3116","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/comments?post=3116"}],"version-history":[{"count":0,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts\/3116\/revisions"}],"wp:attachment":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/media?parent=3116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/categories?post=3116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/tags?post=3116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}