Render Disks make it effortless to deploy production-grade MySQL on Render backed by high performing SSD disks with automatic snapshots so your data is protected against database restarts and failures.
However, surely it’s not safe to simply snapshot the render disk (and therefore /var/lib/mysql) on a running MySQL instance? This may well lead to data corruption.
The example MySQL repo example seems to hint at this as there’s a separate Dockerfile for backups which runs a mysqldump command (one of the correct ways of backing up the data). This writes to /var/lib/mysql/backups so I suppose would be snapshotted as part of the render disk. However, I don’t see how this links up to the service? Is this supposed to be set up as a cron? If so, how can it access the render disk associated with the MySQL example service?
I really feel like either I’m missing something or the docs are. Please advise!
Hi @russx2, I agree that the MySQL docs should say more about backups. You can create a cron job that does mysqldump backups. Note however that we don’t currently support cron jobs with attached disks (this feature is planned, see Disks for Cron Jobs | Feature Requests | Render). As an alternative I’d recommend uploading your backups to a cloud object store like S3, GCS, or wasabi. Note that cron jobs get 2 GiB of temporary disk space. So if your backup is larger than 2 GiB you’ll want to stream the mysqldump output right into an object storage bucket.
Sorry that this requires jumping through a few hoops. We hope to improve this experience going forward. For what it’s worth, our managed postgres offering does come with daily pg_dump backups.
Render doesn’t currently offer a managed MySQL service, so you’d need to implement a solution to take the backups yourself.
Maybe a Background Worker with a Disk running a job queue that can take regular backups and save them to the disk.
I know you specifically mentioned not using external services but a Cron Job taking backups and saving them to AWS S3 (Cron Jobs can’t have disks) is quite a clean solution and may be more cost-effective than running a Background Worker 24/7. We have an example of this flow for Postgres: https://github.com/render-examples/postgres-s3-backups
As the running instance is the only one that can access the disk, you’d likely need some sort of endpoint in your app that could trigger a backup script/process on that instance.
If you’re thinking of using S3, maybe cut out the disk and upload assets to it directly. That approach would also then avoid some of the considerations required when using a disk.