Not the answer you need?
Register and ask your own question!

Why xtrabackup doesn't need "flush tables with read lock"?

leafonswordleafonsword ContributorInactive User Role Beginner
From mannual I knew that xtrabackup needs FTWRL to copy myisam tables and record binlog position information, but I did an experiment and it seems xtrabackup doesn't do a FTWRL :
Session 1:
> lock table test.test;

Session 2:
# innobackupex --user=root /tmpdir/

But session 2 didn't be blocked by session 1, if xtrabackup do a FTWRL , session 2 should be blocked by session 1~

Following is xtrabackup 's output:
root@li741-164 ~# innobackupex --user=root /tmpdir/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

150630 08:48:25  innobackupex: Executing a version check against the server...
150630 08:48:25  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
150630 08:48:25  innobackupex: Connected to MySQL server
150630 08:48:25  innobackupex: Done.
150630 08:48:25  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
150630 08:48:25  innobackupex: Connected to MySQL server
150630 08:48:25  innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex:  Using server version 5.6.23-72.1-log

innobackupex: Created backup directory /tmpdir/2015-06-30_08-48-25

150630 08:48:25  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmpdir/2015-06-30_08-48-25 --datadir="/var/lib/mysql/" --innodb_log_file_size="50331648" --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=24893) to suspend
innobackupex: Suspend file '/tmpdir/2015-06-30_08-48-25/xtrabackup_suspended_2'

xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 51200
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (26294577)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /tmpdir/2015-06-30_08-48-25/ibdata1
[01]        ...done
[01] Copying ./percona/checksums.ibd to /tmpdir/2015-06-30_08-48-25/percona/checksums.ibd
[01]        ...done
[01] Copying ./user01/account07.ibd to /tmpdir/2015-06-30_08-48-25/user01/account07.ibd
[01]        ...done
[01] Copying ./user01/account02.ibd to /tmpdir/2015-06-30_08-48-25/user01/account02.ibd
[01]        ...done
[01] Copying ./user01/account05.ibd to /tmpdir/2015-06-30_08-48-25/user01/account05.ibd
[01]        ...done
..................................................................
ignored output~
..................................................................
>> log scanned up to (26294577)
xtrabackup: Creating suspend file '/tmpdir/2015-06-30_08-48-25/xtrabackup_suspended_2' with pid '24893'

150630 08:48:27  innobackupex: Continuing after ibbackup has suspended
150630 08:48:27  innobackupex: Executing LOCK TABLES FOR BACKUP...
150630 08:48:27  innobackupex: Backup tables lock acquired

150630 08:48:27  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up file '/var/lib/mysql//percona/db.opt'
>> log scanned up to (26294577)
innobackupex: Backing up file '/var/lib/mysql//percona/checksums.frm'
innobackupex: Backing up files '/var/lib/mysql//user01/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (11 files)
innobackupex: Backing up files '/var/lib/mysql//c2c_lottery/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (16 files)
innobackupex: Backing up file '/var/lib/mysql//test/db.opt'
innobackupex: Backing up file '/var/lib/mysql//test/test1.frm'
innobackupex: Backing up file '/var/lib/mysql//test/test.frm'
innobackupex: Backing up file '/var/lib/mysql//test/test2.frm'
innobackupex: Backing up file '/var/lib/mysql//test/gf.frm'
innobackupex: Backing up files '/var/lib/mysql//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
>> log scanned up to (26294577)
innobackupex: Backing up files '/var/lib/mysql//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
innobackupex: Backing up files '/var/lib/mysql//sakila/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (19 files)
>> log scanned up to (26294577)
150630 08:48:29  innobackupex: Finished backing up non-InnoDB tables and files

150630 08:48:29  innobackupex: Executing LOCK BINLOG FOR BACKUP...
150630 08:48:29  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
150630 08:48:29  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '26294577'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (26294577)

xtrabackup: Creating suspend file '/tmpdir/2015-06-30_08-48-25/xtrabackup_log_copied' with pid '24893'
xtrabackup: Transaction log of lsn (26294577) to (26294577) was copied.
150630 08:48:30  innobackupex: Executing UNLOCK BINLOG
150630 08:48:30  innobackupex: Executing UNLOCK TABLES
150630 08:48:30  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tmpdir/2015-06-30_08-48-25'
innobackupex: MySQL binlog position: filename 'mysqld-bin.000004', position 3782345
150630 08:48:30  innobackupex: Connection to database server closed
150630 08:48:30  innobackupex: completed OK!

Comments

  • leafonswordleafonsword Contributor Inactive User Role Beginner
    I've knew the reason:
    percona server supports 'lock tables for backup' and 'lock binlog for backup' to replace FTWRL~
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.