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

Xtrabackup freezes at unlocking the tables; with db locked, production server stops

PepsiLightPepsiLight EntrantInactive User Role Beginner
Hi there,

My problem is this: Xtrabackup while backing up the whole database (InnoDB and MyISAM system tables), reaches the point where it is supposed to unlock the tables and finish the process, but doesn't do that and freezes, and the database remains locked. At this point, on break, the database crashes. This has been happening for last 3 days since the last MySQL update I applied.

Details:
Linux x64
mysql: 5.6.15-63.0
xtrabackup 2.1.8 compiled from percona-xtrabackup-2.1.8.tar.gz
with: AUTO_DOWNLOAD="yes" ./utils/build.sh 5.6
(compile without the "5.6" switch causes a "libiao" error at database start; it tries to call "libcrypt.so.11" which isn't there)

The log of the whole thing happening:
/.../
[01] Copying ./phpmyadmin/pma__navigationhiding.ibd to /backup/2014.03.08/phpmyadmin/pma__navigationhiding.ibd
[01]        ...done
xtrabackup: Creating suspend file '/backup/2014.03.08/xtrabackup_suspended_2' with pid '14135'
>> log scanned up to (640660457184)

140308 22:39:04  innobackupex: Continuing after ibbackup has suspended
140308 22:39:04  innobackupex: Starting to lock all tables...
140308 22:39:04  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
140308 22:39:04  innobackupex: Connected to MySQL server
140308 22:39:04  innobackupex: Connection to database server closed
Query killing process is finished
140308 22:39:04  innobackupex: All tables locked and flushed to disk

140308 22:39:04  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/mysqldb/db'
innobackupex: Backing up files '[I]/var/lib/mysql/mysqldb/db/mysql/[/I]*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (78 files)
>> log scanned up to (640660499850)
innobackupex: Backing up file '/var/lib/mysql/mysqldb/db/stats/db.opt'
innobackupex: Backing up file '/var/lib/mysql/mysqldb/db/stats/tables.frm'
innobackupex: Backing up files '[I]/var/lib/mysql/mysqldb/db/performance_schema/[/I]*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
>> log scanned up to (640660501221)
innobackupex: Backing up files '[I]/var/lib/mysql/mysqldb/db/phpmyadmin/[/I]*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (16 files)
140308 22:39:07  innobackupex: Finished backing up non-InnoDB tables and files

140308 22:39:07  innobackupex: Waiting for log copying to finish

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

xtrabackup: Creating suspend file '/backup/2014.03.08/xtrabackup_log_copied' with pid '14135'
xtrabackup: Transaction log of lsn (640643368160) to (640660501221) was copied.

So a successful ending is missing. Clearly, it freezes at the tables unlock.

This is how the end should look like:
140308 22:42:40  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/backup/2014.03.08'
140308 22:42:40  innobackupex: Connection to database server closed
140308 22:42:40  innobackupex: completed OK!

On break I get
DBD::mysql::db do failed: Lost connection to MySQL server during query at /usr/bin/innobackupex line 3009.
innobackupex: Error:
Error executing 'UNLOCK TABLES': DBD::mysql::db do failed: Lost connection to MySQL server during query at /usr/bin/innobackupex line 3009.

...which also confirms it stops at the tables unlock.

Interestingly, if the httpd deamon is not running, the backup works just fine. The problem hence occurs only under load.

I am lost at this. Please kindly advise.

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    As you said, problem occurs only under load means, backup should take more time then usual. Can you check wait_timeout value? and try to increase it and then check?
    It might be possible that process is getting timeout as the first error is
    " DBD::mysql::db do failed: Lost connection to MySQL server during query at /usr/bin/innobackupex line 3009."

    It seems, server killing the backup connection due to timeout.

    I would also suggest to read this http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/improved_ftwrl.html
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.