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… Read more »
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.
See excerpt from mysqlhotcopy code:
# flush tables to make on-disk copy uptodate $start = time; $dbh->do("FLUSH TABLES /*!32323 $hc_tables */"); printf "Flushed tables ($hc_tables) in %d seconds.\n", time-$start unless $opt{quiet}; $dbh->do( "FLUSH LOGS" ) if ( $opt{flushlog} ); $dbh->do( "RESET MASTER" ) if ( $opt{resetmaster} ); $dbh->do( "RESET SLAVE" ) if ( $opt{resetslave} ); if ( $opt{record_log_pos} ) { record_log_pos( $dbh, $opt{record_log_pos} ); $dbh->do("FLUSH TABLES /*!32323 $hc_tables */"); }mysqlhotcopy is the fastest way with myisam though.. Such a shame there is nothing better. In my country, i see some commercial websites that regularly close their site at 5am for about 1 hour, i presume they use MyIsam and are doing dumps.. I don’t understand why some of them use it if they have the money to buy powerful enough systems to use Innodb with big databases (especially because innodb is so much better). But in the case of a normal user, MyIsam is still the best choice when dealing with huge databases as it is much faster. The… Read more »