Re: howto set mysql to readonly
Hi Jehan, OK so it seems to be a bad idea ... I was expecting that I missed a magic command like set readonly on all databases ... I think you did: SET GLOBAL read_only=1; This will keep all non-SUPER and non-replication users from writing to the database. Regards, Jeremy -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: howto set mysql to readonly
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]
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]
Re: howto set mysql to readonly
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]
Re: howto set mysql to readonly
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]
Re: howto set mysql to readonly
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]
