Search code examples
mysqldatabase-replicationdatabase-backups

innoDB with --single-transaction or replication

Is it enough to add the option --single-transaction in mysqldump to create a backup with consistency..

Or do you need to setup master/slave replication?

mysqldump --single-transaction -h localhost -u root ...

The database consists of only innoDB tables

Solution

  • Yes,

    mysqldump --single-transaction ...

    executes

    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
UNLOCK TABLES
...

    so this will provide you consistency. You do not need to set up replication.

    For reference:

    mysqldump ... (same as mysqldump --lock-tables ...)

    executes:

    LOCK TABLES `table1` READ /*!32311 LOCAL */
...
UNLOCK TABLES
...
LOCK TABLES `table2` READ /*!32311 LOCAL */
...
UNLOCK TABLES
...

    and

    mysqldump --lock-all-tables ...

    executes:

    FLUSH TABLES
FLUSH TABLES WITH READ LOCK
...

    so for your needs, mysqldump --single-transaction is correct.