Moving MySQL InnoDB database to separate drive

Question

In my MySQL installation I have one InnoDB database that I know will grow very large so I have decided to move it to its own disk. I was hoping to do this by moving the files to the other disk and then create a symlink but I run into errors!

This is what I have done:

1) In my.cnf I have set

[mysqld] innodb_file_per_table

(This works, I have one .ibd per .frm in the database folder.)

2)I have checked if symlinks are ok with SHOW VARIABLES LIKE "have_symlink";

(I know that the documentation says:

Symlinks are fully supported only for MyISAM tables. For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links.

But I need foreign keys...)

3) I moved the database folder and created a symlink.

4) Restarted mysql and tried:

 mysql> USE db_name
 Database changed
 mysql> SHOW TABLES;
 ERROR 1018 (HY000): Can't read dir of './db_name/' (errno: 13)
 mysql> exit
 user@comp# perror 13
 OS error code  13:  Permission denied

symlink is (as expected) lrwxrwxrwx mysql mysql db_name -> /path-to/db_name/

database folder permissions are drwx------ mysql mysql

all file permissions are -rw-rw---- mysql mysql

I am using Ubuntu 10.04 Server with MySQL 5.1.41 (default from apt).

Have any of you done this successfully?

Answer 1

Norling Jr. saved my day with the AppArmor tip, but since I had some trouble configuring it, I'm writing a more detailed answer. I'm using Ubuntu 12.04.

Start becoming root to save the need to type all that sudos:

sudo su -

Following MySQL docs, you first move your already created database dir to another path:

mv /var/lib/mysql/yourdatabase /new/path/

Here was my first trap. Check if the mysql user has access to this new path:

sudo -u mysql ls /new/path/yourdatabase

If you got access denied, you should probably give execute permission to every parent dir:

chmod a+x  /new  /new/path/

Test to access the file again. If it still doesn't work, try asking a question in Stack Overflow:-)

Link the new dir location and give it the correct permissions:

 ln -s /new/path/yourdatabase /var/lib/mysql/
 chown mysql:mysql /var/lib/mysql/yourdatabase

Let's edit AppArmor local configuration file. You shoudn't change /etc/apparmor.d/usr.sbin.mysqld file. Edit the local conf so you won't loose it after system updates:

emacs /etc/apparmor.d/local/usr.sbin.mysqld

add Norling Jr. configurations:

/new/path/yourdatabase/ r,
/new/path/yourdatabase/** rwk,

Don't miss the last comma. Save the file and reload AppArmor configuration:

apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

This will not just reload AppArmor MySql configuration, but also test it if there isn't any syntax error (a very important thing). If you don't run the parser, the new conf won't be applied.

Finally just open mysql client and type SHOW DATABASES. If your database appears, everything is probably fine. Type 'USE yourdatabase' for another check.

A more robust test would also reload the mysql service: 'service mysql restart' and try to access your database.

Now I'll remember next time I need to do it. Google and SO together are the best notebook in the world :-)

Answer 2

It should be possible to use local mounts (bindings) with appropriate permissions and mount oprions (including the selinux or apparmor contexts):

/dev/sdc on /var/lib/mysql/my-db/
/dev/sdd on /var/lib/mysql/her-db/
fs.example.com:/path/to/wherever on /var/lib/mysql/my-other-db/

Though I haven't tested this solution so use at your own risk.

Answer 3

Turns out this works but my old enemy appArmor blocked MySQL from reading the moved directory.

sudo nano /etc/apparmor.d/usr.sbin.mysqld

add lines:

/new-db-path/ r,
/new-db-path/** rwk,

Thanks for helping out!

Answer 4

I'm not sure your solution is the best idea. See my post here:

http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/

There is also this other thread here:

Innodb; multiple data directories