Migrate MySQL to a New Volume via SSH in Ubuntu

As your server grows, it may become necessary to create a new block storage device and migrate your MySQL database over to the new directory. Steps for creating a new storage device and mounting it to the file system can be found in an earlier post, Display, Format, and Mount Hard Drives in Ubuntu.

This article will assume that you’ve already mounted the new Hard Drive/block storage device on your machine under the directory: /vol, as shown in the previous article. It is also assuming this is being done using SSH on an Ubuntu machine.

Always, always, always backup your MySQL directories. Don’t think twice, just do it. Once that is done, and you are sure it’s backed up onto a secure drive independent of the system and the new volume you’ve created, stop MySQL:

sudo service mysql stop

Now, make the new MySQL directories that the MySQL files will migrate to:

sudo mkdir /vol/etc /vol/lib /vol/log
sudo mkdir /vol/etc/mysql

After this, migrate the necessary files:

sudo mv /etc/mysql     /vol/etc/mysql
sudo mv /var/lib/mysql /vol/lib/
sudo mv /var/log/mysql /vol/log/

Create symbolic directories in the place of the old MySQL directories:

sudo mkdir /etc/mysql
sudo mkdir /var/lib/mysql
sudo mkdir /var/log/mysql

Tell MySQL where the old files have been moved to, which is done with a bind/mount command:

echo "/vol/etc/mysql /etc/mysql     none bind" | sudo tee -a /etc/fstab
sudo mount /etc/mysql

echo "/vol/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/lib/mysql

echo "/vol/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/log/mysql

Cross your fingers, and start MySQL up:

sudo service mysql start

Leave a Reply

4 + seventeen =