mysql数据库自动备份到另一台服务器

时间:2022-04-30 04:45:30

CentOS 6.7

服务器:192.168.1.112到192.168.1.111

主要分以下三步:

1.先配置免密登陆

ssh-keygen
ssh-copy-id 192.168.1.111


2.shell脚本如下

#!/bin/sh
# mysql_backup.sh: backup mysql databases and keep newest 5 days backup.
#
# Last updated: 2017年8月8日
# ----------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 伟岸
# mysq数据备份
# ----------------------------------------------------------------------
# mysql地址:/usr/local/mysql/
# mysql启动命令:service mysqld start
# -----------------------------


#declare
MKDIR="/bin/mkdir"
RM="/bin/rm"
MV="/bin/mv"
GZIP="/bin/gzip"


#file path
db_user="root"
db_passwd="root"
db_host="localhost"
# 备份目录
backup_dir="/home/mysql/mybackup"
# 获取当前时间
time="$(date +"%Y-%m-%d")"
# mysql, mysqldump and some other bin's path
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
# check the directory for store backup is writeable
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0


# 要备份数据库名称
db="test"
$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/$time.$db.gz"


#发送备份文件到另一台服务器上
scp -r $backup_dir/$time.$db.gz 192.168.1.111:/home/mysql/mysqlbackup/
exit 0;

3.写crontab脚本(不懂安装百度一下)