How do I move my database files to a Block Storage Volume?
I just purchased a Block Storage Volume and would like to move my database files there to make use of the extra space.
Before moving forward with a database migration from Linode plan storage to Block Storage, keep in mind that you'll be moving your database from SSD to HDD disks. If your application requires high IOPS, you might see performance degradation without additional preparation.
Preparing your Block Storage Volume
Purchase, attach and create a new filesystem on a Block Storage Volume according to the following guide (Note: you can omit the final step for adding your the volume to your
/etc/fstab file, (Step 7 at the time of this writing).
The guide uses the example of mounting your volume at
/etc/BlockStorage1. I will continue assuming that you have done the same. If you have mounted your Block Storage Volume somewhere else, adjust my instructions accordingly.
Before you can use a Block Storage Volume for your database, you will need to determine where your files are stored and then shutdown your database to move the files out of the directory where they are kept to the Block Storage Volume so you can mount the Block Storage Volume on that spot instead.
Finding where your data is stored
As you don't mention which database software you are using I will provide instructions for the most common ones.
MySQL / MariaDB
You will want to login to your database from your Linode's command line:
$ mysql -u root -p
Give it your database's root password when it asks. You can then ask the database where it keeps its files.
mysql> select @@datadir;
You should see something like:
+-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec)
/var/lib/mysql is where your databse keeps its files in this example. If you see something else, make sure you adjust the following commands accordingly.
exit to get out of the database.
Shutdown your database:
sudo systemctl stop mysql
In this case you are going to copy the files form
sudo rsync -av /var/lib/mysql /mnt/BlockStorage1
We are going to move this directory out of the way for now.
sudo mv /var/lib/mysql /var/lib/mysql.old_before_move_to_blockstorage
Lets create a new mount point for our volume:
sudo mkdir -p /var/lib/mysql
Now complete the step you skipped from the previous guide using the path you just created as the mount point. You can then reboot your Linode and you should be good to go.
If you are using PostgreSQL
You can use the following article that explains how to relocate a PostgreSQL data directory:
You will want to adapt the instructions from the MySQL example to use data directory for PostgreSQL instead.