By Jeremy Smyth on Apr 05, 2013
Problem: You've a large table (or two) in a database on a partition that's running out of space, and you want to see if you can move that table to another drive.
Solution: Well, several actually. No silver bullet, but several options, some with conditions and some that require preparation. Let's look at some background information first.
How MySQL Stores Data
OK, that's somewhat of an ambitious heading for an incidental paragraph or two, so to tone it back a bit, I'll summarise briefly.
- The data directory is where MySQL stores databases, and it's set by the datadir server option. Each database is stored in a subdirectory of the data directory.
- Storage engines are what MySQL uses to do the grunt work of storing and retrieving data. InnoDB is the default and is quite full-featured and robust, but some systems still use MyISAM, which is fast but not as well-featured or robust. Other storage engines are available.
- MyISAM and InnoDB store data in the data directory.
- MyISAM stores data in .MYD files and index information in .MYI files, both in the database subdirectories mentioned above.
- InnoDB stores data and index information in .ibd files in the database subdirectories, when you have the innodb_file_per_table option enabled. This is the default in MySQL 5.6, but must be enabled in 5.5; when disabled, InnoDB stores all data and indexes in a shared tablespace in the root of the data directory.
Now you know where the data lives, it's time to look at how to move it somewhere else.
Moving the Whole Data Directory
As the datadir option controls where MySQL stores its data, one option we have is to move the contents of the current data directory to a new partition, and then change the datadir option to point to that location.
For example, on my machine, the datadir option points to the /var/lib/mysql directory. As expected, it contains a directory for each of my databases, and it also contains the InnoDB system tablespace files. To increase the space available, I could add a new drive and point the datadir there.
Consider the following scenario:
- Add a new hard drive, partitioned using a suitable file system such as ext4. That file system is visible to my OS as /dev/sdf1
- Back everything up to provide a rollback route.
- Ensure that the datadir option points to the /var/lib/mysql directory (or note the directory otherwise. we'll assume /var/lib/mysql here)
- Mount the partition in /data by doing the following:
# mount /dev/sdf1 /data
- Give the mysql user ownership of the new filesystem:
# chown mysql:mysql /data
- Stop MySQL
- Copy the contents of /var/lib/mysql to /data
- Set the datadir option to point to /data
- Restart MySQL
# cp -a /var/lib/mysql/* /data
Beware of the various Mandatory Access Control subsystems that might affect you. If you've got AppArmor installed, you'll also need to change the profile for mysqld so it can access files in the new directory. If you've got SELinux installed, you'll need to add a context mapping for MySQL.
In fact, you don't even have to point MySQL to the new partition. If the mountain won't go to Mohammed... let's undo the last couple of steps:
- Assume you haven't set the datadir option to point to /data, and that it still points to /var/lib/mysql. Also assume MySQL is still stopped. Now the data directory contents are at the new location, but on its next boot MySQL still looks in /var/lib/mysql.
- Unmount the new partition /dev/sdf1 (or you can use its mountpoint, which is easier to type):
# umount /data
- Mount /dev/sdf1 to /var/lib/mysql:
# mount /dev/sdf1 /var/lib/mysql
- Restart MySQL
But I Only Want to Move One Database
Moving a whole database to another location is achieved by using symbolic links. Simple summary (read the link for more details):
- Make the new partition as in the example above
- Rather than move the whole data directory, this time just move a single database directory; this only works with innodb_file_per_table enabled, because otherwise the InnoDB data is in the shared tablespace and can't be moved
- At this point you can either mount the new partition into the database directory, or use symlinks as described in the link above.
But I Only Want to Move One Table
Ah, now we're getting interesting. The technique for doing this depends on the version of MySQL you're running—I'll talk about 5.5 and 5.6—and what storage engine the table uses.
Firstly, in MySQL 5.5 you can use symbolic links only for tables that use the MyISAM storage engine, and only on certain operating systems such as Linux and Oracle Solaris. The technique is similar to that described above, although you can also use the DATA DIRECTORY and INDEX DIRECTORY options to CREATE TABLE when creating the tables if you want to set up the links from within MySQL rather than at the Linux command prompt. You should not use symbolic links to InnoDB tables, because InnoDB stores some information about each table in the shared tablespace, even when you use the innodb_file_per_table option.
In MySQL 5.6 you can also move InnoDB tables to a different location by using the DATA DIRECTORY option to CREATE TABLE. When you do so, the tablename.ibd file is moved to a subdirectory of that location named for the table's database, and a corresponding tablename.isl file created in the database's directory that acts as a link to the table's .ibd file. The .isl file is not an actual symbolic link, but is treated as such by MySQL.
Plenty of Space
All told, you have many options available to you regardless of which storage engine or version you use, although it won't surprise you to know that MySQL 5.6 gives more options than previous versions. Don't forget that modern UNIX-like operating systems often have mandatory access control systems such as AppArmor, SELinux, or Extended Policy, so be sure to do your homework before moving files around.