Dumping large MySQL InnoDB tables

Backing up MySQL databases normally involves running mysqldump (either manually or from a script). If you have worked with very big mysql tables (let’s say over 10GB) you already know that dumping such a table will take a while (of course this will depend from many things like hardware, mysql configuration, etc. but still whatever you do it will still take a long time for such big tables).

If we are running this operation in a production environment (let’s say to take regular database backups), the default mysqldump setting will lock the tables during the dump and this might cause problems with the live application using the database. There are different things that can be done to avoid this (like backup from a slave mysql, using mysqlhotcopy, etc.) but if the tables are InnoDB then we can add the parameter –single-transaction to mysqldump and avoid this issue.

From the mysqldump man page: –single-transaction
This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications.
When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or HEAP tables dumped while using this option may still change state.
The –single-transaction option was added in MySQL 4.0.2. This option is mutually exclusive with the –lock-tables option, because LOCK TABLES causes any pending transactions to be committed implicitly.
To dump big tables, you should combine this option with –quick.

So using something like:

mysqldump --single-transaction very_large_db > bakup_of_db.sql

will not speed the process, but it will allow the live application to use the mysql database as normal without locking any tables and preventing the application to change the database.

For the full manual page of mysqldump you can always check the online resources from mysql: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html