We can sync local DB (which is running in each physical store) with remote DB (centralized DB) which is running in web.

Complexity
– Local DB always not connected with internet.
– We can not use mysql replication (two way sync) because it requires that both DB
should run in web. otherwise, remote DB not understand and sync with local DB.
– Even, if we are achieving i.e two way sync in some other way, all remote DB data going to sync with local DB. This is not good idea to maintain all the store details in all places.
– May some tables are one-way sync from remote DB to local DB vice versa.
– May some tables are condition based sync from remote DB to local DB (eg): Products table

Pre Condition
– Assume that we have mysql running in local & remote server
– We had created database in both places (local & remote server) which we are going to sync
– We have created tables in both Database (local & remote server)

SymmetricDS
– SymmetricDS is best fit for this requirements after reviewing number of open source tools like SqlYog, Todd, MySql Benchmark..etc..,
– It is developed by Java but it can run as standalone application in server. Means, no need tomcat or weblogic servers.
– SymmetricDS should be run in both places (local & remote)
– It is listening and syncing all the changes based on the configuration.

System Requirements
– SymmetricDS is written in Java 5 and requires a Java SE Runtime Environment (JRE) or Java SE Development Kit (JDK) version 5.0 or above.
– It supports MySQL version 5.0.2 and above

Installing SymmetricDS
– Download the source from http://sourceforge.net/projects/symmetricds/files/
– Unzip the file in any directory you choose. This will create a symmetric-ds-3.x.x directory, which corresponds to the version you downloaded.
– Please install the same version in both local & remote system.

SymmetricDS configuration for Remote
– Copy the [samples/corp-000.properties] properties files for the server into the [engines] directory of the SymmetricDS installed
– Change the property file name to [server.properties] instead of [symmetric.properties]
– Do the changes in the following property file

– Import configuration tables that is needed for symmetricDS by executing the following
command using CLI. It will create 41 tables with prefix [sym_]

– –Two way sync configuration (or) Default router
The simplest router is a router that sends all the data that is captured by its
associated triggers to all the nodes that belong to the target node group defined in
the router. A router is defined as a row in the ROUTER table. It is then linked to
triggers in theTRIGGER_ROUTER table.

The following SQL statement defines a router that will send data from the ‘corp’ group
to the ‘store’ group.

One way sync configuration (or) Column Match Router
Consider a table that needs to be routed to only nodes in the target group whose
STORE_ID column matches the external id of a node. The following SQL statement will
insert a column router to accomplish that.

SymmetricDS configuration for Local
– Copy the [samples/store-001.properties] properties files for the local into the [engines] directory of the SymmetricDS install
– change the property file name to [client.properties] instead of [symmetric.properties]
– Do the changes in the following property file

– Please apply [store id] from DB table to [external.id]
– set the following property in the client.properties file to specify where the server node
can be contacted

Starting SymmetricDS
Database setup and configuration is now completed. Time to put SymmetricDS into action.
We will now start both SymmetricDS nodes and observe the logging output.
– Start SymmetricDS by executing:

— The server and client nodes both start up. The server node creates all the
triggers that were configured by the sample configuration. It listens on port
8080 for synchronization and registration requests.
— The client node server starts up and uses the auto-creation feature to create
the SymmetricDS system tables. It begins polling the server node in order to
register. Since registration is not yet open, the client node receives an
authorization failure (HTTP response of 403).

– Registering a Node
Open registration for the client node server by executing:

— The registration is now opened for a node group called “store” with an external
identifier of “1”. This information matches the settings in client.properties for
the client node. Each node is assigned to a node group and is given an
external ID that makes sense for the application. In this tutorial, we have retail
stores that run SymmetricDS, so we named our node group “store” and we
used numeric identifiers starting with “1”. More information about node groups
will be covered in the next chapter.
— Watch the logging output of the client node to see it successfully register with
the server node. The client is configured to attempt registration once per
minute. Once registered, the server and client are enabled for synchronization!

– Sending an Initial Load
— Next, we will send an initial load of data to our store (that is, the client node),
again using the server node administration feature.
— Open a command prompt and navigate to the samples subdirectory of your
SymmetricDS installation.
— Send an initial load of data to the client node server by executing:

— With this command, the server node queues up an initial load for the client
node that will be sent the next time the client performs its pull. The initial load
includes data for each table that is configured for synchronization.
— Watch the logging output of both nodes to see the data transfer. The client is
configured to pull data from the server every minute.

Conclusion
The above steps are needed to be followed for syncing local & remote DB. The following point
are very useful for us.
– [external.id] should be unique for each store configuration and it should not be
changeable and it must be a store id which is created by Administrator in store table.
This store id is a reference for one way sync.
– Data will not sync with remote DB if server not started in local but data will be sync
after starting local symmtricDS server viceversa.
– We need to add entry in [sym_router] & [sym_trigger_router] for every new table
that is used by our application.
– For every new store, need to [registering a node] with remote server.
– For every new store, need to recognize [initial load] that is sent by remote server.
then only, sync will happen.