How to update remote MySql with new entries from local database?

Hello, I have a mysql database on remote server and the same database on local server. On every 5 minutes the local database is updated with new entries. New entries are coming from rss feeds.

Until now I used crappy way with php script to update remote database, when processing rss feeds:

"INSERT INTO news (src,cat,title,text,url,img,date) VALUES ('$src','$cat','$title','$text','$url','$img','$date') ON duplicate KEY UPDATE url=url"

This sends all entries from RSS feed, but some feeds are with 100 entries, so each time, i send 100 INSERT queries, but actually new are only a few. The remote database rejects them, because there is DUPLICATE for url.

I want to update remote database, but with minimal server loading on remote server, with sending only a new data. Can you advise me how to do that.

Thank you very much. Sorry for rough english. I have limited mysql knowledge.
core2Asked:
Who is Participating?
 
todd_farmerCommented:
you might just add a timestamp column to the tables that need updating, and select everything that has a value > the previous timestamp used.
0
 
todd_farmerCommented:
You probably need to either look into replication.  If you are using 5.1, you could use FEDERATED tables.

Otherwise, you would have to know what the other database has before you extract the data to be input into the second box.
0
 
core2Author Commented:
Version of Mysql on remote and local server are 4.1.22-standard.

I think that every time when update remote DB, script can check sum of all records on remote table and send to remote DB the difference. If I have on remote table 3200 entries and on local table 3500 i must send the entries the last 300 entries. I don't know how exactly to do that.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
core2Author Commented:
Thanks, I'm almost done. This is really simple and will work just fine. Now i'm on last stage, inserting a full array of collected data.
0
 
todd_farmerCommented:
You might just do SELECT ... INTO OUTFILE on the first machine, then load it using LOAD DATA INFILE on the second machine.  That probably would be the easiest.
0
 
core2Author Commented:
Ah... my hosting provider probably is denying LOAD DATA INFILE, cos it's gives me error:

#1045 - Access denied for user 'xxx'@'localhost' (using password: YES)

I'm logged on phpmyadmin and remote is the same. If I make simple querys there is no problem with user and pass. Can I do something wrong?

$link3 = mysql_connect(xxx.xxx.xxx.xxx', 'username', 'password') or die('Could not connect: ' . mysql_error());
mysql_select_db('my_db_name') or die('Could not select database');
$query3 = "LOAD DATA INFILE '/tmp/result.txt' INTO TABLE news";
$result3 = mysql_query($query3, $link3) or die('Query failed: ' . mysql_error());
mysql_free_result($result3);
mysql_close($link3);

0
 
core2Author Commented:
I've do it with inserting a array, It works fine. I prefer to use LOAD DATA, but this is good enough too. Thanks for help todd_farmer.
0
 
todd_farmerCommented:
Thanks for the points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.