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 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  

Author

Programster

Stuart is a software developer with a passion for Linux and open source projects.

comments powered by Disqus
We are a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites. More info.