Distributing Your MySQL Database Across Disk Partitions

Published October 29th, 2008

Problem Statement:

By default, MySQL writes all the database files (tablespaces, logs, etc.) in /var/lib/mysql under LAMP platform. If your /var partition is running out of disk space and you are not using fancy filesystems such as logical disk volumes that can be scaled up/down, you are in potentially big trouble!

You need disk space to manage the growing size of your databases. Here are some scenarios that we faced that might help.

Solution A: Relocating entire /var/lib/mysql to a different disk partition

If you have another disk partition available with more space and would like to relocate the entire MySQL data storage to that partition, follow these steps below:

• Stop MySQL database server
• Move /var/lib/mysql to your desired partition (e.g. mv /var/lib/mysql /big/partition)
• Make sure that files and directories in /big/partition/mysql is accessible (read+write) by the mysql user
• Create a symbolic link between the new and the old default location
• Or, edit the /etc/my.cnf file to point data_dir to new location under [mysqld] section
• Restart MySQL server
• Check if you can read, write data for all of your databases; if not, recheck file permission on new partition where you moved the mysql files from /var/lib/mysql

Solution B: Relocating a single database to a different disk partition

If you cannot move all of your databases from /var/lib/mysql to another partition, you can partially move one or more databases as follows:

1. Stop MySQL database server
2. Move the /var/lib/mysql/<database> directory to new partition
3. Make sure the <database> dir in the new partition has read, write and execute privilege for the mysql user and group
4. Create a symbolic link between new directory on the new partition to /var/lib/mysql/<database>
5. Restart MySQL database server
6. Check if you can access (read, write) the database you relocated on a new partition
7. If you have problem reading or writing to the relocated database, check dir/file permissions of the new location

Solution C: Relocating a large MYISAM tablespace to a different disk partition

If you have a large MYISAM table space that you want to relocate to a different disk partition, simply do the following:

1. Stop MySQL database server
2. Move the /var/lib/mysql/<database>/<table>.<ext> to new partition. Here the <ext> can be either the MYISAM data file (.MYD) or the MYISAM index file (.MYI).
3. Make sure the <table>.<ext> file in the new partition has read, write and execute privilege for the mysql user and group
4. Create a symbloic link between <table>.<ext> on the new partition to /var/lib/mysql/<database>/<table>.<ext>
5. Restart MySQL database server
6. Check if you can access (read, write) the table you relocated on a new partition
7. If you have problem reading or writing to the relocated table, check dir/file permissions of the new location

Category: Tablespace | Tags: