MySQL Bugs: #90762: what is the use case of LOCK INSTANCE FOR BACKUP

Bug #90762	what is the use case of LOCK INSTANCE FOR BACKUP
Submitted:	5 May 2018 8:23	Modified:	7 May 2018 15:38
Reporter:	chen chen (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am curious about the use case of LOCK INSTANCE FOR BACKUP, i thought it would replace FLUSH TABLES WITH READ LOCK when backup by mysqldump.But in fact,it still use "FLUSH TABLES WITH READ LOCK" to get consistent backup.

How to repeat:
turn on the general log when you use mysqldump

mysqldump --all-databases --master-data=2 --single-transaction

Re-open.

Hi,

This command is not a replacement neither for MEB nor for mysqldump. It is fully described in the chapter 13.3.5. You do not need to do FTWRL if you are backing up only InnoDB tables.

All available backup / restore options are described in our manual.

Just for the clarification you don't need a single-transaction for ensuring consistent dump anymore using mysqldump if you explicitly mention the 

LOCK INSTANCE FOR BACKUP;
mysqldump <params> .....
UNLOCK INSTANCE;

This should give a consistent dump and no FTWRL is actually performed? 
Did check here https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html

Please add/correct me if I am missing something. 

Thanks, 
Bajrang

Hi,

The purpose of the above LOCK command is not intended for mysqldump. It is too prohibitive command for the utility that does not require any locking, except for non-transactional engines and massive DDLs.