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

Does Percona XtraBackup lock the database during the backup?

davidjhpdavidjhp EntrantInactive User Role Beginner
The Percona XtraBackup Documentation, Release 2.0.6 states:

Percona XtraBackup is a hot backup utility that does not lock your database during the backup.

However the documentation also states that it will execute "FLUSH TABLES WITH READ LOCK." That certainly will lock the database.

Arent these statements contradictory? I need to know if Percona XtraBackup will lock tables during backup? If yes, then I wouldnt be able to use this in production.

Our database consists only of InnoDB tables.

Comments

  • revinrevin Contributor Current User Role Beginner
    davidjhp wrote: »
    The Percona XtraBackup Documentation, Release 2.0.6 states: Percona XtraBackup is a hot backup utility that does not lock your database during the backup. However the documentation also states that it will execute "FLUSH TABLES WITH READ LOCK." That certainly will lock the database. Arent these statements contradictory? I need to know if Percona XtraBackup will lock tables during backup? If yes, then I wouldnt be able to use this in production. Our database consists only of InnoDB tables.

    David,

    FTWRL is only needed to copy other files like *.FRM or MYI/MYD files if you have MyISAM. Because you cannot take a consistent snapshot of tables other than InnoDB without FTWRL this is often necessary.

    On the other hand, if you are absolutely sure of 2 things 1) No DDL occurs on the tables being backed up at the time 2) You only have InnoDB tables aside from the mysql schema, and if you do have MyISAM (nothing will ever write to it during backup), you can use the --no-lock option with innobackupex to avoid the FTWRL.

    So these are not necessarily contradicting statements :)
  • r4z0rr4z0r Entrant Inactive User Role Beginner
    revin wrote: »
    1) No DDL occurs on the tables being backed up at the time 2) You only have InnoDB tables aside from the mysql schema, and if you do have MyISAM (nothing will ever write to it during backup)

    ​When do these restrictions apply? During the whole backup process or just the time when the non-innodb tables are being copied?
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    This applies for .frm files and for non-innodb tables.
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.