Linux 使用crontab完成mysql定时三中心备份

通过expect实现 

vi install.sh

#!/bin/bash
typeset DIR="$( cd "$( dirname "$0"  )" && pwd  )"

#标识
INDEX='DEMO'
#数据库用户名
DB_USER='root'
#数据库密码
DB_PASSWD='123456'

#服务器备份目录（提前创建）
DATA_PATH='/home/mysql/backup'

#是否开启远程备份 open:1  
SCP_BACK='1'
SCP_IP='10.1.1.10'
SCP_USER='root'
SCP_PWD='123456'

#是否开启异地备份 open:1  
REMOTE_BACK='1'
REMOTE_IP='10.1.1.10'
REMOTE_USER='root'
REMOTE_PWD='123456'
#目标服务器备份目录（提前创建）
SCP_PATH='/home/back/'

sed -i s/index_str/$INDEX/g ${DIR}/mysqldump.sh
sed -i s/db_user_str/$DB_USER/g ${DIR}/mysqldump.sh
sed -i s/db_pwd_str/$DB_PASSWD/g ${DIR}/mysqldump.sh
sed -i s#db_data_path_str#$DATA_PATH#g ${DIR}/mysqldump.sh


sed -i s/scp_back_str/$SCP_BACK/g ${DIR}/mysqldump.sh
sed -i s/scp_ip_str/$SCP_IP/g ${DIR}/mysqldump.sh
sed -i s/scp_user_str/$SCP_USER/g ${DIR}/mysqldump.sh
sed -i s#scp_pwd_str#$SCP_PWD#g ${DIR}/mysqldump.sh

sed -i s/remote_back_str/$REMOTE_BACK/g ${DIR}/mysqldump.sh
sed -i s/remote_ip_str/$REMOTE_IP/g ${DIR}/mysqldump.sh
sed -i s/remote_user_str/$REMOTE_USER/g ${DIR}/mysqldump.sh
sed -i s#remote_pwd_str#$REMOTE_PWD#g ${DIR}/mysqldump.sh
sed -i s#remote_path_str#$SCP_PATH#g ${DIR}/mysqldump.sh

echo "mysql配置完成"

sed -i s#dir_str#${DIR}#g ${DIR}/mysqlcron.cron
echo "cron配置完成"
crontab mysqlcron.cron
crontab -l

 

vi mysqldump.sh

#!/bin/bash

#标识
INDEX='index_str'
#数据库用户名
DB_USER='db_user_str'
#数据库密码
DB_PASSWD='db_pwd_str'
#备份时间
CURRENT_BACKUP_TIME=`date +%Y%m%d%H%M%S`
#数据备份路径
DATA_PATH='db_data_path_str'
#开启异地备份
SCP_BACK='scp_back_str'
SCP_PATH='/home/back/'
typeset DIR="$( cd "$( dirname "$0"  )" && pwd  )"

#正式备份数据库
echo "------------------------------------------------------------------" >> ${DIR}/back.log
echo "----------------------------START---------------------------------" >> ${DIR}/back.log
echo "------------------------$CURRENT_BACKUP_TIME----------------------------" >> ${DIR}/back.log

echo "开始本地备份" >> ${DIR}/back.log
source=`mysqldump  -u$DB_USER -p$DB_PASSWD -A > $DATA_PATH/$INDEX-$CURRENT_BACKUP_TIME.sql` >> ${DIR}/back.log;
#备份成功以下操作
if [ "$?" == 0 ];then
  echo "本地备份完成" >> ${DIR}/back.log
  cd $DATA_PATH
  echo "开始压缩备份" >> ${DIR}/back.log
  #为节约硬盘空间，将数据库压缩
  tar zcf $INDEX-$CURRENT_BACKUP_TIME.tar.gz $INDEX-$CURRENT_BACKUP_TIME.sql > /dev/null
  #删除原始文件，只留压缩后文件
  echo "开始清理备份" >> ${DIR}/back.log
  rm -f $DATA_PATH/$INDEX-$CURRENT_BACKUP_TIME.sql
  #删除三十天之前的备份
  find $DATA_PATH -name "${INDEX}-*.tar.gz" -type f -mtime +30 -exec rm -rf {} \; 
  #开始远程备份
  echo "开始同城备份$CURRENT_BACKUP_TIME" >> ${DIR}/back.log
  if [ "X${SCP_BACK}" == "X1" ];then
  /usr/bin/expect <<EOF >> ${DIR}/back.log
	set timeout -1
	spawn scp $DATA_PATH/$INDEX-$CURRENT_BACKUP_TIME.tar.gz scp_user_str@scp_ip_str:$SCP_PATH
	expect {
		"*yes/no"
		{
			send "yes\n";
			exp_continue
		}
		"*password:"
		{
			send "scp_pwd_str\n"
		}
	}
	expect eof
EOF
  fi
  echo "开始异地备份$CURRENT_BACKUP_TIME" >> ${DIR}/back.log
  if [ "X${REMOTE_BACK}" == "X1" ];then
  /usr/bin/expect <<EOF >> ${DIR}/back.log
	set timeout -1
	spawn scp $DATA_PATH/$INDEX-$CURRENT_BACKUP_TIME.tar.gz remote_user_str@remote_ip_str:$SCP_PATH
	expect {
		"*yes/no"
		{
			send "yes\n";
			exp_continue
		}
		"*password:"
		{
			send "remote_pwd_str\n"
		}
	}
	expect eof
EOF
  fi
fi
echo "-----------------------------END----------------------------------" >> ${DIR}/back.log
echo "------------------------------------------------------------------" >> ${DIR}/back.log

vi mysqlcron.cron

0 5 * * * /bin/sh dir_str/mysqldump.sh