3 ms·
With smallish databases, just pipe the .dump command to gzip. No need to snapshot an entire filesystem just to back up a few hundred megabytes (what I would con
by kijin 3y ago
With smallish databases, just pipe the .dump command to gzip. No need to snapshot an entire filesystem just to back up a few hundred megabytes (what I would consider "smallish"). Backup and restore speeds aren't a significant concern at those sizes, anyway, and you get human-readable backups that can be inspected with standard Unix tools.
- deberon 3y agoWe would back up double-digit GB MySQL databases by piping mysqldump into gzip as well. Like you I’m sure there’s a size limit where that doesn’t work. I never found out if it was CPU or memory constrained though.
- sgarland 3y agoWhere I’m at, the DBAs shifted from pipes to temporary files. They said at the double-digit TB size, it would occasionally fail otherwise. This was with Postgres / barman / pigz, FWIW. Never saw it myself, so I have no idea what the cause was.
- kijin 3y agoI've used mysqldump|gzip when migrating a ~1TB database to an incompatible version of MySQL/MariaDB. It's slow but very reliable. I think the single-threaded pipe is the bottleneck. The process never took up more than one full CPU core.
- maskros 3y agoOr use "VACUUM INTO" to get a backup that is is even faster (and easier) to restore.