What’s the Best Way to Back Up MySQL in Docker?

I've been running a daily cron job for two years to back up my DB in a Docker container. It just uses a simple shell script to take a snapshot and cleans up any backups older than four weeks. It's really straightforward and it works well for me.

I recommend going with the first option, using mysqldump. The `--single-transaction` flag you're using ensures you get a consistent snapshot for InnoDB without locking the tables, which is key. The second option with tar could lead to issues unless you ensure the database is flushed and locked before taking the backup, which could introduce downtime.

You might also want to pipe your dump through gzip to save space, like this: `docker exec mysql-container mysqldump --single-transaction --all-databases | gzip > /backup/$(date +%F).sql.gz`. And don't forget to schedule test restores in a throwaway container to ensure your backups are effective! They're only as good as your ability to restore them.

I’d be cautious with your second option; it’s likely to result in a corrupt backup since backing up a running database isn’t usually reliable unless you pause writes. I get that it feels simple and quick, but using a backup tool designed specifically for MySQL will be much safer in the long run. You might also explore the tool mydumper, which is reportedly faster than the standard MySQL backup methods!

I found that mysqldump works well for smaller databases, but once your DB gets larger, you might spend hours just restoring it. Consider looking into tools like Percona XtraBackup if you have databases over a few hundred gigs. It can copy files faster, but do remember to stop the server or lock the tables while backing up to avoid any corruption. Also, if you have a replica, that’s even better since you won’t disturb your master while making backups.