mysql引擎转化脚本示例(1)

1、用于将mysql innodb引擎的数据表.ibd转化为myisam的.MYD和.MYI。后者可以直接迁移至其他磁盘，做到缓解当前磁盘使用率的目的

2、整个过程包括:(1)将innodb数据引擎转化为myisam。(2)将转化后的MYI和MYD进行数据库插件的压缩（后者只能读不能写）。

#!/bin/sh

DBUSER=***
DBPASS=***
DBHOST="127.0.0.1"
DBPORT="3306"
CONVERT_ENGINES="MYISAM"

DBNAME_1="rmlog_bs_db_01 rmlog_bs_db_02 rmlog_bs_db_03 rmlog_bs_db_04 rmlog_bs_db_05 rmlog_bs_db_06 rmlog_bs_db_07 rmlog_bs_db_08 rmlog_bs_db_09 rmlog_bs_db_10 rmlog_bs_db_11 rmlog_bs_db_12 rmlog_bs_db_13 rmlog_bs_db_14 rmlog_bs_db_15 rmlog_bs_db_16"
TBNAME_PRE_1="log_deliver_mail_ log_imap_mail_ log_pop_mail_ log_read_mail_ log_read_new_mail_ log_send_mail_ log_ua_info_"

MYSQL_HOME="/mysql/product/"
DBDATA_HOME="/mysql/dbdata/"
DAY_NUM=30
DATE="`date +%F`"
LOGDIR="/home/mysql/scripts/convert_engines_to_myisam/logs/"
LOGFILE="$LOGDIR/convert_engines_to_myisam.log.$DATE"

if [ ! -d $LOGDIR ];then
        mkdir -p $LOGDIR
fi

for i in $DBNAME_1; do
        for j in $TBNAME_PRE_1; do
                DAY=$DAY_NUM
                for k in `seq 20`; do
                        TBNAME_1=$j`date -d "-$DAY day" +%Y%m%d`
            if [ -f $DBDATA_HOME/$i/$TBNAME_1.ibd ];then
                            date +"%F %T START mysql_convert_table_format $i $TBNAME_1" >> $LOGFILE 2>&1
                            $MYSQL_HOME/bin/mysql_convert_table_format  --user=$DBUSER --password=$DBPASS --host=$DBHOST --port=$DBPORT --type=$CONVERT_ENGINES $i $TBNAME_1 >> $LOGFILE 2>&1
                            date +"%F %T END mysql_convert_table_format $i $TBNAME_1" >> $LOGFILE 2>&1
            fi
            if [ -f $DBDATA_HOME/$i/$TBNAME_1.MYI ];then
                            date +"%F %T START myisampack $i $TBNAME_1" >> $LOGFILE 2>&1
                $MYSQL_HOME/bin/myisampack $DBDATA_HOME/$i/$TBNAME_1.MYI >> $LOGFILE 2>&1
                            date +"%F %T START myisamchk $i $TBNAME_1" >> $LOGFILE 2>&1
                $MYSQL_HOME/bin/myisamchk -rq --sort-index --analyze $DBDATA_HOME/$i/$TBNAME_1.MYI >> $LOGFILE 2>&1
                            date +"%F %T END myisampack myisamchk $i $TBNAME_1" >> $LOGFILE 2>&1
            fi
                        let DAY=DAY+1
                done
        done
done


date +"%F %T START mysqladmin flush-tables" >> $LOGFILE 2>&1
$MYSQL_HOME/bin/mysqladmin -u$DBUSER -p$DBPASS flush-tables
date +"%F %T END mysqladmin flush-tables" >> $LOGFILE 2>&1