使用crontab+ssh每天自动完全备份mysql数据

自动ssh/scp方法配置
A为本地主机(即用于控制其他主机的机器) ;
B为远程主机(即被控制的机器Server), 假如ip为192.168.60.110;
A和B的系统都是Linux

在A上运行命令:

#cd ~
# ssh-keygen -t rsa (连续三次回车,即在本地生成了公钥和私钥,不设置密码)
# “mkdir .ssh” (建立目录，需要输入密码，只需一次)
# scp ~/.ssh/id_rsa.pub backup:.ssh/id_rsa.pub (copy本地公钥到B，需要输入密码)

 ===============================================
08-07-03改动
当B机重装或改动时可能遇到下面状况
可能遇到的问题：

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that the RSA host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
be:5f:d2:45:66:4d:0c:9e:2b:6b:45:65:a7:b2:85:28.
Please contact your system administrator.
Add correct host key in /root/.ssh/known_hosts to get rid of this message.
Offending key in /root/.ssh/known_hosts:11
RSA host key for localhost has changed and you have requested strict checking.
Host key verification failed.

如上问题，请删除 ~/.ssh/known_hosts指定的行，如上面的第11行，然后再试。
=========================================================
 

在B上用backup用户的命令:
#cd ~
# touch .ssh/authorized_keys (如果已经存在这个, 跳过这条)
# cat .ssh/id_rsa.pub >> .ssh/authorized_keys (将id_rsa.pub的内容追加到authorized_keys 中)

回到A机器:
# ssh backup(不需要密码, 登录成功)

在a机上建立放的目录

# cd /opt/lampp
# mkdir

copy文件到上面目录

# chmod +x mysqlbackup.sh

在本地/home/backup下建立本地目录

# cd /home/backup
# mkdir mysqlbackup && chown :website mysqlbackup && chmod 0666 mysqlbackup

设置,每天凌晨3点执行

# crontab -e
0 3 * * * /bin/sh /opt/lampp/shell/mysqlbackup.sh

发下为脚本mysqlbackup.sh

#!/bin/bash
#每天备份数据
#保留3天的备份包

#需备份的服务器名
server=test

#FTP主机
ftphost=’202.100.222.2′
#FTP用户名
ftpusername=test
#FTP密码
ftppassword=test

#ssh主机
sshhost=’202.100.222.2′
#ssh用户名
sshuser=backup
#备份存放目录
remotefolder=backup

#备份文件存放的目录
backuppath=/home/user/mysqlbackup
#备份文件名
file=${server}-mysql-$(date +%Y-%m-%d).tar.gz
#需备份的目录
sourcepath=/opt/lampp/var/mysql
#备份日志
logfile=${backuppath}/mysqlbackup.log

function Backup() #处理涵数
{
/bin/tar -czf ${backuppath}/${file} –exclude=${sourcepath}/mysql.sock ${sourcepath} #执行备份操作
echo -e $(date +%Y-%m-%d_%H:%M:%S)’:tar:’${backuppath}/${file} ${sourcepath}”\r” >>${logfile}

}

function Send()
{
#ssh,使用前请先做好配置
/usr/bin/rsync -av ${backuppath}/${file} -e /usr/bin/ssh>>${logfile}

#cd $backuppath
#ftp -i -n open $ftphost user $ftpusername $ftppassword
#if [ ! [ -d BACKUP/$server ]]; then
#mkdir BACKUP/$server #如目录不存在则创建
#fi
#cd BACKUP/$server
#put $file #上传文件
#bye #退出
##!
}

echo -e ‘——————\r’ >>${logfile}
/bin/chown local:website ${logfile} && /bin/chmod 0666 ${logfile}
echo -e $(date +%Y-%m-%d_%H:%M:%S)”:server “${server}” beginning\r” >>${logfile}
/bin/rm -rf ${backuppath}/${serve}r-mysql-$(date +%Y-%m-%d –date=’3 days ago’).tar.gz #删除3天前的压缩文件
echo -e $(date +%Y-%m-%d_%H:%M:%S)”:delete backup file\r” >>$logfile
echo -e $(date +%Y-%m-%d_%H:%M:%S)”stop mysql\r” >>$logfile
/opt/lampp/lampp stopmysql >/dev/null 2>&1 #停止Mysql服务
Backup #调用处理涵数
/opt/lampp/lampp startmysql >/dev/null 2>&1 #启动Mysql服务
echo -e “\r\n”$(date +%Y-%m-%d_%H:%M:%S)”:start mysql\r” >>$logfile
Send

echo -e $(date +%Y-%m-%d_%H:%M:%S)”:mysql backup ok\r” >>$logfile
echo ‘ok’