#!/bin/bash
#定义数据库连接字段
db_user="root"
db_passwd="root"
db_host="127.0.0.1"
db_port="3306"
#定义备份目录,备份时间及用到的命令
backup_dir="/home/bak_mysql"
date=`date +%Y-%m-%d`
#删除旧的备份数据
find $backup_dir -name "mysql_*" -type d -ctime +7 |xargs rm -rf;
#检测备份目录是否存在,不存在则建立目录
test ! -d "$backup_dir/mysql_$date" && mkdir "$backup_dir/mysql_$date/"
#相关命令
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
#列出所有数据库
all_db="$($MYSQL -u $db_user -h $db_host -P $db_port -p$db_passwd -Bse 'show databases')"
#循环备份数据库
for db in $all_db
do
$MYSQLDUMP -u $db_user -h$db_host -P $db_port -p$db_passwd $db --skip-lock-tables | $GZIP -9 > $backup_dir/mysql_$date/$db.gz
done
#copy到远程服务器配置
remote_backup_dir="/home/bak_mysql_all/"`hostname`
#定义远程连接说明
remote_user="root"
#ssh-keygen -t rsa
#cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys
remote_host="1.1.1.1"
remote_port="22"
#删除旧的备份数据
COMMOND="ssh -p $remote_port $remote_user@$remote_host 'find $remote_backup_dir -name \"mysql_*\" -type d -ctime +7 <span style="font-family: Arial, Helvetica, sans-serif;">|xargs rm -rf</span><span style="font-family: Arial, Helvetica, sans-serif;">'"</span>
eval $COMMOND
#检测备份目录是否存在,不存在则建立目录
COMMOND="ssh -p $remote_port $remote_user@$remote_host 'test ! -d \"$remote_backup_dir \" && mkdir -p \"$remote_backup_dir/ \"'"
eval $COMMOND
COMMOND="scp -P $remote_port -r $backup_dir/mysql_$date $remote_user@$remote_host:$remote_backup_dir"
eval $COMMOND