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