Hive元数据备份

由于hive把元数据存储在mysql中，需要定期对mysql中hive用到的库进行备份，写了脚本如下。

支持的功能：

1、备份指定的mysql数据库；

2、批量备份mysql中所有的数据库（默认关闭）；

3、将备份的文件压缩后存储；

4、将备份文件传输到指定ftp服务器上备份（默认关闭）；

5、定期删除n天之前的备份文件（默认关闭）。

 

#!/bin/bash
###############################################################################
# FileName: MysqlBackupScript
# Author: chenzheng
# Version: 1.0
# Description: Backup Mysql Database
# Histroy:
# Version: v1.0
# Date: 20140703
##############################################################################

# your MySQL server's name
SERVER="Hadoop4"

# directory to backup to
BACKDIR=~/MysqlBackups

# date format that is appended to filename
DATE=`date +'%Y-%m-%d'`

#----------------------MySQL Settings--------------------#

# your MySQL server's location (IP address is best)
HOST=10.190.113.999

# MySQL username
USER=hive

# MySQL password
PASS=xxxxoooo

# List all of the MySQL databases that you want to backup in here,
# each separated by a space
DBS="hive"

# set to 'y' if you want to backup all your databases. this will override
# the database selection above.
DUMPALL=n

#----------------------FTP Settings--------------------#

# set "FTP=y" if you want to enable FTP backups
FTP=n

# FTP server settings; should be self-explanatory
FTPHOST="10.190.113.999:21"
FTPUSER="hadoop"
FTPPASS="xxxx0000"

# directory to backup to. if it doesn't exist, file will be uploaded to
# first logged-in directory
FTPDIR="backups"

#-------------------Deletion Settings-------------------#

# delete old files?
DELETE=n

# how many days of backups do you want to keep?
DAYS=30

#----------------------End of Settings------------------#

# check of the backup directory exists
# if not, create it

if [ -e $BACKDIR ]
then
echo "Backups directory already exists !"
else
mkdir $BACKDIR
echo "Create backup directory succeed !"
fi

if [ $DUMPALL = "y" ]
then
echo "Creating list of all your databases..."
mysql -h $HOST --user=$USER --password=$PASS -e "show databases;" > ~/dbs_on_$SERVER.txt

# redefine list of databases to be backed up
DBS=`sed -e ':a;N;$!ba;s/\n/ /g' -e 's/Database //g' ~/dbs_on_$SERVER.txt`
fi

echo "Backing up MySQL databases..."

for database in $DBS
do
mysqldump -h $HOST --user=$USER --password=$PASS $database > $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
gzip -f -9 $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
done

if [ $FTP = "y" ]
then
echo "Initiating FTP connection..."
cd $BACKDIR
ATTACH=`for file in *$DATE.sql.gz; do echo -n -e "put ${file}\n"; done`
ftp -nv <<EOF
open $FTPHOST
user $FTPUSER $FTPPASS
cd $FTPDIR
$ATTACH
quit
EOF
echo -e "FTP transfer complete! \n"
fi

if [ $DELETE = "y" ]
then
NEWDATE=`date --date='30 days ago' +'%Y-%m-%d'`
rm -rf $BACKDIR/$SERVER-mysqlbackup-$database-$NEWDATE.sql.gz
echo "The backup from $DAYS days ago has been deleted."
fi

echo "Your backup is complete!"