Single transaction and hard-linking

  • Michael Maraist

    Michael Maraist - 2008-01-13

    Before noticing the patches section, I made several incremental improvements.  But haven't yet cleaned them up to be patch-worthy.

    Specifically I've added md5 checked hard-links with --skip-comments (otherwise the DB's will have the time-stamp of the dump and thus change every night).  I've added hard-links that span days/weeks/months.. Such that if a DB doesn't change for 6 months, you'll have 7 daily, 5 weekly and inf-monthly links to the same file.  Great to see if a file has changed over time.  Even if a file DOES change every day, the weekly and monthly are still just hard-links.  Monthly now is just a trivial check to see if it's the first week of the month, if so, it makes a monthly hard-link to that week's file.

    Note that in my current code, this breaks the prior monthly backups concept (e.g. DBs that you don't care if you lose 29 days worth of data)

    Separately, I'm aware of the differences between --lock-tables and --single-transaction..

    Namely --lock-tables assures that any MyISAM and INNODB tables will lock in READ mode.  The --opt flag implies --lock-tables.  The problem is that for hundred-gig DBs, we can't afford to have multi-hour down-time (we're using DRDB instead of master-slave).  Thus we prefer --single-transaction which for INNODB ONLY DBs no locking occurs (not even a read-lock), and thus no application pausing is apparent (except for the extra disk-IO)

    I'm concerned about using --single-transaction with MyISAM tables though.. From what I've seen, what happens is that INNODB operates normally, but if there is a transaction that also hits a MyISAM table, that table locks.  So now instead of blocking all new transactions, you're keeping open a subset of transactions.. Potentially an even worse outcome.  I'm not sure if the MyISAM table locks if it's only performing a read.

    Does anybody know if it is safe to run --single-transaction with MyISAM tables?

    In the mean time, I've added a INNODB_DEFAULT="yes|no" and INNODB_DS="" and MYISAM_DBS="" set of variables, and added the --single-transaction option for fine-grained DB dumps as appropriate.

    We have multiple mysql machines and hundreds of DBs in every possible flavor (pure MyISAM, pure INNODB and mixed).

    Further, does anybody know the implications of --single-transaction for other ENGINE types?  Doesn't affect us, but as a general patch, I'd like to be safe.

    • gmckeown

      gmckeown - 2008-01-17

      I have not seen any problems with MyISAM tables with --single-transaction. I backup a log table with ~15 million rows with no issues. For InnoDB, nothing beats ibbackup from InnoBase. It costs $400 eur per year, but it is the best backup for InnoDB tables - especially for setting up slaves.

      One thing that I was surprised to see was no --port= setting, which I added to my script. Also, if you need to backup encrypted fields, they have to be blobs and then turn on --hex-blob, or the data will be useless after a restore. I also added in logging to a table over a wan link to my monitoring system, which is then viewable via an intranet.

      I would be glad to share if anybody needs the mods.


Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

No, thanks