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

Posted on 2012-09-07
Last Modified: 2012-09-13

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
Question by:Bruno Buesser
    LVL 24

    Expert Comment

    the single-transaction option will lock all tables from other writers until the dump is finished.
    LVL 1

    Author Comment

    by:Bruno Buesser
    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?
    LVL 21

    Expert Comment

    Instead of single transaction you should go for --lock-all-tables.
    LVL 24

    Accepted Solution

    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.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now