Get only updated or inserted rows.

[Shanti C]

Hello All,

My problem is, i have one database and application in web server, another application in local server which is having another database . But some tables are same in database server and in my local database.

I want to update these particular tables from data base server into my local database for only insert or updated rows.

Please suggest me to do this.

[SEOCoder]

What ever application is performing the inserts and updates, edit it to update both servers.

[Shanti C]

thanks for your reply.
But one database is sitting in my local system.

[SEOCoder]

aslong as you have the IP address of the database and the correct user name and password you can update it. (This is ofcourse assuming that you keep the local system powered up and connected to the net)

[griswolf]

SEOCoder has the best answer: Push the data when you have it. If that is not possible (for instance, your local system is not always available to the server), then you need a "pull" system.

If the web server table has a unique and growing key (timestamp, id), you can SELECT from the remote server where (whatever-it-is) > (whatever-it-was-last-time-i-did-this)

If not, please consider that to be a problem and fix it...

[Shanti C]

thanks all for your replies.
But how to update my local database table if any existing rows got updated in server database.

[SEOCoder]

Your application would have to update the table, so in the same way you would update your local database every time an INSERT is perform you can do the same every time an UPDATE is performed.

If the purpose of this is to create a back up, you can just use PHPMYADMIN to create daily backup to ZIPPED files.

[Shanti C]

Thanks SEOCoder..

Your application would have to update the table, so in the same way you would update your local database every time an INSERT is perform you can do the same every time an UPDATE is performed.

can u give any example for this?

[smantscheff]

MySQL supports replication. Have your web database as the master and replicate it to your home server - which means in effect that only the changes are transferred to the replica.
Or try the poor man's version: run your web database with a text log and play it from time to time into your local installation.

[SEOCoder]

Its hard to give examples without knowing exactly what you are working with.

Basically, wherever you have a MYSQL connection, you would need to replicate this for your additional server. Then where you have an INSERT or UPDATE statment make a connection to your database to update it.

[mwasif]

MySQL replication is the perfect solution if you have own the server or can make changes to mysql configuration file. Because setting up replication requires changes to my.cnf.

[Shanti C]

thanks smantscheff,mwasif and SEOcoder for all your suggestions.
I will check all these suggestions.
thanks again.

[mwasif]

A nice place to start with Replication.

[Shanti C]

Thank you so much mwasif.
but i have one doubt,
can replication be done between remote database server and local database server???

[smantscheff]

Of course. Replication is a mechanism to spread a master database to several slave locations. If you can establish establish a connection between your remote and your local machine, then you can set up replication.

[Shanti C]

thank you smantscheff and mwasif.