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.