You are here

How is correct MySQL backup done?

You find many different possibilities how to do MySQL backup with mysqldump. But which one is the correct one?

Correct is a question of definition... I suggest starting with the following command:

for my.cnf

BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
BACKUP_DIR='/mybackupdir'
cp /etc/my.cnf $BACKUP_DIR/my_$BACKUP_TIMESTAMP.cnf

for MySQL databases with MyISAM tables

BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
BACKUP_DIR='/mybackupdir'
mysqldump --user=root --all-databases --lock-all-tables --master-data=1 --flush-privileges \
--flush-logs --triggers --routines --events --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

for MySQL databases with InnoDB tables only

BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
BACKUP_DIR='/mybackupdir'
mysqldump --user=root --all-databases --single-transaction --master-data=1 --flush-privileges \
--flush-logs --triggers --routines --events --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

  • --flush-privileges works since 5.1.12
  • --flush-logs has a bug (#61854) in MySQL 5.5 which fixed in 5.5.21.
  • --triggers seems to be enabled by default now. It's unclear to me since when.
  • While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
  • With mysqldump from Percona you should also use --lock-for-backup

If you want to be on the safe side use the FromDual Backup Manager for MySQL and MariaDB.

Shinguzcomment