View Source

Here is a small, but quite useful shell script which creates a backup of all databases in a MySQL Server.

If there is no change to the content of a particular MySQL database, the original backup file is not modified either and it keeps its timestamp. Thus, it will not be unnecessarily copied if you make a backup of the file system.

And here is how it works:

Set the variable MYSQLDIR to the path name of the directory where MySQL stores its database files.
Set the variable BACKUPDIR to the location where you want your backed up MySQL files.
If the MySQL root user needs a password, define it with the PW variable.
On execution the script will enumerate all directories in the ${MYSQLDBDIR} directory, which are the names of the MySQL databases. It will then run the mysqldump command with some generic options to create one backup file per MySQL database. An existing backup file is replaced only if it is outdated.
Now save this script into /etc/cron.daily/backup-mysql to have an automated daily database backup.

#!/bin/bash

PW=
MYSQLDBDIR=/var/lib/mysql
BACKUPDIR=/home/backups/mysql
mkdir -p ${BACKUPDIR}

cd ${MYSQLDBDIR}
for db in `find * -type d -exec basename {} \;`; do
    echo mysqldump ${db}
    mysqldump --all --complete-insert --extended-insert=FALSE --add-drop-table --databases --flush-logs --lock-tables --quick --comments=0 \
                             --user=root --password=${PW} --host=localhost ${db} > ${BACKUPDIR}/${db}.new.sql
    if [ -f ${BACKUPDIR}/${db}.sql ]; then
        # echo diff -q ${BACKUPDIR}/${db}.sql ${BACKUPDIR}/${db}.new.sql
        diff -q ${BACKUPDIR}/${db}.sql ${BACKUPDIR}/${db}.new.sql > /dev/null 2>&1
        result=$?
        if [ $result -eq 0 ]; then
            # echo rm ${BACKUPDIR}/${db}.new.sql
            rm ${BACKUPDIR}/${db}.new.sql
        elif [ $result -eq 1 ]; then
            echo mv ${BACKUPDIR}/${db}.new.sql ${BACKUPDIR}/${db}.sql
            mv ${BACKUPDIR}/${db}.new.sql ${BACKUPDIR}/${db}.sql
        fi
    else
        echo mv ${BACKUPDIR}/${db}.new.sql ${BACKUPDIR}/${db}.sql
        mv ${BACKUPDIR}/${db}.new.sql ${BACKUPDIR}/${db}.sql
    fi
done