[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Is mysqldump 100% save against table changes of other connections?


I need to automatically make copies of a mysql database with only InnoSetup tables. mysqldump with the single-transaction parameter could do the job.
I think this tool will read all tables one after another. The single-transaction parameter causes the StartTransaction and Begin commands beeing called at the beginning of the dump process.
My question: Will StartTransaction secure all tables at once so that while reading the first table all changes to e.g. to the last table (which will be only read after 2 minutes) from another db connection will not be visible when mysqldump reaches this table?
Does anybody know what mysql is exactly doing on StartTransaction and Begin? Does it secure all tables in the db for the calling connections? I always thought that it only makes a internal snapshot of the records already read. Is mysqldump 100% save against other connections writing in tables mysqldump did not already read?
Thanks, Bruno
Bruno Buesser
Bruno Buesser
  • 2
1 Solution
the single-transaction option will lock all tables from other writers until the dump is finished.
Bruno BuesserAuthor Commented:
Other writers are not blocked during single-transaction as stated in the mysqldump help.
That means that MySQL makes a internal copy of the whole database during transaction?
Otherwise it would not be absolutely save. Is that true?
K VDatabase ConsultantCommented:
Instead of single transaction you should go for --lock-all-tables.
Yes I need to correct myself there - using singe-transaction other writers are not locked, but MySQL does not make an internal copy of the whole database - it uses multi-version concurrency control to keep track of the changes and ensure a consistent state of the backup (see http://ronaldbradford.com/blog/understanding-innodb-mvcc-2009-07-15/).  However, you will only get a consistent state for the Innodb tables - if you have tables using other engines, they will still be written  on.  If you do have those then lock-all-tables may be the better option for you.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now