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
source: https://ixnfo.com/en/moving-the-mysql-data-directory-to-a-new-location.html
