Innodb Replication Setup with using xtrabackup

[tfas]

Many thanks to Percona for creating xtrabackup - it is a great
community resource for us. We currently use mysqldump for our nightly
backups and are looking for an improved backup process.

We only use Innodb tables, so I believe we can get away with just
calling xtrabackup directly rather than using innobackupex. However,
we will occasionally want to use these backups to setup new
replication slaves so I'm thinking of including logic in our backup
wrapper script that does an rsync copy of the MySql files to get the
database folders and schema files (our schema changes are seldom and
would never happen during a backup). Then I could just use the binary
log information output by xtrabackup to setup replication on the slave
server.

I'd like to avoid the locking that the innobackupex does - it appears
to last for about 30 seconds while running on our test system.

Am I missing anything?

Here is the wrapper script I've been testing:

BACKUP_HOME=/mnt/disk2/backup/xtradb
BACKUP_DIR=${BACKUP_HOME}/$(date +%Y%m%d)
MYSQL_HOME=/var/lib/mysql

mkdir ${BACKUP_DIR}

# Sync the current MySql folder and the core schema information tables
rsync -az --include="*/" --include="*.frm" --include="*.opt" --
include="*.MYI" --include="*.MYD" --exclude="*" ${MYSQL_HOME}/ $
{BACKUP_DIR}

# Run xtrabackup to perform Innodb backup
xtrabackup --backup --target-dir=${BACKUP_DIR}

xtrabackup --prepare --target-dir=${BACKUP_DIR}

[Vadim Tkachenko]

Hi,

Unfortunately  if you want to setup slave, there is no way to avoid locking, you need
to get master binary log position, and you can get correct one only after execution
FLUSH TABLES WITH READ LOCK.


--
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-888-401-3403,  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Percona Training Workshops - 30 October - New York
http://www.percona.com/training/

[MARK CALLAGHAN]

On Wed, Oct 21, 2009 at 10:47 AM, Vadim Tkachenko <va...@percona.com> wrote:
> Hi,
>
> Unfortunately  if you want to setup slave, there is no way to avoid locking,
> you need
> to get master binary log position, and you can get correct one only after
> execution
> FLUSH TABLES WITH READ LOCK.

There might be a way. Someone can sponsor someone else to evaluate
this patch and make it production quality:
http://bugs.mysql.com/bug.php?id=48124

--
Mark Callaghan
mdca...@gmail.com

[tfas]

Thanks very much for the reply Vadim. I'm glad I asked because I
assumed the binary log information output from "xtrabackup --
prepare ..." call would be the starting point for the slave.

Here is the line in the output I noticed:
"InnoDB: Last MySQL binlog file position 0 10478804, file name ./mysql-
bin.000002"

I'll plan to move forward with the innobackupex script approach.

Thanks
Todd

Here is the full output from "xtrabackup --prepare":

xtrabackup: cd to /mnt/disk2/backup/xtradb/20091021
xtrabackup: This target seems to be not prepared yet.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path =
ibdata1:1000M;ibdata2:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory
parameter)
InnoDB: Log scan progressed past the checkpoint lsn 14 1630462684
091021 9:20:22 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Doing recovery: scanned up to log sequence number 14
1630572233 (5 %)
091021 9:20:23 InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 10478804, file name ./mysql-
bin.000002
091021 9:20:24 InnoDB: Started; log sequence number 14 1630572233
InnoDB: Last MySQL binlog file position 0 10478804, file name ./mysql-
bin.000002
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
091021 9:20:24 InnoDB: Starting shutdown...
091021 9:20:25 InnoDB: Shutdown completed; log sequence number 14
1630572233
xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(14
1630462684)

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:

On Oct 21, 10:47 am, Vadim Tkachenko <va...@percona.com> wrote:
> Hi,
>
> Unfortunately  if you want to setup slave, there is no way to avoid locking,
> you need
> to get master binary log position, and you can get correct one only after
> execution
> FLUSH TABLES WITH READ LOCK.
>
> --
> Vadim Tkachenko, CTO, Percona Inc.
> Phone +1-888-401-3403,  Skype: vadimtk153
> Schedule meeting:http://tungle.me/VadimTkachenko
>

> Percona Training Workshops - 30 October - New Yorkhttp://www.percona.com/training/

[Yasufumi Kinoshita]

Hi,

If you never change for not-innodb tables, "FLUSH TABLES WITH READ
LOCK" is not needed. That means you don't modify your MyISAM tables
and never change table definitions and user definition and so on
not to change system tables in database 'mysql'..

[Vadim Tkachenko]

Yasufumi,

So you say in this case we can rely on

"InnoDB: Last MySQL binlog file position 0 10478804, file name ./mysql-

bin.000002" ?

Mark, will not it solve your issue with FLUSH TABLES READ LOCK ?

--
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-888-401-3403,  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Percona Training Workshops - 30 October - New York
http://www.percona.com/training/

[MARK CALLAGHAN]

On Wed, Oct 21, 2009 at 6:56 PM, Vadim Tkachenko <va...@percona.com> wrote:
> Yasufumi,
>
> So you say in this case we can rely on
> "InnoDB: Last MySQL binlog file position 0 10478804, file name ./mysql-
> bin.000002" ?
>
> Mark, will not it solve your issue with FLUSH TABLES READ LOCK ?

Maybe, but I need to extract that via a SQL command. In the patch I
had a choice of extracting the data internally from mysql_bin_log
(binlog code) or from InnoDB. I get it from mysql_bin_log as the code
change was simpler.

--
Mark Callaghan
mdca...@gmail.com

[tfas]

Yes Yasufumi - those assumptions are correct in our scenario. We will
only have Innodb tables in our databases and no modifications will be
occurring to the mysql database, etc.

So sounds like I can trust the binlog position straight from the
"xtrabackup --prepare" output to setup replication on our slaves?

Appreciate all of the input from everyone. I've seen some confusion in
various places about this particular issue so wanted to be sure I was
correct in my assumptions.

Thanks again.
Todd

On Oct 21, 6:52 pm, Yasufumi Kinoshita <kinoy...@gmail.com> wrote:
> Hi,
>
> If you never change for not-innodb tables, "FLUSH TABLES WITH READ
> LOCK" is not needed. That means you don't modify your MyISAM tables
> and never change table definitions and user definition and so on
> not to change system tables in database 'mysql'..
>
> On Wed, 21 Oct 2009 10:47:47 -0700
>
>
>
> Vadim Tkachenko <va...@percona.com> wrote:
> > Hi,
>
> > Unfortunately  if you want to setup slave, there is no way to avoid
> > locking, you need
> > to get master binary log position, and you can get correct one only
> > after execution
> > FLUSH TABLES WITH READ LOCK.
>
> > --
> > Vadim Tkachenko, CTO, Percona Inc.
> > Phone +1-888-401-3403,  Skype: vadimtk153
> > Schedule meeting:http://tungle.me/VadimTkachenko
>
> > Percona Training Workshops - 30 October - New York
> >http://www.percona.com/training/
>