MySQL remote Incremental Backup

MySQL remote Incremental Backup:

1. #! /Bin/sh 
2.  
3. # MySQL Info
4. Sqluser = root
5. Sqlpsw = root
7. /Usr/bin/mysqladmin-u $ sqluser-p $ sqlpsw flush-logs --Default-Character-set = GBK
9. DATADIR =/data/mysql
10. BAKDIR =/home/backup/mysql/daily
11. TEMPDIR =/home/backup/mysql/temp
13. BINLOGNAME ='Mysql-bin'
14. Cd $ DATADIR
15. FILELIST = $ (cat $ BINLOGNAME. index | cut-c3 -) 
16.  
17. # COUNTER number
18. COUNTER = 0
19. ForFileIn$ FILELIST
20. Do
21. COUNTER = $ (expr $ COUNTER + 1)
22. Done
24. NextNum = 0
25. ForFileIn$ FILELIST
26. Do
27. NextNum = $ (expr $ NextNum + 1)
28. IfTest"$ NextNum"="$ COUNTER"
29. Then
30. Echo"Skip lastest"
31. Else
32. Dest = $ BAKDIR/$ file
33. IfTest-e $ dest
34. Then
35. Echo"Skip exist $ dest"
36. Else
37. Cp $ file $ TEMPDIR
38. Fi
39. Fi
40. Done
41. Echo"Backup mysql binlog OK"
43. Sleep 2 s
45. Cd $ TEMPDIR
46. Tarname = binlog $
47. Tar czf $ tarname./mysql-bin *
49. Sleep 1 s 
50.  
51. # Ftp Info
52. Ftphost = 111.111.11.11
53. Ftpname = xxx
54. Ftppsw = psw
56. Cd $ TEMPDIR
58. Ftp-I-n <!
59. Open $ ftphost
60. User $ ftpname $ ftppsw
61. Type binary
62. Put $ tarname
63. Close
64. Bye
65. !
66. Sleep 10 s
68. Echo"End upload"
70. Cd $ TEMPDIR
71. Mv $ BINLOGNAME * $ BAKDIR
72. Rm-r $ tarname
74. Echo"End"

Note:

Mysqladmin-u $ sqluser-p $ sqlpsw flush-logs: mysql automatically stores the logs in memory in the last file of the binlog log file and generates an empty new log file, after that, you only need to back up the first few items. The last one is generated and empty, so no backup is required.

For details about binlog, refer:

General idea:

Compare the binlog in the datadir and bakdir directories. For example, if there is no binlog in the bakdir directory and the binlog is not the last one, copy the binlog to the tempdir directory. In this way, the binlog in the tempdir directory is a new binlog. Then, compress the binlog in tempdir and ftp it to another server.