Ubuntu: MyISAM problem after moving datadir of a MySQL database



Question:

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!


Solution:1

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »