I have a large MySQL database on my server. The size of the database is around 100 Gb. There are many tables in this database. I want to transfer this database to another server. I want to insert around 1000 records at a time and I'm planning to do this using a PHP script. My plan is to create a lookup table to define table names and insert the data by checking the largest insert id and then take the next 1000 records and then insert them. How good is this approach? Suggest some better solution/code if you have done this before
I guess this is a production Database. So, such large Database while you move data there will be some transactions that are modifying the DB contents. So, much better to use an existing tool. For this case I would recommend you MySQL Replication. I have used it to migrate SQL Server DB's as this is one recommended way and found that for MySQL it is an equivalent way. Once the DB be fully replicated you will need to change your links to the new DB, and stop replication. On this way no information should be lost.