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]
