3

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 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
1

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

#!/bin/sh
HOSTNAME='localhost'
MYSQLUSER='root'
MYSQLPASS='xyz'
for DB in $(mysql -h $HOSTNAME -u $MYSQLUSER --password=${MYSQLPASS} --batch --skip-column-names --execute="show databases");
do
    local FILENAME=${HOSTNAME}_Database_${DB}.sql.gz
    mysqldump $DB -h $HOSTNAME -u $MYSQLUSER --password=${MYSQLPASS} --triggers --routines --event | gzip -c > $FILENAME
done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.