Our DB is hosted in AWS MySQL RDS. The data is such that each company that signs up gets its own set of tables which then receive enormous amount of data in them.
Currently we want to move a chunk of rows from existing company table (having 200+million rows) to a new company table. We need to be careful of the auto increment ID in the new table and make sure all data is moved else rollback will be required.
If you can stop writes long enough (minutes? hours?) to do the copy (and delete?), then there is no issue. Simply copy the current ids.
If the two databases need to somehow not duplicate ids, that is another problem; so state.
If ... -- Please update your question.
Data Archiving
Assuming you want to move "old" data to somewhere else, while leaving the "recent" data intact, PARTITIONing is very efficient -- once it is set up.
The setup requires a huge ALTER TABLE to add partitioning. However, with a 'small' downtime, that can (probably) be done with pt-online-schema-change or gh-ost.
The partitioning would be BY RANGE and each partition would correspond to the chunk you want to 'archive'. However, I recommend no more than about 50 partitions (for other reasons).
With the partitioning in place, you can use "transportable tablespaces" to peel off the oldest partition, turning it into a table. This is fast. Then you can do whatever you like with that table without impacting the 'real' table.
This would require adding a new partition at the same time (essentially zero cost).
