Move large mysql table data from one server to another 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:


Is it the right approach or are there other solutions to my problem?
What is the best way to do it?
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.