You might want to look into replication
(http://dev.mysql.com/doc/refman/5.5/en/replication.html). You can set up a replication
slave to follow the master DB in real time, or offset by minutes, hours, days, or weeks,
or whatever. That way you have a copy already served up waiting in the wings, very
accessible. It's best to have at least one slave that is offset by at least a day IMHO
because the problem could be human error and this will be faithfully replicated to the
slave. MySQL replication works very well. Doing a dump is useful in some situations but
we are more and more looking to more convenient ways, as storage and hardware is pretty
cheap but time in a critical failure is not cheap.
On Mar 15, 2011, at 1:51 PM, Wm Mussatto wrote:
> On Tue, March 15, 2011 12:36, Joerg Bruehe wrote:
>> Adarsh Sharma wrote:
>>> Dear all,
>>> Taking Backup is must needed task in Database Servers. [[...]]
>>> We have options RAID, mylvmbackup , mysqldump. But it depends on the
>>> company requirement too.
>> RAID is no backup!
>> A RAID system may give you protection against a single disk drive
>> failing (depending on the RAID level you configure), but it doesn't
>> protect you against misuse, operator error, malware (virus, trojan),
>> wilful deletion, ...
>> RAID is no backup! (This can't be repeated often enough.)
>> Doing a backup means to take the data (including schema, privileges,
>> passwords, triggers, ...) to some independent media where it is safe
>> from all misfunction on the original machine.
>> IMNSHO, a backup must be taken offline or write-protected in some other
>> way, so that even a misfunction of the backup machine does not damage
>> your backup.
>> Old tape drives (or newer tape cartridges) with their physical write
>> protection (ring, slider, ...) did provide such protection, it is a pity
>> that they are too slow and too small for today's data (or too expensive
>> for most people).
>> With disks, my solution is:
>> - Have the backup disks on a separate machine, via the network.
>> - Have external backup disks, which are powered off if not is use.
>> - Have two (or more) and use them alternating, so that even in case of a
>> misfunction or drive failure (affecting the backup disk currently in
>> use) the previous backup (on the other disk) remains safe.
>>> We have a database of more than 250GB in mysql database & which is
>>> increasing day by day. Currently I am using mysqldump utility of MySQL
>>> I perform a full backup about 28 days ago. But is there any mechanism or
>>> script to backup only the incremental backups on weekly or daily bases.
>>> Data is inserted in separate tables in separate databases. We cann't
>>> afford to have some proprietary solution.
>> If you can afford downtime (shutting down the database), "dirvish" is a
>> good means to take a file system backup (all your data areas). Check it
>> at www.dirvish.org There are plenty of alternatives, but I didn't try
>> most of them. What I did try was "rsnapshot", but I found it too
>> inflexible for my purposes.
>> I can't comment on the other approaches.
>> Whatever approach you take: Make sure the backup gets stored os some
>> separate, protected media.
>> Joerg Bruehe, MySQL Build Team, joerg.bruehe@stripped
> Another advantage of external drives is they can be taken OFF site and
> stored away from the building. We use three. One on site, one in transit
> and one that mirrors (off site of course) the images of the other two. We
> dump nightly and then backup that. We also backup the binary logs which
> get rotated every two days (restore is nightly back followed by the binary
> logs). The only only restore we have had to do is the "nephew who knows
> html". The disks are raided, but as was stated, that is to protect
> against single point failure.
> William R. Mussatto
> Systems Engineer
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1