linux unix mysql 数据库自动备份

文章给大家介绍在linux unix中利用shell命令实现mysql 数据库自动备份脚本代码,有需要了解的朋友只需简单修改一下即可使用了.

文件名 mysql_data_backup.sh (需要执行权限)

mysql 数据库表自动备份shell 脚本 (第二版)

调用示例,代码如下:mysql_data_backup.sh

包含功能:

1.生成备份日志

2.自动检测指定数据库中的库列表,单独备份每个库,最后打包为tar文件

3.可指定备份存档文件(.tar) 保留的数目, 按时间正序清理以前的存档

4.恢复时可以登录 mysql 以后直接用 source xxx.sql 自动删除和创建数据库,并还原数据.

需要修改的变量,代码如下:

1. db_host//数据库主机
2. db_user//数据库帐号
3. db_passwd//数据库密码
4. backupFileNum//保留的备份存档文件数目,超出该数目自动清理
5. backupDir//备份文件存放的目录
6. logfile//日志文件路径

如何恢复数据:

参见 mysql 数据库表自动备份shell 脚本,第一版,代码如下:

1. #!/bin/bash
2. #mysqlinkuserconfig
3. db_host=192.168.0.222
4. db_user=admin
5. db_passwd=123456
6. #backupFilenumber
7. backupFileNum=4
9. #logfile
10. logfile='/sqlbackup/backup.log'
11. #backupofdirectoryDonotbring/
12. backupDir="/sqlbackup"
15. #checkdatabasename
16. echo'Backupbegin'
17. begin_time=`date'+%s'`
19. #getdatabaseslist
20. databases_content=`mysqlshow-u$db_user-p$db_passwd-h$db_host|sed's/|//g'|sed's///g'|sed'1,4d'|sed'$d'`
21. #checkdatabasesifemptyedthenexit
22. databases_content_check=`echo-n$databases_content>/tmp/mysql_backup_check.tmp`
23. if[!-s/tmp/mysql_backup_check.tmp];then
24. echo'DatabaseEmptyedBackupstop.'
25. exit
26. fi
28. #planbackupofsqldatabases
29. databases=(${databases_content})
30. #getdatabaseslength
31. databases_len=${#databases[*]}
33. #backupofdirectoryfordate
34. datestamp=$(date"+%Y%m%d")
36. #finalbackupdirectroy
37. fileDir="$backupDir/$datestamp"
39. #autocreatetargetdirectory
40. if[!-d$backupDir];then
41. mkdir$backupDir
42. fi
43. if[!-d$fileDir];then
44. mkdir$fileDir
45. fi
46. if[!-d$logfile];then
47. touch$logfile
48. fi
50. echo"total$databases_lendatabases."
52. for((i=0;i<$databases_len;i++))
53. do
54. dbName="${databases[$i]}"
55. fileName="${databases[$i]}.sql"
56. filePath="$fileDir/$fileName"
57. echo"Database[$dbName]Backup..."
58. echo"DROPDATABASEIFEXISTS`$dbName`;">$filePath
59. echo"CREATEDATABASE`$dbName`CHARACTERSET`utf8`;">>$filePath
60. echo"use$dbName;">>$filePath
61. mysqldump-h$db_host-u$db_user-p$db_passwd$dbName>>$filePath
62. done
66. #tarfiles
67. cd$backupDir
68. tarcvf${datestamp}.tar./${datestamp}
69. #deletesourcearchive
70. rm-rf./${datestamp}
71. end_time=`date'+%s'`
72. total_time=$[end_time-begin_time]
73. echo'BackupDone'
74. echo"Totaltime:${total_time}second"
76. now_date=`date'+%Y-%m-%d%k:%M:%S'`
77. #savelog
78. echo"${now_date}:BackupDatabases:Totaltime[${total_time}s]">>$logfile
81. #begincleanexcessofbackupfile
83. #countbackupdirnameoflength;
84. backupDir_length=`exprlength"$backupDir"`
85. #gettarfilelist
86. backupFile_list=`find$backupDir-name"*.tar"|sort-n-r-k1.$backupDir_length`
87. #setarray
88. backupFiles=(${backupFile_list})
89. backupFile_length=${#backupFiles[*]}
90. if[$backupFile_length-gt$backupFileNum];then
91. for((i=$backupFile_length;i>$backupFileNum;i--))
92. do
93. fileName="${backupFiles[$i-1]}"
94. rm-rf$fileName
95. #savelog
96. echo"file:$fileNamedeleted"
97. echo"file:$fileNamedeleted">>$logfile
98. done--phpfensi.com
99. else
100. echo"backupfilenumbernormal."
101. fi