Clean MySQL backup using mysqldump

If you need to configure a backup of a MySQL database server, you shouldn’t simply copy it’s database files from /var/lib/mysql since they might be inconsistent (due to simultaneous changes). mysqldump is a safe choice for this task. I wrote a simple script for Debian that uses the pre-configured “debian-sys-maint” account and compresses the output:

#!/bin/bash

BACKUP_FILENAME=/var/backups/mysql/mysql.dump
mysqldump --defaults-extra-file=/etc/mysql/debian.cnf --all-databases --lock-all-tables --result-file=$BACKUP_FILENAME
gzip $BACKUP_FILENAME

Please take care that you need to create the output directory (/var/backups/mysql in the above example) first!

If you need more than one revision of the backup, you might want to use “date” to format the filename. Since I run a normal (rdiff-)backup of a big part of the server afterwards, I don’t need this.

Leave a Reply

Your email address will not be published. Required fields are marked *