I'm currently working on a cron job that involves masking and modifying specific columns in a MariaDB 10.6 database. The process includes taking a dump of the production database using mysqldump and then applying it using the mysql command. Afterward, certain tables with sensitive data are updated. However, I'm encountering a significant issue related to the restore step, where the mysql restore command takes a substantial amount of time (around 2 hours) compared to the speedy 10-15 minutes it takes to produce the 35 GB dump.
Here are the commands I'm using:
the mysqldump command:
mysqldump -h ${sourceDbParams.host} -u ${sourceDbParams.user} --single-transaction --extended-insert --quick --disable-keys --set-charset -p${sourceDbParams.password} ${sourceDbParams.database} > /tmp/database_dump.sql
the mysql command:
mysql -h ${targetDbParams.host} -u ${targetDbParams.user} -p${targetDbParams.password} ${targetDbParams.database} < /tmp/database_dump.sql
Some context:
The production database is hosted on an AWS RDS instance with 16 vCPUs and 64GB of memory. The masked database is on an AWS RDS instance with 8 vCPUs and 16GB of memory. The cron job runs on an EC2 instance with 2 CPUs and 4GB of memory. How can I improve the SQL restore performance for this process?
I've tried optimizing the mysqldump command with options like --opt. Any suggestions to make the mysql restore process more efficient and reduce the time it takes to complete the restore operation would be greatly appreciated.