通过脚本每月进行数据库表碎片整理。抓取Data_free项目,若不为0.代表有碎片存在,需要整理。
排除MySQL系统库

!/bin/bash

starttime=date +'%Y-%m-%d %H:%M:%S'
host_ip=${db_address} #这里可通过jenkins传值
user_name=数据库用户名
user_pwd=数据库密码
source /etc/profile

set -x

databases_system_arr=("base" "information_schema" "mysql" "performance_schema" "sys");
databases_arr=$(mysql -h${host_ip} -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
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_ip -u $user_name -p$user_pwd -A -Bse " use $database_name ")
d_tables=$(mysql -h $host_ip -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
sleep 10s
let tb_num_index=tb_num_index+1
echo '****库表信息*'
echo '1 database_name:'$database_name
echo '2 table_name:'$table_name
echo '3 db_num_index/db_num:'$db_num_index'/'$db_num
echo '4 tb_num_index/tb_num:'$tb_num_index'/'$tb_num
echo '*****
'
check_result=$(mysql -h $host_ip -u $user_name -p$user_pwd $database_name -A -Bse "SHOW TABLE STATUS LIKE '${table_name}'\G"|sed -n '/Data_free/p'|awk -F ' ' '{print $2}');
if [ "$check_result" = "0" ]
then
echo "It's no need to repair table $table_name"

echo

         else
         # 优化表,可提高性能
         # 优化开始时间
         echo "<code>date "+%Y-%m-%d %H:%M:%S"</code> start optimize table $table_name..."
          # 单个开始时间
          stime=<code>date +'%Y-%m-%d %H:%M:%S'</code>
          echo $(mysql -h $host_ip -u $user_name -p$user_pwd $database_name -A -Bse  "optimize table $table_name")
          # 单个结束时间
          etime=<code>date +'%Y-%m-%d %H:%M:%S'</code>
          # 计算单个耗时
          s_seconds=$(date --date="$stime" +%s);
          e_seconds=$(date --date="$etime" +%s);
          echo "<code>date "+%Y-%m-%d %H:%M:%S"</code>  optmize finished,耗时: "$((e_seconds-s_seconds))"s"
          echo "===========>${table_name} optmize finished<============"
         fi
        done
 fi
 done

记录结束时间

endtime=date +'%Y-%m-%d %H:%M:%S'

计算总耗时

start_seconds=$(date --date="$starttime" +%s);
end_seconds=$(date --date="$endtime" +%s);
echo "date "+%Y-%m-%d %H:%M:%S" 结束本次运行,总耗时: "$((end_seconds-start_seconds))"s"
###########################################################################################

最后修改日期: 2021年1月14日

作者

留言

撰写回覆或留言

发布留言必须填写的电子邮件地址不会公开。