How best to implement synchronization of remote and local databases in both directions. So let's say I pressed a button and they exchanged data that is not in this or that database. There is an app for evaluating stores and it happens that there is no internet in the stores. And then came the check with the tablet rated the store by the criteria and the estimates are stored in a local database on the tablet. And when there is an Internet using the "Synchronize" command , both databases are synchronized (remote-local). Help at least in what direction to dig. Thank!
As one of the options, add three
date_delete fields to each table (for example,
TIMESTAMP DEFAULT NULL ). In the first two write the execution time of the same operations
UPDATE . Instead of actually deleting the string, set the current time in the
date_delete field. Also, you need to fix the last sync time somewhere.
At the time of synchronization, do a sample of all records that have been added, changed, or marked for deletion since the last synchronization. After successful synchronization, do a real deletion of records marked for deletion.
You also need to decide on the primary keys. In systems with distributed data storage, the primary key must be globally unique. The basis can be taken
UUID() . It generates a GUID-identifier. Autoincrement is not suitable here - the bases live separately from each other.
Synchronize data using the transaction on the receiving side. Given the connection instability, the transaction will ensure the integrity of the synchronization operation.
It's very interesting how you are going to install MySQL on the tablet.
To solve problems like yours, in HTML5 we made Local storage . At the same time, your web application can work without a server at all, and when connected to the server, transfer the data accumulated in the local storage to it.
Why install a MySQL on a tablet? SQLite copes with tasks. Most client software is written using SQLite. At the moment I am developing software for stores. Synchronizing the database using the method described by KiTE. Server - MySQL, SQLite clients