Mysql: How to disable single database without dropping or renaming it

mysql rename forbid disable database howto logo, how to disable single database without dropping it
A colleague of mine working on MySQL database asked me How it is possible to disable a MySQL database. He is in situation where the client has 2 databases and application and is not sure which of the two databases the application uses. Therefore the client asked one of the database is disabled and wait for few hours and see if something will break / stop working and in that way determine which of the two database is used by application.

My first guess was to backup both databases and drop one of them, then if it is the wrong one to restore from the SQL dump backup, however this wasn't acceptable solution. So second I though of RENAME of database to another one and then reverting the name, however as it is written in MySQL documentation RENAME database function was removed from MySQL (found to be dangerous) since version 5.1.23 onwards. Anyhow there is a quick hack to rename mysql database using a for loop shell script one below:

mysql -e "CREATE DATABASE `new_database`;"
for table in `mysql -B -N -e "SHOW TABLES;" old_database`
do
  mysql -e "RENAME TABLE `old_database`.`$table` to `new_database`.`$table`"
  done
  mysql -e "DROP DATABASE `old_database`;"

Other possible solution was to change permissions of Application used username, however this was also complicated from mysql cli, hence I thought of installing and using PHPMyAdmin to make modify of db user permissions easier but on this server there wasn't Apache installed and MySQL is behind a firewall and only accessible via java tomcat host.

Finally after some pondering what can be done I came with solution to request to disable mysql database using chmod in /var/lib/mysql/data/, i.e.:

sql-server:~# chmod 0 /var/lib/mysql/databasename

Where databasename is the same as the database is named listable via mysql cli.

After doing it that way with no need to restart MySQL server database stopped to appear in show databases; and client confirmed that disabled database is no longer needed so we proceeded dropping it.

Hope this little article will help someone out there. Cheers :

Share this on:

More helpful Articles

Download PDFDownload PDF

Tags: , , , , , , , , , , , , , , , , , ,

3 Responses to “Mysql: How to disable single database without dropping or renaming it”

  1. komputr says:
    Google Chrome 35.0.1916.153 Google Chrome 35.0.1916.153 Windows 7 x64 Edition Windows 7 x64 Edition
    Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36

    really good tip. simple and does exactly whats required.

    View CommentView Comment
  2. James says:
    Google Chrome 36.0.1985.143 Google Chrome 36.0.1985.143 Windows 8.1 x64 Edition Windows 8.1 x64 Edition
    Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36

    Wow. So simple and it works! Thank you so much!

    View CommentView Comment
  3. Shaun says:
    Firefox 38.0 Firefox 38.0 Fedora x64 Fedora x64
    Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:38.0) Gecko/20100101 Firefox/38.0

    Also you might rename the database user if that user is the only one using that database. Then any applications using those credentials would fail.

    View CommentView Comment

Leave a Reply

CommentLuv badge