mysql数据库有什么好备份方案吗

lonefly

军哥LNMP里的备份脚本

1. #!/usr/bin/env bash
   
2. 
   
3. #Funciont: Backup website and mysql database
   
4. #Author: licess
   
5. #Website: https://lnmp.org
   
6. 
   
7. #IMPORTANT!!!Please Setting the following Values!
   
8. 
   
9. Backup_Home="/home/backup/"
   
10. MySQL_Dump="/usr/local/mysql/bin/mysqldump"
    
11. ######~Set Directory you want to backup~######
    
12. Backup_Dir=("/home/wwwroot/vpser.net" "/home/wwwroot/lnmp.org")
    
13. 
    
14. ######~Set MySQL Database you want to backup~######
    
15. Backup_Database=("lnmp" "vpser")
    
16. 
    
17. ######~Set MySQL UserName and password~######
    
18. MYSQL_UserName='root'
    
19. MYSQL_PassWord='yourrootpassword'
    
20. 
    
21. ######~Enable Ftp Backup~######
    
22. Enable_FTP=0
    
23. # 0: enable; 1: disable
    
24. ######~Set FTP Information~######
    
25. FTP_Host='1.2.3.4'
    
26. FTP_Username='vpser.net'
    
27. FTP_Password='yourftppassword'
    
28. FTP_Dir="backup"
    
29. 
    
30. #Values Setting END!
    
31. 
    
32. TodayWWWBackup=www-*-$(date +"%Y%m%d").tar.gz
    
33. TodayDBBackup=db-*-$(date +"%Y%m%d").sql
    
34. OldWWWBackup=www-*-$(date -d -3day +"%Y%m%d").tar.gz
    
35. OldDBBackup=db-*-$(date -d -3day +"%Y%m%d").sql
    
36. 
    
37. Backup_Dir()
    
38. {
    
39.     Backup_Path=$1
    
40.     Dir_Name=`echo ${Backup_Path##*/}`
    
41.     Pre_Dir=`echo ${Backup_Path}|sed 's/'${Dir_Name}'//g'`
    
42.     tar zcf ${Backup_Home}www-${Dir_Name}-$(date +"%Y%m%d").tar.gz -C ${Pre_Dir} ${Dir_Name}
    
43. }
    
44. Backup_Sql()
    
45. {
    
46.     ${MySQL_Dump} -u$MYSQL_UserName -p$MYSQL_PassWord $1 > ${Backup_Home}db-$1-$(date +"%Y%m%d").sql
    
47. }
    
48. 
    
49. if [ ! -f ${MySQL_Dump} ]; then  
    
50.     echo "mysqldump command not found.please check your setting."
    
51.     exit 1
    
52. fi
    
53. 
    
54. if [ ! -d ${Backup_Home} ]; then  
    
55.     mkdir -p ${Backup_Home}
    
56. fi
    
57. 
    
58. if [ ${Enable_FTP} = 0 ]; then
    
59.     type lftp >/dev/null 2>&1 || { echo >&2 "lftp command not found. Install: centos:yum install lftp,debian/ubuntu:apt-get install lftp."; }
    
60. fi
    
61. 
    
62. echo "Backup website files..."
    
63. for dd in ${Backup_Dir[@]};do
    
64.     Backup_Dir ${dd}
    
65. done
    
66. 
    
67. echo "Backup Databases..."
    
68. for db in ${Backup_Database[@]};do
    
69.     Backup_Sql ${db}
    
70. done
    
71. 
    
72. echo "Delete old backup files..."
    
73. rm -f ${Backup_Home}${OldWWWBackup}
    
74. rm -f ${Backup_Home}${OldDBBackup}
    
75. 
    
76. if [ ${Enable_FTP} = 0 ]; then
    
77.     echo "Uploading backup files to ftp..."
    
78.     cd ${Backup_Home}
    
79.     lftp ${FTP_Host} -u ${FTP_Username},${FTP_Password} << EOF
    
80. cd ${FTP_Dir}
    
81. mrm ${OldWWWBackup}
    
82. mrm ${OldDBBackup}
    
83. mput ${TodayWWWBackup}
    
84. mput ${TodayDBBackup}
    
85. bye
    
86. EOF
    
87. 
    
88. echo "complete."
    
89. fi

复制代码