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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> >> To: <mysql@lists.mysql.com> >> 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/[EMAIL PROTECTED] >> > >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]