Mysql Script Tools

Mysql Script Tools

There are a number of operations in mysql that could benefit from being optimized in a script that performs these actions on all tables. A List of maintenance commands can be found. As it is, currently I have created a number of scripts for performing Analysis, Backup, Check, Checksum, Optimize, Repair, Restore. The tools are designed to operate on all table names accessible from the INFORMATION_SCHEMA.TABLES with the exception of the information_schema tables.

Mysql Analyze Tables Script

#!/usr/bin/bash
read -p "Username: " unamestty -echoread -p "Password: " passw;
echo
stty
echo
DATE=`date "+%y%m%d_%H%M"`
echo "SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != &squo;information_schema&squo;" |/usr/local/mysql/bin/mysql --skip-column-names -u$uname --password=$passw -B |sed -e &squo;s/([^        ]*)[   ]*([^$]*)/USE `1`;ANALYZE TABLE `1`.`2`;/&squo; > tmp
exec 3<&#038;0exec 0<"tmp"
while read line
do
echo $line|/usr/local/mysql/bin/mysql -u$uname --password=$passw -B --skip-column-names >> analysis_res_$DATE        
if [ "$?" != "0" ]; then
echo $line >> FAILED_ana_$DATE.log
fi
done
exec 0<&#038;3rm tmp
unset $uname ;
unset $passw ;

Mysql Backup Tables Script

#!/usr/bin/bash
read -p "Username: " unamestty -echoread -p "Password: " passw;
echo
stty 
echo
CWD=`pwd`
DATE=`date "+%y%m%d_%H%M"`
echo "SELECT TABLE_SCHEMA&#44;TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != &squo;information_schema&squo;" |/usr/local/mysql/bin/mysql --skip-column-names -u$uname --password=$passw -B |sed -e &squo;s/([^  ]*)[   ]*([^$]*)/USE `1`;BACKUP TABLE `1`.`2` TO &squo;"$CWD"&squo;/1_2_&squo;"$DATE"&squo;;/&squo; > tmp
exec 3<&#038;0exec 0<"tmp"
while read line
do
echo $line|/usr/local/mysql/bin/mysql -u$uname --password=$passw -B --skip-column-names >> backup_res_$DATE
if [ "$?" != "0" ]; then
echo $line >> FAILED_bkup_$DATE.log
fi
done
exec 0<&#038;3

rm tmp
unset $uname ;
unset $passw ;

Mysql Check Tables Script

#!/usr/bin/bash

read -p "Username: " uname
stty -echo
read -p "Password: " passw; 
echo
stty 
echo

DATE=`date "+%y%m%d_%H%M"`echo "SELECT TABLE_SCHEMA&#44;TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != &squo;information_schema&squo;" |/usr/local/mysql/bin/mysql --skip-column-names -u$uname --password=$passw -B |sed -e &squo;s/([^  ]*)[   ]*([^$]*)/USE `1`;CHECK TABLE `1`.`2`;/&squo; > tmp

exec 3<&#038;0
exec 0<"tmp"
while read line
do
echo $line|/usr/local/mysql/bin/mysql -u$uname --password=$passw -B --skip-column-names >> check_res_$DATE
if [ "$?" != "0" ]; then
echo $line >> FAILED_chk_$DATE.log
fi
done
exec 0<&#038;3
rm tmp
unset $uname ;
unset $passwd ;

Mysql Checksum Table Script

#!/usr/bin/bash

read -p "Username: " unamestty -echoread -p "Password: " passw; echostty echo

DATE=`date "+%y%m%d_%H%M"`echo "SELECT TABLE_SCHEMA&#44;TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != &squo;information_schema&squo;" |/usr/local/mysql/bin/mysql --skip-column-names -u$uname --password=$passw -B |sed -e &squo;s/([^  ]*)[   ]*([^$]*)/USE `1`;CHECKSUM TABLE `1`.`2`;/&squo; > tmp

exec 3<&#038;0
exec 0<"tmp"
while read line
do
echo $line|/usr/local/mysql/bin/mysql -u$uname --password=$passw -B --skip-column-names >> checksum_res_$DATE
if [ "$?" != "0" ]; then
echo $line >> FAILED_chksm_$DATE.log
fi

done
exec 0<&#038;3
rm tmp
unset $uname ;
unset $passw ;

Mysql Optimize Tables Script

#!/usr/bin/bash

