Shell script for a rolling three day mySQL database backup (Linux)

Asked By Cyril P 110 points N/A Posted on - 05/07/2011

I am currently attempting to write a shell script to backup a MySQL database. The requirement I have is to retain only 3 days of backup files on the server. All I presently could do is to use mysqldump utility to write it to a file on the server. How do I implement a 3 day rolling backup?

• Status: Open
• Question Views: 2166
• Answer Count: 7
• Vote Up 11 Vote Down

• Answer Accepted: Yes
• Question Category: Shell Scripting

Best Answer by Electrica

Go To Solution

Answered By Electrica 0 points N/A #90916

Shell script for a rolling three day mySQL database backup (Linux)

First of all you need to write a file with the required commands. Then you change the permission of the file to execute permissions. Lastly you schedule the script via the Linux job scheduler via the "crontab" utility. But in order to help you specifically, can you outline the logic you are looking for?

About Electrica

Questions
0

Answers
23

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/07/2011
• Question Category: Shell Scripting

Answered By Cyril P 110 points N/A #90917

Shell script for a rolling three day mySQL database backup (Linux)

The login I am looking for is for a 3  GFC solution. (Grand Father , Father, Child). An example of the roll over of the 3 backup files is outlined below:

Grand Father = 18th May
Father = 19th May
Child = 20th May

After the backup on 21st the state of the files will be as follows:

Grand Father = 19th May (overwritten)
Father = 20th May  (overwritten)
Child = 21th May  (latest)

At any given day the "Child" will be the latest backup file where as the rest will be for previous days' backup.

About Cyril P

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/07/2011
• Question Category: Shell Scripting

Best Answer

Best Answer

Answered By Electrica 0 points N/A #90918

Shell script for a rolling three day mySQL database backup (Linux)

Thank you Cyril for detailing the logic. Now I am clear as to what you are arriving at in terms of a solution. What I can advise you is to "roll" the files towards its parent. For this solution you need to have a constant file name that will not change. Then you apply the following file movement:

1. Move the "Father" to the "Grand Father"
2. Move the "Child" to the "Father"
3. Overwrite the "Child" with the new backup,

The following code will help you to understand it better. It suffixes the file name with a number and an extension of ".bak"

DBFILEBAK="database.backup"

mv $DBFILEBAK.2.bak $DBFILEBAK.3.bak -f
mv $DBFILEBAK.1.bak $DBFILEBAK.2.bak -f

mysqldump >  $DBFILEBAK.1.bak

Hope the above code helps?

About Electrica

Questions
0

Answers
23

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/07/2011
• Question Category: Shell Scripting

Answered By WizKid 0 points N/A #90919

Shell script for a rolling three day mySQL database backup (Linux)

You can also use the Linux "find" command to find files that are older than a certain date and then send them to the remove command to delete older files. The following code illustration removes files that are greater than 2 days by scanning the folder called /database/backups:

find /database/backups/ -type f -mtime +2 -exec rm {} ;

This method might suite you if your backup file names are not constant (Example you have the date as the filename).

About WizKid

Questions
0

Answers
25

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/07/2011
• Question Category: Shell Scripting

Answered By Cyril P 110 points N/A #90920

Shell script for a rolling three day mySQL database backup (Linux)

Hi Experts,

Thank you for your time in posting alternative solutions to my question. I used the code supplied by Electrica and WizKid. The combination of both suites the need! My data file has a constant name, therefore the mv command was appropriate. WizKids' code help me in cleaning up server log files and temporary log files. I was routinely cleaning these and was about to post a question for a solution! Therefore I got 2 solutions for my initial question! Thank you all!

About Cyril P

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/07/2011
• Question Category: Shell Scripting

Answered By WizKid 0 points N/A #90921

Shell script for a rolling three day mySQL database backup (Linux)

Glad to know that my code help you to solve a parallel problem! Have a good day!

About WizKid

Questions
0

Answers
25

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/07/2011
• Question Category: Shell Scripting

Answered By Electrica 0 points N/A #90923

Shell script for a rolling three day mySQL database backup (Linux)

You are most welcome Cyril. If you have further problems with the script, post it here and we will be glad to help!

About Electrica

Questions
0

Answers
23

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/07/2011
• Question Category: Shell Scripting