PDA

View Full Version : error restoring first incremental backup after a full



robinbowes
September 18th, 2008, 05:09 AM
Hi,

I've got two ZRM backup jobs scheduled:


14 12 * * * mysql-zrm-scheduler --now --backup-set app01-full
34 * * * * mysql-zrm-scheduler --now --backup-set app01-txn-incr

So, that's a full backup at 12:14 everyday, and an incremental backup at 34 mins past the hour, also every day.

I am testing the restore process on a different machine (same MySQL version).

First I restore the full backup:


mysql-zrm-restore --source-directory /var/lib/mysql-zrm/app01-full/20080917121401/

Then I restore the first incremental backup since the full backup:


mysql-zrm-restore --source-directory /var/lib/mysql-zrm/app01-txn-incr/20080917123401/

However, this fails with the following output:


restore:INFO: ZRM for MySQL Community Edition - version 2.0
BackupSet1:restore:WARNING: Binary logging is off.
BackupSet1:restore:ERROR: Output of command: 'mysql -e "source /tmp/hz90tKx0lD;"' is {
ERROR 1062 (23000) at line 14 in file: '/tmp/hz90tKx0lD': Duplicate entry 'bdf7c536-d5fc-102b-9fc1-001d09667b50-1300' for key 1
}
ERROR: Incremental restore failed
BackupSet1:restore:ERROR: Incremental restore failed
BackupSet1:restore:ERROR: could not delete /var/lib/mysql-zrm/app01-txn-incr/20080917123401
BackupSet1:restore:ERROR: Restore failed

Restores of subsequent incremental restores work just fine - it's only the one immediately following the full backup that is a problem.

Am I doing something wrong here? Could it be that the full backup contains transactions that are also in the incremental backup?

Any suggestions?

R.

kulkarni_mangesh
September 18th, 2008, 05:38 AM
It is not best practice to have different different backup-sets for Full and incremental backup of same database. In this case restore becomes difficult.

The recommended is, have only one backup set for a same database and through CRON, manage the backup levels

So in this case, cron entry may be

14 12 * * * mysql-zrm-scheduler --now --backup-set app01-full --backup-level 0
34 * * * * mysql-zrm-scheduler --now --backup-set app01-full --backup-level 1

This will help in restore.

kulkarni_mangesh
September 18th, 2008, 05:46 AM
Full backup will contain all the transaction till that time. Hence user can safely delete previously taken backups if needed. Restoring such backup will restore the database, till the time on which user has taken a backup.

robinbowes
September 18th, 2008, 05:57 AM
It is not best practice to have different different backup-sets for Full and incremental backup of same database. In this case restore becomes difficult.

Hi,

Yes, I wondered if this might be a problem, so I modified my backup sets and now have just one and run it exactly as you suggest:


14 12 * * * mysql-zrm-scheduler --now --backup-level 0 --backup-set app01
34 * * * * mysql-zrm-scheduler --now --backup-level 1 --backup-set app01

I still see exactly the same problem.

R.

kulkarni_mangesh
September 18th, 2008, 06:24 AM
Is it possible to test it with another database with new backup-set..?

You can perform following steps with test database.:


Create test database.
Take one full of test backup.
Add some rows in to one of the table of test database.
Take incremental of test bakup.
Drop test database.
Restore full backup, and see the status of test database.
Restore incremental backup and see the data of the table.

kulkarni_mangesh
September 18th, 2008, 06:27 AM
Oops missed following entry from your log.

restore:INFO: ZRM for MySQL Community Edition - version 2.0
BackupSet1:restore:WARNING: Binary logging is off.

Binary logging should be on for incremental backup.

Enable binary logging and perform the steps.

robinbowes
September 18th, 2008, 06:43 AM
Full backup will contain all the transaction till that time. Hence user can safely delete previously taken backups if needed. Restoring such backup will restore the database, till the time on which user has taken a backup.

Yes, I understand that.

Are you saying that the first incremental following a full will contain a transaction log holding transactions from *before* the time of the full backup?

Ugh, I guess that makes sense. And yes, I have verified that each incremental backup immediately after a full backup contains *two* transaction logs; one containing the transactions since the last incremental but *before* the full, and another containing the transactions *since* the full backup.

So, to do a restore, it is necessary to skip that first file. Ugh, that's going to take some manual frigging around.

Unless there's someway for zrm to do this automatically?

R.

robinbowes
September 18th, 2008, 06:44 AM
Oops missed following entry from your log.

restore:INFO: ZRM for MySQL Community Edition - version 2.0
BackupSet1:restore:WARNING: Binary logging is off.

Binary logging should be on for incremental backup.

Enable binary logging and perform the steps.

That msg is from the scratch machine I'm using to test the restores - binary logging is enabled on the production machine.

R.

robinbowes
September 18th, 2008, 06:45 AM
Is it possible to test it with another database with new backup-set..?

