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 BuesserAsked:
Who is Participating?
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  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.
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?
theGhost_k8Database ConsultantCommented:
Instead of single transaction you should go for --lock-all-tables.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.