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.

MySQL 5.5's new features

The recently released MySQL 5.6 gets a lot of attention, but for those who are still on 5.5 there is also good news: There are two new features in 5.5.

The first feature is that there are more INFORMATION_SCHEMA tables for InnoDB. This means that it's possible to 'see' what's in the buffer pool. It also makes it possible to get more information about the LRU list.

From the 5.5.28 changelog:
InnoDB: Certain information_schema tables originally introduced in MySQL 5.6 are now also available in MySQL 5.5 and MySQL 5.1: INNODB_BUFFER_PAGE, INNODB_BUFFER_PAGE_LRU, and INNODB_BUFFER_POOL_STATS. (Bug #13113026)

This is in the "Bugs Fixed" section instead of the "Functionality Added or Changed" section, which is a bit weird in my opinion.

The second feature is a variable which makes it possible to get more information about the deadlocks.

From the 5.5.30 changelog:
InnoDB: The innodb_print_all_deadlocks configuration option from MySQL 5.6 was backported to MySQL 5.5. This option records each deadlock condition in the MySQL error log, allowing easier troubleshooting if frequent deadlocks point to application coding issues. (Bug #14515889)

So updating to the latest 5.5 (or even  5.1) is not only good for security, stability and performance but it also brings some nice new features.