Sh脚本实现数据库备份

backup.sh：备份个人库、团队库，备份建表语句到hdfs上一个文件夹
restore.sh：恢复个人库、团队库，备份建表语句
delbak.sh：crontab配置，删除hdfs上超过两周的文件夹

backup.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
#!/bin/bash

BACKUP_ROOT=/root/cicc/
BACKUP_DATE=$(date +%Y%m%d)
BACKUP_LOG=${BACKUP_ROOT}/log/$(basename $0).${BACKUP_DATE}.log

backup_beeline() {
    beeline -u "jdbc:hive2://tq-dev-node2:10000/;guardianToken=wOAseBo1stuRYlvjtZDR-TDH" --color=false --showHeader=false --showWarnings=false --silent=true --outputformat=csv --maxWidth=1000 -n admin -p admin -e "$1";
}


backup_timelyre_database() {
    echo "$(date +%c): backup timelyre database $1 starting..."|tee -a ${BACKUP_LOG}

    ORIGIN_DATABASE=$1;
    BACKUP_DATABASE=$1_backup_$BACKUP_DATE;

    BACKUP_PATH=${BACKUP_ROOT}/dat/${BACKUP_DATE}
    if [ ! -d ${BACKUP_PATH} ]; then
        mkdir -p ${BACKUP_PATH};
    fi
    mkdir -p $BACKUP_PATH/$ORIGIN_DATABASE;

    backup_beeline "create database if not exists $BACKUP_DATABASE;"

    BACKUP_TABLES=$(backup_beeline "use $1; show tables;"|xargs)
    for TABLE in $BACKUP_TABLES
    do
        backup_beeline "show create table $ORIGIN_DATABASE.$TABLE;"|xargs > $BACKUP_PATH/$ORIGIN_DATABASE/$TABLE.sql
        backup_beeline "create table if not exists $BACKUP_DATABASE.$TABLE stored as CSVFILE as select * from $ORIGIN_DATABASE.$TABLE"
    done

    echo "$(date +%c): backup timelyre database $1 finished!!!"|tee -a ${BACKUP_LOG}
}


backup_timelyre() {
    echo "$(date +%c): backup timelyre starting..."|tee -a ${BACKUP_LOG}
    BACKUP_DATABASES=$(backup_beeline "show databases;"|xargs)
    for DATABASE in $BACKUP_DATABASES
    do
        echo "process database $DATABASE"
	if [[ "$DATABASE" == *"_private" || "$DATABASE" == *"_public" || "$DATABASE" == *"_meta" ]]; then
	    backup_timelyre_database $DATABASE
	fi
    done
    echo "$(date +%c): backup timelyre finished!!!"|tee -a ${BACKUP_LOG}
}


# main
echo "$(date +%c) backup ${BACKUP_PATH} starting..."|tee -a ${BACKUP_LOG}

source /var/lib/transwarp-manager/master/content/resources/tdh_client/init.sh;

#backup_timelyre

backup_timelyre_database "user3_private"

echo "$(date +%c) backup ${BACKUP_PATH} finished!!!"|tee -a ${BACKUP_LOG}

exit 0;

restore.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
estore#!/bin/bash

