How to I move MySQL data files onto different partition?

I have my hard drive partitioned with two partitions, so I can easily re-install Ubuntu and try out different versions without losing my home directory data. It is setup like this:

20GB  -> /     (root)
180GB -> /home 

I do a lot of development work, so I have my /var/www folder symlinking to /home/valorin/workspace.

But I want to do this with my MySQL data files as well, as I am getting annoyed that each time I reinstall my machine I need to do a full SQLdump and then restore all the DB's before I can do more work.

What is the best way to do this without breaking MySQL?

Solution 1:

Well, actually there is a potential Ubuntu specific answer to this question.

As mentioned by Gergoes link, this is basically about modifying /etc/mysql/my.cnf and set a new value for datadir = in the [mysqld] section. So far the unspecific part of the answer.

Assuming you are running a somewhat modern version of Ubuntu you might very well have AppArmor installed by default, with a profile for /usr/sbin/mysqld in enforced mode. That default profile will most likely not accept your new datadir.

Let us assume that your new datadir will be /home/data/mysql.

If you open the file /etc/apparmor.d/usr.sbin.mysqld you will among the rules find these two lines.

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

Assuming our example above, they will have to be replaced or (probably preferable) complemented by these two lines.

/home/data/mysql/ r,
/home/data/mysql/** rwk,

Before we can startup our MySQL server, with its new datadir, we will also have to explicitly reload our new apparmor profile.

$ sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

Solution 2:

Super user has a nice step by step instructions on how to solve this probelm

Here is an other set of instruction on doing the same thing http://www.ubuntugeek.com/how-to-change-the-mysql-data-default-directory.html

Here it is reposted. Go and up vote the original if you can on super user.

After some general confusion about permissions I realized that the problem wasn't that I didn't have my permissions and paths right but that AppArmor was preventing mysql from reading and writing to the new location.

This is my solution:

First stop MySQL so nothing weird happens while you're fiddling:

$ sudo stop mysql

Then move all the database directories to their new home:

$ sudo mv /var/lib/mysql/<all folders> /new-mysql-dir/

Don't move the files, they will be generated by mysql, just move the folders (which are the databases).

Then politely ask AppArmor to allow mysql to use the new folder:

$ sudo vim /etc/apparmor.d/usr.sbin.mysqld

add lines:

/new-mysql-dir/ r,
/new-mysql-dir/** rwk,

Then tell mysql that the datadir has moved:

$ sudo vim /etc/mysql/my.cnf 

change the line:

datadir=/var/lib/mysql

to:

datadir=/my-new-db-dir/

NOTE: Depending on your database setup you might need to change innodb-data-home-dir etc. as well.

Then restart AppArmor to read the new settings:

$ sudo /etc/init.d/apparmor restart

And start up MySQL again using the new datadir:

$ sudo start mysql

Hope this helps!