[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"_"$currentdate
    cTableName=$sourcetable"_"$nextdate
    echo "源表名：$tableName"
    echo "要创建的表名：$cTableName"

    # destinct host schema
    cat /xxx/dbhost.txt | while read sourcehost sourcedb; do
        createSql="create table if not EXISTS "$cTableName" like "$tableName";"
        echo "建表信息如下："
        echo $sourcehost $sourcedb $tableName $cTableName $createSql
        mysql -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" ]; then
            echo "$sourcedb $cTableName 创建成功"
        else
            echo "$sourcedb $cTableName 创建失败"
        fi
    done
}
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 & end
  startYmd_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 & tmp
    tableName=$sourcetable"_"$today
    echo "源表名：$tableName"
    cTableName=$sourcetable"_"$startYmd
    echo "要创建的表名：$cTableName"
    tmp=$(date -d "$startYmd 1 day" +%Y%m%d)
    startYmd=$tmp

    # destinct host schema
    cat /xxx/dbhost.txt | while read sourcehost sourcedb; do
      createSql="create table if not EXISTS "$cTableName" like "$tableName";"
      echo "建表信息如下："
      echo $sourcehost $sourcedb $tableName $cTableName $createSql
      mysql -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" ]; then
        echo "$sourcedb $cTableName 创建成功"
      else
        echo "$sourcedb $cTableName 创建失败"
      fi
    done
  done
}
main

3. crontab

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

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

4. 最后

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