Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Employee mysqldump now safe for InnoDB!
+6 Vote Up -0 Vote Down

It's now safe to backup InnoDB with mysqldump

Before MySQL 5.6, running mysqldump to make backup of InnoDB tables could cause your backup to 'loose' some data. The problem is described in our manual  here.

In latest MySQL 5.6 this is no longer a problem, this means you no longer risk 'loosing' data when using mysqlbackup together with DDL statements on your InnoDB tables. If you are interested in metadata-locking (MDL) you can read more about MDL  here.

To test this we need to create a few tables and also look at which order mysqldump processes tables.

mysql> CREATE DATABASE ted;
mysql> USE ted;
mysql> CREATE TABLE `a` (`i` int(11) DEFAULT NULL) ENGINE=InnoDB
mysql> CREATE TABLE `b` (`i` int(11) DEFAULT NULL) ENGINE=InnoDB
mysql> CREATE TABLE `c` (`i` int(11) DEFAULT NULL) ENGINE=InnoDB
Run a backup using mysqldump:

mysqldump -S /tmp/mysql.sock -uuser -ppass --single-transaction --databases ted > ted.sql
Flow of backup will be:
-- Table structure for table `a`
-- Dumping data for table `a`
-- Table structure for table `b`
-- Dumping data for table `b`
-- Table structure for table `c`
-- Dumping data for table `c`
So tables are accessed in the following order by mysqldump: a,b,c.

So lets add some data to table b so we have some time to run our DDL statements.
I have created a file with 10 mio rows to give us some time to run our test cases.
(I recomend relaxing InnoDB (innodb_flush_log_at_trx_commit=2) to speed up load of data)


mysql> INSERT INTO a VALUES (1);
mysql> LOAD DATA INFILE '/tmp/data-10000000' INTO TABLE b;
mysql> INSERT INTO c VALUES (1);
We want to test two cases:
  • Run DDL statement after table have been accessed by mysqldump, this will be done by running ALTER TABLE statement at table `a` during mysqldump.
  • Run DDL statement before table have been accessed by mysqldump, this will be done by running ALTER TABLE statement at table `c` during mysqdump.

  • Test case 1)

    Run mysqldump:
    mysqldump -S /tmp/mysql.sock -uuser -ppass --single-transaction --databases ted > ted.sql
    Start client and run DDL statement against table a whilst mysqldump is still runing:
    mysql> ALTER TABLE a ADD COLUMN (j int);

    Result:
    mysqldump will continue as normal and successfully finish its run with all data in place.
    ALTER TABLE will 'wait' until mysqldump is done and then successfully return, everything works as expected.

    mysql> SELECT * FROM a;
    +------+---------+
    | i    | j       |
    +------+---------+
    | 1    | NULL    |
    +------+---------+
    1 row in set (0.00 sec)



    Test case 2)

    Run mysqldump:
    mysqldump -S /tmp/mysql.sock -uuser -ppass --single-transaction --databases ted > ted.sql
    Start client and run DDL statement against table c whilst mysqldump is still runing:
    mysql> ALTER TABLE c ADD COLUMN (j int);

    Result:
    The "ALTER TABLE" statement immediately return successfully.
    mysqldump stops with error: mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `c` at row: 0

    Conclusion

    It's is now safe to run mysqldump and at the same time run DDL statements as long as you look at return value from mysqldump!
    These tests where done with MySQL 5.6.11

    MySQL configuration:
    innodb_flush_log_at_trx_commit=2
    innodb_buffer_pool_size=1G
    innodb_log_file_size=512M


    Votes:

    You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.