Home / ubuntu / Questions / 224
Accepted
Stephen RC
Asked: 2010-07-29 15:56:24 +0800 CST

How to I move MySQL data files onto different partition?

  • 772

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?

mysql

5 Answers

  1. Best Answer

    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
    • 29

  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!

    • 5

  3. 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

    • 0

  4. 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

    • 0

  5. 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/..
    • -1