Moving the MySQL database directory to different drive on Ubuntu
I have a server where there are two RAID1 disks where the second disk is mounted on /mnt/data, having the MySQL database there would be great as it then would have a whole disk all by itself without having to share with other processes that read and write to the same disk. I/O is often the bottleneck and now we make sure it gets as good as it can be with the hardware we have.
First we will do the noatime trick to avoid things slowing down due to timestamp updates:
nano /etc/fstab
Make sure the noatime is there on the line of the disk in question:
/dev/sdb1 /mnt/data ext4 defaults,noatime 1 1
Remount:
mount -a
That was that, now let’s move the databases.
First we need to make sure apparmor allows the new location:
nano /etc/apparmor.d/usr.sbin.mysqld
And add the following lines:
/mnt/data/mysql/ r,
/mnt/data/mysql/** rwk,
Restart:
/etc/init.d/apparmor restart
Stop MySQL:
/etc/init.d/mysql stop
Copy the database directory (note the pr flag, very important to preserve ownership):
cp -pr /var/lib/mysql /mnt/data/
We open the MySQL config file:
nano /etc/mysql/my.cnf
And change the following line to point to our new directory:
datadir = /mnt/data/mysql
Remove these pesky logfiles that prevents MySQL from starting up after changing stuff:
rm /mnt/data/mysql/ib_logfile*
Start MySQL:
/etc/init.d/mysql start