[tlug] Empty databases after moving MySQL data files

[Dave M G <dave@example.com>]

TLUG,

In order to have consistent backups, I wanted to move all my MySQL data
from it's default directory to my home directory (which I keep on a
separate partition and backup regularly). I looked on the web and found
various instructions.

I did this on two computers, my home desktop and my laptop. On my home
computer, after a couple of stumbles, I got it to work.

However on my laptop, despite having followed the same instructions
twice, it's not working. If I look at my databases in phpmyadmin (my
preferred way of handling MySQL), they're empty. However, I'm sure the
data is there because if I reverse what I did and set the data files
back to the default location, the databases are no longer empty.

Just for further clarification, what I mean is that when I go into
phpmyadmin, I can see all the databases listed on the left side of the
screen. All my users and privileges are also set if I look under the
"privileges" tab. However, if I click on any one database to view its
contents, it says that it has no tables.

Further, there's another snag. After my latest attempt to follow the
instructions and move the data to the home directory, when I try to stop
the MySQL server, it won't let me:

$ sudo /etc/init.d/mysql stop
[sudo] password for dave:
 * Stopping MySQL database server mysqld   [fail]

Hmmm... it seems dangerous to be moving the files if I can't stop the
server.

Okay, so, here's how I tried to move the data files. Perhaps someone can
tell me what I might have screwed up:

1. Stop MySQL server

sudo /etc/init.d/mysql stop

2. Create new directory in home folder

mkdir /home/dave/Dropbox/MySQL

3. Move all MySQL data files from /var/lib/mysql to new directory. Move
everything that is a directory. DO NOT move anything that is a file.

4. Make sure new directory and contents are owned by MySQL

sudo chown -R mysql:mysql /home/dave/Dropbox/MySQL
sudo chown -R mysql:mysql /home/dave/Dropbox/MySQL/*

5. Edit the my.cnf file

sudo gedit /etc/mysql/my.cnf

5a. Replace:

datadir        = /var/lib/mysql

With:

datadir        = /home/dave/Dropbox/MySQL

6. Edit the apparmor settings file

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

6a. Replace:

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

With:

/home/dave/Dropbox/MySQL/ r,
/home/dave/Dropbox/MySQL/** rwk,

7. Restart apparmor

sudo /etc/init.d/apparmor restart

8. Restart MySQL

sudo /etc/init.d/mysql start


That's basically it. Worked okay on my desktop. Not sure what else to do
to diagnose and fix the problem on my laptop.

Any ideas?

-- 
Dave M G