mysql数据跨库传输

# 这个文件是我设置的数据源，包含了我使用变量引用的数据源，省略prod_dbs.sh文件

source /opt/soft/ods/script/odl/prod_dbs.sh

#source https://blog.csdn.net/zhaojike/article/details/prod_dbs.sh

MYSQL=`which mysql`

DATA_DATE=`date +%Y%m%d -d "1 days ago"`

##########################################################当天新增

T_RPT_MERCHANT_AUTO_AUDIT_PART1="

SELECT stat_date, data_flag, sales_region, region_id, apply_cnt, pass_cnt, returned_cnt, rejected_cnt, pass_rate, rejected_rate

FROM odl_rpt.t_rpt_merchant_auto_audit_part1 a WHERE stat_date=${DATA_DATE};

"

##########################################################源库(A环境)表生成文件

echo "run sql on odlrpt_host"

echo "start running at `date ‘+%Y-%m-%d %T‘`"

echo "${T_RPT_MERCHANT_AUTO_AUDIT_PART1}"

$MYSQL -h${odlrpt_host} --default-character-set=utf8 -P${odlrpt_port} -u${odlrpt_user} -p${odlrpt_passwd} --database=${odlrpt_db} --skip-column-names -e "${T_RPT_MERCHANT_AUTO_AUDIT_PART1}" > "${DATA_DIR}/T_RPT_MERCHANT_AUTO_AUDIT_PART1.txt"

##########################################################推送数据到B环境库目标表

SQL_TMP_TO_TASK="

DELETE FROM ODL_RPT.T_RPT_MERCHANT_AUTO_AUDIT_PART1 WHERE? stat_date=${DATA_DATE};

LOAD DATA LOCAL INFILE ‘/opt/soft/ods/srcdata/T_RPT_MERCHANT_AUTO_AUDIT_PART1.txt‘ INTO TABLE ODL_RPT.T_RPT_MERCHANT_AUTO_AUDIT_PART1;

"

echo "run sql on odlrpt_host150"

echo "start running at `date ‘+%Y-%m-%d %T‘`"

echo "${SQL_TMP_TO_TASK}"

$MYSQL -h${odlrpt_host150} --default-character-set=utf8 -P${odlrpt_port150} -u${odlrpt_user150} -p${odlrpt_passwd150} --database=${odlrpt_db150} --skip-column-names -e "${SQL_TMP_TO_TASK}"

完成了，简单吧。

mysql数据跨库传输

标签：mys   环境   local   引用   database   目标   int   flag   包含