mysql_backup.sh MySQL資料庫自動備份程式

woff

這四個地方記得改一下
db_user="root"
db_passwd=""
db_host="localhost"
backup_dir=""
------------------------------------------------------------

1. #!/bin/sh
   
2. # mysql_backup.sh: backup mysql databases and keep newest 5 days backup.
   
3. #
   
4. # Last updated: 20 March 2006
   
5. # ----------------------------------------------------------------------
   
6. # This is a free shell script under GNU GPL version 2.0 or above
   
7. # Copyright (C) 2006 Sam Tang
   
8. # Feedback/comment/suggestions : http://www.real-blog.com/
   
9. # ----------------------------------------------------------------------
   
10. 
    
11. # your mysql login information
    
12. # db_user is mysql username
    
13. # db_passwd is mysql password
    
14. # db_host is mysql host
    
15. # -----------------------------
    
16. db_user="root"
    
17. db_passwd=""
    
18. db_host="localhost"
    
19. 
    
20. # the directory for story your backup file.
    
21. backup_dir=""
    
22. 
    
23. # date format for backup file (dd-mm-yyyy)
    
24. time="$(date +"%d-%m-%Y")"
    
25. 
    
26. # mysql, mysqldump and some other bin's path
    
27. MYSQL="$(which mysql)"
    
28. MYSQLDUMP="$(which mysqldump)"
    
29. MKDIR="$(which mkdir)"
    
30. RM="$(which rm)"
    
31. MV="$(which mv)"
    
32. GZIP="$(which gzip)"
    
33. 
    
34. # check the directory for store backup is writeable
    
35. test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0
    
36. 
    
37. # the directory for story the newest backup
    
38. test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"
    
39. 
    
40. # get all databases
    
41. all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
    
42. 
    
43. for db in $all_db
    
44. do
    
45. $MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"
    
46. done
    
47. 
    
48. # delete the oldest backup
    
49. test -d "$backup_dir/backup.5/" && $RM -rf "$backup_dir/backup.5"
    
50. 
    
51. # rotate backup directory
    
52. for int in 4 3 2 1 0
    
53. do
    
54. if(test -d "$backup_dir"/backup."$int")
    
55. then
    
56. next_int=`expr $int + 1`
    
57. $MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"
    
58. fi
    
59. done
    
60. 
    
61. exit 0;

複製代碼

woff

或是按照日期來備份檔案

1. #!/bin/sh
   
2. # mysql_backup.sh: backup mysql databases at day
   
3. updated: 5 Aug 2009
   
4. # ----------------------------------------------------------------------
   
5. # This is a free shell script under GNU GPL version 2.0 or above
   
6. # Copyright (C) 2009 Woff Lin
   
7. # Feedback/comment/suggestions : http://www.tshopping.com.tw
   
8. --------------------------------------------------------
   
9. 
   
10. # your mysql login information
    
11. # db_user is mysql username
    
12. # db_passwd is mysql password
    
13. # db_host is mysql host
    
14. # -----------------------------
    
15. db_user="root"
    
16. db_passwd=""
    
17. db_host="localhost"
    
18. 
    
19. # the directory for story your backup file.
    
20. backup_dir=""
    
21. 
    
22. # mysql, mysqldump and some other bin's path
    
23. MYSQL="$(which mysql)"
    
24. MYSQLDUMP="$(which mysqldump)"
    
25. MKDIR="$(which mkdir)"
    
26. RM="$(which rm)"
    
27. MV="$(which mv)"
    
28. GZIP="$(which gzip)"
    
29. 
    
30. # check the directory for store backup is writeable
    
31. test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0
    
32. 
    
33. # the directory for story the newest backup
    
34. test ! -d "$backup_dir/" && $MKDIR "$backup_dir/"
    
35. 
    
36. # get all databases
    
37. all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
    
38. 
    
39. for db in $all_db
    
40. do
    
41. $MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/mgtsql.$db.`date +%Y%m%d`.gz"
    
42. done
    
43. exit 0;
    

複製代碼

akaimo

我等你喲!  

angel78631

HOHO~~~~感謝分享~~