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.
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;
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.
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 )