MySQL - LOCK INSTANCE FOR BACKUP Statement

LOCK INSTANCE FOR BACKUP Statement

You can create an instance level backup lock using the MySQL LOCK INSTANCE FOR BACKUP Statement. This lock allows manipulations on the data during an online backup. This prevents operations that effect the consistency of the transactions.

To execute this statement, you need BACKUP_ADMIN privilege (if you have RELOAD privilege this will be automatically granted).

When you have this lock, you −

• Cannot create, rename or, remove records.
• Cannot repair, truncate and optimize tables.
• Can perform DDL operations hat only affect user-created temporary tables.
• Can create, rename, remove temporary tables.
• Can create binary log files.

Syntax

Following is the syntax of the MySQL LOCK INSTANCE FOR BACKUP statement −

LOCK INSTANCE FOR BACKUP

Example

Suppose we have created a table that contains the sales details along with the contact details of the customers as shown below −

CREATE TABLE SALES_DETAILS (
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255),
   CustomerAge INT,
   CustomrtPhone BIGINT,
   DispatchAddress VARCHAR(255),
   Email VARCHAR(50)
);

Now, lets insert 2 records into the above created table using the INSERT statement as −

Insert into SALES_DETAILS values
(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 
'Hyderabad', 25, '9000012345', 'Hyderabad  Madhapur', 
'[email protected]'),
(2, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 
'Chennai', 30, '9000012365', 'Chennai- TNagar', 
'[email protected]');

If we want another table with just the contact details of the customer create a table as −

CREATE TABLE CustContactDetails (
   ID INT,
   Name VARCHAR(255),
   Age INT,
   Phone BIGINT,
   Address VARCHAR(255),
   Email VARCHAR(50)
);

Following query insets records into the CustContactDetails table using the INSERT INTO SELECT statement. Here, we are trying to insert records from the SALES_DETAILS table to CustContactDetails table −

INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email)
SELECT
 ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email
 FROM SALES_DETAILS
WHERE ID = 1 AND CustomerName = 'Raja';

INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email)
SELECT
ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email
FROM SALES_DETAILS
WHERE ID = 2 AND CustomerName = 'Vanaja';

You can verify the contents of the CustContactDetails table as shown below −

SELECT * FROM CustContactDetails;

Output

The above query produces the following output −

Following set of queries locks the instance flushes the above created tables and unlocks the instance.

LOCK INSTANCE FOR BACKUP;

FLUSH TABLES emp, custcontactdetails WITH READ LOCK;

UNLOCK TABLES;

UNLOCK INSTANCE;