How to sync SQLite database on Android phone with MySQL database on server?

Question

I am developing an android application. I want to update the local SQLite database with MySQL database on server. I am not able to figure out that what is the most appropriate and standardized way to do so?

Answer 1

Create a webservice (REST is probably best) and serialize your SQLite/MySQL data and PUT/POST/GET it to/from your web service. This will give you a nice layer of abstraction in case you decide to switch from MySQL to something else server side.

Answer 2

You may want to take a look at fyrecloud.com/amsler This is source code for a demonstration Android application that implements MySQL replication between a MySQL server and the SQLite db on an Android device.

Amsler rests on two pillars:

1. It communicates with the MySQL server using the MySQL Client/Server protocol in order to connect to the server for authentication and for receiving replication events as they occur.

2. It uses the Antlr lex and parse software in order to lex and parse incoming replication events and then to translate the MySQL commands into equivalent SQLite commands.

This is great for one-way replication. You can simulate two-way replication by modifying the MySQL server indirectly via RESTful type methods and then watching while MySQL sends a new replication event back.

Accessing a server via REST is easy enough. However, modifying an existing MySQL installation in order to support serialization presents too many headaches to enumerate here. Amsler takes advantage of pre-existing replication services. REST also depends upon some polling strategy in order to keep the local device reasonably up-to-date. Again, many problems with this approach. Amsler maintains a TCP/IP connection to the server which enables server-push notification of updates.

The most difficult part of Amsler is in figuring out the lexing/parsing. The Syntax between MySQL, SQLite, and the various versions of the same have many subtle differences. So many differences that it's impractical to provide a shrink-wrap translator and instead you must resort to modifying the grammar yourself.

Nevertheless, good, bad, or ugly, here it is. Take a look and maybe the glove fits.

Answer 3

This is probably going to be helpful: sync databases Mysql SQLite

The real answer is that there is no standard or built in magic way to just copy a MySQL database that lives on a server somewhere to a device. You will have to implement either a webservice or somehow convert the MySQL db on the server to the android sqlite implementation and download that file into your app's data directory (not a route I'd recommend taking).

Answer 4

Late to the party, but http://www.symmetricds.org/ is a good solution.

Java, runs on Android too.

LGPL.

Can handle 10,000's of clients.