[Ilugc] MySQL database sync
Hi, This is my scenario: * A master database in the internet with data from multiple office branches. * Each branch office has sporadic access to the internet. Hence sections of the database need to be stored offline in the branch. * Every evening, the head office should be a able to view/modify/delete data from the branches (only operate on the master database) (changes from branch uploaded in evening). * Next day morning the branch office should be able to see updated database. (download the modified contents overnight or early morning) Both the master database and the branch run on Linux. To my knowledge, I understand that MySQL replication is a one-way street. One master many slave system and slaves only sync themselves to the master and the master does not sync itself to the slaves. Am i wrong? Any simple solution for this? Regards, Arun ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
On Friday 28 October 2011 12:18 PM, Arun Venkataswamy wrote: > * A master database in the internet with data from multiple office branches. > * Each branch office has sporadic access to the internet. Hence sections of > the database need to be stored offline in the branch. > * Every evening, the head office should be a able to view/modify/delete data > from the branches (only operate on the master database) (changes from branch > uploaded in evening). > * Next day morning the branch office should be able to see updated database. > (download the modified contents overnight or early morning) If you are not bound to MySQL and RDBMS, do have a look at CouchDB. -- .o. I'm a Free man. I use Free Software. ..o ooo http://www.joesteeve.org/ signature.asc Description: OpenPGP digital signature ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
On Fri, Oct 28, 2011 at 12:23 PM, Joe Steeve wrote: > > If you are not bound to MySQL and RDBMS, do have a look at CouchDB. > > Pretty much bound to MySQL for now. Even considering another RDBMS is not possible! Regards, Arun ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
Is there anything stopping you from running a cron job that updates the master, with the records from the slave at the given time of the day when you have connectivity? On Fri, Oct 28, 2011 at 12:27 PM, Arun Venkataswamy wrote: > On Fri, Oct 28, 2011 at 12:23 PM, Joe Steeve wrote: > > > > > If you are not bound to MySQL and RDBMS, do have a look at CouchDB. > > > > > Pretty much bound to MySQL for now. > Even considering another RDBMS is not possible! > > Regards, > Arun > ___ > ILUGC Mailing List: > http://www.ae.iitm.ac.in/mailman/listinfo/ilugc > -- Regards Santhosh *github: *https://github.com/santhotech *twitter | Facebook :* @santhotech *Linkedin: *http://www.linkedin.com/pub/santhosh-kumar/27/bb/5a1 ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
Hi, On Friday 28 October 2011 01:00:35 PM IST, santhosh kumar wrote: > Is there anything stopping you from running a cron job that updates the > master, with the records from the slave at the given time of the day when > you have connectivity? Is there anything that is stopping you from reading the mailing list guidelines (http://www.ilugc.in/content/mailinglist-guidelines/) and avoid top posting? Interleaved, trimmed posting style is the recommended one. So *do not top post*. Thanks & Regards, Guruprasad ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
On Fri, Oct 28, 2011 at 1:00 PM, santhosh kumar wrote: > Is there anything stopping you from running a cron job that updates the > master, with the records from the slave at the given time of the day when > you have connectivity? > > This solves the update on the master side. With binlogs option in MySQL this is very simple. It beautifully stores all the operations done and we can replay it on the master. The problem is mainly on the reverse side as I dont want to send mega bytes of unwanted updates on the non related branches to all brances. Regards, Arun ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
Greetings, On Fri, Oct 28, 2011 at 1:13 PM, Arun Venkataswamy wrote: > On Fri, Oct 28, 2011 at 1:00 PM, santhosh kumar wrote: > > This solves the update on the master side. With binlogs option in MySQL this > is very simple. It beautifully stores all the operations done and we can > replay it on the master. The problem is mainly on the reverse side as I dont > want to send mega bytes of unwanted updates on the non related branches to > all brances. > Now we are talking about distributed database with two phase commits etc. Oracle of course has this feature. At a Price of course. I am not sure MySQL does it. Checkout PostgreSQL. -- Regards, Rajagopal ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
On Friday 28 October 2011 01:13 PM, Arun Venkataswamy wrote: > This solves the update on the master side. With binlogs option in MySQL this > is very simple. It beautifully stores all the operations done and we can > replay it on the master. The problem is mainly on the reverse side as I dont > want to send mega bytes of unwanted updates on the non related branches to > all brances. IMHO, this is not something that should be solved by the database software. This is something that the application should solve. The application should be architected in such a way that, it syncs up with the head-quarters when requested. -- .o. I'm a Free man. I use Free Software. ..o ooo http://www.joesteeve.org/ signature.asc Description: OpenPGP digital signature ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
Thanks all for pitching in to help! I am not in a position to change the database software from MySQL. I guess the only way to do it is programmatically record all the changes at the master side (all SQL update/insert/delete commands) on a per branch basis and send that information for playback at the branch databases. Regards, Arun ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
On 29 October 2011 11:06, Arun Venkataswamy wrote: > Thanks all for pitching in to help! > I am not in a position to change the database software from MySQL. > > I guess the only way to do it is programmatically record all the changes at > the master side (all SQL update/insert/delete commands) on a per branch > basis and send that information for playback at the branch databases. If you plan to implement the above. I think the following information would be helpful. Mysql maintains a binlog file ( as specified in my.cnf configuration file) which has a log of all mysql db changes made. So having a application to reading it and updating the db would be far easier than having to write a application to record all db changes and relaying to the master db. --ashwin ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] MySQL database sync
On Sat, Oct 29, 2011 at 8:59 PM, ashwin kesavan wrote: > On 29 October 2011 11:06, Arun Venkataswamy wrote: > > Thanks all for pitching in to help! > > I am not in a position to change the database software from MySQL. > > > > I guess the only way to do it is programmatically record all the changes > at > > the master side (all SQL update/insert/delete commands) on a per branch > > basis and send that information for playback at the branch databases. > > If you plan to implement the above. I think the following information > would be helpful. Mysql maintains a binlog file ( as specified in > my.cnf configuration file) which has a log of all mysql db changes > made. So having a application to reading it and updating the db would > be far easier than having to write a application to record all db > changes and relaying to the master db. > > --ashwin > ___ > ILUGC Mailing List: > http://www.ae.iitm.ac.in/mailman/listinfo/ilugc > You can use inotify to detect changes. Whenever there is a change in a file an event will be generated by the system. Your application can register for that event and do necessary things, in other time the application can sit idle (wait for the event without using any system resources). ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc