Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to create a script so to automate the backup process for my database server. Today there are 19 databases on the server and to prevent some schema is created and not contemplated by the script, I'd like it to backup all the databases listed by the "show schemas" query.

My idea is that each schema is dumped twice (i.e., at 0h00 and at 12h00) a day, compressed and saved to the folder /home/{user}/database_backup/daily/[day_of_week].

Once a week all [day_of_week] folders are compressed and saved to /home/{user}/database_backup/weekly/[week_of_month].

Finally, once a month, all [week_of_month] would be compressed an saved to /home/{user}/database_backup/monthly/ and left there so another application can back it up to tape.

Today, this tape backup application backs up /var/lib/mysql on a daily basis, but should need be, restoring these files or using them on a different database server is somewhat difficult.

I know this is a muck too open question, but help is, as always, appreciated.

share|improve this question
Though not an answer in the form of a cut-and-paste script, a reference may be enough? The core of it seems to revolve around mysqldump (dev.mysql.com/doc/refman/5.1/en/mysqldump.html) e.g.: 'mysqldump -u root -p$THE_PASSWORD --all-databases > $PATH/mysql_dump.sql'... That's a line in a script that runs from cron. Add compression. – Christopher Mar 20 '13 at 15:16
dump-all-databases.sh mostly implements what @Christopher suggests. – manatwork Mar 20 '13 at 15:20
Thanks, guys! I have read about the --all-databases, but to my knowledge it will generate a single file, regarless of how many schemas there are on the database server. – gtludwig Mar 20 '13 at 16:44
@manatwork, thanks for the script! it's a big help! :) – gtludwig Mar 20 '13 at 17:00

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.