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.
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.
datadirserver option. Each database is stored in a subdirectory of the data directory.
.MYDfiles and index information in
.MYIfiles, both in the database subdirectories mentioned above.
.ibdfiles in the database subdirectories, when you have the
innodb_file_per_tableoption 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.
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:
datadiroption points to the
/var/lib/mysqldirectory (or note the directory otherwise. we'll assume
/databy doing the following:
# mount /dev/sdf1 /data
# chown mysql:mysql /data
# cp -a /var/lib/mysql/* /data
datadiroption to point to
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:
datadiroption 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
/dev/sdf1(or you can use its mountpoint, which is easier to type):
# umount /data
# mount /dev/sdf1 /var/lib/mysql
/etc/fstabto automatically mount
/var/lib/mysqlon reboot, so persisting your new configuration
Moving a whole database to another location is achieved by using symbolic links. Simple summary (read the link for more details):
innodb_file_per_tableenabled, because otherwise the InnoDB data is in the shared tablespace and can't be moved
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
MySQL 5.6 also allows you to copy or move InnoDB tables to another server by performing a file copy, by using the transportable tablespaces feature.
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.
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.