List:General Discussion« Previous MessageNext Message »
From:David Lerer Date:March 21 2014 7:02pm
Subject:RE: Locking a Database (not tables) x
View as plain text  
Thanks Wayne. This a great idea to prevent user activity on the server. I’ll use it in the future.
But I’m looking for a way to prevent user activity on a database ((i.e. "database"  being a "schema" or a "catalogue).
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<mailto:dlerer@univision.net>  |  http://www.univision.net


[cid:1e909b.png@efba91b0.48b65711]<http://www.univision.net>

From: Wayne Leutwyler [mailto:wleutwyl@stripped]
Sent: Friday, March 21, 2014 2:12 PM
To: David Lerer
Subject: Re: Locking a Database (not tables) x

You could set max_connections = 0; then kill off any remaining connections. Do your data load and then set you max_connections back to what it was prior.
show variables like ‘max_connections’; (note this number)
set global max_connections = 0
This will leave 1 connection open for a superuser, I dont know what ID you use for that a lot of people use root.
Now import your data.
Once the import is done set global max_connections back to what it was.

On Mar 21, 2014, at 1:42 PM, David Lerer <dlerer@stripped<mailto:dlerer@stripped>> 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.

Thanks, David.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql


Walter "Wayne" Leutwyler, RHCT
Sr. MySQL Database Administrator
Mobile: 614 519 5672
Office: 614 889 4956
E-mail: wayne.leutwyler@stripped<mailto:wayne.leutwyler@stripped>
E-mail: wleutwyl@stripped<mailto:wleutwyl@stripped>
Website: http://penguin-workshop.dyndns.org

"Courage is being scared to death, but saddling up anyway." --John Wayne

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.
Attachment: [text/html]
Attachment: [text/html]
Attachment: [text/html]
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