You are here

How is a correct MariaDB/MySQL backup done?

You find many different possibilities how to do a MariaDB/MySQL backup with mariadb-dump/mysqldump. But which one is the correct one?

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

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 MariaDB/MySQL databases with MyISAM/Aria 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 MariaDB/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 MariaDB and MySQL.

Shinguzcomment