I am using innobackupex to run nightly full backups of a mysqld database with great success. These are being streamed as tar, piped through gzip and redirected to backup.tar.gz.
Now I would like to add incremental backups to this base backup. The problem is, I need to be able to access the to_lsn value. Is there any easy way to do this??
The only lsn I can find in stderr is xtrabackup:
Transaction log of lsn (1096298065) to (1096298065) was copied.
I wrote a script which grabbed this and generated an 'xtrabackup_checkpoints' file using this (1096298065) as the to_lsn. The problem is, if I extract the xtrabackup_checkpoints file from the tar archive, it has the to_lsn of: 1399401679
Where does innobackupex pull this from? I can't find it in the output (stderr). Surely I'm not the only one who wants to stream to tar and also run incremental backups, am I? How does everyone else do it?
On Tuesday, 24 July 2012 16:44:44 UTC+10, Paranoid SysAdmin wrote:
> Hi,
> I am using innobackupex to run nightly full backups of a mysqld database > with great success. These are being streamed as tar, piped through gzip > and redirected to backup.tar.gz.
> Now I would like to add incremental backups to this base backup. The > problem is, I need to be able to access the to_lsn value. > Is there any easy way to do this??
> The only lsn I can find in stderr is xtrabackup:
> Transaction log of lsn (1096298065) to (1096298065) was copied.
> I wrote a script which grabbed this and generated an > 'xtrabackup_checkpoints' file using this (1096298065) as the to_lsn. > The problem is, if I extract the xtrabackup_checkpoints file from the tar > archive, it has the to_lsn of: 1399401679
> Where does innobackupex pull this from? I can't find it in the output > (stderr). > Surely I'm not the only one who wants to stream to tar and also run > incremental backups, am I? How does everyone else do it?
My personal opinion on this - if I want a backup regularly updated using
incremental backups so that I have the most recent possible data when I
have to do a restore, I would also want to be able to do the restore
quickly, so I wouldn't want the extra time needed to extract the backup
from a tar.gz file. So we always keep our more recent backup in unarchived
form on a separate volume, ready to be mounted in case failure happens.
The only time I tar.gz a backup is for transfer to offsite, and our
objective is still to take the quickest time possible to restore so what I
did is to do the full backup without streaming so that I can then
immediately prepare the backup. After preparing the backup I deleted the
xtrabackup_logfile so that when I do make at tar.gz, it's significantly
smaller.
On Friday, July 27, 2012, Paranoid SysAdmin wrote:
> Really? I'm the only one who wants to run incremental backups on streamed
> full backups?
> On Tuesday, 24 July 2012 16:44:44 UTC+10, Paranoid SysAdmin wrote:
>> Hi,
>> I am using innobackupex to run nightly full backups of a mysqld database
>> with great success. These are being streamed as tar, piped through gzip
>> and redirected to backup.tar.gz.
>> Now I would like to add incremental backups to this base backup. The
>> problem is, I need to be able to access the to_lsn value.
>> Is there any easy way to do this??
>> The only lsn I can find in stderr is xtrabackup:
>> Transaction log of lsn (1096298065) to (1096298065) was copied.
>> I wrote a script which grabbed this and generated an
>> 'xtrabackup_checkpoints' file using this (1096298065) as the to_lsn.
>> The problem is, if I extract the xtrabackup_checkpoints file from the tar
>> archive, it has the to_lsn of: 1399401679
>> Where does innobackupex pull this from? I can't find it in the output
>> (stderr).
>> Surely I'm not the only one who wants to stream to tar and also run
>> incremental backups, am I? How does everyone else do it?
>> Cheers
> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/percona-discussion/-/GkPZZ6JmRc0J.
> To post to this group, send email to percona-discussion@googlegroups.com<javascript:_e({}, 'cvml', 'percona-discussion@googlegroups.com');>
> .
> To unsubscribe from this group, send email to
> percona-discussion+unsubscribe@googlegroups.com <javascript:_e({},
> 'cvml', 'percona-discussion%2Bunsubscribe@googlegroups.com');>.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.
Thanks for the Opinion. I see your point however we are also piping through openssl and cannot afford to have unencrypted versions of our backups sitting on the server. I am setting up a work around using a truecrypt volume as a temporary space and writing a bash script to move the checkpoints file, tar and encrypt the backup then send it off to Amazon S3.
Security is the priority for our product rather than speed/uptime.
You just gave me one though. Is it okay to prepare the backup immediately after performing it? The documentation says to prepare the backup just before restoring it. But you said you prepare the backup, delete the logfile and then tar/gzip it. So this can be done long immediately after the backup is performed?
On Friday, 27 July 2012 10:20:42 UTC+10, Johann Tagle wrote:
> My personal opinion on this - if I want a backup regularly updated using > incremental backups so that I have the most recent possible data when I > have to do a restore, I would also want to be able to do the restore > quickly, so I wouldn't want the extra time needed to extract the backup > from a tar.gz file. So we always keep our more recent backup in unarchived > form on a separate volume, ready to be mounted in case failure happens. > The only time I tar.gz a backup is for transfer to offsite, and our > objective is still to take the quickest time possible to restore so what I > did is to do the full backup without streaming so that I can then > immediately prepare the backup. After preparing the backup I deleted the > xtrabackup_logfile so that when I do make at tar.gz, it's significantly > smaller.
> Just sharing my opinion =)
> Johann
> On Friday, July 27, 2012, Paranoid SysAdmin wrote:
>> Really? I'm the only one who wants to run incremental backups on >> streamed full backups?
>> On Tuesday, 24 July 2012 16:44:44 UTC+10, Paranoid SysAdmin wrote:
>>> Hi,
>>> I am using innobackupex to run nightly full backups of a mysqld database >>> with great success. These are being streamed as tar, piped through gzip >>> and redirected to backup.tar.gz.
>>> Now I would like to add incremental backups to this base backup. The >>> problem is, I need to be able to access the to_lsn value. >>> Is there any easy way to do this??
>>> The only lsn I can find in stderr is xtrabackup:
>>> Transaction log of lsn (1096298065) to (1096298065) was copied.
>>> I wrote a script which grabbed this and generated an >>> 'xtrabackup_checkpoints' file using this (1096298065) as the to_lsn. >>> The problem is, if I extract the xtrabackup_checkpoints file from the >>> tar archive, it has the to_lsn of: 1399401679
>>> Where does innobackupex pull this from? I can't find it in the output >>> (stderr). >>> Surely I'm not the only one who wants to stream to tar and also run >>> incremental backups, am I? How does everyone else do it?
>>> Cheers
>> -- >> You received this message because you are subscribed to the Google Groups >> "Percona Discussion" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/percona-discussion/-/GkPZZ6JmRc0J. >> To post to this group, send email to percona-discussion@googlegroups.com. >> To unsubscribe from this group, send email to >> percona-discussion+unsubscribe@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/percona-discussion?hl=en.
Yes. I asked about this last year since our xtrabackup_logfile is huge
every time we do a backup so I didn't want to have to make it part of the
tar.gz file. I remember it was also mentioned that being able to prepare
it is also a good sign the backup was a good one. I don't think you can
still apply an incremental after you do that though.
On Friday, July 27, 2012, Paranoid SysAdmin wrote:
> Thanks for the Opinion. I see your point however we are also piping
> through openssl and cannot afford to have unencrypted versions of our
> backups sitting on the server. I am setting up a work around using a
> truecrypt volume as a temporary space and writing a bash script to move the
> checkpoints file, tar and encrypt the backup then send it off to Amazon S3.
> Security is the priority for our product rather than speed/uptime.
> You just gave me one though. Is it okay to prepare the backup immediately
> after performing it? The documentation says to prepare the backup just
> before restoring it. But you said you prepare the backup, delete the
> logfile and then tar/gzip it. So this can be done long immediately after
> the backup is performed?
> Cheers
> On Friday, 27 July 2012 10:20:42 UTC+10, Johann Tagle wrote:
>> My personal opinion on this - if I want a backup regularly updated using
>> incremental backups so that I have the most recent possible data when I
>> have to do a restore, I would also want to be able to do the restore
>> quickly, so I wouldn't want the extra time needed to extract the backup
>> from a tar.gz file. So we always keep our more recent backup in unarchived
>> form on a separate volume, ready to be mounted in case failure happens.
>> The only time I tar.gz a backup is for transfer to offsite, and our
>> objective is still to take the quickest time possible to restore so what I
>> did is to do the full backup without streaming so that I can then
>> immediately prepare the backup. After preparing the backup I deleted the
>> xtrabackup_logfile so that when I do make at tar.gz, it's significantly
>> smaller.
>> Just sharing my opinion =)
>> Johann
>> On Friday, July 27, 2012, Paranoid SysAdmin wrote:
>>> Really? I'm the only one who wants to run incremental backups on
>>> streamed full backups?
>>> On Tuesday, 24 July 2012 16:44:44 UTC+10, Paranoid SysAdmin wrote:
>>>> Hi,
>>>> I am using innobackupex to run nightly full backups of a mysqld
>>>> database with great success. These are being streamed as tar, piped
>>>> through gzip and redirected to backup.tar.gz.
>>>> Now I would like to add incremental backups to this base backup. The
>>>> problem is, I need to be able to access the to_lsn value.
>>>> Is there any easy way to do this??
>>>> The only lsn I can find in stderr is xtrabackup:
>>>> Transaction log of lsn (1096298065) to (1096298065) was copied.
>>>> I wrote a script which grabbed this and generated an
>>>> 'xtrabackup_checkpoints' file using this (1096298065) as the to_lsn.
>>>> The problem is, if I extract the xtrabackup_checkpoints file from the
>>>> tar archive, it has the to_lsn of: 1399401679
>>>> Where does innobackupex pull this from? I can't find it in the output
>>>> (stderr).
>>>> Surely I'm not the only one who wants to stream to tar and also run
>>>> incremental backups, am I? How does everyone else do it?
>> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/percona-discussion/-/ZIDSboxEjAIJ.
> To post to this group, send email to percona-discussion@googlegroups.com<javascript:_e({}, 'cvml', 'percona-discussion@googlegroups.com');>
> .
> To unsubscribe from this group, send email to
> percona-discussion+unsubscribe@googlegroups.com <javascript:_e({},
> 'cvml', 'percona-discussion%2Bunsubscribe@googlegroups.com');>.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.
Paranoid SysAdmin <paul.christi...@gmail.com> writes:
> Thanks for the Opinion. I see your point however we are also piping > through openssl and cannot afford to have unencrypted versions of our > backups sitting on the server. I am setting up a work around using a > truecrypt volume as a temporary space and writing a bash script to move the > checkpoints file, tar and encrypt the backup then send it off to Amazon S3.
> Security is the priority for our product rather than speed/uptime.
We're working on encrypting built into XtraBackup streaming, so you'd be
able to stream an encrypted backup straight out of XtraBackup up into
S3.
Are there any specific crypto requirements you have?
