shell 脚本迁移mysql数据库中的表
#!/bin/bashmysqluser='root'
mysqlpass='dbpassword'
mysqlhost='127.0.0.1'
mysqldb='dbname'
mysqlpath='/usr/local/mysql/bin'
mysqlport=3306
datetimes=`date "+%Y-%m-%d %H:%M:%S"`
datetimes2=`date "+%Y%m%d%H%M"`
datetimes3=`date "+%Y%m%d%H%M%S"`
backupdir="backup"
structdir="struct"
logfile="logs/test_${datetimes3}.log"
tablist="ltab.txt"
# Set the echo color
gray='\033[30;1m'
red='\033[31;1m'
green='\033[32;1m'
yellow='\033[33;1m'
blue='\033[34;1m'
pink='\033[35;1m'
white='\033[37;1m'
reset='\033[0m'
[ ! -d $backupdir ] && mkdir -p $backupdir
[ ! -d $structdir ] && mkdir $structdir
[ ! -d logs ] && mkdir logs
# logging function
function logging {
if [ ! -z "$1" ] && [ ! -z "$2" ];then
echo -e "${green} ${1} --- ${2} ${reset}"
echo -e "${datetimes} --- ${1} --- ${2}" >> $logfile
fi
}
function error {
if [ $? -eq 0 ];then
logging "INFO" "$1"
else
logging "ERROR" "${reset}${red} $1 ,have an error occurred!"
exit 1
fi
}
function yesorno {
echo -e "${yellow} $1 ${reset}"
read var
case "$var" in
)
echo "Your input is YES,Program to continue" ;;
)
echo "Your input is no.";
exit 0;;
**)
echo -e "${red} Input Error! ${reset}"
exit 0
;;
esac
}
echo -e "${yellow} This script is used to mysql table DATA DIRECTORY and INDEX DIRECTORY set to/data2/db/mysql and the migration of data to /data2/db/mysql directory. ${reset} "
yesorno "Do you want to continue, yes or no?"
logging "INFO" "You choose the name of the table below:"
# Confirm the table to by update
for tab in `cat $tablist`;do
logging "INFO" "${tab}"
done
yesorno "The above is the table you choose, you want to continue? Yes or no?"
# dump table data and struct to $backupdir and $structdir
for tab in `cat $tablist`;do
$mysqlpath/mysqldump -u$mysqluser -p$mysqlpass -h$mysqlhost -P$mysqlport --no-create-info $mysqldb $tab > ${backupdir}/${tab}.sql
error "table insert statements $tab backuping"
$mysqlpath/mysqldump -u$mysqluser -p$mysqlpass -h$mysqlhost -P$mysqlport --no-data $mysqldb $tab > ${structdir}/${tab}.sql
error "table struct $tab backuping"
done
# append the DATA DIRECTOY and DATA DIRINDEX to table struct.
for tab in `cat $tablist`;do
if grep 'ENGINE=InnoDB' ${structdir}/${tab}.sql;then
sed -i "s/ENGINE=InnoDB/& DATA DIRECTORY\=\'\/data2\/db\/mysql\' INDEX DIRECTORY\=\'\/data2\/db\/mysql\'/" ${structdir}/${tab}.sql
error "append the ENGINE=InnoDB --> DATA DIRECTORY\=\'\/data2\/db\/mysql\'/ to ${structdir}/${tab}.sql"
elif grep 'ENGINE=MyISAM' ${structdir}/${tab}.sql;then
sed -i "s/ENGINE=MyISAM/& DATA DIRECTORY\=\'\/data2\/db\/mysql\' INDEX DIRECTORY\=\'\/data2\/db\/mysql\'/" ${structdir}/${tab}.sql
error "append the ENGINE=InnoDB --> DATA DIRECTORY\=\'\/data2\/db\/mysql\'/ to ${structdir}/${tab}.sql"
else
logging "ERROR,Table structure is not found in the match engine ."
exit 1
fi
done
# drop old database
for tab in `cat $tablist`;do
if [ -f ${backupdir}/${tab}.sql ] && [ -f ${structdir}/${tab}.sql ];then
$mysqlpath/mysql -u$mysqluser -p$mysqlpass -h$mysqlhost -P$mysqlport $mysqldb -e "drop table ${tab};"
error "There is no back up the table"
fi
# import table struct to db
if [ -f ${structdir}/${tab}.sql ];then
$mysqlpath/mysql -u$mysqluser -p$mysqlpass -h$mysqlhost -P$mysqlport $mysqldb < ${structdir}/${tab}.sql
error "The import table structure"
fi
# import table data to db
if [ -f ${backupdir}/${tab}.sql ];then
$mysqlpath/mysql -u$mysqluser -p$mysqlpass -h$mysqlhost -P$mysqlport $mysqldb < ${backupdir}/${tab}.sql
error "Insert data to the table"
fi
done
logging "INFO" "Successfully completed the operation !"
页:
[1]