批量备份mysql数据库以及将mysql建表语句改为hive建表语句

#!/bin/bash

############功能说明部分#######################################
#脚本功能是按照每天对mysql数据库中所有表进行备份，备份文件为：数据库名-表名.sql
#实例：backup/2016-08-25/uic-test.sql
#测试数据还原实例：mysql -uroot -pprism%didi%dev%135  -h139.219.102.134 prism < backup/2018-11-07/prism-news.sql
###############脚本变量定义##################################

MYSQL=/usr/bin/mysql
BACKUP_HOME=$(cd `dirname $0`; pwd)/bak
save_time_threshold=1 #备份天数
#RM_DATA_PATH=$BACKUP_HOME/$(date -d "-30 days" +%F)
HOST='ip'
USER='账号'
PASS=‘密码'
DATABASE='库名'

DATE=$(date +%F)-${DATABASE}-${HOST}
BACKUP_DATA_PATH=$BACKUP_HOME/$DATE
###############函数部分#######################################
function backupMysql(){
    local db=$1
    local table=$2
    local dbdir=$3
    local backfile="${dbdir}/${db}-${table}.sql"
    sed -i "s/replaceTable/${table}/g" /data/mysqlToHive.sql
    sed -i "s/replaceDb/${db}/g" /data/mysqlToHive.sql
    $MYSQL -u${USER} -p${PASS} -h${HOST} -N < /data/mysqlToHive.sql  >  /data/${table}_ddl.hive
    sed -i "s/${table}/replaceTable/g" /data/mysqlToHive.sql
    sed -i "s/${db}/replaceDb/g" /data/mysqlToHive.sql
}

##########脚本逻辑部分#########################################
ls $BACKUP_HOME &>/dev/null || mkdir -p $BACKUP_HOME
#chown az-user.az-user $BACKUP_HOME
ls $BACKUP_DATA_PATH &>/dev/null || mkdir $BACKUP_DATA_PATH
#chown az-user.az-user $BACKUP_DATA_PATH
#find $BACKUP_HOME/ -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;
#if [ -d $RM_DATA_PATH ];then
#    rm -rf $RM_DATA_PATH    
#fi
cd $BACKUP_DATA_PATH || exit

echo "begin backup mysql"
cmd="show databases like '%${DATABASE}%'"
cnt=$(mysql -h${HOST} -u${USER} -p${PASS} -s -e "${cmd}")
for db  in $cnt
do
    echo "==current op db : ${db}" 
    cmd="use $db;show tables"
    tablelist=$(mysql -h${HOST} -u${USER} -p${PASS} -s -e "${cmd}")
    for table in $tablelist
    do
    echo "==current op tab : ${table}"
    if [ "$table" == "project_files" ] ;then
        cmd1="use table;SELECT project_id,version,chunk,size from $table"
        mysql -h${HOST} -u${USER} -p${PASS} -s -e "${cmd1}" | gzip > ${BACKUP_DATA_PATH}/${db}-${table}_txt.gz 
    else
    echo "==start backup db ddl and data "
        backupMysql "$db" "$table" "$BACKUP_DATA_PATH"
    echo "==write table list to tables.txt "
    echo "$table" >> ${db}_tables.txt
    fi
    done
    cat /data/*_ddl.hive > /data/ddl_all.hive
    rm -rf /data/*_ddl.hive
    echo "==done ${db}"
done
echo "==all done"

需要有些shell基础，根据脚本修改成自己需要的即可，比如不需要转换ddl可以将ddl转换成hive ddl的部分注释。

下面是mysqlToHive.sql:

select 
replace(
concat(
concat("DROP TABLE IF EXISTS replaceTable ;CREATE TABLE IF NOT EXISTS replaceTable  (",
GROUP_CONCAT(concat(colname,data_type) SEPARATOR ' ')),") ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;"),",)",")") from (
select concat('`',column_name,'` ') colname,
       case when NUMERIC_PRECISION is not null and (data_type='decimal' or data_type='numeric') then
		'decimal(16,2) ,'
            when  (CHARACTER_MAXIMU$M_LENGTH is not null or data_type = 'uniqueidentifier') and data_type not like '%text%' then
                'string ,'
            when data_type = 'datetime' then 
                'timestamp ,'
	    when data_type = 'money' then 
		'decimal(16,2) ,'
            when data_type = 'tinyint' then 
                'int ,'
            else
                'string ,'
       end as data_type
 from information_schema.columns
where table_name = 'replaceTable' and table_schema='replaceDb'
 ) a
;