Expose your database on the internet on the cheap

the-green-cloud

Here’s the (unlikely) scenario: you have data sitting in a database on a machine at home or in the office that you want to refer to from time to time. You don’t or don’t want to leave the machine running 24/7. The data set is relatively small. You don’t need completely up-to-date data all the time.

The usual solution to make data from a machine in a private network available on the internet is to leave the machine running 24/7, setup dynamic DNS and port forwarding for the database server port. All quite straightforward. But if the machine is not in use most of the time, it’s a bit of a waste leaving it on all the time.

Here’s an alternative solution: if you have a webhosting account (for hosting your blog or website, like this one you’re reading right now), you have a machine that’s available 24/7, so why not use it (webhosting accounts are cheap by the way)?

Ok, here comes the difficult part. You need to replicate the database tables onto the database running in your webhosting server. MySQL actually comes with a replication function, but as you would have guessed, you wouldn’t normally be given the privileges required for performing replication with a typical webhosting account. All is not lost, as you can still do “brute force” syncing of tables, that is, relying on the normal SQL statements (insert, update, delete). Unfortunately, there are no free tools that will do this for you, but commercial ones are available. You’ll just have to schedule the syncing to run at intervals that will meet your minimum requirement for data freshness.

Now, the logical next step that should come to your mind is accessing the database from a mobile device, and there are MySQL client apps for both iOS and Android – that is if you’re comfortable with writing SQL statements. However, if your aim is to let a layperson access the data, you’d probably need to create an app.

I had a go at this, creating an Android app that allows me to access my Openbravo POS customer data (ok, this just reminded me of security considerations). For readers who are technically inclined, this involved creating a RESTful webservice that queries the database and output the data in JSON format, Base64 encoding/deconding of utf-8 database columns and using a Loader in Android to run the download-and-store-in-database task in the background. All in all, a two days’ effort for a newbie in Android. Sure beats having to beam contacts manually.

(Visited 27 times, 1 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *