Ubuntu – How to move MySQL data files onto different partition

MySQL

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?

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

Related Solutions

Ubuntu – How to move the MySQL data directory

Forgot about app armour.

For anyone that is interested I did the following to move the folder.

Stop the mysql server:

stop mysql

Create the new directory:

mkdir /array2/mysql

Copy over ONLY the database folders:

cp -R /var/lib/mysql /array2/mysql
cp -R /var/lib/mysql/users /array2/mysql

Backup the my.cnf file:

cp /etc/mysql/my.cnf /root/my.cnf.backup

Edit the my.cnf file:

nano /etc/mysql/my.cnf

Change all mentions of the old datadir and socket to your new location

Mine became:

datadir=/array2/mysql

socket=/array2/mysql/mysql.sock

Update the directory permissions:

chown -R mysql:mysql /array2/mysql

Rename the old directory:

mv /var/lib/mysql /var/lib/mysql-old

Create a symlink, just in case:

ln -s /array2/mysql /var/lib/mysql

Let AppArmor know about the new datadir:

echo "alias /var/lib/mysql/ -> /your/new/datadir/," >> /etc/apparmor.d/tunables/alias

Reload the apparmor profiles

sudo /etc/init.d/apparmor reload

Then start mysql:

start mysql

Best Answer

Related Solutions

Ubuntu – How to move the MySQL data directory

Related Question