shell实现远程mysqlDUMP数据库备份

说明：
本文实现利用shell实现远程mysql数据库备份。通过在数据服务器mysqldump导出需要备份的数据库，scp拷贝到指定的备份服务器。为了不需要输入密码就能直接scp，这里，会先生成ssh key。

数据库服务器为A，用来远程存放备份的服务器为B

实现：
1.生成ssh key认证，避免SSH输入密码
openssh在A服务器用openssh生成一堆密钥

# ssh-keygen -d  //passphrase一定留空，否则又要输入密码。会生成~/.ssh/id_rsa 和 ~/.ssh/id_rsa.pub文件
# chmod 600 ~/.ssh/id_rsa  //设置属性

把~/.ssh/id_rsa.pub文件的内容复制后添加到B服务器用户目录~/.ssh/authorized文件里，设置属性
# chmod 600 ~/.ssh/authorized

 
puttygen在A服务器生成密钥对

菜单｜Conversions｜Export OpenSSH Key  //导出OpenSSH格式的私钥，保存为id_rsa，放到A服务器用户目录~/.ssh/下
# chmod 600 ~/.ssh/id_rsa  //设置属性
公钥在puttygen最上面那一堆最长的字符串就是，复制后添加到B服务器用户目录~/.ssh/authorized文件里，同样设置属性
# chmod 600 ~/.ssh/authorized

 
2.在数据库服务器A新建sql_backup.sh，用于mysqldump导出要备份的数据库，scp到备份服务器上
# vim sql_bakup.sh //添加以下内容

#!/bin/bash
sqlfile=$(date -d yesterday "+%Y%m%d")".sql"  #指定要拷贝的数据库文件名，以日期(减一天)命名
/usr/local/webserver/mysql/bin/mysqldump -u***** -p*** test > $sqlfile

gzip $sqlfile  #压缩，对于大数据库非常必要
scp $sqlfile".gz" root@192.168.0.2:/www/backup/mysql/
echo `date -d yesterday "+%Y%m%d"` succ >> /root/shell/sum.txt  #将备份操作记录到sum.txt中去

mysql_backup.sh还有一种写法，写得更紧凑。实现本地不保存，直接备份到B服务器。mysqldump -udbuser -pdbpass dbname|gzip -c|ssh remoteuser@remoteip ‘cat > ~/’`date “+%Y%m%d%H%M%S”`’.sql.gz’其中dbuser是数据库用户名,dbpass是数据库密码,dbname是数据库名,remoteuser是远程B服务器的用户名,remoteip是远程B服务器的IP

如果本地不保存，直接备份到B服务器

#!/bin/bash
BDATE=`date +%Y%m%d%H%M%S`
BPATH=/home/zqdba/backupdumpmysql
LOGFILE=${BPATH}/log_${BDATE}.log
REMOTE_IP=192.168.8.3
/home/mysql8.0.16/mysql/bin/mysqldump -h192.168.11.253  -uroot -p'123456' -P 3307  --single-transaction --default-character-set=utf8  --all-databases |gzip -c|ssh $REMOTE_IP  'cat >/home/quant_group/mysql/192.168.11.253/'full-`date "+%Y%m%d%H%M%S"`'.sql.gz'
echo > ${LOGFILE}

 

#!/bin/bash
mysqldump -udbuser -pdbpass dbname|gzip -c|ssh remoteuser@remoteip 'cat > ~/'`date "+%Y%m%d%H%M%S"`'.sql.gz'

其中dbuser是数据库用户名,dbpass是数据库密码,dbname是数据库名,remoteuser是远程B服务器的用户名,remoteip是远程B服务器的IP

#!/bin/bash

backup_time=`date +%Y_%m_%d_%H`
current_ip="${要备份的机器ip}"
remote_ip="${远程机器ip}"
remote_path="/home/quant_group/mysql/${current_ip}/full/${backup_time}.tar"
mysql_host="--host=${current_ip}"
mysql_user="--user=${备份的用户名}"
mysql_pass="--password=${备份的密码}"
mysql_port="--port=3306"
defaults_file="--defaults-file=/home/quant_group/mysql/3306/my.cnf"

mysqldump -udbuser -pdbpass dbname | gzip | sshpass -p '' ssh root@${remote_ip}  "cat - > ${remote_path}"

3.数据库服务器A上编辑crontab，让mysql_backup脚本定期执行
# crontab -e //每天0点备份昨天A的test数据库到B的/www/backup/mysql

00 00 * * * /bin/bash   /root/shell/sql_bakup.sh 

 
4.还原数据库

# gzid -d 20121127.sql.gz  #解压
# mysql -u**** -p*** -S /tmp/mysql
> use test   #没有就新建个数据库
> source /www/backup/mysql/20121127.sql