hana备份和清理自动化02 Feb 2015

tech hana

本文主要介绍HANA的每天自动备份和删除N天前的数据的方法

###HANA手动备份和删除备份

在这里我就不做过多赘述，通过HANA Studio可以很轻松的完成数据库的备份和恢复还有删除备份，这里就做一下截图吧

###HANA自动备份和清理

#####创建用户

在HANA STUDIO中的SQL console里创建用户并赋予backup admin权限

如果只做备份不做清理可以只赋予backup operater权限

执行以下语句创建用户

create user <Backup User Name> password "<Password>";
alter user <Backup User Name> DISABLE PASSWORD LIFETIME;
grant backup admin to <Backup User Name>;

使用hdbuserstore加密用户名和密码

hdbuserstore set USER4BACKUP localhost:3<Instance Number>15 <Backup User Name> <Password>

#####创建自动备份脚本backup.sh

change to PIDadm user

#!/bin/sh
#/* Backup script */
source $HOME/.bashrc
date="$(date +"%Y-%m-%d")"
time="$(date +"%Y-%m-%d %H:%M:%S")"
backup="backup data using file ('$date')"
echo "daily backup  :"  $time  "Start" >> $DIR_INSTANCE/backup.log
$DIR_EXECUTABLE/hdbsql -U USER4BACKUP -x $backup 
time="$(date +"%Y-%m-%d %H:%M:%S")"
echo "daily backup  :"  $time  "End" >> $DIR_INSTANCE/backup.log
time="$(date +"%Y-%m-%d %H:%M:%S")"

#####将脚本加入cronjob

30 05 * * * /usr/bin/sh /usr/sap/NDB/HDB00/BackupScript.sh 2>>  /usr/sap/NDB/HDB00/backup.log

至此，HANA的自动备份完成，如果读者想做一些扩展，基于以上描述也是非常简单，我们项目是在备份之后，同步到远程存储

######备份自动清理

由于HANA官方对于自动清理只支持基于backupID，所以首先要查询满足条件的backupID，然后进行脚本删除

获取backupID的sql如下：

"select top 1 REPLACE(BACKUP_ID,',','') as A from M_BACKUP_CATALOG where ENTRY_TYPE_NAME='complete data backup' and UTC_START_TIME <= ADD_DAYS(CURRENT_UTCDATE,-7) order by UTC_END_TIME desc"

经过shell处理后的脚本如下：

sql="select top 1 REPLACE(BACKUP_ID,',','') as A from M_BACKUP_CATALOG where ENTRY_TYPE_NAME='complete data backup' and UTC_START_TIME <= ADD_DAYS(CURRENT_UTCDATE,-7) order by UTC_END_TIME desc"
backupid=`$DIR_EXECUTABLE/hdbsql -U USER4BACKUP  -x $sql | grep \" | sed -e 's/\"//g'`
echo $backupid

获取了$backupid之后，就很容易获取清理数据了

if [ -z $backupid ]
then
        echo "backupid not found"
else
        sql="BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $backupid COMPLETE"
        $DIR_EXECUTABLE/hdbsql -U USER4BACKUP -x $sql
fi