Well, it would be possible, but I think I've bottomed out the issue (see my earlier post).

Now, I just need to find a way to deal with it!

R.

robinbowes
September 18th, 2008, 07:38 AM
So, I've been thinking about this...

To recap, when a full backup is performed the transaction log is rolled over. if you have, for example, a full backup daily at 12:00 and an incremental backup hourly on the half hour, then the backups contain the following content:

11:30
- transaction log 10:30 - 11:30
12:00
- full backup
12:30
- transaction log 11:30 - 12:00
- transaction log 12:00 - 12:30
etc.

When doing a restore, you would typically restore from a full backup, then restore all the incrementals since the last full backup. However, this fails because the incremental backup immediately following the full backup contains transactions that occurred *before* the full backup.

I'm wondering if it would actually make sense for the transaction log immediately preceding the full backup to be stored with the full backup image, eg:

11:30
- transaction log 10:30 - 11:30
12:00
- full backup
- transaction log 11:30 - 12:00
12:30
- transaction log 12:00 - 12:30
etc.

Would that be an easy change to make?

R.

robinbowes
September 18th, 2008, 02:55 PM
Hmmm, well, that's that theory out of the window.

I just tried another test - did a full backup, then an incremental shortly after, and there was only one transaction log in the incremental backup and it contained transactions that were also in the full backup.

This is a real showstopper guys, it makes it non-trivial to do a DB restore.

Do you have any suggestions how to fix it?

R.

robinbowes
September 18th, 2008, 03:49 PM
Further information:

This is the output from a "parse-binlogs" command on the first incremental backup after a full backup:


------------------------------------------------------------
Log filename | Log Position | Timestamp | Event Type | Event
------------------------------------------------------------
DELIMITER /*!*/;
20080918214612/mysql-bin.000379 | 4 | 08-09-18 14:45:06 | Start: binlog v 4, server v 5.0.41-community-log created 080918 14:45:06 |
20080918214612/mysql-bin.000379 | 98 | 08-09-18 14:45:06 | Query | use chrome_expense/*!*/; /*!\C utf8 *//*!*/; INSERT INTO tbl_BatchHistory (PID, BatchID, StartTime)^M VALUES ( NAME_CONST(
'_id',_utf8'3d8c0a5e-d71b-102b-9fc1-001d09667b50'), NAME_CONST('inBatchID',1200), NAME_CONST('inStartTime',_binary'2008-09-18 21:45:06'))/*!*/;
20080918214612/mysql-bin.000379 | 390 | 08-09-18 14:45:06 | Xid = 78353430 | COMMIT/*!*/;
20080918214612/mysql-bin.000379 | 417 | 08-09-18 14:45:06 | Query | /*!\C latin1 *//*!*/; FLUSH TABLES /*!32323 `mysql`.`columns_priv`, `mysql`.`db`, `mysql`.`func`, `mysql`.`help_category`, `m
ysql`.`help_keyword`, `mysql`.`help_relation`, `mysql`.`help_topic`, `mysql`.`host`, `mysql`.`proc`, `mysql`.`procs_priv`, `mysql`.`tables_priv`, `mysql`.`time_zone`, `mysql`.`time_zone_leap_se
cond`, `mysql`.`time_zone_name`, `mysql`.`time_zone_transition`, `mysql`.`time_zone_transition_type`, `mysql`.`user` *//*!*/;
20080918214612/mysql-bin.000379 | 891 | 08-09-18 14:45:06 | Query | use chrome_expense/*!*/; /*!\C utf8 *//*!*/; UPDATE tbl_BatchHistory SET FinishTime = NAME_CONST('inEndTime',_binary'2008-09
-18 21:45:06')^M WHERE PID = NAME_CONST('inPID',_utf8'3d8c0a5e-d71b-102b-9fc1-001d09667b50') and BatchID = NAME_CONST('inBatchID',1200)/*!*/;
20080918214612/mysql-bin.000379 | 1184 | 08-09-18 14:45:06 | Xid = 78353450 | COMMIT/*!*/;
20080918214612/mysql-bin.000379 | 1211 | 08-09-18 14:45:08 | Query | INSERT INTO tbl_BatchHistory (PID, BatchID, StartTime)^M VALUES ( NAME_CONST('_id',_utf8'3eb57140-d71b-102b-9fc1-001d096
67b50'), NAME_CONST('inBatchID',1500), NAME_CONST('inStartTime',_binary'2008-09-18 21:45:08'))/*!*/;
20080918214612/mysql-bin.000379 | 1503 | 08-09-18 14:45:08 | Xid = 78353887 | COMMIT/*!*/;

If I do a restore using --start-position 891, i.e. skipping the first "INSERT INTO ..." statement, and instead starting at the first "UPDATE tbl_BatchHistory ..." statement, the restore works OK.

So, it seems that some how transactions are definitely ending up in the full backup *and* the immediately following incremental backup.

Has no-one run into this before? Or am I special?? :)

R.

robinbowes
September 18th, 2008, 04:21 PM
Well, continuing posting to my own personal forum...

So, it seems that the mysqldump options that mysql-zrm uses by default don't quite work correctly.

I was finding that the first incremental backup following a full backup contained some transactions that had already been committed to the DB and were in the full backup. That meant that the restore of the incremental backup failed.

I did went back to basics and read up on the mysqldump options used by mysql-zrm-backup. Out-of-the-box, it uses:



my $MYSQLDUMP5="mysqldump --opt --extended-insert --single-transaction --create-options";

"--opt" is shorthand for "--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset"

According to the mysqldump man page, "--lock-tables" and "--single-transaction" are mututally exclusive, and "--single-transaction" is a much better option.

So, I changed the mysqldump options to:


my $MYSQLDUMP5="mysqldump --opt --skip-lock-tables --single-transaction --create-options";

In particular, this:

- disables --lock-tables
- removes the --extended-insert option as it's implied by --opt

Using these options seems to have fixed the problem - the incremental backup no longer contains any transactions that are already in the full backup!

Can I suggest this change is applied to the mysql-zrm source tree?

R.

kulkarni_mangesh
September 18th, 2008, 08:51 PM
I think you are the special one. :)

ZRM next release there will be option to provide user specific options including "--single-transaction".

Following are some points regarding "--single-transaction" based on my knowledge.


--single-transaction avoids locking tables, it acquires a global read lock is acquired only for a short time at the beginning of the dump . But this depends on the implementation. InnoDB is such an implementation, and, indeed, it avoids locking.

--single-transaction only works for TRANSACTIONAL tables (e.g. InnoDB) and does NOT work the way you expect it to on non-transactional tables (e.g. MyISAM).

--single-transaction doesn't _do_ the dump as a transaction, it inserts the dump in begin/commit statements so it's atomic when restoring.

--single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does). The dump is NOT guaranteed to be consistent for other storage engines. This option automatically turns off --lock-tables hence not suitable if database contains mix type of engines ( InnoDB and MyISAM ).


