Yc-Ma Blog

MySQL远程导出文件

 
  • Yc-Ma
  • Dec 27, 2021

Here’s the table of contents:

  1. MySQL远程导出文件
    1. MySQL Dump
    2. MySQL
  2. Shell脚本循环执行SQL构建为CSV文件

MySQL远程导出文件

MySQL Dump

  • 打印执行结果 
    mysqldump -halibaba.com.cn -p3306 -utestlab_dev -ptestlabgogo analytics_company_data MSTR_ORG_PRE_1 --where "puid>0 LIMIT 10"
  • 将执行结果写入TXT文件 
    mysqldump -halibaba.com.cn -p3306 -utestlab_dev -ptestlabgogo analytics_company_data MSTR_ORG_PRE_1 --where "puid>0 LIMIT 10" > test.txt
  • 忽略表结构的输出 
    mysqldump -halibaba.com.cn -p3306 -utestlab_dev -ptestlabgogo analytics_company_data -t -N MSTR_ORG_PRE_1 --where "puid>0 LIMIT 2"

MySQL

  • CSV无双引号【替换NULL值】 
    mysql -halibaba.com.cn -p3306 -utestlab_dev -ptestlabgogo analytics_company_data --execute "SELECT a.name, a.original_name, a.hisvaild FROM (SELECT TO_CH_SIMPLE(LOWER(REGEX_EXTRACT_STR(name_cn,5,NULL))) AS name, name_cn as original_name, hisvalid AS hisvaild FROM MSTR_ORG_PRE_1 LIMIT 10) a WHERE a.name IS NOT NULL AND a.name<>''"  -s |sed -e  "s/\t/,/g" -e "s/NULL/  /g" -e "s/\n/\r\n/g"  > test.csv
  • CSV有双引号 
    mysql -halibaba.com.cn -p3306 -utestlab_dev -ptestlabgogo analytics_company_data --execute "SELECT a.name, a.original_name, a.hisvaild FROM (SELECT TO_CH_SIMPLE(LOWER(REGEX_EXTRACT_STR(name_cn,5,NULL))) AS name, name_cn as original_name, hisvalid AS hisvaild FROM MSTR_ORG_PRE_1 LIMIT 10) a WHERE a.name IS NOT NULL AND a.name<>''"  -s |sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g'  > test.csv
  • CSV有双引号【替换NULL值】 
    mysql -halibaba.com.cn -p3306 -utestlab_dev -ptestlabgogo analytics_company_data --execute "SELECT a.name, a.original_name, a.hisvaild FROM (SELECT TO_CH_SIMPLE(LOWER(REGEX_EXTRACT_STR(name_cn,5,NULL))) AS name, name_cn as original_name, hisvalid AS hisvaild FROM MSTR_ORG_PRE_1 LIMIT 10) a WHERE a.name IS NOT NULL AND a.name<>''"  -s |sed -e 's/NULL/  /g' -e 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g'  > test.csv
  • CSV有双引号【替换NULL值】【追加写入CSV】 
    mysql -halibaba.com.cn -p3306 -utestlab_dev -ptestlabgogo analytics_company_data --execute "SELECT a.name, a.original_name, a.hisvaild FROM (SELECT TO_CH_SIMPLE(LOWER(REGEX_EXTRACT_STR(name_cn,5,NULL))) AS name, name_cn as original_name, hisvalid AS hisvaild FROM MSTR_ORG_PRE_1 LIMIT 10) a WHERE a.name IS NOT NULL AND a.name<>''"  -s |sed -e 's/NULL/  /g' -e 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' >> test.csv

Shell脚本循环执行SQL构建为CSV文件

#!/usr/bin/env bash

# TABLE MIN ID
MIN_ID=0
# TABLE MAX ID
MAX_ID=102
# CSV FILE NAME
CSV_FILE_NAME=test.csv
# SQL
SQL='SELECT a.name, a.original_name, a.hisvaild FROM (SELECT TO_CH_SIMPLE(LOWER(REGEX_EXTRACT_STR(name_cn,5,NULL))) AS name, name_cn as original_name, hisvalid AS hisvaild FROM MSTR_ORG_PRE_1'

# BATCH SIZE
DATA_SIZE=10

BATCH=`expr ${MAX_ID} / ${DATA_SIZE}`
BATCH_FIX=2
BATCH=`expr ${BATCH} + ${BATCH_FIX}`

echo ${BATCH}
# BUILD CSV
AUTO_ID=0
for ((i = 1; i <=${BATCH}; i++));do
  mysql -hcontentdb.localhost.rds.cn-north-1.alibaba.com.cn -p3306 -utestlab -ptestlab analytics_company_data --execute "${SQL} WHERE puid>${AUTO_ID} LIMIT ${DATA_SIZE}) a WHERE a.name IS NOT NULL AND a.name<>''"  -s |sed -e 's/\"/""/g' -e 's/NULL/  /g' -e 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' >> ${CSV_FILE_NAME}
  AUTO_ID=`expr ${i} \* ${DATA_SIZE}`
done

################### background execution this csv shell

# START_CSV_SHELL_SERVER:
# nohup ./test.sh > test.file 2>&1 &

# STOP_CSV_SHELL_SERVER:
# kill -9 `ps -ef|grep test.sh|grep -v grep|awk '{print $2}'`
  • 修改为ID范围
#!/usr/bin/env bash

# TABLE MIN ID
#!/usr/bin/env bash

# TABLE MAX ID
MIN_ID=385613309
MAX_ID=385613507
# CSV FILE NAME
CSV_FILE_NAME=test.csv

# BATCH SIZE
DATA_SIZE=1000000

BATCH_SUB=`expr ${MAX_ID} - ${MIN_ID}`
BATCH=`expr ${BATCH_SUB} / ${DATA_SIZE}`
BATCH=`expr ${BATCH} + 2`

echo 'batch size:'${BATCH}

# BUILD CSV [Left on the right off]
AUTO_MIN=${MIN_ID}
AUTO_MAX=`expr ${AUTO_MIN} + ${DATA_SIZE}`
for ((i = 1; i <=${BATCH}; i++));do
  echo 'START_MIN_ID:'${AUTO_MIN}
  echo 'START_MAX_ID:'${AUTO_MAX}
  mysql -hcontentdb.localhost.rds.cn-north-1.alibaba.com.cn -p3306 -utestlab -ptestlab analytics_master_data --execute "SELECT hcode AS name,SUBSTRING(hcode,LENGTH('HORG')+1,LENGTH(hcode)) AS code,'HORG' AS prefix_code,CONVERT(DATE_FORMAT(hupdatetime,'%Y%m%d%H%i%S'),SIGNED INTEGER) AS hupdatetime,hisvalid FROM analytics_master_data.MSTR_ORG WHERE huid>=${AUTO_MIN} AND huid<${AUTO_MAX}"  -s |sed -e 's/\"/""/g' -e 's/NULL/  /g' -e 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' >> ${CSV_FILE_NAME}
  AUTO_MIN=${AUTO_MAX}
  AUTO_MAX=`expr ${AUTO_MIN} + ${DATA_SIZE}`
done

echo 'build csv end!!!'
################### background execution this csv shell

# START_CSV_SHELL_SERVER:
# nohup ./test.sh > test.file 2>&1 &

# STOP_CSV_SHELL_SERVER:
# kill -9 `ps -ef|grep test.sh|grep -v grep|awk '{print $2}'`
Search