Friday, February 19, 2010

Backup MySQL Databases (revisited)

I've once described how I backup MySQL databases on my box. A few days ago I noticed an error message in the backup job summary emails that Bacula sends me every day:
mysqldump: Got error: 1044: Access denied for user 'debian-sys-maint'@'localhost' to database 'information_schema' when using LOCK TABLES
A quick search got me to Debian bug #550037, which seemed rather relevant.

Here's the core of my revised MySQL backup script (note the special handling of the information-schema database):
backup_mysql ()
{
    echo "Backing up MySQL databases..."
    rm -rf "$1"
    mkdir -p "$1"
    mysql --defaults-file=/etc/mysql/debian.cnf --batch --skip-column-names -e "show databases" |
    while read DB ; do
        echo Dumping "${DB}" ...
        if [ "${DB}" == "information_schema" -o "${DB}" == "performance_schema" ]; then
            OPT="--skip-lock-tables"
        else
            OPT="--opt"
        fi
        mysqldump --defaults-file=/etc/mysql/debian.cnf "${OPT}" "${DB}" > "$1/${DB}.sql"
    done
}
[04 Jun 2012] UPDATE: fixed handling of another special database: performance_schema

No comments:

Post a Comment