5

I am trying to move a single MySql database to another physical disk in my Ubuntu machine. I am using Ubuntu 17.04.

the external drive is mounted in /etc/fstab as follows (last line):

#zoneminder external drive
/path/to/new/location/zoneminder/images /var/cache/zoneminder/images none defaults,bind  0 2
/path/to/new/location/zoneminder/events /var/cache/zoneminder/events none defaults,bind 0
/dev/disk/by-uuid/a98ab607-15eb-4089-9197-7d26c3576cf9 /path/to/new/location ext4 x-gvfs-show 0 0

I have tried the following steps:

stop mysql service:

sudo service mysql stop

Change owner of the mounted drive (I need to update the fstab entry to incorporate this,but one problem at a time!):

chown mysql:mysql /path/to/new/location

move to mysql folder

cd /var/lib/mysql

copy database (called zm) to the new location

sudo rsync -av zm /path/to/new/location/zm

rename old database

sudo mv zm zm.old

create symbolic link to the database we rsynced

sudo ln -s /path/to/new/location/zm

move to the new database location

cd /path/to/new/location

doing an ll at this point shows me that mysql is the owner of /path/to/new/location/zm

set file permissions

sudo chmod 700 zm
cd zm
sudo chmod 660 *

add the new location to apparmor

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

add

/path/to/new/location/ r,
/path/to/new/location/** rwk,

(re)start services

sudo service apparmor restart
sudo service mysql start

Log into mysql:

mysql -u root -p

open the new database

use zm;

show tables

show tables;

and here I get an error:

ERROR 1018 (HY000): Can't read dir of './zm/' (errno: 13 - Permission denied)

I've also tried chmod 777 and chmod 777 * in /path/to/new/location without success

  • What kind of filesystem and mount is /path/to/new/location/ on and what is that directory's owner and permissions? – thomasrutter Dec 18 '17 at 23:55
  • I've updated the question with my fstab entry – SEarle1986 Dec 18 '17 at 23:58
  • Any apparmor denials in /var/log/kern.log? – Robie Basak Dec 21 '17 at 11:23
  • To @thomasrutter's question: who is the new directory's owner? I believe this to be the cause of your problem. – richbl Dec 21 '17 at 20:06
  • @richbl Changing /path/to/new/locations owner to mysql means I an now restart the service. However, I can't see the tables in the zm database now.....question updated – SEarle1986 Dec 21 '17 at 21:02
3

Fixed it!

The issue was the apparmor entry:

/path/to/new/location/ r,
/path/to/new/location/** rwk,

actually needed to be one directory up from what it was:

/path/to/new/ r,
/path/to/new/** rwk,

EDIT Ubuntu 18.04 required me to change the owner of the symlink to mysql:

(in /var/lib/mysql:)

chown -h mysql:mysql zm
0

You can try this variation:

1) first check in my.cnf [/etc/mysql/my.cnf] for database files destination.

2) let say that destination is in folder /var/lib/mysql/ and yours database name is "abccompany"

3) stop mysql database

4) move folder [var/lib/mysql/abccompany] to new destination (let say "/diskY")

5) make symbolic link:
     ln  -s  /diskY/abccompany   abccompany

6) start mysql database and everything should work just fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.