MySQL moving ibdata & ib_logfile

Question

I'm trying to move ibdata & ib_logfile on ssd drive. I tried this way, but it don't work:

service mysql stop
cd /var/lib/
cp -ra mysql mysql_backup
cp -a mysql/ibdata1 mysql/ib_logfile* /ssd_drive/mysql

my.cnf looks like this (relevant parts):

innodb_log_group_home_dir=/ssd_drive/mysql
innodb_data_home_dir=/ssd_drive/mysql

After all changes I get following errors:

InnoDB: Unable to lock /ssd_drive/mysql/ibdata1, error: 13
InnoDB: Check that you do not already have another mysqld process

Do I need to remove some lock files, or there is something else that I forgot...

Also I setup mysql apparmor so it can rwk on this directory, and rebooted afterward:

/usr/sbin/mysqld {
.................
 /ssd_drive/mysql/* rwk,
.................
}

Update: I forgot putting k option, so it can lock files...

Solution 1

Few things to check:

• make sure no mysql porcess is running ps faux|grep mysql
• check if the /ssd_drive/mysql ownership is correct chown mysql:mysql -R /ssd_drive/mysql

Does everything work fine when you start mysql from the original location?

edit: some googling gave me this: http://norman.hooper.name/blog/post/19/moving-mysql-in-ubuntu/ did you adjust also the socket part for the apparmor?

Solution 2

Note that moving ibdata and ib_logfiles to SSD is the best thing you can do from a performance point of view since these are random I/O files which improve on SSD.

Comments

[XoR]

Well no mysql process are running during that time, and I used cp -a, that use same privileges as origivan files. I checked, mysql have access to that directory. And all is fine when I start from original directory.

[pQd]

@XoR what file system do you use on ssd?

[XoR]

ext4fs, with 4K blocks

[pQd]

@XoR did you check link i added?

[XoR]

Yes, now I see, I forgot adding k option, for enabling of locks