You are here
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?Taxonomy upgrade extras:
Correct mysqldump backup
Correct is a question of definition... I suggest starting with the following command for a full backup:
for
my.cnf
for MariaDB/MySQL databases with MyISAM/Aria tables
for MariaDB/MySQL databases with InnoDB tables only
--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.--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.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.