====== Changing MySQL data directory ======

===== Why changing MySQL data dir ? =====


Under Ubuntu server, MySQL data are located under **/var/lib/mysql** which coud cause a problem whether I have a huge database to cope with : on default debian installation, much of the disk space is left for the **/home/** directory.

As there already is a mysql user, it is a better solution to store its data to a **/home/mysql** folder

<code>
 Filesystem            Size  Used Avail Use% Mounted on
 /dev/sda1             2.9G  1.4G  1.4G  51% /
 varrun                493M   60K  493M   1% /var/run
 varlock               493M   12K  493M   1% /var/lock
 udev                  493M   20K  493M   1% /dev
 devshm                493M     0  493M   0% /dev/shm
 /dev/sda3             226G   94G  122G  44% /home
</code>


===== How to change MySQL data dir ? =====

==== Stopping mysql daemon ====

First, su on your machine.

Then, stop mysql daemon, this could be useful to prevent anybody doing any modification during the process. This action will stop all mysql jobs running i.e. if you are hosting any applications on your server, they will be unreachable during the operation (about 5 minutes).

To stop mysql daemon, simply drop the command line
<code bash>/etc/init.d/mysql stop</code>

==== /home/mysql directory ====

Create a /home/mysql directory and give clearance to mysql
<code bash>
mkdir /home/mysql
chown -R mysql /home/mysql
chgrp -R mysql /home/mysql
</code>

Copy data and preserve permissions, etc.
<code bash>cp -Rp /var/lib/mysql/* /home/mysql/</code>

====Change MySQL configuration ====

MySQL configuration is basically stored into the my.cnf file. If you have any exotic mysql configuration such as clustering or if you are making use of the /etc/mysql/conf.d/ folder for specific user tweaking, this could be dangerous and think of making a backup of this config file before modifying it.

When you are ready, just edit it
<code bash>vi /etc/mysql/my.cnf </code>

Find the line
<code>datadir         = /var/lib/mysql</code>
And change it to
<code>datadir         = /home/mysql</code>

==== Re-enable MySQL and test ====

Okay, all went fine ? let's start mysql back
<code bash>/etc/init.d/mysql start</code>

Test MySQL, great, it works, now how do we get sure that it is now operating in the new **/home/mysql/** directory ?

To be sure, simply create a new database (via command line or phpMyAdmin) and check in the filesystem if a folder has been created.
<code bash>ls -la /home/mysql/</code>

==== Cleaning previous mysql data files ====

After all, you may desire to clean up the **/var/lib/mysql** folder. My advice would be to keep the files debian-5.0.flag and mysql_upgrade_info (because i don't know what they stand for) and the mysql folder which could be a backup directory to restore your database permissions (can be useful some days, trust me).

Have it been benefic to your filesystem ? check that with a new df -h

<code>
 Filesystem            Size  Used Avail Use% Mounted on
 /dev/sda1             2.9G  798M  2.0G  29% /
 varrun                493M   60K  493M   1% /var/run
 varlock               493M   12K  493M   1% /var/lock
 udev                  493M   20K  493M   1% /dev
 devshm                493M     0  493M   0% /dev/shm
 /dev/sda3             226G  101G  115G  47% /home
</code>

===== Known bugs =====
==== Apparmor ====
Whereas this runs smoothly under Ubuntu Server, on some Ubuntu desktop distribs, you may notice one of those lines in your logs :
<code logs>
Feb 18 20:01:11 gaspard-desktop mysqld[25096]: 090218 20:01:11  InnoDB: Operating system error number 13 in a file operation.
Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: The error means mysqld does not have the access rights to
Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: the directory.
Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: File name ./ibdata1
Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: File operation call: 'open'.
Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: Cannot continue operation.

Feb 18 20:01:11 gaspard-desktop mysqld[25096]: 090218 20:01:11 [Warning] Can't create test file /home/mysql/gaspard-desktop.lower-test
</code>

This comes from the Apparmor configuration

Edit /etc/apparmor.d/usr.sbin.mysqld and add the following two lines:
<code bash>
/home/mysql/ r,
/home/mysql/** rwk,
</code>
somewhere in the middle of the file. After the /var/lib/mysql lines would be fine.

Restart AppArmor
<code bash>/etc/init.d/apparmor restart</code>
Restart MySQLd
<code bash>/etc/init.d/mysql restart</code>