How is a correct MariaDB/MySQL backup done?

Mon, 2015-05-18 07:40 — oli

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

Correct mariadb-dump/mysqldump backup

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 or Aria tables

$ BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
$ BACKUP_DIR='/mybackupdir'
$ mariadb-dump --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.

--dump-history

Since MariaDB 10.11 it would be good to use --dump-history if MariaDB System-Versioned Tables are used!

tags: 
backup restore mysqldump mariadb-dump