Debian - Move MySQL Database To Another Disk
Databases are constantly growing and it is likely to outgrow your
/ partition at some point, especially these days in the age of "the cloud" where you may be limited to a small
/ partition, but can pay to add bulk storage, or attach more disks. Alternatively, you may wish to move your database to a separate location because you want to configure some sort of snapshotting technique, such as with ZFS or LVM. This tutorial will shown you how to move your database on a Debian 7.x server to another location, and are assuming another drive.
To make the steps agnostic, I am going to use the following variables which may vary on your system/configuration.
Find the new disk with
Create a filesystem
sudo mkfs -t ext4 $DISK_LOCATION
Create a mount point
sudo mkdir $MOUNT_POINT
Mount the drive
sudo mount $DISK_LOCATION $MOUNT_POINT
/etc/fstab file to automatically mount on boot. Use
sudo blkid to get the UUID of your disk.
UUID="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" $MOUNT_POINT ext4 defaults 0 2
Stop the mysql service
sudo service mysql stop
Move the mysql data to the new location.
sudo mv /var/lib/mysql/ $MOUNT_POINT/.
Update the mysql configuration so that it knows where the data is stored.
SEARCH="datadir[[:space:]]\+= /var/lib/mysql" REPLACE="datadir = $MOUNT_POINT/mysql" FILEPATH="/etc/mysql/my.cnf" sudo sed -i "s;$SEARCH;$REPLACE;" $FILEPATH
You may also want to change the following variables as the logs can take up a lot of space.
relay-log = ... log_bin = ...
If you are running Ubuntu, you need to update your apparmour config at this point.
Start the mysql service.
sudo service mysql start
First published: 16th August 2018