Mysql database sync between two databases

Question

We are running a Java PoS  Point of Sale  application at various shops  with a MySql backend  I want to keep the databases in the shops synchronised with a database on a host server   When some changes happen in a shop  they should get updated on the host server  How do I achieve this

User · Answer

Have a look at Schema and Data Comparison tools in dbForge Studio for MySQL  These tool will help you to compare  to see the differences  generate a synchronization script and synchronize two databases

User · Answer

SymmetricDS is the answer  It supports multiple subscribers with one direction or bi-directional asynchronous data replication  It uses web and database technologies to replicate tables between relational databases  in near real time if desired   Comprehensive and robust Java API to suit your needs

User · Answer

Replication is not very hard to create  Here s some good tutorials  http   www ghacks net 2009 04 09 set-up-mysql-database-replication  http   dev mysql com doc refman 5 5 en replication-howto html http   www lassosoft com Beginners-Guide-to-MySQL-Replication Here some simple rules you will have to keep in mind  there s more of course but that is the main concept    Setup 1 server  master  for writing data  Setup 1 or more servers  slaves  for reading data   This way  you will avoid errors  For example  If your script insert into the same tables on both master and slave  you will have duplicate primary key conflict  You can view the  quot slave quot  as a  quot backup quot  server which hold the same information as the master but cannot add data directly  only follow what the master server instructions  NOTE  Of course you can read from the master and you can write to the slave but make sure you don t write to the same tables  master to slave and slave to master   I would recommend to monitor your servers to make sure everything is fine  Let me know if you need additional help

User · Answer

three different approaches    Classic client server approach   don t put any database in the shops  simply have the applications access your server   Of course it s better if you set a VPN  but simply wrapping the connection in SSL or ssh is reasonable   Pro  it s the way databases were originally thought   Con  if you have high latency  complex operations could get slow  you might have to use stored procedures to reduce the number of round trips  replicated master master  as  Book Of Zeus suggested   Cons  somewhat more complex to setup  especially if you have several shops   breaking in any shop machine could potentially compromise the whole system   Pros  better responsivity as read operations are totally local and write operations are propagated asynchronously  offline operations   sync step  do all work locally and from time to time  might be once an hour  daily  weekly  whatever  write a summary with all new modified records from the last sync operation and send to the server   Pros  can work without network  fast  easy to check  if the summary is readable    Cons  you don t have real-time information

[mysql] Mysql database sync between two databases

Examples related to mysql

Examples related to database