Two weeks ago we had the first small get-together of MySQL users in Helsinki. About a dozen people attended, so we had a good chance to talk shop in detail. It would be interesting to expand this group further, plus I know several people who'd probably have interesting stuff to share but couldn't make it. So, we'll have to do this again. Any volunteer organizers?

Our DBAs had put together a bit of material that I spoke about, since they couldn't make it this time. I promised to post it here, but I've been really busy with other stuff, so here goes - better late than never.

How to back up MySQL using free tools

As far as production backups go, mysqldump doesn't really cut it. Too slow, too much load to the database, locks too many things, etc. Innobackup is good, but sometimes you don't want to invest in the licenses. Here's how to do it with free tools only, Linux + LVM (applicable to other environments).

The good:

  • It’s free
  • Works with InnoDB and MyISAM
  • Requires only split second lock for database
  • Backup copy is complete copy of database files
  • Very straightforward approach

The bad:

  • Can only do full backup.
  • Partial database recovery is not possible.
  • Requires split second lock for database. Do not have long queries running when starting backup.
  • Performance is decreased when snapshot is on. Copying hundreds of gigabytes of database files takes long time.
  • Linux LVM is not perfectly stable in high I/O load. Running out of LVM snapshot allocation halts the volume entirely.

How to:

  1. If database is “large” take backup from replica (any big database should afford to have a replica!)
  2. Stop slave! Relay-logs are very easily corrupt and LVM snapshot will corrupt them
  3. Flush tables with read lock (see below)‏
  4. Take snapshot of database volume with lvcreate -s
  5. Release lock from step 3
  6. Mount the snapshot
  7. Copy data to safe place (for transferring to tapes, if that's your desire)
  8. Unmount snapshot and remove it (keeping the snapshots around in production is a bad idea!)
  9. Backup is done
Flush tables with read lock doesn't properly prepare InnoDB for the backup. You have to do it from within a transaction that writes to InnoDB log files in order to get everything consistent:
-- Transaction is needed to fool InnoDB,

-- otherwise InnoDB logs might be corrupt in backup copy.

DROP TABLE IF EXISTS tmp_backup_dummy;

CREATE TABLE tmp_backup_dummy(a INT) TYPE=INNODB;

SET AUTOCOMMIT=0;

INSERT INTO tmp_backup_dummy VALUES (1);

-- Freeze things.

FLUSH TABLES WITH READ LOCK;

COMMIT;

This is fairly trivially set up as a script that can be executed in cron. The backup is a set of the actual binary database files, which can be placed in a new MySQL instance that will recover the InnoDB log files on startup and is a bit-for-bit copy of the original database. You can even continue to apply binary logs on top of this in order to do point-in-time recovery, if you make the backup script record the binlog position. Perhaps we'll share the actual script we use later after cleaning it up some.