MySQL Forums
Forum List  »  Backup

How to make a consistent backup of both InnoDB and MyISAM tables without unnecessary locking
Posted by: Bojan Tomic
Date: May 21, 2012 07:10AM

We have around 30 tables, 3 of which are MyISAM. If we just use the usual "mysqldump -opt ..." the application suffers from connection pool exhaustion during the whole backup. Presumably, this is because of the table locking implied by -opt. The --single-transaction flag sounds like exactly what we need for InnoDB tables to run uninterrupted. But then we have no way to guarantee the consistency across MyISAM tables, right?*
Is it possible to somehow lock MyISAM tables, dump InnoDB with single transaction, dump MyISAM tables, unlock?
I see InnoDB Hot Backup does exactly that, but is it possible to achieve the same without this tool?

Thanks.

* Since the consistency across MyISAM tables is managed by the application and not the engine, anything inserted into InnoDB tables during the --single-transaction backup will not be present in the dump, but anything inserted into a MyISAM table at the same time would, thus damaging consistency. Is this correct?



Edited 1 time(s). Last edit at 05/21/2012 07:22AM by Bojan Tomic.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to make a consistent backup of both InnoDB and MyISAM tables without unnecessary locking
3065
May 21, 2012 07:10AM


Sorry, you can't reply to this topic. It has been closed.

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.