List:General Discussion« Previous MessageNext Message »
From:Kishore Jalleda Date:July 2 2006 12:50am
Subject:Re: howto set mysql to readonly
View as plain text  
put this in your my.cnf
"read_only"

this would put the DB in a read only mode, except for the slave threads and
the super users, which/who can still do writes, this option is mostly used
on slaves though .....

see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Kishore Jalleda
http://kjalleda.googlepages.com



On 6/30/06, Jehan PROCACCIA <Jehan.Procaccia@stripped> wrote:
>
> OK so it seems to be a bad idea ... I was expecting that I missed a
> magic command like "set readonly on all databases" ...
> however, still thinking in a probably bad solution , what about setting
> unix file system acces mode to the database files to read only (400)
> wouldn't be a good idea ? (if I don't care about clients trying to
> write, i just want those trying to read to be able to do so )
>
> Dan Buettner wrote:
> > I personally would be wary of a solution like what you're proposing
> > (locking all tables, that is).
> >
> > The problem I see is this - you lock all tables and proceed to move
> > your data over to another host.  Meanwhile, clients could well be
> > queueing up insert or update commands that are simply blocking,
> > waiting for you to release the locks.
> >
> > At the end, when you either release the locks or shutdown the database
> > server, those clients' operations may complete, but against the
> > outdated databases on the old host, or they may go into a deadlock
> > waiting for the host to come back (and not come out of it), or they
> > may fail ... or you may have users who think their computer is frozen
> > and reboot, losing work.
> >
> > Seems risky, too much potential for data loss.
> >
> > I would insist on finding a window in which to shut down the database
> > server and accomplish the migration in an orderly fashion.
> >
> > Dan
> >
> >
> > On 6/30/06, Brent Baisley <brenttech@stripped> wrote:
> >> Instead of "locking" the table, why not just change the privileges
> >> for the specific accounts so they have select only privileges?
> >> Then you still of write access through "admin" accounts if need be.
> >>
> >> ----- Original Message -----
> >> From: "Jehan PROCACCIA" <Jehan.Procaccia@stripped>
> >> To: <mysql@stripped>
> >> Sent: Friday, June 30, 2006 6:28 AM
> >> Subject: howto set mysql to readonly
> >>
> >>
> >> > Hello
> >> >
> >> > I need to move my databases from on server to an other.
> >> > As lots of data are in production I cannot just stop mysql for 1/2
> >> an hour and reopen it on the new server.
> >> > What I expect to do is while backuping and restoring to the new
> >> server, I wish to set the original server in "read only" mode so
> >> > that nobody can write in the databases while transfering to the new
> >> one.
> >> > I've seen the LOCK table and flush commands, but I'am not sure if
> >> this is the right method, and how to use them.
> >> > Lock table just locks tables as it's name implies and not a whole
> >> database ? is there a kind of "lock all databases" ?
> >> >
> >> > thanks.
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=1
> >> >
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=1
> >>
> >>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

Thread
howto set mysql to readonlyJehan PROCACCIA30 Jun
  • Re: howto set mysql to readonlyBrent Baisley30 Jun
    • Re: howto set mysql to readonlyDan Buettner30 Jun
      • Re: howto set mysql to readonlyJehan PROCACCIA30 Jun
        • Re: howto set mysql to readonlyKishore Jalleda2 Jul
        • Re: howto set mysql to readonlyJeremy Cole2 Jul