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.
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 only pain in the *** is mysqldump.
I often check your site and hope that you publish a *magical* tool to work around this issue but it seems this is a hopeless problem lol.