[shell]-定时建表

森格 | 2023年4月

工作中常常会按天按月对不同的数据库建表，下面是利用shell编写的两个建表脚本。

0. 写在最前

大致思路就是编写一个文本保存你要对哪些库进行建表，然后使用read循环读取，例如：

ip1 schema1
ip2 schema2
... ...
... ... 
ipn scheman

读取完后，利用create table if not EXISTS “要创建的表名” like “源表名”，这里的源表名可以是第一个月的表，create tables like 只会复制源表的表定义，不会复制数据。

明白上述两点思路下面就很简单了，直接看脚本。

1. 按月建表

#!/bin/bash
source ~/.bash_profile
# author：liangsen
# data：2023.04.02
# description：create tables in every month# common setting
sourcepasswd='your_password'
sourceuser=your_user
port=330x
sourcetable="xxx"
currentdate=$(date +%Y%m)
nextdate=$(date -d '+1 month' +%Y%m)main() {tableName=$sourcetable"_"$currentdatecTableName=$sourcetable"_"$nextdateecho "源表名：$tableName"echo "要创建的表名：$cTableName"# destinct host schemacat /xxx/dbhost.txt | while read sourcehost sourcedb; docreateSql="create table if not EXISTS "$cTableName" like "$tableName";"echo "建表信息如下："echo $sourcehost $sourcedb $tableName $cTableName $createSqlmysql -h$sourcehost -u$sourceuser -p$sourcepasswd -P$port -A $sourcedb -e "$createSql"flagTable=$(mysql -h$sourcehost -u$sourceuser -p$sourcepasswd -P$port -A $sourcedb -e "show tables like '$cTableName%';" | grep -v Tables)if [ "$cTableName" == "$flagTable" ]; thenecho "$sourcedb $cTableName 创建成功"elseecho "$sourcedb $cTableName 创建失败"fidone
}
main

2. 按天建表（每月15号）

#!/bin/bash
source ~/.bash_profile
# author：liangsen
# data：2023.04.02
# description：create tables on the fifteenth of each month# common setting
sourcepasswd='xxx'
sourceuser=xxx
sourcetable="xxx"
port=330x
today="202x0x15"main() {# Time: start & endstartYmd_tmp=$(date -d "$today +1 month" +%Y%m%d)echo "$startYmd_tmp"startYmd=$(date -d "$startYmd_tmp -14 day" +%Y%m%d)echo "$startYmd"endYmd_tmp=$(date -d "$today +2 month" +%Y%m%d)echo "$endYmd_tmp"endYmd=$(date -d "$endYmd_tmp -15 day" +%Y%m%d)echo "$endYmd"while [[ $startYmd -le $endYmd ]]; do# sourceTableName & destinctTableName & tmptableName=$sourcetable"_"$todayecho "源表名：$tableName"cTableName=$sourcetable"_"$startYmdecho "要创建的表名：$cTableName"tmp=$(date -d "$startYmd 1 day" +%Y%m%d)startYmd=$tmp# destinct host schemacat /xxx/dbhost.txt | while read sourcehost sourcedb; docreateSql="create table if not EXISTS "$cTableName" like "$tableName";"echo "建表信息如下："echo $sourcehost $sourcedb $tableName $cTableName $createSqlmysql -h$sourcehost -u$sourceuser -p$sourcepasswd -P$port -A $sourcedb -e "$createSql"flagTable=$(mysql -h$sourcehost -u$sourceuser -p$sourcepasswd -P$port -A $sourcedb -e "show tables like '$cTableName%';" | grep -v Tables)if [ "$cTableName" == "$flagTable" ]; thenecho "$sourcedb $cTableName 创建成功"elseecho "$sourcedb $cTableName 创建失败"fidonedone
}
main

3. crontab

建立完上述脚本后，我们只需要设置crontab就可以了，顺便将输出的结果到日志中，方便日后查看。

x x x * * source ~/.bash_profile;sh /xx.sh >> /xx/xx.log

4. 最后

今天就到这里了，如果问题欢迎指正。溜了~