I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysqldump --all-databases > all.sql
UNLOCK TABLES;
In this scenario master is locked for significant amount of time. My master is production, so I would rather perform this operation without downtime. My plan is to do this instead:
mysqldump --single-transaction --master-data --all-databases > all.sql
Am I right to assume that in this case I do not need to keep database lock and dump is consistent and good to be used to rebuild the slave machine? I understand that at the beginning of my statement there will be short lock. I just do not see any need to perform additional lock in my case. Is that correct?
Additionally is –single-transaction assuring consistency of single database or whole instance? Is it just one transaction no matter how many databases are there on the instance or new transaction for each database?
Best Answer
--single-transaction is assuring the consistency of the whole instance so yes, across multiple database.
It assumes most tables are transactional (i.e. innodb). For myisam tables these are still read locked briefly for the time taken to dump them.
The mysqldump snapshot method is in the manual.