Mysql – synchronizing local and server database

backupMySQLmysqldumprestore

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.

Best Answer

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.

Related Solutions

Mysql – How to open a MySQL Database Dump

Although there are a number of tools out there, I prefer the command line for tasks such as this. Better performance, and just simpler.

Make sure you're logged into the mysql client:

mysql -u <username> -p

The first step is to create a database in mysql that you want the data in:

CREATE DATABASE <database name>;

Then you need to open--or use--that new database, which is empty:

USE <database name>;

All that's left is to do the import:

\. /path/to/file (or if in Windows, \. C:\PATH\TO\FILE)

If all goes well, you'll see a bunch of output about "Query successful, N rows affected" and so on.

Mysql – Synchronise two databases (but not really)

Yes, but by using tricks. MySQL has statement based replication, which causes the same SQL to be executed on the target as on the source. So the question is, can you write a SQL statement that does what you want? The answer is yes if you can exclude the rows in the target somehow. One way to do this would be a column that is 0 on all the sources, but which you update to 1 on the target by some mechanism (batch job, trigger, etc). Then when you execute the DELETE on the source WHERE ... AND is_target=0 the statement will do nothing when it is replicated.

There is no way that I am aware of to simply have the target system filter out deletes (this can be done with with Shareplex so I assume GoldenGate too, but that costs money).

Best Answer

Related Solutions

Mysql – How to open a MySQL Database Dump

Mysql – Synchronise two databases (but not really)

Related Question