#!/bin/bash
host_name=126.168.6.1
user_name=root
user_pwd=123456
# set -x
need_optmize_table=true
databases_system_arr=("base" "information_schema" "mysql" "performance_schema" "sys");
databases_arr=$(./mysql -h$host_name -u$user_name -p$user_pwd -A -Bse "show databases");
db_num=0
for database_name in $databases_arr
do
let db_num=db_num+1
done
# aaaa=$(echo $databases_arr|sed ':jix;N;s/\n/ /g;b jix' )
# echo '===================01=================='
# echo ${databases_system_arr[*]}
# echo ${databases_arr[*]}
# echo ${#databases_system_arr[@]}
# echo ${#databases_arr[@]}
db_num_index=0
for database_name in $databases_arr
do
let db_num_index=db_num_index+1
database_name=$(echo $database_name|awk '{printf "%s",$1}'|sed 's/ //g')
is_system_db=false
for database_system_name in ${databases_system_arr[@]}
do
if [ "$database_name" = "$database_system_name" ]
then
is_system_db=true
else
echo
fi
done
use=$(./mysql -h$host_name -u$user_name -p$user_pwd -A -Bse " use $database_name ")
d_tables=$(./mysql -h$host_name -u$user_name -p$user_pwd $database_name -A -Bse "show tables")
if [ $is_system_db = false ]
then
tb_num=0
for d_table in $d_tables
do
let tb_num=tb_num+1
done
tb_num_index=0
for table_name in $d_tables
do
let tb_num_index=tb_num_index+1
echo '************1*************'
echo '======database_name:'$database_name
echo '======table_name:'$table_name
echo '======db_num_index/db_num:'$db_num_index'/'$db_num
echo '======tb_num_index/tb_num:'$tb_num_index'/'$tb_num
echo '*************2************'
check_result=$(./mysql -h$host_name -u$user_name -p$user_pwd $database_name -A -Bse "check table $table_name" | awk '{ print $4 }')
if [ "$check_result" = "OK" ]
then
echo "It's no need to repair table $table_name"
# echo
else
echo $(./mysql -h$host_name -u$user_name -p$user_pwd $database_name -A -Bse "repair table $table_name")
echo
fi
# 优化表,可提高性能
if [ $need_optmize_table = true ]
then
echo $(./mysql -h$host_name -u$user_name -p$user_pwd $database_name -A -Bse "optimize table $table_name")
echo '*************************'
fi
done
fi
done
done