Playing hid-and-seek with databases

As far as I know there isn't a well accepted set of best practices for MySQL, but there are many best practices known and used by most MySQL DBA's. One of those best practices is that the datadir must not be equal to a mountpoint; it has to be a subdirectory of a mountpoint. I learned this the hard way a few years ago when I used a NetApp Filer via NFS as a data directory. The NetApp filer exposed the snapshots via a .snapshot directory. A database in MySQL is a directory, so MySQL thought that the .snapshot directory was a database. This resulted in some issues with our monitoring scripts, so we had to create a mysql_data directory and move all the databases to that directory.

For other setups directories like lost+found, .zfs, etc. gave similar issues.

In MySQL 5.6 a new feature was introduced to make it possible to make some databases hidden. To do this a ignore-db-dir option needs to be specified for each directory. On a running server the list of ignored database directories is shown in the ignore_db_dirs variable.

mysql> SHOW DATABASES LIKE 'testdir%';
+---------------------+
| Database (testdir%) |
+---------------------+
| testdir3            |
+---------------------+
1 row in set (0.00 sec)

mysql> \! ls -ld data/testdir*
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir1
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir2
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir3
mysql> SELECT @@ignore_db_dirs;
+-------------------+
| @@ignore_db_dirs  |
+-------------------+
| testdir1,testdir2 |
+-------------------+
1 row in set (0.00 sec)

mysql> \! grep ignore-db-dir my.sandbox.cnf
ignore-db-dir='testdir1'
ignore-db-dir='testdir2'

This helps a lot if there are foreign directories in the datadir, but it's always better to not have those directories there as it might be loaded into the database with a LOAD DATA statement or it might get deleted with a DROP DATABASE statement. So the best practices is still to have a data directory which is a subdirectory of a mountpoint, not the mountpoint itself.

A hidden directory is still available and can be used, it's just not shown in the output of various commands.