Hacker News Reader

Comment by pmlnr | Hacker News Reader

I only have a personal server running in Hetzner but it's mirrored onto a tiny local computer at home.

They both run postfix + dovecot, so mail is synced via dovecot replication. Data is rsync-ed daily, and everything has ZFS snapshots. MySQL is not set into replication - my home internet breaks often enough to have serious issues, so instead I drop everything every day import a full dump from the main server, and do a local dump as backup on both sides.

I don't have automatic failover set up.

View on HN

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.

Do 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.

I 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.

Good point, but my DB is tiny, so for now, I can afford the mysqldump. But I'll keep this in mind.

Reply on news.ycombinator.com