Moving the MySQL data directory to a new location

In this article I will describe the process of moving the MySQL data directory to a separate location, namely on a level 10 raid with 4 disks, this will increase performance.

I’ll take an HP DL360p Gen8 server with a Smart Array P420i raid controller, and I’ll also take 6 Samsung 883 DCT Enterprise 240GB 2.5″ SATA III V-NAND MLC (MZ-7LH240NE).
From two disks we will create a mirrored raid 1 and install the Ubuntu 18.04 operating system on it, from the remaining 4 SSD we will create a level 10 raid that will be used only for the database.

Switch to root user:

sudo su

Stop the MySQL server:

systemctl stop mysql
systemctl status mysql

Let’s make a copy of the database files:

mkdir /var/lib/mysql_ixnfo.com
cp -r /var/lib/mysql/* /var/lib/mysql_ixnfo.com/

We delete files:

rm /var/lib/mysql/* -Rf

Let’s see the list of available disk systems:

fdisk -l
fdisk -l | grep '/dev/sd'

I got it displayed:

Disk /dev/sda: 223.6 GiB, 240021504000 bytes, 468792000 sectors
/dev/sda1   2048      4095      2048     1M BIOS boot
/dev/sda2   4096 468789247 468785152 223.5G Linux filesystem
Disk /dev/sdb: 447.1 GiB, 480047095808 bytes, 937591984 sectors

Let’s create a section on the second raid:

fdisk /dev/sdb
n
p
1
Enter
Enter
w
fdisk -l | grep '/dev/sd'
mkfs.ext4 /dev/sdb1
df -h

Mount the newly created partition in the database directory:

mount /dev/sdb1 /var/lib/mysql

Let’s see its UUID:

sudo blkid

I got it displayed:

/dev/sdb1: UUID="54bf0d45-c190-4185-968d-b440537122bd" TYPE="ext4" PARTUUID="d2090c9f-01"

To automatically mount when the operating system starts, open the /etc/fstab file in a text editor (in the nano editor, press Ctrl+X to exit and y/n to save or discard changes):

nano /etc/fstab

I added:

UUID=54bf0d45-c190-4185-968d-b440537122bd /var/lib/mysql ext4 defaults 0 0

From the previously made copy, copy the database files to the mounted raid 10:

cp -r /var/lib/mysql_ixnfo.com/* /var/lib/mysql/

Let’s set the correct owner and rights:

chown mysql:mysql /var/lib/mysql/ -R
chmod 755 /var/lib/mysql

Let’s check:

ls -l /var/lib/mysql/

We start the MySQL server:

systemctl start mysql
systemctl status mysql

Let’s restart the server to make sure that raid 10 is automatically mounted and the database server has started successfully:

reboot

After restarting, I noticed the default directory for lost files, I deleted it and restarted MySQL:

rm -d /var/lib/mysql/lost+found
systemctl restart mysql

If you wish, you can mount the raid to another directory and specify the path to it in my.cnf:

[mysqld]
datadir=/mnt/sdb1/mysql
socket=/mnt/sdb1/mysql/mysql.sock
log-bin=/mnt/sdb1/mysql/mysql-bin
[mysqld_safe]
datadir=/mnt/sdb1/mysql

You can see which directory is specified like this:

mysql -u root -p
select @@datadir;

Let’s copy the files using rsync:

rsync -av /var/lib/mysql /mnt/sdb1/mysql
mv /var/lib/mysql /var/lib/mysql.mybakup

Let’s specify a new directory for apparmor in the /etc/apparmor.d/tunables/alias file:

alias /var/lib/mysql/ -> /mnt/sdb1/mysql/,

And let’s restart it:

systemctl restart apparmor

See also my articles:
Smart Array P420i Firmware Update
Managing disk partitions in Ubuntu using fdisk

Leave a comment

Leave a Reply