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
