Back to top

MySQL shell script to backup (dump) all databases

Have you ever wanted to create a backup (aka dump) file of all databases on a server on a regular basis.

Well, here's an easy way to do that:

#!/bin/bash
# setup
suffix=`date +%Y%m%d`
dest=/mirror/mysqldumps
cmd='/usr/bin/mysqldump'

path="${dest}/${suffix}"
mkdir -p ${path}

databases=(`echo 'show databases;' | mysql -u your_user --password='your_password' | grep -v ^Database$`)

for d in "${databases[@]}"; do
  echo "DATABASE ${d}"
  ${cmd} --user=your_user --password='your_password' --quick --add-drop-table --create-options ${d} | bzip2 -c > ${path}/${d}.sql.bz2
done

# delete old dumps (retain 2 days)
find ${dest} -mtime +2 -exec rm {} \;

So, basically this script gets the current date in the format YYYYmmdd to use as a directory for all the dumps. Then it has a destination for that directory to be created in. The cmd should be the full path to your mysqldump command. From those variables, the funs starts: creating the directory for that date, getting a list of databases, looping over that, and for each one creating a bzipped dump of the creation. You need to hardcode your login credentials to this file so make sure that the permissions are locked down (e.g. chmod 700).

The last line will try to remove all files older than 2*24 hours (sometimes referred to as two days).

The last step is to stick this into your daily scripts (/etc/cron.daily on a RedHat or CentOS machine) and then make sure it's executable.

Yay!

More details on the mysqldump command and the original script I modified to get this can be found at the official mysql mysqldump documentation.

Category: 
People Involved: 

Comments

Nitpick suggestion

For retrieving the list of tables, you could also use the -N flag for the mysql command. -N suppresses the column names in the output. That makes the 'grep -v Database' obsolete.