Automated backup script for daily, weekly and monthly

HI,
Could anyone help me out to create shell backup script for mysql databases.

Please find following condition needs to be include in script:

(1)Creating a automatic MySQL database backup daily
(2)At the end of the week, creating a weekly backup
(3)At the end of the month, creating a monthly backup
(4)Storing backups as compressed files(tar.gz) to save space (upto 90% space-saving)
(5)Deleting old daily, weekly, and monthly MySQL database backups – lets you define what is old:
(i) save daily backup like day.sql.tar.gz(Sun.sql.tar.gz):delete old one when find same day on next week
(ii) take weekly backup on every Saturday(keep 4 weeks backup): delete older then 4 weeks
(iii) take monthly backup on last day of every month(keep last 3 monthly backups):delete older then 3 months

NOTE: needs to take backup of all databases as separate dump not in single backup set

Suggestion: Also please made suggestions on how to take incremental backups in mysql(means take the full backup on start of week and then only take incremental backups..etc)


Thanks,
Pradeep
pradeep_jmdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pradeep_jmdAuthor Commented:
Hi,
Also please provide details with automates script for:
(1) Full backup on every Monday for each databases on seperate dump file
(2) Differential backup then after till Sunday
(3) Also provide incremetal backup after full backup
(3) When next full backup done on Monday it delete old one


Thanks,
Pradeep
0
Steve BinkCommented:
I think your request has moved well away from the idea of a utility script, and into the realm of a backup management application.

Generally, you can back up MySQL by using mysqldump.  For a better background in backing up MySQL, you might want to read the whole chapter.

One of the more important points is that incremental backups really only work for transactional databases/tables, e.g., InnoDB.  If you perform an incremental backup on a non-transactional table (e.g., MyISAM), the entire file will be included if any single data point has changed, which negates the utility of incremental backups.

Given the depth and specificity of your requirements, I recommend looking at some established projects, commercial and open source, that could meet your needs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobOwner (Aidellio)Commented:
(No points) just want to +1 what has been said and if you are in need of a good backup solution then a 3rd party software dedicated this is the way to go.  Especially if it is part of a larger backup scheme with tape or other media.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

pradeep_jmdAuthor Commented:
Hi,
I just need script for automation of my backup, daily, weekly and monthly.


Thanks,
Pradeep
0
pradeep_jmdAuthor Commented:
HI,
Our database consists InnoDB and MyISAM tables.

I need the script to take full backup weekly and incremental backup then after.

Also provide script for to take differential backup.

and how to restore from full backup and incremental backup if database crash during weekdays.


Thanks,
Pradeep
0
RobOwner (Aidellio)Commented:
If that's the case set up a cron job for each day, week and month using mysqldump. Just have a simple one line script and repeat it for each event.  I've found this easier to manage rather than trying to do it all in one script.
0
RobOwner (Aidellio)Commented:
How big is your database? If you've got the space do a full backup every Night regardless, if not, buy a big enough hard drive and do the same thing.  If you're data is important don't stuff around with incremental. Do a full backup as storage is cheap.
0
RobOwner (Aidellio)Commented:
When I went through the backup strategy for my workplace, I thought about doing incremental backups but it didn't take all nIght to do a full one and the storage was cheap so I just ended up doing full backups.  Also means when you do need to do a restore you don't need all the tapes, disks etc, you just need the one media
0
Steve BinkCommented:
As tagit stated, unless your database is huge, incremental backups are just more trouble than they are worth.  A nightly full back up from mysqldump is fine in most cases.  If the size is still an issue, zip up the resulting files.

Restoring from a backup is as simple as running the SQL file from command line.
0
pradeep_jmdAuthor Commented:
Thanks for all suggestions.

Just give me some idea about:

(1) Do full backup on each week on SUNDAY at once
(3) Then Monday To Saturday Differential backups using diff

(3) and how to restore during weekday with the help of weekly full backup and differential backups

Please provide me example script then i can take it up too write for our databases.

Thanks,
Pradeep
0
RobOwner (Aidellio)Commented:
Have a look at @routinet's comment above: http:#a39568258.  It describes what you need to know.  No point reiterating it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.