Backup Policy

Dear all,

Taking Backup is must needed task in Database Servers. I research a lot
and find techniques to perform it in Mysql.

We have options RAID, mylvmbackup , mysqldump. But it depends on the
company requirement too.

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.


Any suggestions will always Welcome.


Thanks & best Regards,

Adarsh Sharma

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Backup Policy

Hi,

What storage engine are you using?

Peter Boros

On 03/15/2011 02:12 PM, Adarsh Sharma wrote:
> Dear all,
>
> Taking Backup is must needed task in Database Servers. I research a lot
> and find techniques to perform it in Mysql.
>
> We have options RAID, mylvmbackup , mysqldump. But it depends on the
> company requirement too.
>
> 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.
>
>
> Any suggestions will always Welcome.
>
>
> Thanks & best Regards,
>
> Adarsh Sharma
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Backup Policy

--00032555afce5c0654049e875846
Content-Type: text/plain; charset=ISO-8859-1

xtrabackup, mysqlhotcopy for myisam, incremental backup using zamanda.

Krishna

On Tue, Mar 15, 2011 at 9:09 PM, petya wrote:

> Hi,
>
> What storage engine are you using?
>
> Peter Boros
>
> On 03/15/2011 02:12 PM, Adarsh Sharma wrote:
>
>> Dear all,
>>
>> Taking Backup is must needed task in Database Servers. I research a lot
>> and find techniques to perform it in Mysql.
>>
>> We have options RAID, mylvmbackup , mysqldump. But it depends on the
>> company requirement too.
>>
>> 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.
>>
>>
>> Any suggestions will always Welcome.
>>
>>
>> Thanks & best Regards,
>>
>> Adarsh Sharma
>>
>>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
>
>

--00032555afce5c0654049e875846--

Re: Backup Policy

Hi,

there is a lot of info on different backup methods here:

http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

For example, for incremental backups see "Making Incremental Backups
by Enabling the Binary Log",

cheers Andy.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Backup Policy

----- Original Message -----
> From: "Krishna Chandra Prajapati"
>
> incremental backup using zamanda.

I'm running Zmanda on about two dozen hosts, and it comes well-recommended. It doesn't do anything that you can't do yourself, but it's easy to set up, reports well and backs up in what are basically tarfiles, so if all else fails you can still do manual restores.

If you have plenty of servers, the enterprise version adds a web interface that the free doesn't have - not a huge thing, but very nice to have. Support is reasonably responsive, albeit at times a bit... well, prone to asking the obvious :-)

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Backup Policy

Hi!


Adarsh Sharma wrote:
> Dear all,
>=20
> Taking Backup is must needed task in Database Servers. [[...]]

Correct.

>=20
> 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.

>=20
> 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 o=
r
> script to backup only the incremental backups on weekly or daily bases.=

>=20
> 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.


HTH,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
(+49 30) 417 01 487
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Backup Policy

On Tue, March 15, 2011 12:36, Joerg Bruehe wrote:
> Hi!
>
>
> Adarsh Sharma wrote:
>> Dear all,
>>
>> Taking Backup is must needed task in Database Servers. [[...]]
>
> Correct.
>
>>
>> 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.
>
>
> HTH,
> J�rg
>
> --
> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com

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
http://www.csz.com
909-920-9154


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Backup Policy

--Apple-Mail-1--205417438
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=utf-8

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.

Jim McNeely=20

On Mar 15, 2011, at 1:51 PM, Wm Mussatto wrote:

> On Tue, March 15, 2011 12:36, Joerg Bruehe wrote:
>> Hi!
>>=20
>>=20
>> Adarsh Sharma wrote:
>>> Dear all,
>>>=20
>>> Taking Backup is must needed task in Database Servers. [[...]]
>>=20
>> Correct.
>>=20
>>>=20
>>> We have options RAID, mylvmbackup , mysqldump. But it depends on the
>>> company requirement too.
>>=20
>> RAID is no backup!
>>=20
>> 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, ...
>>=20
>> RAID is no backup! (This can't be repeated often enough.)
>>=20
>> 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.
>>=20
>> 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).
>>=20
>> 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.
>>=20
>>>=20
>>> 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.
>>>=20
>>> Data is inserted in separate tables in separate databases. We cann't
>>> afford to have some proprietary solution.
>>=20
>> 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.
>>=20
>> I can't comment on the other approaches.
>>=20
>> Whatever approach you take: Make sure the backup gets stored os some
>> separate, protected media.
>>=20
>>=20
>> HTH,
>> Jï¿=BDrg
>>=20
>> --
>> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
>=20
> 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
> http://www.csz.com
> 909-920-9154
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Djim@newcenturydata.com
>=20


--Apple-Mail-1--205417438--

Re: Backup Policy

Interestingly, this page does not say anything about MySQL Enterprise
Backups.


On Mar 15, 2011, at 8:48 AM, a.smith@ukgrid.net wrote:

> Hi,
>
> there is a lot of info on different backup methods here:
>
> http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html
>
> For example, for incremental backups see "Making Incremental Backups
> by Enabling the Binary Log",
>
> cheers Andy.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=abvk@apple.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org