Script function Brief

1. Export remote mysql database table data to local and save as file
2. Support the configuration of table information to be exported to the configuration file. The script will cycle the configuration file and parse the configuration file, and then complete the subsequent data export.
3. Incremental export by time is supported, with built-in time variables ${start ﹣ time} ${end ﹣ and time stamp built-in variables ${start ﹣ timestamp} and ${end ﹣ timestamp}
4. Input parameters in the form of - D are supported, such as: - D CFG = CFG ﹣ file ﹣ path ﹣ X - D starttime = yyyy-mm-dd - D Endtime = yyyy-mm-dd

Script project directory

project_name/bin
project_name/cfg
project_name/sql
Take sync? M2local as an example

Script introduction
Step 1. Create the db.cfg file configuration database connection information in the CFG directory
$ cat cfg/db.cfg
DB_URL_="/usr/bin/mysql -h192.168.1.3 -udyb -pdyb123"

Step 2. Create the sync service.cfg configuration file in the CFG directory, and configure the table information to be exported
$ cat cfg/sync_service.cfg
#database_name:table_name:sql_statement_file:[db_connect]
cloud_tm_mi:order_info:sql/order_info.sql:default
cloud_tm_mi:user_info:sql/user_info.sql:default

Step 3 write the corresponding sql file in the sql directory
$cat sql/order_info.sql (full export)
sql_="select concat_ws(x'01',REPLACE(IFNULL(id,''),'\n',''),REPLACE(IFNULL(is_delete,''),'\n','')) from order_info ;"

$cat sql/user_info.sql (exported in increments of time YYYY-MM-DD, the default time is 0:00-23:59:59 seconds yesterday, which can be specified by parameters)
sql_="select concat_ws(x'01',REPLACE(IFNULL(id,''),'\n',''),REPLACE(IFNULL(username,''),'\n','')) from user_info where create_time between '${START_TIME}' and '${END_TIME}';"

Step 4. Modify the path variable configuration in main.sh
Temp path (temporary storage directory is used for directory path after initial download)
Target? Path
JOB_MARK (generally defined as the name of the project, which plays an identification role)

Step 5. Configure task scheduling crontab or execute script manually
. / main.sh (CFG / sync ﹣ service.cfg configuration file will be loaded by default)
perhaps
./main.sh "-D cfg=../cfg/sync_service_test.cfg"
perhaps
./main.sh "-D cfg=../cfg/sync_service.cfg -D starttime=20200302 -D endtime=20200302"

Script details (example)

bin/main.sh script content

#!/bin/bash
#Function: used to synchronize remote mysql database table data to local
#Author: Ding Yibo

source /etc/profile
source ~/.bash_profile
export LANG=en_US.UTF-8
export RUN_HOME=$(cd "$(dirname "$0")"; echo "${PWD%/*}")
source $RUN_HOME/bin/utils.sh
source $RUN_HOME/cfg/db.cfg
JOB_MARK=pension

#Definition of temporary directory and target directory
TEMP_PATH=/data/mysqldata/mysqldata/database_${JOB_MARK}_temp
TARGET_PATH=/data/oradata/external_table/${JOB_MARK}_service/data

#Parameter processing
if [[ "$1" == "-help" || "$1" == "-h" || $# -gt 1 ]]; then
  echo "usage: $0 "'"-D cfg=cfg_file_path_x -D starttime=YYYY-MM-DD -D endtime=YYYY-MM-DD"'
  exit
fi

if [[ $# -ne 1 ]]; then
  _loadConf "${RUN_HOME}/cfg/sync_service.cfg"
else
  parameter=$1
  for i in `echo $parameter|sed 's/-D//g'`
  do
     key=`echo $i|cut -d'=' -f1`
     value=`echo $i|cut -d'=' -f2`
     if [ $key == "cfg" ]; then cfg=$value; fi
     if [ $key == "starttime" ]; then starttime=$value; fi
     if [ $key == "endtime" ]; then endtime=$value; fi
  done
  echo $cfg
  if [ "$cfg" == "" ]; then 
   _loadConf "${RUN_HOME}/cfg/sync_service.cfg"
  elif [ -f "$cfg" ]; then
   _loadConf "$cfg"
  else
   echo "cfg file is not exist!"
   exit
  fi
 
  _isValidDate $starttime
  if [ $? -ne 0 ]; then echo "date format error!"; exit; fi
  _isValidDate $endtime
  if [ $? -ne 0 ]; then echo "date format error!"; exit; fi
  _initDate "$starttime" "$endtime"
fi

if [ ! -d ${TEMP_PATH} ]; then
  mkdir -p ${TEMP_PATH}
fi

#Synchronize task execution body, loop through parameter file execution, and change to multi process execution if necessary
for cfg in ${CFG_[@]}
do
  dburl=`echo ${cfg}|cut -d':' -f4`
  database=`echo ${cfg}|cut -d':' -f1`
  table=`echo ${cfg}|cut -d':' -f2`
  sqlfile=`echo ${cfg}|cut -d':' -f3`
  source ${RUN_HOME}/$sqlfile
  if [[ "$dburl" == "default" ]]; then
    echo ${sql_}
    ${DB_URL_} -D${database} -N -e "${sql_}" >${TEMP_PATH}/${database}.${table}.dat
    if [ $? -eq 0 ]; then
      echo "TEMP_PATH: ${TEMP_PATH}/${database}.${table}.dat"
      echo "TARGET_PATH: ${TARGET_PATH}/${database}.${table}.dat"
      if [ -f ${TARGET_PATH}/${database}.${table}.dat ]; then rm -f ${TARGET_PATH}/${database}.${table}.dat; fi
      mv ${TEMP_PATH}/${database}.${table}.dat ${TARGET_PATH}/${database}.${table}.dat
    else
      echo "${table} sync fail"
    fi
  fi
done

bin/utils.sh script content

#Tool script

function _formatDate()
{
  _ymdhms=$1
  _ymd=`echo ${_ymdhms:0:10}`
  _year=`echo ${_ymdhms:0:4}`
  _month=`echo ${_ymdhms:5:2}`
  _day=`echo ${_ymdhms:8:2}`
  _hour=`echo ${_ymdhms:11:2}`
  _minute=`echo ${_ymdhms:14:2}`
  _second=`echo ${_ymdhms:17:2}`
}

function _loadConf()
{
 i_=0
 for cv_ in `cat ${1}`;do
   if [ "${cv_:0:1}" == "#" -o -z $cv_ ];then continue; fi
   CFG_[$i_]=${cv_}
   echo "Profile content ${i_}:${CFG_[${i_}]}"
   i_=`expr ${i_} + 1`
done
}

isDate_=0
function _isValidDate(){  
  if [[ "$1" == "" ]]; then
    return 0
  fi
  date -d "$1" "+%F"|grep -q "$1" 2>/dev/null 
  if [ $? = 0 ]; then
    ((isDate_++))
    return 0
  else  
    return 1 
  fi  
}

#Initialization time variable
RUN_TIME=`date -d yesterday +'%Y-%m-%d %H:%M:%S'`
START_TIME="`date -d yesterday +%Y-%m-%d` 00:00:00"
END_TIME="`date -d yesterday +%Y-%m-%d` 23:59:59"
START_TIMESTAMP=`date -d "${START_TIME}" +%s`
END_TIMESTAMP=`date -d "${END_TIME}" +%s`

#Initialize time variable based on input date
function _initDate()
{
  if [[ "$1" != "" ]]; then
    START_TIME="$1 00:00:00"
    START_TIMESTAMP=`date -d "${START_TIME}" +%s`
  fi
  if [[ "$2" != "" ]]; then
    END_TIME="$2 23:59:59"
    END_TIMESTAMP=`date -d "${END_TIME}" +%s`
  fi
}

cfg/db.cfg configuration file

DB_URL_="/usr/bin/mysql -h10.1.31.12 -udyb -pdyb123"

CFG / sync \ service.cfg configuration file

#database_name:table_name:sql_statement_file:[db_connect]
testdb:test:sql/test.sql:default

sql/test.sql file

sql_="select concat_ws(x'01',REPLACE(IFNULL(ORG_ID,''),'\n',''),REPLACE(IFNULL(jsjg_unified_org_no,''),'\n','')) from test where create_time between '${START_TIME}' and '${END_TIME}';"