Comments on: A MyISAM backup is blocking as read-only, including mysqldump backup https://shlomi-noach.github.io/blog/mysql/a-myisam-backup-is-blocking-as-read-only-including-mysqldump-backup Blog by Shlomi Noach Mon, 06 Jun 2011 05:34:24 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: Kevin https://shlomi-noach.github.io/blog/mysql/a-myisam-backup-is-blocking-as-read-only-including-mysqldump-backup/comment-page-1#comment-42633 Mon, 06 Jun 2011 05:34:24 +0000 https://shlomi-noach.github.io/blog/?p=2441#comment-42633 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.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/a-myisam-backup-is-blocking-as-read-only-including-mysqldump-backup/comment-page-1#comment-13815 Wed, 19 May 2010 12:04:53 +0000 https://shlomi-noach.github.io/blog/?p=2441#comment-13815 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 */");
    }
]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/a-myisam-backup-is-blocking-as-read-only-including-mysqldump-backup/comment-page-1#comment-13814 Wed, 19 May 2010 12:02:33 +0000 https://shlomi-noach.github.io/blog/?p=2441#comment-13814 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.

]]> By: Kostja Osipov https://shlomi-noach.github.io/blog/mysql/a-myisam-backup-is-blocking-as-read-only-including-mysqldump-backup/comment-page-1#comment-13813 Wed, 19 May 2010 11:45:54 +0000 https://shlomi-noach.github.io/blog/?p=2441#comment-13813 What about mysqlhotcopy?

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/a-myisam-backup-is-blocking-as-read-only-including-mysqldump-backup/comment-page-1#comment-13811 Wed, 19 May 2010 10:59:22 +0000 https://shlomi-noach.github.io/blog/?p=2441#comment-13811 @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.

]]> By: Pierre-Yves https://shlomi-noach.github.io/blog/mysql/a-myisam-backup-is-blocking-as-read-only-including-mysqldump-backup/comment-page-1#comment-13810 Wed, 19 May 2010 10:33:19 +0000 https://shlomi-noach.github.io/blog/?p=2441#comment-13810 “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?

]]> By: Mark Robson https://shlomi-noach.github.io/blog/mysql/a-myisam-backup-is-blocking-as-read-only-including-mysqldump-backup/comment-page-1#comment-13784 Tue, 18 May 2010 21:29:54 +0000 https://shlomi-noach.github.io/blog/?p=2441#comment-13784 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.

]]>