MySQL and MySQL Community information

  • April 5, 2013

Running Out of Physical Disk Space

Jeremy Smyth
Manager, MySQL Curriculum

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
    # cp -a /var/lib/mysql/* /data
  • Set the datadir option to point to /data
  • Restart MySQL

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
Assuming it all works, you should then modify the file /etc/fstab to automatically mount /dev/sdf1 to /var/lib/mysql on reboot, so persisting your new configuration

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.

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.

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.

Join the discussion

Comments ( 4 )
  • Magesh Kumar R Wednesday, December 11, 2013

    if i create a table with innodb storage engine in different location by using create table .... data directory 'path'. the new table will be created with ibd file in new location and table.isl in default data directory of mysql.

    In case if i want to move the table back to the default data dir how can i proceed with that. ALter table doesn't support data directory.

    I think we cannot move a table in a middle using data directory option.

    we should be planning in advance and it has to be created with data directory.

  • Jeremy Smyth Wednesday, December 11, 2013

    The .isl file in the data directory is little more than a pointer to the location of the .ibd file. The InnoDB shared tablespace and the .frm file contain the table metadata, but not the location of the data file.

    To move the data file back to the data directory you could do the following:

    - Stop MySQL

    - Remove the .isl file from the data directory

    - Move the .ibd file back to the data directory

    - Start MySQL

    This is not a documented procedure, so it is not guaranteed to work in all cases and you should make sure you've taken backups of the appropriate files before trying it. I've tested it on a couple of tables and it works in my simple tests.

  • guest Thursday, August 27, 2015

    the above article is not in the mildest way understandable and datadir sub-directory does not exist, can you provide a better assistance on how I can configure MySQL to store datafiles in another directory

  • guest Tuesday, December 8, 2015

    How to "Move" an existing InnoDB table? This article only covers how to create a new table outside data directory

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.