Re: How to make a consistent backup of both InnoDB and MyISAM tables without unnecessary locking

[Sveta Smirnova]

You must lock MyISAM during backup to ensure data consistency. What you ask is really works though.

> I see InnoDB Hot Backup does exactly that, but is it possible to achieve the same without this tool?

Well, InnoDB HotBackup is EOLed now, but its development moved to new product MySQL Enterprise Backup (http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html). You can use this tool.

You can also do backups in two steps: firstly backup InnoDB tables, then MyISAM tables. You will get two consistent backups in this case. If yo want logic backups like mysqldump does just use --single-transaction first time, then --lock-all-tables.

If you prefer binary backup you can do

mysqlbackup --only-innodb-with-frm
mysqlhotcopy [MyISAM tables]

This approach is not good if you need to save binary log position for further use in replication.

> 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?

Yes, this is correct.