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.

These steps can also be followed on Ubuntu systems, but will require some additional steps for configuring Apparmor. Refer here.

Vars

To make the steps agnostic, I am going to use the following variables which may vary on your system/configuration.

DISK_LOCATION="/dev/sdb"
MOUNT_POINT="/media/data"

Steps

Find the new disk with ls /dev/sd*

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

Update your /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

Be sure to check the $FILEPATH. For MariaDB on Debian 10 it was /etc/mysql/mariadb.conf.d/50-server.cnf

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