Wednesday, May 6, 2009

Manage MySQL datadir folder

Mysql no-doubtly is the most popular database server among web user for it available on most hosting and SMEs. It's distribution also available on most linux distro, including the popular Ubuntu.

Mysql data file is the base of mysql database storage. Relocating folder usually for harddrive partition storage management issue. Managing MySQL data file is kinda tricky, because since Ubuntu 8.04, MySQL data file is protected by apparmor by default. Meaning, we cant simply move around files related to MySQL, as all location need to be registered.

Basic step is to edit mysql config file, in /etc/mysql/my.cnf.
Make sure the service is stopped.

$ sudo /etc/init.d/mysql stop
$ sudo vi /etc/mysql/my.cnf

Look for datadir directive, change it to your new location. Datadir location has to be actual location, not a symbolic link. Save and quit the editor. Dont forget to move old database location into the new folder, most importantly "mysql", as the folder contains user authentication info. Otherwise you'll find unable to log in to the database.

Apparmor Part

Apparmor is a security software for Linux. Apparmor protects unauthorised changes to system critical files. To open apparmor protection for MySQL, open a shell terminal, and type :

$ sudo vi /etc/apparmor.d/usr.sbin.mysqld

Add the new location:

/var/www/mysql_datadir/ r,

/var/www/mysql_datadir/** rwk,

Restart apparmor by :

$ sudo /etc/init.d/apparmor restart

If everything went right, mysql start status should showing [ok], otherwise, consulting syslog somehow could help. Type this before restarting mysql service

$ tail -f /var/log/syslog

In other terminal,

$ sudo /etc/init.d/mysql start

Now your database is located in folder you prefer to.