best way to import production data into mariadb for clean up for test environments

Question

Hi,

previously I was using mysqldump to get a copy of our production database, and importing it into another database, and scripting the scrubbing of all the data, however, the dump file has now exceed 38gb, and we are looking at trying to improve the process.

We were looking at using snapshots, but can load into existing db.  so a new db needs to be stood up each time.
we would still need to use mysqldump to get a copy of the scrubbed data out, but I am trying to improve the process to be most efficient due to speed and size.

Thanks!

Answer

Hi,

From the notes seems like you need a copy of data from Prod to Test environment (RDS MariaDB to another RDS MariaDB)(Correct me if I understand wrong). And you have used below method:

- mysqldump --> I assume as the size of the dump file grow you want something faster since it is single thread process
- snapshot restore --> each time you will need to spin up a new instance which is not ideal for you as well

Note: If my understanding is wrong feel free to correct me.

If use case is migrating from instance A to B, and not have to spin up different instance each time/fast migration/easy to manage, I would suggest below two method.

1. DMS, with this approach, you can take the initial load, and using snapshot or dump the database and objects then use DMS to migrate data only. Use target prep mode Truncate before Load, you can have a fresh copy of the data each time when restart the task with just one click, DMS truncate the target and reload the data. DMS comes with FullLoad only, CDC only (on going replication) or FullLoad + CDC.

The only thing is that for MySQL/MariaDB to MySQL/MariaDB migration is that it comes with limitations, but if none of the limitations applies to your environment, the set up the DMS can save a lot of time and easy to manage once get used to it.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html#CHAP_Source.MySQL.Limitations

2. Use third party migration tool such as mydumper myloader: https://github.com/mydumper/mydumper

It is similar to the native mysqldump but having the ability of multi-thread so it can be faster than native tool.

best way to import production data into mariadb for clean up for test environments

Relevant content