Linux上备份mysql数据库文件到备份服务器

1.Mysql备份所需的安装文件

首先安装gcc 建议yum安装 离线安装可能导致ssh无法连接
LinuxCentOS7版本离线安装gcc插件
1.下载gcc-rpm.tar.gz包，
2.上传服务器并解压： #tar -zxvf gcc-rpm.tar.gz
3.进入gcc-rpm目录安装 #cd gcc-rpm #rpm -Uvh *.rpm --nodeps --force
4.查看是否是否安装成功 #gcc -v #g++ -v
5.安装expect-5.43.0.tar和tcl8.4.11-src.tar

2.编写自动获取服务器密码脚本

#!/usr/local/src/expect-5.43/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 jyb-2019 

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
	}
}

3.编写备份数据库脚本

#!/bin/bash
# FileName:bakmysql.sh
# This is a ShellScript For Auto DB Backup
#
backupdir=/home/mysqlbak         #本服务器备份路径
dockerMysqlName=mymysql          #docker中mysql容器名
databaseName=hzz3            #备份的数据库名字
bakdatabaseName=xq_bak_hzz3    #备份的数据库全部表的文件名
mysqluser=root                   #正式服务器A的mysql用户名
mysqlpwd=ss1516                #正式服务器A的mysql密码
targetServerUser=root            #测试服务器B的用户名
targetServerIP=192.168.2.111              #测试服务器B的IP地址
targetpath=/home/mysqlbak        #测试服务器B备份路径
#targetpath=/d:/mysqlbak
time=` date +%Y%m%d%s `            #当天的日期年月日

#从数据库导出数据库备份文件
docker exec -i mymysql mysqldump  -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
/home/mysqlbak/scp.exp $backupdir/$bakdatabaseName$time.sql.gz $targetServerUser@$targetServerIP:$targetpath/$bakdatabaseName$time.sql.gz
echo $time+'send sqlbak'

4.将脚本加入crontab定时执行脚本

1.输入下面命令

crontab -e

2.需要定时执行的脚本代码

0 1 * * *  /usr/bin/sh /usr/local/src/backmysql_apolloconfigdb.sh >>/home/mysqlbak/logs/backmysql_apolloconfigdb.log
2 1 * * *  /usr/bin/sh /usr/local/src/backmysql_apolloportaldb.sh >>/home/mysqlbak/logs/backmysql_apolloportaldb.log
4 1 * * *  /usr/bin/sh /usr/local/src/backmysql_activiti.sh >>/home/mysqlbak/logs/backmysql_activiti.log
6 1 * * *  /usr/bin/sh /usr/local/src/backmysql_hzz3.sh >>/home/mysqlbak/logs/backmysql_hzz3.log
8 1 * * *  /usr/bin/sh /usr/local/src/backmysql_gateway.sh >>/home/mysqlbak/logs/backmysql_gateway.log
10 1 * * *  /usr/bin/sh /usr/local/src/backmysql_uuc.sh >>/home/mysqlbak/logs/backmysql_uuc.log
12 1 * * *  /usr/bin/sh /usr/local/src/backmysql_bigdata.sh >>/home/mysqlbak/logs/backmysql_bigdata.log
14 1 * * *  /usr/bin/sh /usr/local/src/backmysql_index.sh >>/home/mysqlbak/logs/backmysql_index.log
16 1 * * *  /usr/bin/sh /usr/local/src/backmysql_river.sh >>/home/mysqlbak/logs/backmysql_river.log
18 1 * * *  /usr/bin/sh /usr/local/src/backmysql_water.sh >>/home/mysqlbak/logs/backmysql_water.log
20 1 * * *  /usr/bin/sh /usr/local/src/backmysql_video.sh >>/home/mysqlbak/logs/backmysql_video.log

3.定时表达式解释

4.结果

1.主服务器

2.备份服务器