mysql异地备份

两台服务器需要单向免密

ssh-keygen -t rsa
然后一直回车 
cd /root/.ssh
ssh-copy-id -i id_rsa.pub user@ip(对方ip和user)

数据库的异地备份

#!/bin/bash

Date=$(date +%Y%m%d)
Time=$(date +%Y-%m-%d\ %H:%M)

## mysql info
mysql_user='root'
mysql_passwd='mysql_passwd'
dbname='dbname'
tbname='tbl_user_info'

## ip info
user='root'
ip='ip'

## tmp dump dir
tmp_dir="/tmp/$Date$RANDOM/"
SQL_file=$tmp_dir$tbname\_$Date.sql
mkdir -p $tmp_dir

## backup dir
Dir="/tmp/pjl_test"

## save strategy script name 
script_name="save_strategy.sh"

## backup  table 
mysqldump -u$mysql_user -p$mysql_passwd $dbname $tbname  >$SQL_file  2>/dev/null

## scp the table
scp $SQL_file $user@$ip:$Dir >/dev/null 2>&1

if [[ $? -eq 0 ]];then
   echo -e " $Time\n mysql table $tbname Backup Succeeded"
else
   echo -e " $Time\n mysql table $tbname Backup Failed"
fi

rm -rf $tmp_dir

## run the script
ssh $user@$ip "cd $Dir;bash $script_name" >/dev/null 2>&1
if [[ $? -eq 0 ]];then
   echo -e " Run $script_name Succeeded \n"
else
   echo -e " Run $script_name Failed \n" 
fi

数据保留一个月
save_strategy.sh

#!/bin/bash

Date=$(date  +%Y%m%d --date '1 month ago')

for i in `find . -name "*.sql"`;do
    T=$(echo $i |awk -F '_' '{print $4}' |awk -F '.' '{print $1}')
    if [[ $Date -gt $T ]];then
       rm -rf $i  > /dev/null 2>&1
    fi
done