Take individual MySQL database offline

Question

MySQL does not have any SQL commands or internal mechanisms for

• making an individual database unavailable / offline
• moving an individual database

Thtis being the case, how can you take an individual database offline?

Answer 1

What I am about to suggest is very high risk and never been tried.

I suggest this simply because there is no SQL command in MySQL to rename a database.

Since my suggestion will not harm the data, it is worth a shot.

• Try this out in a test environment, first.
• Make sure the database is fully accessible after bringing the database back on line

Here it goes...

SUGGESTION

Suppose

• datadir is /var/lib/mysql
• the database to take offline called mydb

You could do something devious like this:

service mysql stop
cd /var/lib/mysql
mv mydb mydb_offline
service mysql start

The database is accessible as mydb_offline

From the standpoint of your app, mydb is no longer visible. No application can connect to it.

To bring the database back online, do the following:

service mysql stop
cd /var/lib/mysql
mv mydb_offline mydb
service mysql start

Unfortunately, it requires a shutdown and startup of mysql.

I REPEAT

• Try this out in a test environment, first.
• Make sure the database is fully accessible after bringing the database back on line

UPDATE 2012-11-30 18:09 EDT

I will be giving the DBA.SE community about 4 weeks to see if there are other answers. If none is forthcoming, I will be attempt to script something using MySQL and/or Shell Scripting.

UPDATE 2012-11-30 18:56 EDT

I changed my mind. I wrote the code using MySQL and Shell Scripting

DB_TO_MOVE= mydb
TAKE_OFFLINE=1

if [ ${TAKE_OFFLINE} -eq 1 ]
then
    DB1=${DB_TO_MOVE}
    DB2=${DB1}_offline
else
    DB2=${DB_TO_MOVE}
    DB1=${DB2}_offline
fi
MYSQL_CONN=`-u...-p...`
DB_EXISTS_SQL="SELECT COUNT(1) FROM information_schema.schemata WHERE schema_name='${DB1}'"
DB_EXISTS=`mysql ${MYSQL_CONN} -ANe"${DB_EXISTS_SQL}"`
if [ ${DB_EXISTS} -eq 0 ]
then
    echo "Database ${DB1} Does Not Exist"
    exit
fi
echo "DROP DATABASE IF EXISTS ${DB2}; "  > TakeDBOffline.sql
echo "CREATE DATABASE ${DB2};"          >> TakeDBOffline.sql
DBTABLES_SQL="SELECT table_name FROM information_schema.tables WHERE table_schema='${DB1}'"
for TB in `mysql ${MYSQL_CONN} -ANe"${DBTABLES_SQL}"`
do
    echo "ALTER TABLE ${DB1}.${TB} RENAME ${DB2}.${TB};" >> TakeDBOffline.sql
done

FURTHER AGNEDA

• This does not take into account moving of stored procedures. I deal with that later...
• I'll see if I can script a Stored Procedure
• None of this has been tested (feel free to try this in production.. HAHA, please try in a DevServer)

Answer 2

This may not be a suitable answer, but would achieve your goal...

What about, for the time you want it offline, renaming the database? This way, whatever application you have accessing the data would fail to connect to the database.