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
Â

Tags: innodb, mysqldump

5 Responses to “Dumping large MySQL InnoDB tables”

1
Arstan
29th August 2007, 03:48
Thanks for the tips, I was exactly looking for it!
2
grick
27th October 2008, 10:10
I love you
3
links for 2008-11-12 « Brent Sordyl’s Blog
12th November 2008, 14:03
[...] Dumping large MySQL InnoDB tables | MDLog:/sysadmin 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. (tags: mysqldump) [...]
4
Dagbok för 14 May 2009 | En sur karamell
14th May 2009, 20:11
[...] Dumping large MySQL InnoDB tables | MDLog:/sysadmin — 20:00 via [...]
5
Sam IT
10th February 2010, 19:52
single-row locking is one of the main reasons I use InnoDB.
Can you imagine an hourly backup on a Master MySQL server with a lot of data?
The whole application “freezes” up.
On the other hand, when you use this, you need to remember that since it doesn’t lock the table, it’s possible for new data to be inserted (which can also be a bad thing).
For example, if you insert two lines per application “event”, you may only get one of the lines in the backup. The same goes for multiple tables with JOINs.
If you insert multiple rows into multiple tables, there’s a chance you’ll get partial data.
The safest way is to either lock all tables of deny access to the db server.
To prevent downtime, it’s recommended to setup slave servers, you can do pretty much anything you want on them (stop slave; mysqldump; start slave;).
Good luck!