Shell Script to Optimize all tables for a database

Home » Shell Script to Optimize all tables for a database

This shell script finds all the tables for a database and run a command against it.

 

See File optimize-tables

The file’s contents are below as well:

01<span class="com">#!/bin/sh</span><span class="pln">
02 
03</span><span class="com"># this shell script finds all the tables for a database and run a command against it</span><span class="pln">
04</span><span class="com"># @usage "mysql_tables.sh --optimize MyDatabaseABC"</span><span class="pln">
05</span><span class="com"># @date 6/14/2006</span><span class="pln">
06</span><span class="com"># @version 1.1 - 1/28/2007 - add repair</span><span class="pln">
07</span><span class="com"># @version 1.0 - 6/14/2006 - first release</span><span class="pln">
08</span><span class="com"># @author Son Nguyen</span><span class="pln">
09 
10DBNAME</span><span class="pun">=</span><span class="pln">$2
11 
12printUsage</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
13  echo </span><span class="str">"Usage: $0"</span><span class="pln">
14  echo </span><span class="str">" --optimize <dbname>"</span><span class="pln">
15  echo </span><span class="str">" --repair <dbname>"</span><span class="pln">
16  </span><span class="kwd">return</span><span class="pln">
17</span><span class="pun">}</span><span class="pln">
18 
19 
20doAllTables</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
21  </span><span class="com"># get the table names</span><span class="pln">
22  TABLENAMES</span><span class="pun">=</span><span class="str">`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`</span><span class="pln">
23 
24  </span><span class="com"># loop through the tables and optimize them</span><span class="pln">
25  </span><span class="kwd">for</span><span class="pln"> TABLENAME </span><span class="kwd">in</span><span class="pln"> $TABLENAMES
26  </span><span class="kwd">do</span><span class="pln">
27    mysql </span><span class="pun">-</span><span class="pln">D $DBNAME </span><span class="pun">-</span><span class="pln">e </span><span class="str">"$DBCMD TABLE $TABLENAME;"</span><span class="pln">
28  </span><span class="kwd">done</span><span class="pln">
29</span><span class="pun">}</span><span class="pln">
30 
31</span><span class="kwd">if</span><span class="pln"> </span><span class="pun">[</span><span class="pln"> $</span><span class="com"># -eq 0 ] ; then</span><span class="pln">
32  printUsage
33  </span><span class="kwd">exit</span><span class="pln"> </span><span class="lit">1</span><span class="pln">
34</span><span class="kwd">fi</span><span class="pln">
35 
36</span><span class="kwd">case</span><span class="pln"> $1 </span><span class="kwd">in</span><span class="pln">
37  </span><span class="pun">--</span><span class="pln">optimize</span><span class="pun">)</span><span class="pln"> DBCMD</span><span class="pun">=</span><span class="pln">OPTIMIZE</span><span class="pun">;</span><span class="pln"> doAllTables</span><span class="pun">;;</span><span class="pln">
38  </span><span class="pun">--</span><span class="pln">repair</span><span class="pun">)</span><span class="pln"> DBCMD</span><span class="pun">=</span><span class="pln">REPAIR</span><span class="pun">;</span><span class="pln"> doAllTables</span><span class="pun">;;</span><span class="pln">
39  </span><span class="pun">--</span><span class="pln">help</span><span class="pun">)</span><span class="pln"> printUsage</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">exit</span><span class="pln"> </span><span class="lit">1</span><span class="pun">;;</span><span class="pln">
40  </span><span class="pun">*)</span><span class="pln"> printUsage</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">exit</span><span class="pln"> </span><span class="lit">1</span><span class="pun">;;</span><span class="pln">
41</span><span class="kwd">esac</span><span class="pln">
42</span>

Leave a Comment

Your email address will not be published. Required fields are marked *