MySQL Backup – Backup Stored Routines and Functions with Innobackupex

Please note that I am not a MySQL developer, I use MS SQL Server. But the behavior in your post suggests the following:

It does not look like a deadlock to me, especially with the error messages:

InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

A deadlock normally would quickly determine which connection to roll back instead of processing up to 600 seconds before crashing the server.

Likely what is happening is that the table metadata change (the ALTER TABLE) cannot happen while there are other transactions using the table.

http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

The link says, in part: "To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session."

EDIT: Sorry for the misunderstanding regarding deadlocks.

Per this post, https://stackoverflow.com/questions/24860111/warning-a-long-semaphore-wait

Ensure that: Innodb_adaptive_hash_index=0

You don't need to mess with RECOVERY and NORECOVERY here, all you need is the STANDBY option. Here's a quick demo on how to use it.

Create a database, set it to simple recovery, and create a table.

Insert data, take some diffs.

Fun, right?

USE master;

/*Create a dummy database*/
CREATE DATABASE DiffRestoreTest

/*We simple now*/
ALTER DATABASE DiffRestoreTest SET RECOVERY SIMPLE

/*Context is everything*/
USE DiffRestoreTest

/*If nothing changes, do we even need a diff backup?*/
CREATE TABLE dbo.t1 (Id INT)

/*Take a full backup, dummy*/
BACKUP DATABASE DiffRestoreTest 
TO DISK = 'F:\Backup\DRT_FULL.bak' 
WITH INIT, FORMAT, COMPRESSION

/*Make a change*/
INSERT dbo.t1 (Id )
VALUES ( 1 )

/*Take a differential backup*/
BACKUP DATABASE DiffRestoreTest 
TO DISK = 'F:\Backup\DRT_DIFF_1.bak' 
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 2 )

/*Take another diff backup*/
BACKUP DATABASE DiffRestoreTest  
TO DISK = 'F:\Backup\DRT_DIFF_2.bak' 
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 3 )

/*Take another diff backup*/
BACKUP DATABASE DiffRestoreTest 
TO DISK = 'F:\Backup\DRT_DIFF_3.bak' 
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

Yeah, I lied. That's the boring part.

You can restore your Full backup in STANDBY:

/*Exit stage left*/
USE master

/*Restore the full backup*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_FULL.bak' 
WITH REPLACE, STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

You can restore Diffs in order with STANDBY:

/*Square one*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_1.bak' 
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*Square 2*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_2.bak' 
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*Square 3*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_3.bak' 
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

And unlike those pesky Log files, you can skip ahead when restoring Diffs, too:

/*Restore the full backup*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_FULL.bak' 
WITH REPLACE, STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*What happens if I try to jump the restores?*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_3.bak' 
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

If you want to test readability, just run this in between restore commands. You should see IDs increment with each. If you don't, you did something horribly wrong.

Keep in mind that when you restore files, it will kick any end users out of the database, and it won't wait for their queries to finish.

This also leaves the database in a read only state, no changes can be made here.

SELECT * 
FROM DiffRestoreTest.dbo.t1 AS t

And finally, clean up after yourself.

/*Bring'er online, lad*/
RESTORE DATABASE DiffRestoreTest WITH RECOVERY

DROP DATABASE DiffRestoreTest

Hope this helps!!