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 | dle...@univision.net |
www.univision.net
-----Original Message-----
From: shawn l.green [mailto:shawn.l.gr...@oracle.com]
Sent: Friday, March 21, 2014 3:34 PM
To: mysql@lists.mysql.com
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
