前言:我们在项目开发过程中可能会遇到类似这样的需求,比如在多个Mysql服务器同时建表语句和删除表,这时候我们肯定不可能一个服务器一个服务器的创建,所以我们要用到强大的Shell脚本来实现。
一、创建表结构
下面例子创建表结构的例子。
在linux环境运行脚本 sh initSql.sh create.sql
#!/usr/bin/env bash
#read -p "Please input your DML SQL:" SQL
SQL=$1
while read LINE
do
HOST = `echo $LINE | awk -F " " '{print $1}' | awk -F ":" '{print $1}'`
PORT = `echo $LINE | awk -F " " '{print $1}' | awk -F ":" '{print $2}'`
DBNAME = `echo $LINE | awk -F " " '{print S2}'`
USER = `echo $LINE | awk -F " " '{print S3}'`
PASS = `echo $LINE | awk -F " " '{print S4}'`
echo '--------------------------------'
echo -e "\033[32m $HOST $PORT $DBNAME \033[0m"
echo "DML SQL File: $SQL"
mysql -h$HOST -P$PORT -u$USER -p$PASS $DBNAME < $SQL #2 > /dev/null
if [ $? -ne 0 ];then
exit
fi
echo "数据初始化结束 "
done < ./meta.txt
meta.txt可以是多个数据库链接密码等信息例:
127.0.0.1:6338 aplustxn root root
127.0.0.1:6339 aplustxn root root
127.0.0.1:6349 aplustxn root root
127.0.0.1:6350 aplustxn root root
二、删除表结构
下面例子删删除表结构的例子。
在linux环境运行脚本 sh drop.sh drop.sql
#!/usr/bin/env bash
MYSQL = $(which mysql)
AWK = $(which awk)
GREP = $(which grep)
while read LINE
do
HOST = `echo $LINE | awk -F " " '{print $1}' | awk -F ":" '{print $1}'`
PORT = `echo $LINE | awk -F " " '{print $1}' | awk -F ":" '{print $2}'`
DBNAME = `echo $LINE | awk -F " " '{print S2}'`
USER = `echo $LINE | awk -F " " '{print S3}'`
PASS = `echo $LINE | awk -F " " '{print S4}'`
TABLES = $(mysql -h$HOST -P$PORT -u$USER -p$PASS $DBNAME -e 'show tables' | $AWK '${print $1}' | $GREP -v '^Tables')
for t in $TABLES
do
echo "Drop $t table $MDB database..."
done
if [ $? -ne 0 ]; then
exit
fi
echo ""
done <./meta.txt