Change Mysql Data Directory in Debian 7?

Question

I have a separate partition that holds both my www and mysql folders.

I have that partition set to automount at boot, and the apache2 starts fine, no errors.

However, when I just recently removed all traces of mysql-server-5.5, rebooted then restarted it, it would work normally.

But the second I make changes to my.cnf to point to /media/server/mysql, and try to start mysql then it error's out.

Here is the list of steps I have followed so far. Be advised, that Debian does not have apparmor, as far as I know, so I skipped that step.

Stop MySQL using the following command:

sudo /etc/init.d/mysql stop

Copy the existing data directory (default located in /var/lib/mysql) using the following command:

sudo cp -R -p /var/lib/mysql /newpath

edit the MySQL configuration file with the following command:

gedit /etc/mysql/my.cnf

Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.

In the terminal, enter the command:

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

Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path.
Save and close the file.

Restart the AppArmor profiles with the command:

sudo /etc/init.d/apparmor reload

Restart MySQL with the command:

sudo /etc/init.d/mysql restart

Now login to MySQL, and you can access the same databases you had before.

from How to change MySQL data directory?

Although I also looked at the link here https://askubuntu.com/questions/137424/moving-mysql-datadir

My guess is that this is a permissions issue, but I could be wrong.

root@debian:~# chown -R mysql:mysql /media/server/mysql
root@debian:~# sudo /etc/init.d/mysql restart
[ ok ] Stopping MySQL database server: mysqld.
[FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

I'll admit I am still a newb at linux, so I may be doing things, that other's recommend, without really understanding what it is really doing.

It gives no details about why it is failing.

One other option, I may consider doing, is re-enable it to use the default data dir, and then just copy mysql files back from the partition.

But that denies the whole point of having a dedicated partition for webdev.

I appreciate any comments or efforts, thank you.

Answer 1

Instead of copying your original datadir (/var/lib/mysql), you should move it. It keeps better track on all metadata.

Insted of:

sudo cp -R -p /var/lib/mysql /newpath

use:

sudo mv /var/lib/mysql /newpath

or better save a copy of your original datadir and then move it, like this:

sudo cp -R -p /var/lib/mysql /var/lib/mysql.bak
sudo mv /var/lib/mysql /newpath

Then start mysql service, it should all go smoothly :)

Cheers!