Clean MySQL backup using mysqldump
This article is very old (more than 15 years) and likely outdated. Please be careful and take everything in here with a grain of salt.
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 of creating 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.