Remounting MySQL under a new mount point

Question

/var/lib/mysql is mounted on partition / which is now full. Is there a way to mount this under /home/mysql for more disk space?

Answer 1

1. Stop MySQL
2. Move MySQL data from /var/lib/mysql to /home/mysql
3. Point MySQL to the new data directory
4. Start MySQL

It could be implemented like this (WARNING: not tested)

/etc/init.d/mysql stop
mv /var/lib/mysql /home/.
ln -s /home/mysql /var/lib/mysql
/etc/init.d/mysql start

Always have a backup before you start moving the data directory.

Answer 2

Ideally, you'd give it it's own mountpoint on it's own partition, which can be mounted to /var/lib/mysql.

Failing that, you do have the option of using a loopback filesystem, though it definitely does introduce some I/O delays that MySQL may notice. Only you can know if increasing I/O latencies a bit will harm your expected performance.

The rough procedure....

1. Use dd to create a loopback file somewhere.
   • dd if=/dev/zero of=/home/mysql/datavol bs=1M count=4096
2. Attach the file to a loopback device
   • losetup /dev/loop0 /home/mysql/datavol
3. Format the device as you will.
   • mkfs.ext3 /dev/loop0
4. Mount it somewhere
   • mount /dev/loop0 /mnt
5. Copy your data
   • cp -a /var/lib/mysql/ /mnt/
   • Other methods may be used, this is just one.
6. Clear /var/lib/mysql
7. Remount to /var/lib/mysql
   • umount /mnt ; mount /dev/loop0 /var/lib/mysql
8. Ensure that directory is owned by the right user
   • chown mysql:mysql /var/lib/mysql

At that point, you should have a larger /var/lib/mysql actually hosted from your larger partition.