if [ $# -ne 1 ]; then
    echo "Input Argument Error!"
    echo "Usage: $0 [BACKUP_DATE]"
    exit 1;
fi
RESTORE_DATE=$1

BACKUP_ROOT=/root/cicc
BACKUP_DATE=$(date +%Y%m%d)
BACKUP_LOG=${BACKUP_ROOT}/log/$(basename $0).${BACKUP_DATE}.log

restore_beeline() {
    beeline -u "jdbc:hive2://tq-dev-node2:10000/;guardianToken=wOAseBo1stuRYlvjtZDR-TDH" --color=false --showHeader=false --showWarnings=false --silent=true --outputformat=csv --maxWidth=1000 -n admin -p admin -e "$1";
}


restore_timelyre_database() {
    echo "$(date +%c): restore timelyre database $1 with $2 starting..."|tee -a ${BACKUP_LOG}

    ORIGIN_DATABASE=$1;
    BACKUP_DATABASE=$1_backup_$RESTORE_DATE;
    RESTORE_PATH=$2;

    for file in $(find $RESTORE_PATH/${DATABASE}/* -maxdepth 0 -type f); do
	TABLE=$(basename "${file%.sql}")
	echo "restore table $ORIGIN_DATABASE.$TABLE with $BACKUP_DATABASE.$TABLE and schema $file"|tee -a ${BACKUP_LOG}
        #restore_beeline "drop table $ORIGIN_DATABASE.$TABLE;"
	#restore_beeline "$(cat $file)"
        #restore_beeline "insert into $ORIGIN_DATABASE.$TABLE select * from $BACKUP_DATABASE.$TABLE;"
    done

    echo "$(date +%c): restore timelyre database $1 with $2 finished!!!"|tee -a ${BACKUP_LOG}
}


restore_timelyre() {
    echo "$(date +%c): restore timelyre starting..."|tee -a ${BACKUP_LOG}

    RESTORE_PATH=$BACKUP_ROOT/dat/$RESTORE_DATE;
    if [ ! -d $RESTORE_PATH ]; then
        echo "backup $RESTORE_PATH not exists"|tee -a ${BACKUP_LOG}
        exit -1;
    fi

    for db in $(find $RESTORE_PATH/* -maxdepth 0 -type d); do
        DATABASE=$(basename $db)
        echo "restore database: ${DATABASE} with ${RESTORE_PATH}"|tee -a ${BACKUP_LOG}
	restore_timelyre_database $DATABASE $RESTORE_PATH
    done

    echo "$(date +%c): restore timelyre finished!!!"|tee -a ${BACKUP_LOG}
}


# main
echo "$(date +%c) restore ${RESTORE_DATE} starting..."|tee -a ${BACKUP_LOG}


source /var/lib/transwarp-manager/master/content/resources/tdh_client/init.sh;

restore_timelyre

echo "$(date +%c) restore ${RESTORE_DATE} finished!!!"|tee -a ${BACKUP_LOG}

exit 0;

delbak.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
#!/bin/bash

BACKUP_ROOT=/root/cicc
BACKUP_DATE=$(date +%Y%m%d)
BACKUP_LOG=${BACKUP_ROOT}/log/$(basename $0).${BACKUP_DATE}.log

# this should be +14, means more than 14 days ago
MTIME=-1

backup_beeline() {
    beeline -u "jdbc:hive2://tq-dev-node2:10000/;guardianToken=wOAseBo1stuRYlvjtZDR-TDH" --color=false --showHeader=false --showWarnings=false --silent=true --outputformat=csv --maxWidth=1000 -n admin -p admin -e "$1";
}

clean_old_backups() {
    echo "$(date +%c): clean old backups starting..."|tee -a ${BACKUP_LOG}

    for dir in $(find ${BACKUP_ROOT}/dat/* -maxdepth 0 -mtime $MTIME -type d); do
	echo "delete backup dir: $dir"|tee -a ${BACKUP_LOG}
	DELETE_DATE=$(basename $dir)
        for db in $(find $dir/* -maxdepth 0 -type d); do
	    DATABASE=$(basename $db)
	    echo "drop backup database: ${DATABASE}_backup_${DELETE_DATE}"|tee -a ${BACKUP_LOG}
            #backup_beeline "drop database if exists $DATABASE_backup_$DELETE_DATE;"
	done
	#rm -fr $dir
    done

    echo "$(date +%c): clean old backups finished!!!"|tee -a ${BACKUP_LOG}
}

# main
echo "$(date +%c) delbak starting..."|tee -a ${BACKUP_LOG}
clean_old_backups
echo "$(date +%c) delbak finished!!!"|tee -a ${BACKUP_LOG}

exit 0;