List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:March 21 2014 7:34pm
Subject:Re: Locking a Database (not tables) x
View as plain text  
Hi David.

On 3/21/2014 1:42 PM, David Lerer wrote:
> Frequently, we import a production dump that contains only 1 or 2 databases into one
> of our QA instances that contains many more databases. (i.e. "database" being a "schema"
> or a "catalogue).
> At the beginning of the import script, we first drop all objects in the QA database
> so that it will be a perfect match (object wise) to production.
>
> Is there an easy way to lock the whole database for the duration of the import - so
> that no developers can update the database?
> Obviously, I can revoke permissions, but I was wondering whether there is a better
> approach.
>

If you start with a DROP DATABASE xxxx  that will pretty much ensure 
that nobody gets back into it.

Then re-create your tables in a new DB (yyy)

As a last set of steps do

   CREATE DATABASE xxxx
   RENAME TABLE yyy.table1 to xxxx.table1, yyy.table2 to xxxx.table2, 
....  (repeat for all your tables).
   DROP DATABASE yyy


Because this is essentially a metadata flip, the RENAME will be quite 
speedy.

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Locking a Database (not tables) xDavid Lerer21 Mar 2014
  • Re: Locking a Database (not tables) xshawn l.green21 Mar 2014
    • RE: Locking a Database (not tables) xDavid Lerer24 Mar 2014
      • Re: Locking a Database (not tables) xPeter Brawley24 Mar 2014
  • Re: Locking a Database (not tables) xManuel Arostegui22 Mar 2014
  • Re: Locking a Database (not tables) xKarr Abgarian22 Mar 2014
RE: Locking a Database (not tables) xDavid Lerer21 Mar 2014