Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 21-11-2009
Member
 
Join Date: Nov 2009
Posts: 359
Lock an entire mysql database

Hi,
I am trying to do a small project which has MySql at its back end (database). I have almost done with the front end and even the tables in my data base are mostly completed. I just wanted to know that is there any way to lock a entire database with all its tables. i mean that not even a single cell should be accessed by anyone. This is not my projects requirement, but still I have the curiosity to know if it is possible. I any one knows about it, please post it. Thanks in advance.
Reply With Quote
  #2  
Old 21-11-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: Lock an entire mysql database

Hi
The question you have asked have a very simple solution. It's not that hard to lock an entire database. Just do this, Go to the MySql shell and use the FLUSH command. This command will close all the open tables and lock all the tables from the database. It will only read until you execute the UNLOCK TABLE command. I think this is a very convenient way to take a database backup.
Code:
FLUSH TABLES WITH READ LOCK;

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

/* running my backup */

UNLOCK TABLES;
Reply With Quote
  #3  
Old 21-11-2009
Member
 
Join Date: Nov 2009
Posts: 330
Re: Lock an entire mysql database

Hi,
Thanks for the above posted command. I did not knew that. But I think you are talking about a file system backup, as it is not required to lock a database or anything if you want to take a backup of the database. My question was how can I lock all the tables of a single database or lock a single / particular table. Any advice on this. You know the command to lock entire database so I hope that you even know how to lock a particular table. Thanks in advance.
Reply With Quote
  #4  
Old 21-11-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: Lock an entire mysql database

Hi
I do not have any idea of how to lock all the tables or particular tables at once. But I think you should try this
Code:
lock tables db.* read;
I am not sure that all tables can be locked at once, but If you lock individual table with repeated exercise this will work.
Quote:
Get all tables ( select * from information_schema.tables ) and put in a Cursor...
Iterate in cursor and lock each table... ( Lock Table X )
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Lock an entire mysql database"
Thread Thread Starter Forum Replies Last Post
enabling SLI lock up the entire system KP Monitor & Video Cards 6 15-12-2011 01:24 PM
Postgresql vs Mysql database Xmen Software Development 4 04-03-2010 10:11 PM
How to speed up the MySQL database Kalanidhi Software Development 5 27-02-2010 01:33 AM
Mysql Error : Can't connect to local mysql server through socket ' var lib mysql mysql.sock' 2 roshan45 Software Development 3 07-11-2009 09:36 PM
Backup mysql database jean-paul martell Software Development 3 31-10-2009 01:10 PM


All times are GMT +5.5. The time now is 12:18 PM.