Philippines Finance

Sep 30 2017

How to I move MySQL data files onto different partition? Ask Ubuntu #mysql #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:

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?

asked Jul 28 ’10 at 23:56

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.

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

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

answered Jul 29 ’10 at 17:26

Thank you, that has done the trick 🙂 Stephen RC Aug 1 ’10 at 0:04

This doesn t seem to work on Natty (11.4). I had to start MySQL using /usr/sbin/mysqld to have it read my changed data dir. Otherwise, it continued to read my old data dir. This could be an error on my side. three-cups May 7 ’11 at 21:47

The above failed to solve the problem for me on Ubuntu 12.04 (Precise). I found out that one needs to edit the file /etc/apparmor.d/tunables/alias to include a line alias /var/lib/mysql/ – /home/data/mysql/, With this in place, I did not need any changes in any of the other AppArmor files. It worked immediately after restarting AppArmor with /etc/init.d/apparmor restart and MySQL with restart mysql . mak Jun 4 ’13 at 12:36

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:

Then move all the database directories to their new home:

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:

Then tell mysql that the datadir has moved:

change the line:

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:

And start up MySQL again using the new datadir:

Hope this helps!

Written by admin

Leave a Reply

Your email address will not be published. Required fields are marked *