xtrabackup 2.0 + mysql 5.0 + zarafa - hangs vs. no-lock

[thoraxe]

Hi all,

In looking at the 2.0 docs:
http://www.percona.com/doc/percona-xtrabackup/2.0/innobackupex/innobackupex_option_reference.html

The --no-lock option states the following:

Use this option to disable table lock with FLUSH TABLES WITH READ LOCK. Use it only if ALL your tables are InnoDB and you DO NOT CARE about the binary log position of the backup.

Looking at the Zarafa docs:
http://doc.zarafa.com/7.1/Administrator_Manual/en-US/html-single/#_full_database_dump

We have the following:

When using mysqldump, it is very important not to do any table locking. This means that the --opt option and --lock-tables should never be used while dumping a Zarafa database. The reason is that these options will ‘lock’ the tables while they are being dumped to disk, causing any accesses to the database to ‘freeze’ while the backup runs. This is firstly unnecessary and secondly may cause emails that are arriving during backup to bounce (depending on the MTA settings).

I have absolutely noticed this “freezing” behavior when attempting to use innobackupex.

Here is a little background:
I currently have an active/passive (master/slave) MySQL 5.0 setup with all InnoDB tables. Generally speaking, I’d like to take backups off the slave and I don’t really care about the binary log in that case.

1. Is it “safe” to run innobackupex on the slave without “–no-lock” if I am just doing a general backup / incremental backup and not trying to populate a slave?

2. If I wanted to prepare a backup to populate a slave (where binary coordinates were vital), would I have to use mysqldump without --lock-tables as opposed to innobackupex, since the notes seem to indicate --no-lock may not provide “safe” binary coordinates?

Thanks!

[thoraxe]

In the case of #2 above, I am expecting to run innobackupex on a master with --no-lock, not on an existing slave.

[mirfan]

Xtrabackup requires FLUSH TABLES WITH READ LOCK lock in order to get backups all non-innodb tables and .FRM, .TRG files etc and most of the time FTWRL is for small period of time if all your tables are InnoDB. If you need to disable FTWRL and you don’t care about binary log positions use –no-lock option. I would suggest to backup always from your slave and there is no need to use –no-lock option mostly instead use –safe-slave-backup option which stops slave sql_thread and if you don’t directly using any DML, DDL statement on slave on any other database or summary tables then it’s pretty safe to use –safe-slave-backup along innobackupex –slave-info option. This will help to get consistent backup along with binary log coordinates of master so you can populate new slaves also from this backup. You can check here how to provision new slave from existing slave without impacting master http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html#adding-more-slaves-to-the-master

[mgriffin]

mirfan,

Please correct me if I am wrong, but it is safe to create a slave from a master with innobackupex --no-lock, assuming the following are 100% true:

• There are no tables which use engines other than InnoDB ( in reality, there can be static MyISAM tables, if they have not changed since the last FLUSH TABLES but this is an advanced and easily error prone case)

• During the run of xtrabackup, no DCL nor DDL is run

When I said “safe to create a slave from a master”, what I really meant is that you will get a backup that is internally consistent with the master_log_file and master_log_position, as stored in the innodb shared tablespace, and made visible to use user in xtrabackup_binlog_pos_innodb after the prepare phase.

[mgriffin]

Replying here since my previous comment did not seem to “bump” the thread