DelphiFAQ Home Search:

Lock an entire mysql database with all its tables

 

comments14 comments. Current rating: 4 stars (6 votes). Leave comments and/ or rate it.

Question:

How can I lock my entire mysql database? And how can I lock a single table?

Answer:

Go to the mysql shell and use the FLUSH command.
This clloses all open tables and locks all tables for all databases with a read lock until you execute UNLOCK TABLES. This is very convenient way to get backups.

FLUSH TABLES WITH READ LOCK;

/* alternatively lock a single table:
LOCK TABLES customers READ; 
*/

/* running my backup */

UNLOCK TABLES;

Content-type: text/html

Comments:

2008-04-11, 00:51:52
anonymous from India  
Hai all,

This information about the MySQL in nice..

thank u..!


Keywords:
2008-05-16, 09:46:11
anonymous from Ukraine  
thanks
2009-01-07, 08:41:54   (updated: 2009-01-07, 08:44:26)
anonymous from France  
rating
I guess you're talking about a filesystem backup, as you wouldn't need to lock anything if backing up the database.
2009-01-23, 04:38:09
anonymous from India  
rating
2009-02-20, 02:57:12
anonymous from New Delhi, India  
rating
Dear

How can i lock all the tables of a single database or to lock a particular table.

Thanks
Anil
2009-04-02, 06:44:24
anonymous  
rating
lock tables db.* read;
2009-04-02, 06:54:28
anonymous  
rating
hello world



Keywords:
2009-04-02, 06:59:29   (updated: 2009-04-02, 07:01:39)
anonymous  
rating

You can't lock all tables at once; try to lock individual table with repeated exercise;



Keywords:
2009-09-04, 09:37:47
anonymous from Brazil  
What u have to do to lock all tables...

Get all tables ( select * from information_schema.tables ) and put in a Cursor...
Iterate in cursor and lock each table... ( Lock Table X )
2009-10-29, 10:35:31
anonymous from United States  
2009-12-23, 09:27:44
anonymous from United States  
FLUSH TABLES WITH READ LOCK; works just fine in locking all the tables in a databse.
2010-05-13, 02:51:57
anonymous from India  
2012-06-07, 06:05:56
anonymous from India  
if i have multiple database in same database server than how i can lock only one database at a time.
2012-07-24, 08:52:25
KevinGuancheDarias74 from Spain  

if i have multiple database in same database server than how i can lock only one database at a time.


USE `DbName`;

FLUSH TABLES WITH READ LOCK;

USE `OtherDbName`;

// Do regular stuff.

Kevin Guanche Darias, from Tenerife island, happy coding.


Keywords:

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: