asked on

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.

todd_farmer

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.

core2

ASKER

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.

ASKER CERTIFIED SOLUTION

todd_farmer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

core2

ASKER

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.

todd_farmer

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.

core2

ASKER

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);

core2

ASKER

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.

todd_farmer

Thanks for the points!