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

Asked By 110 points N/A Posted on -
qa-featured

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?

SHARE
Best Answer by Electrica
Answered By 0 points N/A #90916

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

qa-featured

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?

Answered By 110 points N/A #90917

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

qa-featured

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.

Best Answer
Best Answer
Answered By 0 points N/A #90918

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

qa-featured

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?

Answered By 0 points N/A #90919

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

qa-featured

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).

Answered By 110 points N/A #90920

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

qa-featured

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!

Answered By 0 points N/A #90921

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

qa-featured

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

Answered By 0 points N/A #90923

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

qa-featured

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

Related Questions