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?

Taxonomy upgrade extras: 

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