I'm looking for effective ways to implement daily backups for my MySQL 8.0 setup, which runs in a standalone Docker environment using named volumes. Given that I have a consistent high-volume metrics ingestion every 24 hours, I need a reliable way to capture a "master" state of my data right before that ingestion. I'm currently considering two main methods for backups and would love your input on them:
1. A logical backup using mysqldump.
- My current command for this is:
`docker exec mysql-container mysqldump --single-transaction --all-databases > backup.sql`
2. A physical filesystem snapshot using the "Sidecar" pattern.
- My command for this one looks like:
`docker run --rm --volumes-from mysql-container -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /var/lib/mysql`
Are either of these methods good, or do they present risks?
4 Answers
I follow the guidelines from Percona XtraBackup. Mysqldump is fine for smaller databases, but if you’ve got hundreds of gigs, restoring can take forever. Copying files can be quicker, but you should pause the server or lock the tables during backup. Always remember, just dumping files isn’t safe for all storage types, especially with InnoDB tables.
Your second method might lead to a corrupt backup. Backing up a live database without freezing the filesystem can result in inconsistent states. It's safer to stick with methods designed for the type of backup you're doing. If you're looking for speed, you might check out mydumper—it's faster than traditional mysqlbackup options.
I’d suggest going with mysqldump. The --single-transaction flag is crucial because it gives you a consistent snapshot of InnoDB without locking anything. If you choose the tar option on live data, it can lead to issues unless you properly lock it first, which causes downtime. A tip: consider piping through gzip and saving it off-host, like this: `docker exec mysql-container mysqldump --single-transaction --all-databases | gzip > /backup/$(date +%F).sql.gz`. And definitely run regular restore tests on a disposable container to ensure your backups actually work!
I've been using a daily cron job for over two years to back up my database. It's pretty straightforward—just a shell script that takes a snapshot and deletes any backups older than four weeks. Works like a charm!
Ah, so are you doing a full SQL dump, or just snapshots?