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 Lerer | Director, Database Administration | Interactive | 605 Third Avenue,
> 12th Floor, New York, NY 10158
> Direct: (646) 487-6522 | Fax: (646) 487-1569 | dlerer@stripped |
> -----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.
> Because this is essentially a metadata flip, the RENAME will be quite
> 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.