Re: take one database offline
Stephen Brownlow wrote: You can make backup of the database and then drop the database.. Why do you want to do it? When maintenance is necessary, it would be far better to be able to: 1. turn off one database, 2. use myisamchk, Unix or other applications to adjust it in any way, 3. turn it back on. I must second that request, although someone else pointed out that using a LOCK FOR UPDATE would work as well since no processes would be able to touch the database during the lock. As long as the MySQL team thinks thats a safe way to handle things, and is willing to make sure it keeps working that way safely, I'd say we already have a solution though. Oh yeah, without the words SQL or QUERY, this message would be spam. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: take one database offline
At 17:53 +1100 12/11/02, Stephen Brownlow wrote: Hello Paul, I am only lobbying, not demanding. Sorry. I wasn't trying to imply that you were demanding. It's just that experience shows that feature implementation always seems easy to the people who aren't doing the work. :-) Paul Dubois wrote: Mmm, how do you know how much effort it would be? Have you implemented it? Ok. IMHO it would be relatively easy for MySQL to implement, judging by my experience dealing with Monty and his source code (specifically, the myisam API). I await comments from MySQL AB. Write a client that flushes all the tables, then places a write lock on them all. That will keep anyone else from modifying them. Will it stop mysqld from reading them? Yes. When the client places the write lock, it's the server that grants the lock request, so it knows that only the client that holds the lock can make changes. In the situation at hand, that client won't make any changes; it's grabbing the lock only to prevent *other* clients from making changes. Some maintenance processes such as myisamchk will actually move the files around, which could confuse mysqld. That's why the client acquires a write lock and then doesn't make any changes itself. This is described in more detail in chapter 13 of that New Riders book. Run myisamchk. When it's done, flush the tables again and unlock them. While that might be workable, don't you think the proposal would be both safer and simpler? Sure. But in the absence of that capability, the procedure described might be useful in some way. Thanks, Stephen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: take one database offline
Egor Egorov [EMAIL PROTECTED] wrote on December 05, 2002: What do you mean disable database? Ask the MySQL server to Flush it, then not access its tables until further notice. You can't turn off the database.. Can this please be written? It offers big benefits for little effort. You can make backup of the database and then drop the database.. Why do you want to do it? We have a similar need. We have a client with 16 branches. Each branch runs the same software, using the same server. Each branch has its own database. When maintenance is necessary, it would be far better to be able to: 1. turn off one database, 2. use myisamchk, Unix or other applications to adjust it in any way, 3. turn it back on. That way, the other 15 branches can continue normal operations while the maintenance occurs on the one. Thanks, Stephen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: take one database offline
At 13:23 +1100 12/11/02, Stephen Brownlow wrote: Egor Egorov [EMAIL PROTECTED] wrote on December 05, 2002: What do you mean disable database? Ask the MySQL server to Flush it, then not access its tables until further notice. You can't turn off the database.. Can this please be written? It offers big benefits for little effort. Mmm, how do you know how much effort it would be? Have you implemented it? You can make backup of the database and then drop the database.. Why do you want to do it? We have a similar need. We have a client with 16 branches. Each branch runs the same software, using the same server. Each branch has its own database. When maintenance is necessary, it would be far better to be able to: 1. turn off one database, 2. use myisamchk, Unix or other applications to adjust it in any way, 3. turn it back on. That way, the other 15 branches can continue normal operations while the maintenance occurs on the one. Write a client that flushes all the tables, then places a write lock on them all. That will keep anyone else from modifying them. Run myisamchk. When it's done, flush the tables again and unlock them. Thanks, Stephen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: take one database offline
Hello Paul, I am only lobbying, not demanding. Paul Dubois wrote: Mmm, how do you know how much effort it would be? Have you implemented it? Ok. IMHO it would be relatively easy for MySQL to implement, judging by my experience dealing with Monty and his source code (specifically, the myisam API). I await comments from MySQL AB. Write a client that flushes all the tables, then places a write lock on them all. That will keep anyone else from modifying them. Will it stop mysqld from reading them? Some maintenance processes such as myisamchk will actually move the files around, which could confuse mysqld. Run myisamchk. When it's done, flush the tables again and unlock them. While that might be workable, don't you think the proposal would be both safer and simpler? Thanks, Stephen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: take one database offline
On Wed, 2002-12-04 at 22:32, Richardson, David E (MVC Corporation) wrote: On a single Linux box w/mysql 3.23.52 I have mysqld running and there are about 20 databases live in the environment. I want to take one of the databases offline but leave mysqld running with the other db's - without interrupting service. I want mysqld to gracefully refresh itself that the database is offline and not require a restart. I have the luxury of time if that's helpful to the solution. How do I disable a database in a running server and leave the rest of the db's in production? A nasty hack for unix-flavoured os is: * place a write lock on all tables in the database you want to disable (eg. lock table one write, two write, three write, ... ) * flush tables * as root, chdir to the mysql datadir (eg. cd /var/lib/mysql) * create a database to hide your database in (eg. mkdir __safe__) * move your database (eg. mv actual_database __safe__) * in mysql, unlock the tables (eg. unlock tables ) Moving a database into a sub-directory makes the database inaccessible. Locking the tables makes you wait for all users to stop using the tables, and prevents anyone else opening one. flushing the tables releases any filehandles MySQL has cached on any of the affected tables. == Martin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: take one database offline
Richardson, Thursday, December 05, 2002, 12:32:27 AM, you wrote: RDE On a single Linux box w/mysql 3.23.52 I have mysqld running and there are RDE about 20 databases live in the environment. I want to take one of the RDE databases offline but leave mysqld running with the other db's - without RDE interrupting service. RDE I want mysqld to gracefully refresh itself that the database is offline RDE and not require a restart. I have the luxury of time if that's helpful to RDE the solution. RDE How do I disable a database in a running server and leave the rest of the RDE db's in production? What do you mean disable database? You can't turn off the database.. You can make backup of the database and then drop the database.. Why do you want to do it? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: take one database offline
another trick that might work is just to deny access to everyone to the database. if no one has access to it, then its as good as gone. i don't know if MySQL would allow just grant blah on *.* to user; revoke all on disabled_db.* from user; and alot of it depends on how the security is setup and possibly why you want just that database temporaraly gone. On Thursday 05 December 2002 8:05, you wrote: Richardson, Thursday, December 05, 2002, 12:32:27 AM, you wrote: RDE On a single Linux box w/mysql 3.23.52 I have mysqld running and there are RDE about 20 databases live in the environment. I want to take one of the RDE databases offline but leave mysqld running with the other db's - without RDE interrupting service. RDE I want mysqld to gracefully refresh itself that the database is offline RDE and not require a restart. I have the luxury of time if that's helpful to RDE the solution. RDE How do I disable a database in a running server and leave the rest of the RDE db's in production? What do you mean disable database? You can't turn off the database.. You can make backup of the database and then drop the database.. Why do you want to do it? -- mysql, sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
