| Author |
Disable a Single Database
|
|
|
| Hi everyone I was wondering if there was a way to disable a single
database with having to shutdown mysql or rename tables?
Thanks,
Matt
| |
|
| Matt <no email> wrote:
> Hi everyone I was wondering if there was a way to disable a single
> database with having to shutdown mysql or rename tables?
>
> Thanks,
> Matt
Correction... without having to shutdown mysql or rename tables.
| |
| Gordon Burditt 2005-06-22, 8:23 pm |
| >> Hi everyone I was wondering if there was a way to disable a single
>
>Correction... without having to shutdown mysql or rename tables.
You just *MIGHT* manage to do this with "chmod 0
/var/db/mysql/databasename" (or if this is Windows, do something
equivalent to remove access permissions from the whole directory).
followed by a "flush tables;" command with the command-line client
to close any tables the server already had open. Any attempts to
re-open the tables will get a "permission denied" error. The trouble
is, does the server keep any of those open in spite of "flush
tables"? I have not tested this.
Gordon L. Burditt
| |
| Thomas Bartkus 2005-06-23, 9:23 am |
| "Matt" <" <no email> wrote in message
news:11bjjufpqto7g51
@corp.supernews.com...
> Matt <no email> wrote:
>
> Correction... without having to shutdown mysql or rename tables.
You might just rename the database!
All the contained tables would remain intact. The renamed database would
simply vanish for users who do not have permissions to the new database
name.
Thomas Bartkus
| |
| Rajesh Kapur 2005-06-23, 9:23 am |
| If you have set up your database privileges at the DB level (mysql.db
table), you should be able to simply withdraw those privileges to make the
DB unavailable. This will not work if you have set privileges at the user
level (mysql.user table) allowing users privileges across all databases.
"Matt" <" <no email> wrote in message
news:11bjjufpqto7g51
@corp.supernews.com...
> Matt <no email> wrote:
>
> Correction... without having to shutdown mysql or rename tables.
| |
| Aggro 2005-06-23, 11:23 am |
| Thomas Bartkus wrote:
> All the contained tables would remain intact. The renamed database would
> simply vanish for users who do not have permissions to the new database
> name.
It would be visible (with different name) in MySQL 3.x, but users
couldn't use it (or so I hope). Newer versions of MySQL also hide it.
| |
| Gordon Burditt 2005-06-23, 11:23 am |
| >> All the contained tables would remain intact. The renamed database would
There is the issue that the MySQL server may have the tables already
open and continue to use them, not realizing that they have
been renamed. For this, I recommend "flush tables" after doing
the rename.
[color=darkred]
>It would be visible (with different name) in MySQL 3.x, but users
>couldn't use it (or so I hope).
This presumes you have no users with (some) access to *ALL* databases.
That may not be an accurate assumption. The admin might want to
lock out access by scripts running on his behalf (and using a login
with wide read-only access, say, for periodic automatic backups).
>Newer versions of MySQL also hide it.
Gordon L. Burditt
| |
|
| Gordon Burditt wrote:
>
>
> There is the issue that the MySQL server may have the tables already
> open and continue to use them, not realizing that they have
> been renamed. For this, I recommend "flush tables" after doing
> the rename.
>
>
>
>
> This presumes you have no users with (some) access to *ALL* databases.
> That may not be an accurate assumption. The admin might want to
> lock out access by scripts running on his behalf (and using a login
> with wide read-only access, say, for periodic automatic backups).
>
>
>
>
> Gordon L. Burditt
It seems like renaming and then flushing the tables seem to be the best
option. I wish there was a DISABLE <Database Name> syntax that would
return a "Database is disabled" error message. This may even be extended
to DISABLE <Username>.
The reason I asked this question was because I have programs located
on different systems that update some mysql databases but I'm having
a particular issue regarding one of the databases and or the user that
accesses it. This one database was starving the other users accessing
the other databases.
Thanks for all your advice,
Matt
| |
| Gordon Burditt 2005-06-23, 8:23 pm |
| >It seems like renaming and then flushing the tables seem to be the best
>option. I wish there was a DISABLE <Database Name> syntax that would
>return a "Database is disabled" error message. This may even be extended
>to DISABLE <Username>.
You might be able to disable a user by changing their encrypted
password, say, by prefixing some odd character to it. You'd need
a "flush privileges" also. However I don't think that affects the
user if he is currently logged in, only for subsequent logins.
>The reason I asked this question was because I have programs located
>on different systems that update some mysql databases but I'm having
>a particular issue regarding one of the databases and or the user that
>accesses it. This one database was starving the other users accessing
>the other databases.
You can have limits on accounts, starting in MySQL 4.0.2:
queries/hour (any queries)
updates/hour (queries that do updates)
connects/hour (connections to MySQL, *NOT*
necessarily simultaneous)
and starting in MySQL 5.0.3:
max simultaneous connections
Setting a limit of 1 query/hour on that user would do a pretty
effective limit of his ability to starve other users, unless he's
got a horribly complicated query. A limit of 0 seems to mean
"unlimited".
Unfortunately I suspect that if you limited this user to, say, 1000
queries per hour, he might just consistently starve the other users
from *:00 thru *:03, then get locked out until the next hour.
Gordon L. Burditt
|
|
|
|