Skip to content


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

mysql+ftp备份

https://blog.c1gstudio.com/archives/13

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

在A上运行命令:

#cd ~

ssh-keygen -t rsa (连续三次回车,即在本地生成了公钥和私钥,不设置密码)

ssh -p 22 [email protected] ‘mkdir .ssh’ (建立目录,需要输入密码,只需一次)

scp -P 22 ~/.ssh/id_rsa.pub [email protected]:.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行,然后再试。 =========================================================

2008-11-11 用scponly创建一个chroot环境的sftp 在B上用root登录 以backup1为例,通过一临时用户拷贝 #mkdir /home/backup1/.ssh #touch /home/backup1/.ssh/authorized_keys #chown -R backup1:backup1 /home/backup1/.ssh #cat /home/backup/.ssh/id_rsa.pub >> /home/backup1/.ssh/authorized_keys

========================================================= 

2009-08-19 chmod 0700 /home/backup1/.ssh chmod 0600 /home/backup1/.ssh/authorized_keys 记得权限要保持一致,否则还是需要输入密码…

=========================================================

========================================================= 2010-3-15更新 A机

ssh-keygen -t rsa (连续三次回车,即在本地生成了公钥和私钥,不设置密码)

ssh-copy-id -i ~/.ssh/id_rsa.pub “-p 22 [email protected]

#有chroot环境的需先复制到临时用户,再移过去,参考上面步骤

=========================================================

在B上用backup用户的命令: #cd ~

touch .ssh/authorized_keys (如果已经存在这个文件, 跳过这条)

cat .ssh/id_rsa.pub >> .ssh/authorized_keys (将id_rsa.pub的内容追加到authorized_keys 中)

回到A机器:

ssh backup@192.168.60.110 (不需要密码, 登录成功)

在a机上建立放脚本的目录

cd /opt/lampp

mkdir shell

copy文件到上面目录

chmod +x mysqlbackup.sh

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

cd /home/backup

mkdir mysqlbackup && chown root:website mysqlbackup && chmod 0666 mysqlbackup

crontab 设置,每天凌晨3点执行

crontab -e

0 3 * /bin/sh /opt/lampp/shell/mysqlbackup.sh

发下为脚本mysqlbackup.sh

 

#!/bin/bash #每天备份mysql数据 #保留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 ${sshuser}@${sshhost}:${remotefolder}/>>${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’

Posted in Mysql, shell, 备份.

Tagged with , , , , .


No Responses (yet)

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.



Some HTML is OK

or, reply to this post via trackback.