Or rename the users themselves. This backs onto the revoke idea. If only the DBA knows the new names then the privileges stay intact and the old usernames could be restored with a reversal script when the db can be accessed again. Applications will simply get an access denied at connection time. Not the most elegant solution but practical as long as you're sure the other apps are privileged correctly.
On Friday, July 20, 2012, Reindl Harald wrote: > the permissions are in the "mysql" database > so there are two possibilites > > * mysql is not interested and do not touch them at all > means they are still for the old names and work after > rename beack as before > > * mysql does magic and update the permission-tables > > i bet case one will happen > and this is what you want > > after "rename" this way there is no access except with root-permissions > after rename back all is as before > > however, you can export permissions easily with phpMyAdmin > as normal sql-statements > > Am 20.07.2012 23:09, schrieb Kirk Hoganson: > > Would this keep permissions intact? I need something that would make it > easy to automatically restore the database > > including any custom permissions? > > > > Thanks again for the input, > > Kirk > > > > On 07/20/2012 12:16 PM, Rick James wrote: > >> No. RENAME DATABASE does not exist. It may have something to do with > internal table numbers in InnoDB. > >> Instead... > >> CREATE DATABASE new ...; > >> RENAME TABLE old.t1 TO new.t1, ... > >> DROP DATABASE old; > >> This should work cross-device. > >> > >>> -----Original Message----- > >>> From: Reindl Harald [mailto:h.rei...@thelounge.net <javascript:;>] > >>> Sent: Friday, July 20, 2012 2:51 AM > >>> To: Johan De Meersman > >>> Cc: mysql@lists.mysql.com <javascript:;> > >>> Subject: Re: Disable DB without deleting data > >>> > >>> > >>> > >>> Am 20.07.2012 11:20, schrieb Johan De Meersman: > >>>> ----- Original Message ----- > >>>>> From: "Reindl Harald"<h.rei...@thelounge.net <javascript:;>> > >>>>> > >>>>> impossible with innodb > >>>>> > >>>>> * dump > >>>>> * delete > >>>>> > >>>>> with myisam you could stop the server and move the databasedir do > >>> not > >>>>> try this with innodb even with innodb_file_per_table! > >>>> Mmh, it should be pretty easy to write a small script that creates a > >>> new database named<oldname>_offline and renames all tables into it, > >>> no? > >>>> Shame that "rename database" doesn't exist any more :-) > >>> my reply was to "Something like moving the databases subdirectory to a > >>> different directory" > >>> > >>> surely, you can rename the database > >>> but if you want to move it to another mount-point you are lost with > >>> innodb > >> > > > > -- > > Reindl Harald > the lounge interactive design GmbH > A-1060 Vienna, Hofmühlgasse 17 > CTO / CISO / Software-Development > p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 > icq: 154546673, http://www.thelounge.net/ > > http://www.thelounge.net/signature.asc.what.htm > >
