Is there a non-blocking operation to back up MyISAM tables using Percona XtraBackup?

Question

I've been reading through the website of Percona XtraBackup and it claims that:

Percona XtraBackup can back up the following storage engines by briefly pausing writes at the end of the backup: MyISAM, Merge, Archive ...

Is there a flag or option I've missed with the xtrabackup binary to make this functionality happen?

More info...

I installed XtraBackup 2.3.4 onto a CentOS 7.2 server, as well as MySQL 5.6.30. I created a new MyISAM table with approximately 32 million rows and started the backup process with the following:

xtrabackup --user=user --password=password --no-timestamp --compress --backup --target-dir /tmp/my_backup

While the backup was underway I attempted to write a new row to the table, fully expecting it to be inserted into the table as this is what the website implies will happen. However, it was not. During the entire backup process the MyISAM table is locked.

I understand why the table is locked, hence why I was quite excited to read that XtraBackup could perform a backup without any significant write pauses until the end. I just don't understand how to make this functionality happen.

Answer 1

No - the MyISAM architecture prohibits hot backups - it uses a locking mechanism. You might be interested to my response in a related thread.

Take a mysqldump of your database, change the bit where it says ENGINE=MyISAM to ENGINE=InnoDB (InnoDB uses a MVCC architecture) and then restore - XtraBackup will then perform hot backups. It's relatively easy to use sed to do the edit that I have suggested

mysqldump [options] | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > my_schema.sql 

Then restore your dumpfile in the normal way.

MyISAM is going to be deprecated in the near future anyway! [EDIT - added 19/04/2018] - the system tables in MySQL 8 are now InnoDB!

In reply to the OP's question about the Percona's XtraBackup page being a "complete fabrication" - the quote is "can back up the following storage engines by briefly pausing writes at the end of the backup".

I think that the keyword on that page is "briefly"! :-) Take a look here - Bill Karwin is a big hitter on this forum.

Also, take a look at what Shlomi Noach says about MyISAM hot backups (or lack of!) - he's also a big hitter in the MySQL world. The file system snapshot to which he refers is mylvmbackup - which appears to have a reasonably good rep. here.