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.
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 stop mysql
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