Moving One Billion Rows in MySQL (Amazon RDS)

So you may remember from our article in November of 2014 about our switch to Redshift, that Koddi uses Amazon Web Services (AWS) to power our platform. While we have moved some of our data to Redshift, we still have quite a bit in MySQL (RDS), and at the beginning of this year we needed to move our main database from one AWS account to another. The normal process when creating a copy of a database in RDS is to take a snapshot and spin up a new database from this snapshot. However, Amazon doesn’t allow you to share snapshots between accounts. This posed the question, how do we efficiently migrate over a billion rows of data?

Using mysqldump

One common way to way to get around Amazon’s limitations is to use a server as an intermediary between the two databases. Using mysqldump you can create an archive of your entire database, and then load that archive into the new database. This process works wonderfully for small databases (<10GB), however our database was nearing 1TB at the time. As database size increases, mysqldump gets slower and slower to the point that it is no longer a feasible option.

Finding Another Way

After doing some more research, we decided to go with the well known ETL (extract, transform and load) tool, Talend. Our data move process consisted of three parts:

Screenshot 2015-05-13 17.37.21

Step 1

To start we used a tAmazonMysqlConnection to connect to our original RDS database. Once connected, a tAmazonMysqlInput was used to select the data and a tFileOutputDelimited was used to output the selected data to a CSV file. Once complete, the connection to the original database was closed with a tAmazonMysqlClose.

Step 2

Next, we used a tS3Connection to connect to Amazon Simple Storage Service. S3 does exactly what its name says; simply (and securely) stores files. A tS3Put was used to move the CSV file to the cloud, and then we closed off the connection with tS3Close.

Step 3

Last, we connected to the new database using another tAmazonMysqlConnection and used a tAmazonMysqlRow component to load the data from the CSV into the new database. We used MySQL’s LOAD DATA command with the LOCAL INFILE parameters to retrieve the data from the CSV and load it into the new database.

Implementing the Solution

Now that we had a process in place for one table, we just had to copy it for all the other tables in our database. One beneficial feature of Talend is its ability to email on the success (or failure) of specific components. This allowed us to know which tables had been moved, and if any errors had occurred during the transfer. Once set up, the Talend job took about two weeks to complete.

After all the tables had been copied over to the new database, we just had to move over new data that had been loaded during the time Talend had been running. During a scheduled maintenance period, we reran the job to grab this data. Since this was only two weeks worth of data, it didn’t take more than a few hours to complete.

Looking Back

In the end, the overall process of moving one billion rows with Talend wasn’t too bad. However, it was much harder than it should have been. Amazon already provides the ability to share EC2 snapshots between accounts. If they are able to extend that functionality to all their platforms it would simplify the data move process. Until then, this workaround was successful for us, and hopefully it helps someone else as well!

Categories
Technology