Mailing List Archive

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[tlug] Empty databases after moving MySQL data files


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

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


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,


/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

Home | Main Index | Thread Index

Home Page Mailing List Linux and Japan TLUG Members Links