Actually this is (almost) all I wanted to say. This is intentionally posted with all related keywords in title, in the hope that a related search on Google will result with this post on first page.
I’m just still encountering companies who use MyISAM as their storage engine and are unaware that their nightly backup actually blocks their application, basically rendering their product unavailable for long minutes to hours on a nightly basis.
So this is posted as a warning for those who were not aware of this fact.
There is no hot (non blocking) backup for MyISAM. Closest would be file system snapshot, but even this requires flushing of tables, which may take a while to complete. If you must have a hot backup, then either use replication – and take the risk of the slave not being in complete sync with the master – or use another storage engine, i.e. InnoDB.
Yeah, that’s right.
Also note that FLUSH TABLES, which sounds fairly harmless (i.e. it writes unwritten changes to disc), actually also flushes CLEAN blocks out of the key buffer, which means that after your backup finishes, your database read-performance will suck for some time as the key buffer will be totally cold.
There is no way of flushing only dirty blocks from the key buffer (which get kept indefinitely if you have DELAY_KEY_WRITE=1) – if you do a flush, it throws out clean ones too.
“then either use replication – and take the risk of the slave not being in complete sync with the master”
could you explain why the slave would not be in complete sync? (we have seen it happen) but why?
@Pierre-Yves,
A few possibilities:
1. Corruption of data while writing/reading from relay logs. Google patches #4 added binary logs checksum, which solve this issue. but this is not available yet in MySQL.
2. Unsafe queries issued on the master, e.g.:
DELETE FROM t WHERE a LIKE ‘x%’ LIMIT 20;
In what order are rows being deleted? Depends on the storage engine, and depends on internal storing of rows. This query can delete different rows on master and slave.
3. Issues with unflushed binlogs in case of crash; or with open temp tables on slave in case of stopped slave; or with unflushed master.info file, etc. Some changes in 5.5 will solve many potential problems.
4. Someone issuing a query by mistake on the slave, thereby making it different than the master.
5. Just… bugs in the replication mechanism. Don’t get me wrong, I’m not saying replication is not good – but it can be susceptible to bugs.
What about mysqlhotcopy?
mysqlhotcopy is wrongly named. It should have been named mysqlwarmcopy.
It puts a read lock on all tables, along with flushing them, then does a file system copy.