I need to create a script 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.

  • dump-all-databases.sh mostly implements what @Christopher suggests.
    – manatwork
    Commented Mar 20, 2013 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
    Commented Mar 20, 2013 at 16:44
  • @manatwork, thanks for the script! it's a big help! :)
    – gtludwig
    Commented Mar 20, 2013 at 17:00

1 Answer 1


You can loop over all databases on a server and export them one by one like this in a shell script:

for DB in $(mysql -h $HOSTNAME -u $MYSQLUSER --password=${MYSQLPASS} --batch --skip-column-names --execute="show databases");
    local FILENAME=${HOSTNAME}_Database_${DB}.sql.gz
    mysqldump $DB -h $HOSTNAME -u $MYSQLUSER --password=${MYSQLPASS} --triggers --routines --event | gzip -c > $FILENAME

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .