Results 1 to 4 of 4

Thread: Lock an entire mysql database

  1. #1
    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.

  2. #2
    Join Date
    Feb 2008
    Posts
    1,852

    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;

  3. #3
    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.

  4. #4
    Join Date
    Feb 2008
    Posts
    1,852

    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.
    Get all tables ( select * from information_schema.tables ) and put in a Cursor...
    Iterate in cursor and lock each table... ( Lock Table X )

Similar Threads

  1. enabling SLI lock up the entire system
    By KP in forum Monitor & Video Cards
    Replies: 6
    Last Post: 15-12-2011, 01:24 PM
  2. Postgresql vs Mysql database
    By Xmen in forum Software Development
    Replies: 4
    Last Post: 04-03-2010, 10:11 PM
  3. How to speed up the MySQL database
    By Kalanidhi in forum Software Development
    Replies: 5
    Last Post: 27-02-2010, 01:33 AM
  4. Replies: 3
    Last Post: 07-11-2009, 09:36 PM
  5. Backup mysql database
    By jean-paul martell in forum Software Development
    Replies: 3
    Last Post: 31-10-2009, 01:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,700,135.11517 seconds with 16 queries