In Summary:

Use --single-transaction for OLTP like database which are using InnoDB like engines which supports multiversioning. Avoid using --single-transaction option for the engines, which doesn't support this, engines such as MyISAM
If you have a hybrid schema, with both InnoDB and MyISAM, safest for you is use --lock-tables instead of --single-transaction.
If you're using transactional tables only, --single-transaction is advisable. The database is available for read/write in that time, you may see considerable loss of performance.

robinbowes
September 19th, 2008, 02:21 AM
Hi,

It's nice to be special :)

I appreciate your response but am still left a little puzzled.

You recommend against "--single-transaction" as the default yet that is included as the default mysqldump option. All I have done is to rationalize the options by explicitly disabling "--lock-tables.

As it happens, all DBs on this server (except the mysql DB) use the InnoDB engine so I should be OK with "--single-transaction".

However, this doesn't explain to me why I was seeing problems with the default mysqldump options. Why were there transactions appearing in the transaction log that had been backed up in the full backup?

R.

sunil
September 21st, 2008, 11:35 PM
Hi,

It's nice to be special :)

I appreciate your response but am still left a little puzzled.

You recommend against "--single-transaction" as the default yet that is included as the default mysqldump option. All I have done is to rationalize the options by explicitly disabling "--lock-tables.

As it happens, all DBs on this server (except the mysql DB) use the InnoDB engine so I should be OK with "--single-transaction".

However, this doesn't explain to me why I was seeing problems with the default mysqldump options. Why were there transactions appearing in the transaction log that had been backed up in the full backup?

R.

Regarding "Duplicate Key" issue I found a bug with MySQL. Please see if this helps you.

http://bugs.mysql.com/bug.php?id=12695

robinbowes
September 22nd, 2008, 12:23 AM
Sunil,

I'm using a later version (5.0.41) so that bug doesn't apply.

R.

lylo
April 20th, 2009, 04:05 AM
So, is the general conclusion of this thread that we should be reconfiguring mysqldump to add --skip-lock-tables and remove --extended-insert?

lylo
April 20th, 2009, 07:25 AM
I have tried making the changes Robin suggests (--skip-lock-tables) and I'm still seeing failures when restoring the first incremental backup after restoring a full backup.

Can anyone help? This is making things extremely difficult from a restoration point of view:

dailyrun:restore:INFO: restoring using command mysql --user="backup" --password="*****" -e "source /tmp/laShwFfxhM;"
dailyrun:restore:ERROR: Output of command: 'mysql --user="backup" --password="*****" -e "source /tmp/laShwFfxhM;"' is {
ERROR 1062 (23000) at line 211 in file: '/tmp/laShwFfxhM': Duplicate entry '42532' for key 1
}
ERROR: Incremental restore failed