What is the best way to make an online database backup

Asked

Viewed 1,046 times

3

There is a client of mine who owns two machines, one of them is the server where all the data of the bank is, and the other is the cashier.

However, there is customer concern in case something happens to the server the data gets saved somewhere on the internet. Therefore, I via C# (which is made the application of the box) Gero a file ". sql" every night with the backup of all database data. And I send this ". sql" via FTP to a server of mine.

My big doubt is if the method I am using is good, because we notice that the more time passes, the bigger the file ". sql" keeps getting longer if it takes to upload the file to the FTP server. I would like to know whether the method used is good and viable or whether there is something better.

c# mysql backup

  • Search by "redo log", which is basically a file that only grows and is never modified. This file can be copied "hot", using some tool as simple as rsync. Already the database files should never be copied hot (I think Oracle refuses to open a copied database like this) because the file will certainly be corrrompido and is modified everywhere, complicating the life of a rsync of life.

  • If the database is Mysql, here is some information about Innodb’s "redo log": https://blogs.oracle.com/mysqlinnodb/entry/redo_logging_in_innodb

1 answer

4

The problem is bandwidth/file size.

I don’t know if mysql can backup differently. If it was an MS SQL or Oracle I would do a differential backup per day and one full per week (probably, but that depends on each scenario). Anyway you are asking only by the media (save in the cloud, disk, tape, internet, etc) and there will be much difference between one and the other beyond these upload/download times etc.

I’d say your method is good for small bases and a lot of band. But also you should check how long it will take you to restore that base by donwloading your ftp server.

Remember the backup is just one of the requirements to allow a system restore. Check if you have a contingency plan for these occurrences and how long it will take (and if you will actually mitigate the situation).

Back to the x of the question. Check the possibility of differential backups or if there is another server available to be the Storage of these files. When DBA worked "accidental" we had a tape for each day of the week and took several differential backups and one full each day while the other six tapes were in a safe.

  • To complement what @jean said, can’t you "get rid of some tables, or some records?" for example logs, delete those from long ago? And this file it grows at an absurdly large rate?? If you think it grows at a fast pace and very large you could start thinking about other database alternatives, a No-SQL of life for example. But if this change becomes unviable, you may consider using the [Replication](http://dev.mysql.com/doc/refman/5.0/en/replic

  • It does not grow at an absurd rate, but the problem is the weak connection of the place. The bank is now with 2 megas, and there it takes about 10 minutes to send to the server (I tested on my computer, on my connection and only takes 1 minute). Are differential backups not too much trouble? For example, if after a 2 year problem on the server I would not have to take ALL scripts and run all in order?

  • 2 megas is a tiny base. You can fill a rewritable DVD with lots of these backups. No differential backup is ~"what happened since the last full backup". Dai you restore the last full and the differentials, in order they occurred, then. So it is still necessary full backups.

  • 1

    In your case even a 1 GB flash drive will be a Flash drive, get 7 cheap flash drives and use one per week as Flash Drive remembering to change every day and saving the six in a physically separate location from the server you will have no problems

Browser other questions tagged c# mysql backup

You are not signed in. Login or sign up in order to post.