linux mysql 数据库自动备份的设置方法

在linux中实现mysql自动备份很简单,我们可以直接使用计划任务与与相关的任务来实现自动备份与恢复了,下面小编来给大家详细介绍一下具体过程.

mysql 数据库表自动备份shell 脚本,调用示例,代码如下:

1. mysql_data_backup.bash110www_cas
2. mysql_data_backup.bash112www_qiche
3. mysql_data_backup.bash112www_health

包含功能:

1.参数检测,2.生成备份日志,3.自动检测指定数据库中的表,单独备份每个表,最后打包为tar文件,4.单独备份每个表,方便恢复用,5 支持多个数据主机判断

以下为文件内容,代码如下:

1. #!/bin/bash
2. #checkhost
3. if[!$1];then
4. echo'usage:mysql_data_backup[hostID]dbname'
5. exit
6. else
7. if[$1-eq110];then
8. db_host=192.168.1.110
9. db_user=xxxx
10. db_passwd=xxx
11. elif[$1-eq112];then
12. db_host=192.168.1.112
13. db_user=xxx
14. db_passwd=xxx
15. else
16. echo'InvalidHostID'
17. exit
18. fi
19. fi
21. #checkdatabasename
22. if[!$2];then
23. echo'usage:mysql_data_backuphostID[dbname]'
24. exit
25. else
26. echo'Backupbegin'
27. fi
28. begin_time=`date'+%s'`
29. db_name=$2
31. #gettableinfo
32. tables_content=`mysqlshow-u$db_user-p$db_passwd-h$db_host$db_name|sed's/|//g'|sed's///g'|sed'1,4d'|sed'$d'`
33. #checkcontentifemptyed
34. tables_content_check=`echo-n$tables_content>/tmp/mysql_backup_check.tmp`
35. if[!-s/tmp/mysql_backup_check.tmp];then
36. echo'Backupstop'
37. exit
38. fi
40. #planbackupofsqltables
41. tables=(${tables_content})
42. #gettableslength
43. tables_len=${#tables[*]}
44. #backupfile(tar)number
45. backupFileNum=3
46. #backupofdirectoryDonotbring/
47. backupDir="/backup1/sqldata/$db_name"
48. #backupofdirectoryfordate
49. datestamp=$(date"+%Y%m%d")
50. #logfile
51. logfile='/backup1/sqldata/backup.log'
52. #finalbackupdirectroy
53. fileDir="$backupDir/$datestamp"
55. #autocreatetargetdirectory
56. if[!-d$backupDir];then
57. mkdir$backupDir
58. fi
59. if[!-d$fileDir];then
60. mkdir$fileDir
61. fi
62. if[!-d$logfile];then
63. touch$logfile
64. fi
66. echo"total$tables_lentables."
67. for((i=0;i<$tables_len;i++))
68. do
69. tableName="${tables[$i]}"
70. fileName="${tables[$i]}.sql"
71. filePath="$fileDir/$fileName"
72. echo"Table[${tables[$i]}]Backup..."
73. mysqldump-e-h$db_host-u$db_user-p$db_passwd$db_name$tableName>$filePath
74. done
76. #tarfiles
77. cd$backupDir
78. tarcvf${datestamp}.tar./${datestamp}
79. #deletesourcearchive
80. rm-rf./${datestamp}
81. end_time=`date'+%s'`
82. total_time=$[end_time-begin_time]
83. echo'BackupDone'
84. echo"Totaltime:${total_time}second"
86. now_date=`date'+%Y-%m-%d%k:%M:%S'`
87. #savelog
88. echo"${now_date}:BackupDatabase[${db_name}]:Totaltime[${total_time}s]">>$logfile
90. #begincleanexcessofbackupfile
91. #countbackupdirnameoflength;
92. backupDir_length=`exprlength"$backupDir"`
93. #gettarfilelist
94. backupFile_list=`find$backupDir-name"*.tar"|sort-n-r-k1.$backupDir_length`
95. #setarray
96. backupFiles=(${backupFile_list})
97. backupFile_length=${#backupFiles[*]}
98. if[$backupFile_length-gt$backupFileNum];then
99. for((i=$backupFile_length;i>$backupFileNum;i--))
100. do
101. fileName="${backupFiles[$i-1]}"
102. rm-rf$fileName
103. #savelog
104. echo"file:$fileNamedeleted"
105. echo"file:$fileNamedeleted">>$logfile
106. done//phpfensi.com
107. else
108. echo"backupfilenumbernormal."
109. fi

数据恢复可以使用批处理来恢复数据表,G:database20110324 此目录放置需要恢复的表sql文件,main.bat restore.bat 均放置在此目录,点击 main.bat 即可开始备份,每执行完一个文件会暂停,按任意键可以继续.

绿色背景蓝色文字部分需要根据需要修改.

共2处,第一处为 sql 文件路径,第二处为 数据库名称

main.bat内容如下:

@echo off

for %%b IN (./*.sql) DO @restore.bat G:database20110324%%b

restore.bat 内容如下:

1. @echooff
2. pause
3. echo文件%1开始还原
4. mysql-hlocalhost-uroot-tdatabase1--default-character-set=utf8-e"source%1"
5. echo文件%1完成还原
6. echo.
7. echo.
8. echo.