Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

BackupNinja,MySql backup

Posted on 2011-10-31
5
Medium Priority
?
600 Views
Last Modified: 2012-08-14
Hello,

I have Backup Ninja creating backup of MySql databases each night.

My problem is that the last backup always overrides the previous one.
I need to create backup for each day in different folders (the folder names should contain the date). How can this be done?

Thank you
0
Comment
Question by:starhu
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:icenick
ID: 37056818
Hello,

I am not familiar with Backup ninja, but I use a bash script for the job where I backup each night with a different name including the date.

If you use Linux, then this bash code will help you:

mysqldump --add-drop-table -u root -pYourPassword --database database_name | gzip > /path/"backup_name"-`date +%Y%m%d`.sql.gz

Open in new window


I pipe the backup to gzip in order to compress it (run gunzip to decompress; running gunzip will remove the original gz keeping only the uncompressed).

Be careful that backing up the database each day may with time fill your disk.

Good Luck!
0
 

Author Comment

by:starhu
ID: 37078184
The MySql database keeps growing so as every day I make new databases in it.

Can your script backup ALL the databases at once? It would be tiresome to modify the script every time I create a new database.

thank you
0
 
LVL 7

Expert Comment

by:icenick
ID: 37079106
Hello again,

Of course there is a way. Bash is really powerful and you can do a lot of things.

Databases are directories found in /var/lib/mysql/. You can use the find command to find for directories, save the result in a variable and use for loop to backup all databases.

DATABASES=$(find /var/lib/mysql/ -maxdepth 1 -mindepth 1 -type d -exec basename {} \;)
for database in $DATABASES
do
mysqldump --add-drop-table -u root -pYourPassword --database $database | gzip >          \ 
/path/"$database"-`date +%Y%m%d`.sql.gz
done

Open in new window


Good Luck!
0
 

Author Comment

by:starhu
ID: 37080112
Hello,

Just to put this together (I am a novice in Linux):
Should I create a file, put the stuff you wrote and somehow make a Cron job that runs this file for example at midnight?

thank you
0
 
LVL 7

Accepted Solution

by:
icenick earned 2000 total points
ID: 37081484
Hello,

Yes. Create a bash file (with header #!/bin/bash). Put the code above in it. Make it executable. Run it manually first. Put it in crontab after that to run at midnight.

Good Luck!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
"Any files you do not have backed up in at least two [other] places are files you do not care about."
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question