Use AWS MySQL Aurora With Preview Environments

[rophilogene (Romaric Philogene)]

Hi ,

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.

[moisesrodriguez]

Hi @rophilogene I went this route thinking it would be super fast for Preview Environments, but I think I’m doing something wrong because it is not fast at all. It takes a solid 15 minutes to clone and 12min to destroy! It’s a small database of about 20 MB. Almost feels that is faster just to create an SQL script with mysqldump and stay with container-mode kubernetes MySQL DB I have.

This is what I did in my sandbox environment. In AWS created manually an Aurora Serverless v2 database and then for my blueprint lifecycle-job used this:

data "aws_rds_cluster" "rds_cluster" {
  cluster_identifier = "aurora-sandbox"
}

resource "aws_rds_cluster" "sandbox_clone" {
  cluster_identifier  = "pr-${var.branch}"
  engine              = "aurora-mysql"
  source_region       = "us-west-2"
  database_name       = "testing"
  storage_encrypted   = true
  skip_final_snapshot = true
  db_subnet_group_name = data.aws_rds_cluster.rds_cluster.db_subnet_group_name
  vpc_security_group_ids = data.aws_rds_cluster.rds_cluster.vpc_security_group_ids

  restore_to_point_in_time {
    source_cluster_identifier  = "aurora-sandbox"
    restore_type               = "copy-on-write"
    use_latest_restorable_time = true
  }
}

resource "aws_rds_cluster_instance" "cluster_instances" {
  count                 = 1
  identifier            = "pr-${var.branch}-${count.index}"
  cluster_identifier    = aws_rds_cluster.sandbox_clone.id
  instance_class        = "db.serverless"
  engine                = aws_rds_cluster.sandbox_clone.engine
  engine_version        = aws_rds_cluster.sandbox_clone.engine_version
}

output "rds_instance_endpoint" {
  description = "The Cluster connection endpoint"
  value       = aws_rds_cluster.sandbox_clone.endpoint
}

Do you see anything wrong with what I did?

Note: I checked the lifecyle-job lifecycle-job-examples/examples/aws-rds-with-terraform at main · Qovery/lifecycle-job-examples · GitHub, but from what I can tell is not for an Aurora DB.

[rophilogene (Romaric Philogene)]

Hi @moisesrodriguez , it seems to be good for me. However, I know that AWS still takes some time to spawn an Aurora instance, unfortunately. However, this option is great if your database is large (let’s say more than 20GB).

cf.

@rophilogene

Using MySQL restore command : it’s super slow to restore +100GB… it’s almost unusable in the context of Preview Environments.

Otherwise, you’re better using a simple MySQL container instance. It will be faster.

Another option is to consider using PlanetScale, but I have no experience with their product so far. It looks similar to what we did with Neon for Postgres.