MySQL FLUSH Statement

The FLUSH statement in MySQL is used to clear the caches.

Syntax

Following is the syntax of MySQL FLUSH statement −

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
   BINARY LOGS | ENGINE LOGS | ERROR LOGS | GENERAL LOGS | HOSTS | 
   LOGS | 
   PRIVILEGES | OPTIMIZER_COSTS | RELAY LOGS | SLOW LOGS | STATUS | 
   USER_RESOURCES
}

Following are various (options) FLUSH statements −

FLUSH BINARY LOGS;

FLUSH ENGINE LOGS;

FLUSH ERROR LOGS;

FLUSH GENERAL LOGS;

FLUSH HOSTS;

FLUSH LOGS;

FLUSH PRIVILEGES;

FLUSH OPTIMIZER_COSTS;

FLUSH RELAY LOGS;

FLUSH STATUS;

FLUSH USER_RESOURCES;

The FLUSH TABLES statement

Using the FLUSH tables statement you can flush the data in the table or acquire locks.

Syntax

Following is the syntax of the FLUSH TABLES statement −

FLUSH TABLES {
   tbl_name [, tbl_name] ...
   | WITH READ LOCK
   | tbl_name [, tbl_name] ... WITH READ LOCK
   | tbl_name [, tbl_name] ... FOR EXPORT
}

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', 
'pujasharma@gmail.com');
(2, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 
'Chennai', 30, '9000012365', 'Chennai- TNagar', 'vanajarani@gmail.com');

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

Following is the output of the above query −

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;