STATUS: BETA

Sometimes, it might be desirable to move the MySQL Database to a completely new disk which is provisioned in the OS. This can be particularly valuable, if the MySQL database was installed on the root OS disk, and it is desired to have a dedicated disk for this purpose mounted by LVM which can be easily resized.

This procedure describes how this may be achieved. 

Advanced Topic

Warning: this is an advanced topic. Always back up your data / take snapshots of your virtual machines before applying this procedure.

# Stop the slave databases which are replicating from the database machine we want to add the new disk to.
mysql -u root -p
mysql> stop slave;

# Scan the SCSI bus for new devices
echo "- - -" > /sys/class/scsi_host/host0/scan
echo "- - -" > /sys/class/scsi_host/host1/scan
echo "- - -" > /sys/class/scsi_host/host2/scan
echo 1 > /sys/block/sdb/device/rescan

# Check if the disk has been detected
fdisk -l /dev/sdb

# Create Physical and Logical Volumes 
pvcreate /dev/sdb
vgcreate vg1_mysql /dev/sdb
lvcreate -l100%FREE -n lv1_mysql vg1_mysql

# Create the file system
mkfs.xfs /dev/vg1_mysql/lv1_mysql

# Show the UUID of the volumes
blkid

# Create an fstab entry for the mount
echo "$(blkid /dev/mapper/vg1_mysql-lv1_mysql | cut -d' ' -f2 | tr -d '"') /var/lib/mysql  xfs	defaults      0       0" >> /etc/fstab

# Stop the mysql service
service mysqld stop

# Make a temp directory for mounting the new drive, and move the files to mysql2
cd /var/lib
mkdir mysql2
mount /dev/vg1_mysql/lv1_mysql mysql2/
cd mysql2/
mv ../mysql/* .
cd ..

# Possible se-linux warning here
mount -av

# fix mysql se-linux and ownership
restorecon /var/lib/mysql
chown mysql:mysql mysql

# Check se-linux warning is gone
umount mysql/
mount -av

# When gone, unmount mysql2 
umount mysql2/

# Remove temp directory
rmdir mysql2

# Remove lost and found directory
rmdir /var/lib/mysql/lost+found

# start service
service mysqld start


# Start slaves
mysql> start slave;
mysql> show slave status\G
  • No labels