mysqldump数据库自动备份到另一台服务器的数据库中

两台服务器，一台正式服务器A，一台测试服务器B，现需要将正式服务器A中的数据库每天备份到测试服务器B。

1.从正式服务器A中导出需要备份的数据库

使用mysqldump指令导出数据库文件

/usr/sbin/mysqldump -u root -ppassword database | gzip > bakdatabaseName.sql.gz

password为数据库密码，database为需要备份的数据库名
2.将备份的数据库文件发送至测试服务器B

使用scp指令发送备份的数据库文件

scp bakdatabaseName.sql.gz root@测试服务器B的IP:/root/mysqlbak/bakdatabaseName.sql.gz

该操作需要输入测试服务器B的密码，所以通过另外一个expect脚本实现与终端的交互从而实现自动输入密码，expect需要安装。
3.通过expect脚本实现运行scp指令后自动输入密码

#!/usr/bin/expect
#FileName:scp.exp

set timeout 60
if { [llength $argv] < 2} {
    puts "Usage:"
    puts "$argv0 local_file remote_path"
    exit 1
}
set local_file [lindex $argv 0]
set remote_path [lindex $argv 1]
set passwd yourpassword
set passwderror 0
spawn scp $local_file $remote_path
expect {
    "*assword:*" {
        if { $passwderror == 1 } {
            puts "passwd is error"
            exit 2
        }
        set timeout 1000
        set passwderror 1
        send "$passwd\r"
        exp_continue
    }
    "*es/no)?*" {
        send "yes\r"
        exp_continue
    }
    timeout {
        puts "connect is timeout"
        exit 3
    }
}

只需将代码中的yourpassword改为测试服务器B的密码，配置完之后运行下面指令

/usr/sbin/scp.exp bakdatabaseName.sql.gz root@测试服务器B的IP:/root/mysqlbak/bakdatabaseName.sql.gz
完整代码

#!/bin/bash
# FileName:bakmysql.sh
# This is a ShellScript For Auto DB Backup
#
backupdir=/root/mysqlbak         #本服务器备份路径
mysql_bin_dir=/usr/sbin          #mysqldump的路径
databaseName=database            #备份的数据库名字
bakdatabaseName=database_bak_    #备份的数据库全部表的文件名
mysqluser=user                   #正式服务器A的mysql用户名
mysqlpwd=password                #正式服务器A的mysql密码
targetServerUser=root            #测试服务器B的用户名
targetServerIP=B_IP              #测试服务器B的IP地址
targetpath=/root/mysqlbak        #测试服务器B备份路径
time=` date +%Y%m%d `            #当天的日期年月日

#从数据库导出数据库备份文件
$mysql_bin_dir/mysqldump --socket=/tmp/mysql.sock -u $mysqluser -p$mysqlpwd $databaseName | gzip > $backupdir/$bakdatabaseName$time.sql.gz

#保留3天内的备份文件，时间可通过-mtime 后的 +2 修改
find $backupdir -name "$bakdatabaseName*.sql.gz" -type f -mtime +2 -exec rm -f {} \; > /dev/null 2>&1

#将备份文件发送到测试服务器B
/usr/sbin/scp.exp $backupdir/$bakdatabaseName$time.sql.gz $targetServerUser@$targetServerIP:$targetpath/$bakdatabaseName$time.sql.gz

将bakmysql.sh加入到正式服务器A的crontab定时任务中
4.将备份的数据库文件导入到测试服务器B的mysql中

gzip -dc /root/mysqlbak/bakdatabaseName.sql.gz | mysql -u user -ppasswd database

完整代码

#!/bin/bash
# FileName:updatemysql.sh
# This is a ShellScript For Auto DB Backup and Delete old Backup
#
backupdir=/root/mysqlbak           #测试服务器B数据库备份文件路径
mysql_bin_dir=/usr/sbin            #测试服务器B的mysql的安装路径
databaseName=database              #数据库名字
bakdatabaseName=database_bak_      #备份的数据库的文件名
mysqluser=user                     #测试服务器B的mysql用户名
mysqlpwd=passwd                    #测试服务器B的mysql密码
time=` date +%Y%m%d `              #当天的日期:年月日

#先删除前一天的数据库
$mysql_bin_dir/mysql -u $mysqluser -p$mysqlpwd -e "drop database $databaseName;"

#创建新的数据库
$mysql_bin_dir/mysql -u $mysqluser -p$mysqlpwd -e "create database $databaseName;"

#将备份的数据库文件导入到mysql中
gzip -dc $backupdir/$bakdatabaseName$time.sql.gz | $mysql_bin_dir/mysql -u $mysqluser -p$mysqlpwd $databaseName

#保留3天内的备份文件，时间可通过-mtime 后的 +2 修改
find $backupdir -name "*.sql.gz" -type f -mtime +2 -exec rm -f {} \; > /dev/null 2>&1

将updatemysql.sh加入到测试服务器B的crontab定时任务中，定时时间要比bakmysql.sh的定时时间晚一些
注意：

1. 正式服务器A的mysql版本要比测试服务器B的mysql版本高，否则导入会失败

2. 数据库备份文件导入时由于数据量很大，会出现连接超时

    需要修改mysql配置文件/etc/my.cnf

    将[mysqld]中的max_allowed_packet 修改大一点 如20M

    max_allowed_packet = 20M

    重启mysql

    service mysqld restart

    如果还是出现连接超时

    在/etc/my.cnf添加以下代码

    [mysqldump]
    quick
    max_allowed_packet = 16M