On 01/05/18 20:55, @lbutlr wrote: > On 4 Jan 2018, at 14:40, Phil Stracchino ph...@caerllewys.net> wrote: >> The principal shortcoming of mysqldump is that it is a benightedly stupid >> tool that cannot walk and chew gum at the same time, or *correctly* dump >> both MyISAM and InnoDB tables in the same operation. > > OK, I'll bite. I've been using mysql dump to backup my databases for at least > decade (via a cron job), and in that time have often used it to also restore > databases. So far, I'm never had an issue, but then again, I don't want to > have an issue in the future either.
I'll clarify the issue a little. InnoDB is a transactional storage engine, and should in most cases be backed up using --skip-lock-tables --single-transaction, to get a consistent snapshot of the database without locking it. MyISAM, however, is non-transactional, and you cannot dump a MyISAM table without read-locking it, which means that to get a consistent dump of a database that contains MyISAM tables you MUST lock the database. Mysqldump can only do one or the other for an entire backup. It cannot do anything table-by-table, and you have to tell it which of the two unsatisfactory options - lock everything, or lock nothing - you want it to use. The third-party tool I mentioned, mydumper, is smart enough to be storage-engine aware at the table level, so it gets a global read lock, holds it JUST long enough to dump all of the MyISAM tables first, then releases the read lock and dumps all of the InnoDB tables as a background transaction. This gives a fully consistent dump with the shortest possible lock time and minimal impact to database access during the dump. You might want to give it a look. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958
