Ubuntu – MyISAM problem after moving datadir of a MySQL database

filesystemMySQLpermissions

I recently moved my MySQL database data directory from the default location /var/lib/mysql onto a new harddrive located in /media/..UID../mysql, following the steps you can find in several posts.

  1. stop the MySQL
  2. Copy the old folder to the new location
  3. Change the datadir in the [mysqld] of /etc/mysql/my.cnf
  4. Change the two entries in the AppArmor configuration
  5. Adjust the user rights from the data dir
  6. Restart AppArmor
  7. Restart MySql

After those steps I tried to create a new database and it worked, it appears also in the new folder. Then I created a new InnoDB table and it also worked and it places the *frm file into the /mysql/new folder. But then I wanted to create a MyISAM table into this database, I received the error

#1 - Can't create/write to file '/media/..UID../mysql/new/tableName.MYI' (Errcode: 13)

I tried already to set all permission to 777, as I thought it is a permission problem (but as InnoDB can write into that folder, I cannot see why MyISAM cannot!?). For me it looks like that the MyISAM engine needs somewhere else special permission in order to write it's files, but I do not know where.

First I thought that it is a mounting problem of that new harddrive, but then again – the InnoDB can create database folders and also create files inside these folders.

So, any help is appreciated, if someone had a similar issue after moving a MyISAM table and could give some hints where to look or what else to try.

Thanks a lot already in advance!

Best Answer

I had a similar problem.

Here's what worked on Ubuntu 12.04

To move datafiles from /var/lib/mysql to /newdir_path/newdir

sudo mysql stop
sudo rsync -av /var/lib/mysql /newdir_path/newdir ## like copy, but keeps rights
sudo -H gedit /etc/mysql/my.cnf

change datadir to /newdir_path/newdir

sudo -H gedit /etc/apparmor.d/usr.sbin.mysqld

instead of:

/var/lib/mysql/ r,
/var/lib/mysql/** /rwk

enter:

/newdir_path/newdir/ r,
/newdir_path/newdir/** rwk,

Now run:

sudo /etc/init.d/apparmor restart 
sudo chown mysql:mysql <b>/newdir_path
sudo start mysql
Related Question