Skip to content

# A MyISAM backup is blocking as read-only, including mysqldump backup

MySQL Backup InnoDB MyISAM May 18, 2010May 18, 2010

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
7
Leave a Reply

7 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
5 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Mark Robson

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.

Guest
Pierre-Yves

“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?

Author
shlomi

@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 »

Guest
Kostja Osipov

What about mysqlhotcopy?

Author
shlomi

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.

Author
shlomi

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 */");
        }

Guest
Kevin

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 »

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.