MySQL – Selecting and Inserting Data from Two Different Servers

MySQLpsql

I have two server mysql and psql. I am fetching data from mysql server database table and inserting it into my psql server. The way I am doing is by selecting all the column from mysql server and saving it into an array. 

sql->prepare("SELECT id, date,studentName, FROM student", array());

variablArray= array();

Then inserting the data in psql with the help of implode().

psql->prepare("INSERT INTO student",implode(",", $variableArray);

The problem is every time I ran the query I too 45 min to complete, because I truncate the psql database table before inserting so that the query can run.

Is there is an another way to do this?

Best Answer

If the duration is related to data volume, you should consider syncing only the modified data:

  • put an IsDirty flag on each student record
  • sync (delete + insert or simple updates) data only for records where IsDirty = 1

This should work decently if a fairly small amount of records change between the sync.

Another option is to try to sync data directly between MySql and PSql as indicated here. Also, take a look upon dblink.

Related Question