List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 24 2014 3:11am
Subject:Re: Locking a Database (not tables) x
View as plain text  
On 2014-03-23 8:02 PM, David Lerer wrote:
> Thanks Shawn, This may work for us with some script changes. We'll take a look.
>
> By the way, too bad we cannot rename a database, or can we?
> See http://dev.mysql.com/doc/refman/5.1/en/rename-database.html about removal of a
> "dangerous RENMAE DATABASE" statement...
>
> David.
>
>
> David Lerer | Director, Database Administration | Interactive | 605 Third Avenue,
> 12th Floor, New York, NY 10158
> Direct: (646) 487-6522 | Fax: (646) 487-1569 | dlerer@stripped |
> www.univision.net
>
> -----Original Message-----
> From: shawn l.green [mailto:shawn.l.green@stripped]
> Sent: Friday, March 21, 2014 3:34 PM
> To: mysql@stripped
> Subject: Re: Locking a Database (not tables) x
>
> 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

Remember to similarly rename other database objects to, eg sprocs & funcs.

PB

-----

>
>
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
> The information contained in this e-mail and any attached
>
> documents may be privileged, confidential and protected from
>
> disclosure. If you are not the intended recipient you may not
>
> read, copy, distribute or use this information. If you have
>
> received this communication in error, please notify the sender
>
> immediately by replying to this message and then delete it
>
> from your system.
>

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