Move large mysql table data from one server to another

Question

I have a table to which millions of data are inserted every day. As the table size grows more than 10 GB, I have issues with the performance of server in fetching apis and generating reports, which are based on above table. What i am planning to do it is move the old, say 1 week, data to another server and keep the current table size always small. I am running a php application to fetch api and generate reports on the server.

My concerns are:

1. Is it the right approach or are there other solutions to my problem?
2. What is the best way to do it?

Answer 1

You could use a cron job to connect to the existing database, make a backup of the table, select the rows you need to move (over one week old) disconnect from the sql server, connect to the new server, insert the rows you have previously selected using a loop, disconnect from the second server, reconnect to first server, and delete the rows you don't need anymore.

That's one way of doing it, I don't know if it's optimal, but it would work, do not forget to plan the cron job to run Only when the first server is at its lowest usage to make sure you don't impact the performance of the live server.

Answer 2

I would export the data to a flat file, gzip it, transmit it to the remote server, unzip it.

Disable the indexes on the destination host's table, load the data, re-enable indexes.

This might be time consuming at the enable indexes phase, but it would be the fastest transmission and load time.

Answer 3

Look at why your queries are slow. Even with 10G of data this is still quite a small database. Examining the details of how they queries are executed and what indexes are used could solve your report/API problems.