Mysql: disable unused schemas

MySQL

I have many schemas on mysql instance running on my development pc. I've noticed that mysql is taking a large amount of ram and cpu time.

Is there a way to "disable" schemas of project I'm not working for?

I like to avoid to drop schema and then re-import when I need it. I'm searching to a way for just disable them or something similar!

Thanks
Marco

Best Answer

Every subfolder under datadir is considered fair game to be registered as a database. You could just mysqldump that entire database and then drop the database.

SUGGESTION

If you have to leave the database present but inaccessible, here is something radical you can try:

EXAMPLE

Suppose you have a database called mydb and you want to disable access to it. Go into the Linux OS and do the following:

chown -R root:root /var/lib/mysql/mydb

That's it. Since /var/lib/mysql/mydb would be no longer owned by the Linux mysql user, mysqld cannot access anything in that folder.

If you want the database accessible again, just do the reverse:

chown -R mysql:mysql /var/lib/mysql/mydb

Now, if you are dealing with Windows, try shutting down mysql with

net stop mysql

You could then

Rename the folder from mydb to #mydb something mysqld would not like
Run attrib +a against that folder
Just about anything that can deny read/write access to the Windows folder

Finally, start mysql back up

net start mysql

Give it a Try !!!

Related Solutions

MySQL – Why DROP DATABASE Takes So Long

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password: 
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example           | Sleep   | 297  |       |                  |
| 407 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).

MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

The first thing that comes to mind is the Server Model : db.m1.large

What limiting factors are placed on a MySQL RDS?

If you spin up an Amazon RDS instance of MySQL, you would subject yourself to whatever constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

max_connections
innodb_buffer_pool_size

Here is a Chart I posted Last Month

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I posted this in my other posts in the DBA StackExchange

You are using m1.large. Since the InnoDB Buffer Pool is 3/4 of the Instance. That's means you have 7.2G but only 1.8 is usable. That model is capable of having up to 623 connections. Each connection can consume memory because of

join buffers
sort buffers
read buffers
thread info

Amazon RDS is simply micromanaging resources. Since DB Connections can consume RAM, connections are probably being disallowed due to the lack of RAM needed.

SUGGESTIONS

Try reducing InnoDB log IO innodb_flush_log_at_trx_commit=0 during the mass creation
Make sure you are not doing large transactions during any automatic backups or snapshots
Try a bigger server model m1.xlarge or m2.xlarge