使用Shell制作MySQL自动复制日库月库生成新的日库和月库脚本-Toy模板网

这篇具有很好参考价值的文章主要介绍了使用Shell制作MySQL自动复制日库月库生成新的日库和月库脚本。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

日库生成脚本daily.sh

#!/bin/bash
. /etc/profile
. ~/.bash_profile
HOSTNAME="127.0.0.1"                   #数据库信息
PORT="3306"
USERNAME="root"
PASSWORD="123456"
DBNAME="datatest"                           #要使用的数据库，没有则会新建
 
MYSQL_CMD="mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD}"
echo ${MYSQL_CMD}
 
 # 数据库名定义，本日库，
this_time=$(date +%Y_%-m_%-d) #下个月的月份
THIS_DBNAME="datatest_"$this_time    #具体的表名例datatest_2023_1_2
 
 # 数据库名定义，下一日库，
next_time=$(date -d "next day" +%Y_%-m_%-d) #下个月的月份
NEXT_DBNAME="datatest_"$next_time    #具体的表名例datatest_2023_1_3
 
echo "create database ${NEXT_DBNAME}"
create_db_sql="create database IF NOT EXISTS ${NEXT_DBNAME}"

echo ${create_db_sql} | ${MYSQL_CMD}                 #创建数据库  
#判断是否创建成功，&?=0表示上条命令执行成功           
if [ $? -ne 0 ]                                               
then
 echo "create databases ${NEXT_DBNAME} failed ..."
 exit 1
fi
 

show_table_sql="use ${THIS_DBNAME}; show tables;"
show_table_sql_shell=`echo ${show_table_sql} | ${MYSQL_CMD}`
#echo $show_table_sql_shell

create_table_sql=""
for name in $show_table_sql_shell ;
	do
	if [[ $name =~ "Tables_in" ]]
	then
		echo $name
	else
		create_table_sql="${create_table_sql} create table ${NEXT_DBNAME}.${name} like ${THIS_DBNAME}.${name};"
	fi
done

echo "executing ${create_table_sql}"
echo ${create_table_sql} | ${MYSQL_CMD}
echo "create tables done !"

月库生成脚本monthly.sh

#!/bin/bash
. /etc/profile
. ~/.bash_profile

HOSTNAME="127.0.0.1"                   #数据库信息
PORT="3306"
USERNAME="root"
PASSWORD="123456"
DBNAME="datatest"                           #要使用的数据库，没有则会新建
 
 
MYSQL_CMD="obclient -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD}"
echo ${MYSQL_CMD}
 
 # 数据库名定义，本月库，
this_month_time=$(date +%Y_%-m) #下个月的月份
THIS_MONTH_DBNAME="datatest_"$this_month_time    #具体的表名例datatest_2023_1
 
 # 数据库名定义，下月库，
next_month_time=$(date -d "next month" +%Y_%-m) #下个月的月份
NEXT_MONTH_DBNAME="datatest_"$next_month_time    #具体的表名例datatest_2023_2
 
echo "create database ${NEXT_MONTH_DBNAME}"
create_db_sql="create database IF NOT EXISTS ${NEXT_MONTH_DBNAME}"

echo ${create_db_sql} | ${MYSQL_CMD}                 #创建数据库  
#判断是否创建成功，&?=0表示上条命令执行成功           
if [ $? -ne 0 ]                                               
then
 echo "create databases ${NEXT_MONTH_DBNAME} failed ..."
 exit 1
fi
 

show_table_sql="use ${THIS_MONTH_DBNAME}; show tables;"
show_table_sql_shell=`echo ${show_table_sql} | ${MYSQL_CMD}`
#echo $show_table_sql_shell

create_table_sql=""
for name in $show_table_sql_shell ;
	do
	if [[ $name =~ "Tables_in" ]]
	then
		echo $name
	else
		create_table_sql="${create_table_sql} create table ${NEXT_MONTH_DBNAME}.${name} like ${THIS_MONTH_DBNAME}.${name};"
	fi
done

echo "executing ${create_table_sql}"
echo ${create_table_sql} | ${MYSQL_CMD}
echo "create tables done !"