read -p "Username: " uname
stty -echo
read -p "Password: " passw;
echo
stty 
echo

DATE=`date "+%y%m%d_%H%M"`echo "SELECT TABLE_SCHEMA&#44;TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != &squo;information_schema&squo;" |/usr/local/mysql/bin/mysql --skip-column-names -u$uname --password=$passw -B |sed -e &squo;s/([^  ]*)[   ]*([^$]*)/USE `1`;OPTIMIZE TABLE `1`.`2`;/&squo; > tmp

exec 3<&#038;0exec 0<"tmp"
while read line
do
echo $line|/usr/local/mysql/bin/mysql -u$uname --password=$passw -B --skip-column-names >> optimize_res_$DATE
if [ "$?" != "0" ]; then
echo $line >> FAILED_opt_$DATE.log
fi
done
exec 0<&#038;3
rm tmp
unset $uname;
unset $passw;

Mysql Repair Tables Script

#!/usr/bin/bash
read -p "Username: " uname
stty -echo
read -p "Password: " passw;
echo
stty 
echo
DATE=`date "+%y%m%d_%H%M"`echo "SELECT TABLE_SCHEMA&#44;TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != &squo;information_schema&squo;" |/usr/local/mysql/bin/mysql --skip-column-names -u$uname --password=$passw -B |sed -e &squo;s/([^  ]*)[   ]*([^$]*)/USE `1`;REPAIR TABLE `1`.`2`;/&squo; > tmp
exec 3<&#038;0exec 0<"tmp"
while read line
do
echo $line|/usr/local/mysql/bin/mysql -u$uname --password=$passw -B --skip-column-names >> repair_res_$DATE
if [ "$?" != "0" ]; then
echo $line >> FAILED_rpr_$DATE.log
fi
done
exec 0<&#038;3
rm tmp
unset $uname ;
unset $passw ;

Mysql Restore Tables Script

#!/usr/bin/bashread -p "Username: " unamestty -echoread -p "Password: " passw; echostty echoif [ -z "$1" ]; then        echo "Need to specify restore file" ;        exit 1 ;fiif [ -f "$1" ]; then        echo "First Parameter is not a file" ;        exit 1 ;fiCWD=`pwd`DATE=`date "+%y%m%d_%H%M"`echo "SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != &squo;information_schema&squo;" |/usr/local/mysql/bin/mysql --skip-column-names -u$uname --password=$passw -B |sed -e &squo;s/([^  ]*)[   ]*([^$]*)/USE `1`;BACKUP TABLE `1`.`2` FROM &squo;"$CWD"&squo;/&squo;"$1"&squo;;/&squo; > tmp

exec 3<&0exec 0<"tmp"while read linedo        echo $line|/usr/local/mysql/bin/mysql -u$uname --password=$passw -B --skip-column-names >> backup_res_$DATE        if [ "$?" != "0" ]; then                echo $line >> FAILED_bkup_$DATE.log        fidoneexec 0<&3rm tmpunset $uname ;unset $passw ;

Discussion and Download Link

You can see that there is a distinct pattern in these group of script files that can be re-used in order to make the different scripts work. The only exception is where you have a backup or restore that requires an additional parameter to make work. These scripts do not have any filtering to them or any way to specify a database to backup individually or a group of tables individually so be careful.

Download Mysql Script Tools V0.0.0.1

ttessier

About ttessier

Professional Developer and Operator of SwhistleSoft
This entry was posted in Bash Scripting. Bookmark the permalink.

6 Responses to Mysql Script Tools

  1. This Blog was most helpful, your ideas are straight to the point, and the colors are cool too.
    My blog is about Healthy recipes.

  2. I gotta favorite this internet site it seems invaluable invaluable

  3. Great beat ! I wish to apprentice even as you amend your web site, how can i subscribe for a weblog site? The account aided me a acceptable deal. I have been tiny bit familiar of this your broadcast provided bright clear idea

  4. Wow! This website appears to be just exactly like my own old one! It is essentially on a totally different subject matter nevertheless it really has essentially an identical structure and design. Wonderful selection of colors!

  5. Cheers, I’ve been looking for information about this subject matter forever and this particular site is the best I’ve identified so far.

  6. Whats up are using WordPress for your site platform? I’m new to the blog world but I’m trying to get started and create my own. Do you require any html coding expertise to make your own blog? Any help would be really appreciated!

Leave a Reply to Shalanda Trent Cancel reply

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