Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

up vote 19 down vote accepted

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 none specific 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
share|improve this answer
    
Thank you, that has done the trick :) –  Valorin 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
    
I think certain permissions need to be set on the new folder that is to be used. Anyone know what they are? I know the mysql installation creates a mysql user... and that by default, I can't access the contents of the /var/lib/mysql folder. –  NullVoxPopuli Jun 24 '13 at 15:51

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/ /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!

share|improve this answer

This really isn't Ubuntu specific. Nevertheless, here is something that might help: http://developer.spikesource.com/wiki/index.php/How_to_change_the_mysql_database_location

share|improve this answer
    
Instructions didn't really help as the part I couldn't get working was AppArmour and your post only explained it in passing. Also, is this site for questions that are Ubuntu specific? How do new users looking for help know what is Ubuntu-specific or not, and where they should ask them if they are generic? –  Valorin Aug 1 '10 at 0:05
    
Website unavailable. –  Hengjie May 20 at 5:22

For those who like me work with VirtualBox and need to move the MySQL datadir to a shared folder on the host system, follow the simple tutorial at http://vacilando.org/en/article/moving-mysql-data-files-virtualbox-shared-folder

share|improve this answer

This won't work just like that.

user mysql has to have the right to write to the new dir:

sudo chown -R mysql:mysql /newdatadir
sudo chmod -R 754 /newdatadir
sudo chmod 754 /newdatadir/..
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.