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

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.