MySQL 5.6 Backup and RecoveryKristian Köhntopp

A consistent backup2

State(all tables)

New State(table modified)

ConsistentSnapshot

DataChangingStatement

logged tobinary log

A consistent backup

• Many methods to make a backup

• All valid methods:

• Backup a set of tables (atomic!)

• No writes to tables during backup!

• Record a binlog position w/ backup

3

A consistent backup

• No writes to tables during backup mean:

• Long lasting locks OR

• Snapshotting (plus recovery) OR

• Writes logged elsewhere (MVCC) plus log apply

4

Backup Types

• Logical backup: SQL dump of data.

• Small, compresses well.

• MTTR: load takes ages due to index rebuild.

• Generally not useful past 10G size.

• Selective restore (individual tables).

5

Backup Types

• Physical backup: Recording of on-disk tables

• Ensure valid on-disk image

• FLUSH TABLES WITH READ LOCK (or comparable)

• Conserves errors in data files.

• No selective restore.

• Restore at disk speed, scales (esp. w/ LVM snapshots).

6

Logical Dump

• mysqldump

• --user … --host … -P…

• --databases db1, …

• --events --triggers --routines

• --single-transaction

• --master-data=2

7

Logical Dump

• --single-transaction

• Logs writes to undo log (ibdata1 grows).

• works only w/ pure InnoDB databases.

8

Logical Dump

• --master-data=2

• Record CHANGE MASTER statement

• Take note of binlog position

• As a comment

• Implies proper locks

• Knows about --single-transaction

9

Physical Dump

• Proper Sequence:

• FLUSH TABLES; FLUSH TABLES WITH READ LOCK

• Check ‘Slave_open_temp_tables == 0’ (or repeat)

• lvcreate --snapshot …

• UNLOCK TABLES

• You now have a snapshot in LVM.

10

Physical Dump

• mount -o rw,nouuid /dev/…/snapshot

• (optional: recover snapshot)

• Backup Snapshot

• umount /dev/…/snapshot

• lvremove -f /dev/…/snapshot

11

Physical Dump

• Performed by mylvmbackup Perl script

• Lenz Grimmer

• https://launchpad.net/mylvmbackup

12

mylvmbackup vs. MyISAM

• mylvmbackup works fine with MyISAM

• FLUSH TABLES

• Force Index changes to disk, no locks

• FLUSH TABLES WITH READ LOCK

• Fast, no index changes

• Block out writes

13

mylvmbackup vs. InnoDB

• mylvmbackup works fine with InnoDB

• Tablespace + Redo Log + Undo Log.

• Recovery necessary (before backup or on restore).

• requires innodb_flush_log_on_trx_commit = 1

• Recovery:

• before backup or after restore?

14

–Martin Seeger

„Nobody wants backup. Everybody wants restore.“

15

Point in Time Recovery16

Sunday,Midnight

Monday,Midnight

Tuesday,Midnight

binlog.00000108:12

binlog.00000218:31

binlog.00000322:52

binlog.00000404:18

binlog.00000517:49

binlog.00000603:47

binlog.00000716:25

• Daily backups made • Binlogs saved

• On crash, recover from full backup. • Replay binlog from full backup to end.

A Point in Time

• A point in time in MySQL:

• A binlog file name

• An offset into this binlog

• Not a point in time:

• A timestamp, no matter how accurate

• Multi-core machines = true concurrency

17

Point in Time Recovery

• Use mysqlbinlog:

• Start at binlog position of full backup.

• For crashes:

• read until end.

• For oopsed:

• read to before destructive command.

18

Point in Time Recovery

• Recovery script is just a SQL script:

• Can use perl, sed to fix it or filter it.

• Can use editor to mess with it.

19

–Gwenn Dana Stoll, Technical Directory, web.de (2003)

„Every Monday and Thursday the on-shift sysadmin will randomly select a database and restore it on the spare

host.“

20