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

 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

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

/etc/init.d/mysql stop

/home/mysql directory

Create a /home/mysql directory and give clearance to mysql

mkdir /home/mysql
chown -R mysql /home/mysql
chgrp -R mysql /home/mysql

Copy data and preserve permissions, etc.

cp -Rp /var/lib/mysql/* /home/mysql/

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

vi /etc/mysql/my.cnf 

Find the line

datadir         = /var/lib/mysql

And change it to

datadir         = /home/mysql

Re-enable MySQL and test

Okay, all went fine ? let's start mysql back

/etc/init.d/mysql start

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.

ls -la /home/mysql/

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

 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

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 :

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

This comes from the Apparmor configuration

Edit /etc/apparmor.d/usr.sbin.mysqld and add the following two lines:

/home/mysql/ r,
/home/mysql/** rwk,

somewhere in the middle of the file. After the /var/lib/mysql lines would be fine.

Restart AppArmor

/etc/init.d/apparmor restart

Restart MySQLd

/etc/init.d/mysql restart
ressources/mysql/changing_directory.txt · Last modified: 2013/06/16 22:51 (external edit)
 
 
Creative Commons License Powered by PHP Valid XHTML 1.0 April