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<&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<&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,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<&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<&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,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<&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<&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,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<&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<&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,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<&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<&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,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<&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<&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.
This Blog was most helpful, your ideas are straight to the point, and the colors are cool too.
My blog is about Healthy recipes.
I gotta favorite this internet site it seems invaluable invaluable
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
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!
Cheers, I’ve been looking for information about this subject matter forever and this particular site is the best I’ve identified so far.
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!