List:General Discussion« Previous MessageNext Message »
From:Andrew Moore Date:July 20 2012 11:37pm
Subject:Re: Disable DB without deleting data
View as plain text  
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.reindl@stripped
> <javascript:;>]
> >>> Sent: Friday, July 20, 2012 2:51 AM
> >>> To: Johan De Meersman
> >>> Cc: mysql@stripped <javascript:;>
> >>> Subject: Re: Disable DB without deleting data
> >>>
> >>>
> >>>
> >>> Am 20.07.2012 11:20, schrieb Johan De Meersman:
> >>>> ----- Original Message -----
> >>>>> From: "Reindl Harald"<h.reindl@stripped
> <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
>
>

Thread
Disable DB without deleting dataKirk Hoganson19 Jul
  • Re: Disable DB without deleting dataReindl Harald19 Jul
    • Re: Disable DB without deleting dataJohan De Meersman20 Jul
      • Re: Disable DB without deleting dataReindl Harald20 Jul
        • RE: Disable DB without deleting dataRick James20 Jul
          • Re: Disable DB without deleting dataReindl Harald20 Jul
          • Re: Disable DB without deleting dataKirk Hoganson20 Jul
            • Re: Disable DB without deleting dataReindl Harald20 Jul
              • Re: Disable DB without deleting dataAndrew Moore20 Jul
  • Re: Disable DB without deleting dataAndrew Moore19 Jul