4

Basically what I'm looking for is a way to tell mysql (percona-flavored) to take the following steps:

  • Flush all committed transactions to the filesystem
  • Start buffering all transactions (keep the filesystem in a coherent state)
  • Wait for me to do something (take a zfs snapshot)
  • Unbuffer writes and resume normal operations

A solution which allows DDL statements to break the backup are acceptable (because this backup solution will be applied frequently and DDL statements will be very infrequent - worst-case scenario would be having to roll back a db one hour more than expected).

Backup operations should be minimally disruptive to performance, but this is not a system under heavy load. Solutions which perform this procedure per-database or system-wide are both fine, though the latter is somewhat preferred.

HonoredMule
  • 187
  • 11

1 Answers1

2

If all of your databases are in InnoDB format, then you don't need to do this. InnoDB will automatically make your data consistent at startup if you do a restore.

longneck
  • 22,793
  • 4
  • 50
  • 84
  • Is there any benefit to doing it anyway? What if, for storage efficiency, the binlogs were on a separate dataset and not saved with the snapshot (or worse, there is no binlog)? It seems to me if this is true then there isn't much point in having the mysqlbackup command, because the same could be said of just raw-copying the files under a running system. – HonoredMule Dec 19 '16 at 16:17
  • You only need the binlog if you want point-in-time recovery, or you're using replication or MyISAM tables. If you're fine with recovering to the last backup, then file system snapshots work just fine. The mysqlbackup command is useful because it generates consistent backups BETWEEN databases. Within a single database, InnoDB handles consistency, – longneck Dec 19 '16 at 16:25
  • Ok that makes sense. It feels a little sloppy, but given that I'll also be doing (much less frequent) backups with mysqldump on the important databases, my bases should be covered. – HonoredMule Dec 19 '16 at 16:37
  • It's worth noting that I do have some MyISAM tables and that in particular the built-in schemas mysql and information_schema use MyISAM. So the alternative backup approach is really necessary at least for those databases, and I'll be looking into converting them (particularly the mysql and information_schema ones). – HonoredMule Dec 19 '16 at 17:15