I experimented with 3 options to clone MySQL instances with Qovery Preview Environments — My takeaway: use AWS MySQL Aurora or equivalent! You’ll save time and money…
Here are my notes:
Using MySQL restore command : it’s super slow to restore +100GB… it’s almost unusable in the context of Preview Environments. I’d not even recommend this! (no option like with Postgres to use COPY instructions instead of INSERT INTO …)
Percona XtraBackup : I did run some tests — it works nicely (backup and restore are fast because of copying DB flat files and reading binary logs), but using it means that you need to install and run it from your production cluster to create the dump! This is impossible if your prod cluster is a managed MySQL cluster by AWS.
→ However, one option would be to restore the dump into a temporary MySQL database (that’s how I tried) and create the dump with xtrabackup and archive on S3 the dump that preview environments could use.
But if you go that way, it means that you need to attach an AWS EBS (supported by Qovery) for each MySQL instance — and this is super costly!!!
AWS MySQL Aurora : This is for me the best option in terms of cost and speed! Why? Aurora provides a clone feature with Copy-On-Write! This means that cloning is almost instantly, and you pay only for additional data you write on your preview env. So it’s super cost-effective and easy to integrate with the Qovery Preview Environment by using this example: lifecycle-job-examples/examples/aws-rds-with-terraform at main · Qovery/lifecycle-job-examples · GitHub (feel free to use Terraform, Cloudformation or what you prefer).
Hope it helps!
I also published this tip on my Medium.