[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-07-25
8
Medium Priority
?
1,305 Views
Last Modified: 2013-12-12
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.
0
Comment
Question by:core2
  • 4
  • 4
8 Comments
 
LVL 30

Expert Comment

by:todd_farmer
ID: 19567557
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
 

Author Comment

by:core2
ID: 19567714
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
 
LVL 30

Accepted Solution

by:
todd_farmer earned 2000 total points
ID: 19567788
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:core2
ID: 19568523
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
 
LVL 30

Expert Comment

by:todd_farmer
ID: 19568605
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
 

Author Comment

by:core2
ID: 19569266
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
 

Author Comment

by:core2
ID: 19570381
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
 
LVL 30

Expert Comment

by:todd_farmer
ID: 19570467
Thanks for the points!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month18 days, 17 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question