记住用户名密码
1:定义备份的数据库
2:使用mysqldump备份
3:压缩备份sql脚本
4:删除10天前的备份数据
5:通过rsync同步到备份服务器
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
#!/bin/bash # mysql_backup.sh: backup mysql databases and keep newest 5 days backup. # # ${db_user} is mysql username # ${db_password} is mysql password # ${db_host} is mysql host # ${db_port} is mysql port # —————————– #/root/mysql_backup.sh # everyday 3:00 AM execute database backup # 0 3 * * * /root/mysql_backup.sh #/etc/cron.daily db_user= "root" db_password= "123456" db_host= "47.106.111.110" db_port= "3306" # the directory for story your backup file. # backup_dir= "/Host/backup/mysql/" # 要备份的数据库名 # #all_db= "$(${mysql} -u ${db_user} -h ${db_host} -p${db_password} -Bse 'show databases')" # all_db= "test" # 要保留的备份天数 # backup_day=10 #数据库备份日志文件存储的路径 logfile= "/Host/backup/mysql_backup.log" ###ssh端口号### ssh_port=22 ###定义ssh auto key 的文件### id_rsa=/root/auth_key/id_rsa_153.141.rsa ###定义ssh auto username### id_rsa_user=rsync ###定义要同步的远程服务器的目录路径(必须是绝对路径)### clientPath= "/home/backup/mysql" ###定义要镜像的本地文件目录路径 源服务器(必须是绝对路径)### serverPath=${backup_dir} ###定义生产环境的ip### web_ip= "192.168.0.2" # date format for backup file (dd-mm-yyyy) # time = "$(date +" %Y-%m-%d ")" # mysql, ${mysqldump} and some other bin 's path # mysql="/usr/bin/mysql " mysqldump="/usr/bin/mysqldump" # the directory for story the newest backup # test ! -d ${backup_dir} && mkdir -p ${backup_dir} #备份数据库函数# mysql_backup() { # 取所有的数据库名 # for db in ${all_db} do echo "开始备份: "${db} backname=${db}.${time} dumpfile=${backup_dir}${backname} #将备份的时间、数据库名存入日志 echo "------"$(date +' %Y-%m-%d %T ')" Beginning database "${db}" backup--------" >>${logfile} ${mysqldump} -F -u${db_user} -h${db_host} -P${db_port} -p${db_password} ${db} > ${dumpfile}.sql 2>>${logfile} 2>&1 #开始将压缩数据日志写入log echo $(date +' %Y-%m-%d %T ')" Beginning zip ${dumpfile}.sql" >>${logfile} #将备份数据库文件库压成ZIP文件,并删除先前的SQL文件. # tar -czvf ${backname}.tar.gz ${backname}.sql 2>&1 && rm ${dumpfile}.sql 2>>${logfile} 2>&1 #将压缩后的文件名存入日志。 echo "backup file name:"${dumpfile}".tar.gz" >>${logfile} echo -e "-------"$(date +' %Y-%m-%d %T ')" Ending database "${db}" backup-------\n" >>${logfile} done } delete_old_backup() { echo "delete backup file:" >>${logfile} # 删除旧的备份 查找出当前目录下七天前生成的文件,并将之删除 find ${backup_dir} -type f -mtime +${backup_day} | tee delete_list.log | xargs rm -rf cat delete_list.log >>${logfile} } rsync_mysql_backup() { # rsync 同步到其他Server中 # for j in ${web_ip} do echo "mysql_backup_rsync to ${j} begin at "$(date +' %Y-%m-%d %T ') >>${logfile} ### 同步 ### rsync -avz --progress --delete $serverPath -e "ssh -p "${ssh_port}" -i "${id_rsa} ${id_rsa_user}@${j}:$clientPath >>${logfile} 2>&1 echo "mysql_backup_rsync to ${j} done at "$(date +' %Y-%m-%d %T ') >>${logfile} done } #进入数据库备份文件目录 cd ${backup_dir} mysql_backup delete_old_backup # rsync_mysql_backup echo -e "========================mysql backup && rsync done at "$(date +' %Y-%m-%d %T') "============================\n\n" >>${logfile} cat ${logfile} |
1
|
0 3 * * * /home/tioncico/mysql_backup .sh |
每天3点0分进行备份
目前有 0 条留言 其中:访客:0 条, 博主:0 条