Re: Locking a Database (not tables) x
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 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 RENAME TABLE yyy.table1 to .table1, yyy.table2 to .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
RE: Locking a Database (not tables) x
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 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 RENAME TABLE yyy.table1 to .table1, yyy.table2 to .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 -- 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
Re: Locking a Database (not tables) x
Perhaps enabling read only, followed by import with super user will do what you want. On Mar 22, 2014, at 12:26 AM, Manuel Arostegui wrote: > 2014-03-21 18:42 GMT+01:00 David Lerer : > >> 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. >> > > > Hello, > > One more idea: > > Assuming you can stop your DB - restart the database so it only listens in > the unix socket or in a different IP (an alias of your current IP could > work) and connect thru it do all your stuff and enabled it back to its > original port and IP. > Obviously I am assuming your developers connect remotely (thru port 3306 or > whichever you use). > > Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Locking a Database (not tables) x
2014-03-21 18:42 GMT+01:00 David Lerer : > 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. > Hello, One more idea: Assuming you can stop your DB - restart the database so it only listens in the unix socket or in a different IP (an alias of your current IP could work) and connect thru it do all your stuff and enabled it back to its original port and IP. Obviously I am assuming your developers connect remotely (thru port 3306 or whichever you use). Manuel.
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 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 RENAME TABLE yyy.table1 to .table1, yyy.table2 to .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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Locking a Database (not tables) x
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 | dle...@univision.net<mailto:dle...@univision.net> | http://www.univision.net [cid:1e909b.png@efba91b0.48b65711]<http://www.univision.net> From: Wayne Leutwyler [mailto:wleut...@columbus.rr.com] 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 mailto:dle...@univision.net>> 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.leutwy...@gmail.com<mailto:wayne.leutwy...@gmail.com> E-mail: wleut...@columbus.rr.com<mailto:wleut...@columbus.rr.com> 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.
Locking a Database (not tables) x
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. 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 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
