3 ms·
Not saying that you should never do a full mysql dump. Nor that you should not ensure that you can import a full dump. But when you already use ZFS you can do
by clan 6y ago
Not saying that you should never do a full mysql dump. Nor that you should not ensure that you can import a full dump.
But when you already use ZFS you can do a very speedy full backup with:
mysql << EOF
FLUSH TABLES WITH READ LOCK;
system zfs snapshot data/db@snapname
UNLOCK TABLES;
EOF
Transfer the snapshot off-site (and test!). Either as a simple filecopy (the snapshot ensured a consistent database) or a little more advanced with zfs send/receive. This is much quicker and more painless than mysql dump. Especially with sizeable databases.
- mwcampbell 6y agoDo you even need to flush the tables and grab a read lock while taking the ZFS snapshot? My understanding was that since ZFS snapshots are point-in-time consistent, taking a snapshot without flushing tables or grabbing a read lock would be safe; restoring from that snapshot would be like rebooting after losing power.
- clan 6y agoI think you are correct. But then you risk data as you would with the unclean shutdown. I much prefer to have a known clean state which all things considered should be a safer bet. Just like some are OK running without fsync.
- pmlnr 6y agoGood point, but my DB is tiny, so for now, I can afford the mysqldump. But I'll keep this in mind.