Mysql datadir shouldn't be in the root of mounted filesystem

[Doug Shelley]

During work on https://bugs.launchpad.net/trove/+bug/1363177 it was determined that the root problem is that the Mysql datadir should not be located on the root of a mounted filesystem.

I discussed with George Lorch from Percona who pointed me to this document:
http://www.percona.com/files/white-papers/preventing-mysql-emergencies.pdf

One of the recommendations contained in this document is found in section 11.3:
"Servers whose data directory is located in the root directory of a mounted filesystem can encounter problems. First, the presence of a directory named lost+found can cause many tools to malfunction, belieiving it to be a database because it appears in the output of SHOW DATABASES....."

There are other issues enumerated in that article related to this.

The recommendation would be to put the mysql datadir in a directory under the mount point. Also, there is no reason it has to be on /var/lib. We could probably change it to /data as the mount point and have a "mysql" sub-directory under that. (i.e. datadir = /data/mysql)

[George Ormond Lorch III (gl-az)]

This may be useful as a workaround, but the best practice still is to not use the root of a mount.

http://www.chriscalender.com/ignoring-the-lostfound-directory-in-your-datadir/

[Amrith Kumar (amrith)]

Updating importance to "Medium" (bugscrub, 2014-10-23)

[Sushil Kumar (sushil-kumar2)]

Hey Simon ... are you working on this patch, if not, can i please take it up.

[Simon Chang (changsimon)]

Hi Sushil, I'm working on this patch.

[Sushil Kumar (sushil-kumar2)]

Fine then, not a problem.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/147908

[Nikhil Manchanda (slicknik)]

This is probably caused by https://bugs.launchpad.net/trove/+bug/1363177 Need to verify

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/147908
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=ad42de03d3c3438fe00971d168405075cf1d6b14
Submitter: Jenkins
Branch: master

commit ad42de03d3c3438fe00971d168405075cf1d6b14
Author: Doug Shelley <email address hidden>
Date: Fri Jan 16 16:22:37 2015 +0000

    Move mysql datadir to a sub-directory on mounted volume

    Having the mysql datadir located at root of a mounted volume
    (which happens with volume support enabled) causes problems for
    backup/restore and resize. Mysql decides that the "lost+found"
    directory, which the operating system puts at the root of each
    file system, is actually a mysql "database".

    This change causes the guest agent to move the datadir into a
    sub-directory called "data" on the mounted volume.

    Change-Id: Ica628012a5708374d73e5394e370da2514300939
    Co-Authored-By: Simon Chang <email address hidden>
    Closes-bug: 1370646

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.openstack.org/214782

[Amrith Kumar (amrith)]

The changes proposed in https://review.openstack.org/#/c/214782/ are addressed in a fix to bug 1480447

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/214782
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=b911aa3302a81ac094b60c44435c375b83f2c193
Submitter: Jenkins
Branch: master

commit b911aa3302a81ac094b60c44435c375b83f2c193
Author: Petr Malik <email address hidden>
Date: Wed Aug 19 15:37:48 2015 -0400

    Update ignore_dbs for MySQL 5.6

    'lost+found' directories are no longer needed in the ignored list.
    Include 'performance_schema' system database (by default ON in >=5.6.6).
    Set 'performance_schema = ON' in default Trove configuration template and
    add a validation rule for it so that users can turn it OFF if they so
    desire.

    Change-Id: Iecc114594091eabf8a0664e066b9de22f95d1e81
    Closes-Bug: 1486721
    Related-Bug: 1370646