MySQL数据库自动备份脚本

编写数据库备份脚本(mysqlfullbackup.sh)

#数据库备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
#!/bin/sh
###############################
# 此脚本用来全量备份
# 此文件名:mysqlfullbackup.sh
# vim /etc/crontab
# 定时任务,计划在每天晚上23点55分进行数据备份(加 & 为后台执行)
# 55 23 * * * root /backup/mysql/full_backup/mysqlfullbackup.sh &
# # /etc/rc.d/init.d/crond restart
###############################

#备份数据库(多数据库,用逗号隔开)
mysql_databases="test"

#设置用户名和密码
mysql_user="root"
mysql_password="123456"

#mysql安装全路径
mysql_dir=/usr/local/mysql-5.6.38

#设置备份基本路径
backup_dir=/backup/mysql

#创建备份文件夹
[ -d ${backup_dir} ] || mkdir -p ${backup_dir}

full_backup_dir=$backup_dir/full_backup

#备份的时间
today=$(date +"%Y%m%d_%H%M%S")

#备份日志文件
log_file=full_backup.log

#只保留最近10天的备份(其他删除)
time=$(date -d "10 day ago" +"%Y-%m-%d %H:%M:%S")

#开始备份,记录备份开始时间 并压缩备份文件
echo -e '['$(date +"%Y-%m-%d %H:%M:%S")'] - '$mysql_databases' - '"备份开始\n" >> $backup_dir/$log_file

#判断目标目录是否已经存在
if [ ! -d $full_backup_dir ]; then
mkdir -p $full_backup_dir
fi

echo -e '['$(date +"%Y-%m-%d %H:%M:%S")'] - '$mysql_databases' - '"备份并压缩备份文件\n" >> $backup_dir/$log_file

#备份并压缩备份文件
$mysql_dir/bin/mysqldump -u$mysql_user -p$mysql_password --databases $mysql_databases | gzip > $full_backup_dir/$mysql_databases.$today.sql.gz


[ $? -eq 0 ] && echo -e '['$(date +"%Y-%m-%d %H:%M:%S")'] - 'All DB' - '"备份并压缩备份文件 OK!!!\n" >> $backup_dir/$log_file

#找出10天前备份的文件,然后删除
echo -e '['$(date +"%Y-%m-%d %H:%M:%S")'] - '$mysql_databases' - '"删除10天前的备份\n" >> $backup_dir/$log_file
for file in `find -not -type d -mtime +10`
do
rm -rf $file
done

#基于ssh key认证,mysqldump的远程mysql备份方案
copytoremote=1
remoteuser="root"
remoteip="xx.xx.xxx.xx"
if [ $copytoremote = 1 ]; then
if [ -z $remoteuser ];then
echo -e "remote user not set,Copy to remote Failed ...\n" >> $log_file
exit 1
elif [ -z $remoteip ];then
echo -e "remote ip not set,Copy to remote Failed ...\n" >> $log_file
exit 2
else
echo -e '['$(date +"%Y-%m-%d %H:%M:%S")'] - '$mysql_databases' - '"开始使用SSH备份数据文件到远程服务器\n" >> $backup_dir/$log_file
scp $mysql_databases.$today".sql.gz" $remoteuser@$remoteip:$full_backup_dir
fi
fi

echo -e '['$(date +"%Y-%m-%d %H:%M:%S")'] - '$mysql_databases' - '"备份完成\n" >> $backup_dir/$log_file

给脚本添加执行权限

1
chmod +x /backup/mysql/full_backup/mysqlfullbackup.sh

定时任务设置

修改/etc/crontab #添加计划任务

定时任务,计划在每天晚上23点55分进行数据备份(加 & 为后台执行)

1
2
3
4
5
6
7
vim /etc/crontab 

#在最后一行添加以下内容
55 23 * * * root /backup/mysql/full_backup/mysqlfullbackup.sh &

#重启定时任务
/etc/rc.d/init.d/crond restart

设置免密SSH拷贝备份到远程服务器

设置远程无密码拷贝

数据库服务器上执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
ssh-keygen -t rsa
#输出,连续回车
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
60:4d:8e:08:6d:cf:a4:5c:7f:b8:2a:29:62:ce:cf:a4 root@iZ2ze7vlqunx6jumrds0v4Z
The key's randomart image is:
+--[ RSA 2048]----+
| .. . |
| .o.o= |
| o.*+.o. |
| o.o.o . |
| So |
| . |
| . . . |
|o.= o . |
|oE.+ . |
+-----------------+

会在用户目录~/.ssh/产生两个文件,id_rsa,id_rsa.pub

1
2
[root@iZ2ze7vlqunx6jumrds0v4Z .ssh]# ls
id_rsa id_rsa.pub

将id_rsa.pub文件拷贝到远程主机的root用户主目录下的.ssh目录下,并且改名为authorized_keys

这样数据库服务器上使用scp命令复制文件到远程主机上将不提示输入密码了,直接自动复制了

说明:当远程主机的SSH远程端口修改后,SCP指令要加上端口号:

1
scp -p 端口号 $mysql_databases.$today".sql.gz" $remoteuser@$remoteip:$full_backup_dir