synchronizing local and server database

Question

I am developing a billing system software. For this I have created a database it contains many tables database and this is in local system all transactions data will be stored in local systems database.

I would like to provide data backup tables in server. Whenever the person(billing s/w user) wants he/she can upload the data to server(only newly added data should be uploded if existing data is there).

If local system data is currepted or get deleted by some reason it can be downloaded from the database server. This all features should be done by using billing software. How to do this.

Answer 1

Replication, and daily / hourly backups (depending on your need), probably with something like xtrabackup, and storing the binlogs in between, give your pretty good point-in-time recovery. You might even configure pt-slave-delay to let your actual backup lag somewhat, useful for those moments where you are aware of an accidental deletion within the time-frame of your delay, making the restoration a bit less cumbersome.

I do not understand the requirement 'all features should be done by using billing software'. Billing software is for billing, not for database maintenance.

Answer 2

+1 for @Wrikken's post. All you are doing by trying to achieve this functionality in your billing software is increasing the chances of bugs, increasing your development time, decreasing you/your company's potential revenue.

Let the database do what it does best - you concentrate on getting the billing side of your app to work properly. MySQL's replication and Percona's XtraBackup have been tested by 100's of thousands, if not millions of user. What makes you think that you can do this task any better than the MySQL and Percona teams?

People reinventing the wheel are just like those who don't know history - doomed to repeat its mistakes. Take a look at this thread - and my response. It isn't about home-grown replication, but rather home-grown RI (Referential Integrity), however the same points apply.