On Fri, 5 Jan 2018 21:13:36 -0500, Phil Stracchino stated: >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.
I have never used that application. I have been using "mysqlbackup" <https://code.google.com/archive/p/mysqlbackup/> for my MySQL databases. It seems to work fine for me. -- Jerry
