Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Using mysqldump, how can I create a consistent backup of a MySQL (InnoDB) database and have the dump files be separated by table?

To create a consistent backup using mysqldump, I know there is --single-transaction

To create separate dump files, I know I can use my language of choice to loop through the tables and call separate mysqldump commands. But each table dump will be from a different point-in-time.

I was looking at the mysqldump documentation for someway to specify a point-in-time either by timestamp or by binary log coordinates, but I could not find it.

share|improve this question
1  
Tricky. I am tempted to think you would have to setup Mysql Replication. Pause replication on a slave, create your backups on the slave, then resume the replication. Or you simply have to compose a script that will parse out your single mysql dump command into multiple files. –  Zoredache Jan 30 '13 at 20:14
    
A 24x7 replication slave would be too costly. And spinning up a slave as needed for backups would take too long (my database is 60+ GB, and it takes over 4 hours to restore from previous snapshot, plus the time for the slave to catch up). –  user156845 Jan 30 '13 at 21:09
    
Giving all your requirements, I think you are out of luck. I could be wrong though. –  Zoredache Jan 30 '13 at 21:13
add comment

3 Answers

Using your language of choice, you could lock all the tables and then loop through mysqldump for each table; or set the database to read only for the duration of the backup. You could also use innobackup (www.percona.com) which can keep everything consistent and then apply the logs that were written during the actual backup process.

share|improve this answer
    
Thanks. I didn't specify it in the original post, but ideally I would like to keep the db readable/writable. I believe --single-transaction allows this. –  user156845 Jan 30 '13 at 21:02
    
I'd also like to avoid third-party programs. –  user156845 Jan 30 '13 at 21:06
add comment

You need to use --single-transaction to keep it consistant, but keep in mind, with myisam tables, it will not be writable during this time. They'll be readable but not writable. So do your giant consistant dump and then use a third party splitter:

share|improve this answer
add comment

I also loop through each table and instead of calling mysqldump i call "select into outfile".

Transactions are very much important for the application to have a consistent state for the db. so i think if you use the same the current method (loop through) or the "select into outfile" is the right method i suppose

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.