Copying data from a table

[amir bilal]

Hi All,

I have an application in which I am creating dynamic shops from a huge database of products. For achieving performance we are expanding our server architecture.

Now we have a separate database server on which is dedicated for hosting the data of created shops. When a new shop will be created, we will create a database with the name of the shop on this database server and create its core tables.

Now we have to copy the data for each shop from the Master database server to this database. The data can vary from 5000 products rows to 200000 and sometimes even more.

I have different ideas to copy this data like:

1- export the data to a file and then use LOAD FROM FILE to import into new database server
2- MySQL bulk import -> INSERT INTO tbl1(column) values(1),(2),(3);
3- Use the SELECT-INSERT [I do not think that it will be possible to select from one DB server and insert into another DB server]


Pleas give me your valuable thoughts on this so that I can opt for the best approach.

Best regards,