468,290 Members | 1,938 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,290 developers. It's quick & easy.

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
Jul 23 '05 #1
8 17618
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.
Jul 23 '05 #2
>> 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.


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
Jul 23 '05 #3
"Matt" <" <no email> wrote in message
news:11*************@corp.supernews.com...
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.


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

Jul 23 '05 #4
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:11*************@corp.supernews.com...
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.

Jul 23 '05 #5
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.
Jul 23 '05 #6
>> 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.

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.
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
Jul 23 '05 #7
Gordon Burditt 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.

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.

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


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
Jul 23 '05 #8
>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
Jul 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by headware | last post: by
1 post views Thread by MattB | last post: by
10 posts views Thread by dlrider | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.