Relocating Data Directory Contents

The preceding part of this chapter discusses the data directory structure in its default configuration, which is that all databases, status, and log files are located within it. However, you have some latitude in determining the placement of the data directory's contents. MySQL allows you to relocate the data directory itself or certain elements within it. There are several reasons why you might want to do this:

You can put the data directory on a file system that has a capacity greater than the file system where it's located by default.

If your data directory is on a busy disk, you can put it on a less active drive to balance disk activity across physical devices. You can put databases and log files on different drives or distribute databases across drives for the same reasons. Similarly, the InnoDB tablespace is conceptually a single large block of storage, but you can put its individual component files on different drives to improve performance.

Putting databases and logs on different disks helps minimize the damage that can be caused by a failure of a single disk.

You might want to run multiple servers, each with its own data directory. This is one way to work around problems with per-process file descriptor limits, especially if you cannot reconfigure the kernel for your system to allow higher limits.

Some systems keep server PID files in a specific directory, such as /var/run. You might want to put the MySQL PID file there, too, for consistency of system operation. In similar fashion, if your system uses /var/log for log files, you can also put the MySQL logs there. (However, many systems allow only root to write to these directories. That means you'd need to run the server as root, which for security reasons is not a good idea.)

The rest of this section discusses which parts of the data directory can be moved and how you go about making such changes.

Relocation Methods
There are two ways to relocate the data directory or elements within it:

You can specify an option at server startup time, either on the command line or in an option file. For example, if you want to specify the data dir ectory location, you can start the server with a --datadir=dir_name option on the command line or you can put the following lines in an op tion file:

[mysqld]
datadir=dir_name

Typically, the option file group name for server options is [mysqld], as shown in the example. However, depending on your circumstances, other option group names may be more appropriate. For example, the [embedded] group applies to the embedded server. Or if you're running multiple servers using mysqld_multi, the group names will be of the form [mysqldn], where n is some integer associated with a particular server instance.

You can move the thing to be relocated, and then make a symlink (symbolic link) in the original location that points to the new location.

Neither of these methods works universally for everything that you can relocate. Table 10.2 summarizes what can be relocated and which relocation methods can be used. If you use an option file, it is possible to specify options in the global option file (such as /etc/my.cnf under UNIX or C:\my.cnf or my.ini in the system directory under Windows).

It's also possible to use the option file my.cnf in the default data directory (the directory compiled into the server). This is a good option file to use for server-specific options if you run multiple servers, but because the server looks for it only in the compiled-in data directory location, the file won't be found if you relocate that directory. (One workaround for this problem is to move the data directory and then make its original location a symlink that points to the new location.)

Table 10.2. Relocation Method Summary Entity to Relocate Applicable Relocation Methods
Entire data directory Startup option or symlink
Individual database directories Symlink
Individual database tables Symlink
InnoDB tablespace files Startup option
PID file Startup option
Log files Startup option

Assessing the Effect of Relocation
Before attempting to relocate anything, it's a good idea to verify that the operation will have the desired effect. I tend to favor the du, df, and ls-l commands for obtaining disk space information, but all of these depend on correctly understanding the layout of your file system.

The following example illustrates a subtle trap to watch out for when assessing a data directory relocation. Suppose your data directory is /usr/local/mysql/data and you want to move it to /var/mysql because df indicates the /var file system has more free space (as shown by the following example):

% df /usr /var
Filesystem 1K-blocks Used Avail Capacity Mounted on
/dev/wd0s3e 396895 292126 73018 80% /usr
/dev/wd0s3f 1189359 1111924 162287 15% /var

How much space will relocating the data directory free up on the /usr file system? To find out, use du-s to see how much space that directory uses:

% cd /usr/local/mysql/data
% du -s
133426 .

That's about 130MB, which should make quite a difference on /usr. But will it really? Try df in the data directory:

% df /usr/local/mysql/data
Filesystem 1K-blocks Used Avail Capacity Mounted on
/dev/wd0s3f 1189359 1111924 162287 15% /var

That's odd. If we're requesting the free space for the file system containing the data directory (that is, /usr), why does df report the space on the /var file system? The following ls-l command provides the answer:

% ls -l /usr/local/mysql/data
...
lrwxrwxr-x 1 mysqladm mysqlgrp 10 Dec 11 23:46 data -> /var/mysql
...

This output shows that /usr/local/mysql/data is a symlink to /var/mysql. In other words, the data directory already has been relocated to the /var file system and replaced with a symlink that points there. So much for freeing up a lot of space on /usr by moving the data directory to /var!

Moral: A few minutes spent assessing the effect of relocation is a worthwhile investment. It doesn't take long, and it can keep you from wasting a lot of time moving things around only to find that you've failed to achieve your objective.

Relocation Precautions
You should bring down the server before performing any relocation operation and then restart it afterward. For some types of relocations, such as moving a database directory, it is possible to keep the server running, but not recommended. If you do that, you must make sure the server is not accessing the database you're moving. You should also be sure to issue a FLUSH TABLES statement before moving the database to make sure the server closes all open table files. Failure to observe these precautions can result in damaged tables.

Relocating the Entire Data Directory
To relocate the data directory, bring down the server and move the data directory to its new location. Then you should either remove the original data directory and replace it with a symlink that points to the new location or restart the server with a --datadir option that explicitly indicates the new location. The symlink method is preferable if the data directory contains a my.cnf file and you want the server to continue to find it.

Relocating Individual Databases
The server wants to find database directories in the data directory, so the only way to relocate a database is by the symlink method. Under UNIX, do so as follows:

Shut down the server if it is running.

Copy or move the database directory to its new location.

Remove the original database directory.

Create a symlink in the data directory that has the name of the original

database and that points to the new database location.

Restart the server.

The following example shows how you might use this procedure to move a database bigdb to a different location:

% mysqladmin -p -u root shutdown
Enter password: ******
% cd DATADIR
% tar cf - bigdb | (cd /var/db; tar xf -)
% mv bigdb bigdb.orig
% ln -s /var/db/bigdb .
% mysqld_safe &

You should execute these commands while logged in as the MySQL administrator. The procedure shown here renames the original database directory to bigdb.orig as a precaution. After you verify that the server works properly with the relocated database, you can remove the original one:

% rm -rf bigdb.orig

Under Windows, database relocation is handled somewhat differently:

Shut down the server if it is running.

Move the database directory to where you want it.

Create a .sym file in the MySQL data directory that points to the new database location. For example, if you move the sampdb database from C:\mysql\data\sampdb to E:\mysql-book\sampdb, you should create a file named sampdb.sym in C:\mysql\data that contains the following line:

E:\mysql-book\sampdb\

The .sym file acts as a symbolic link to let the MySQL server know where to find the relocated database directory.

Make sure that symbolic link support is enabled when you start the server. You can do this with the --use-symbolic-links option on the command line or by placing the following lines in an option file:

[mysqld]
use-symbolic-links

For Windows database relocation to work properly as just described, you must be running a -max server (mysqld-max or mysqld-max-nt) from MySQL 3.23.16 or later.

If you're moving a database to another file system as an attempt to redistribute database storage, remember that InnoDB table contents are stored within the InnoDB tablespace, not in the database directory. For a database composed primarily of InnoDB tables, relocating the database directory may have little effect on storage distribution.

Removing a Relocated Database
You can remove a database with the DROP DATABASE statement, but servers from versions of MySQL older than 3.23 have trouble removing a database that has been relocated. The tables in the database are removed correctly, but an error occurs when the server attempts to remove the database directory because the directory is a symlink and not a real directory. If you encounter this problem, you must complete the DROP DATABASE operation by manually removing the database directory and the symlink that points to it.

Relocating Individual Tables
Relocation of an individual table is supported only under certain limited circumstances:

You must be using MySQL 4.0 or later.

Your operating system must have a working realpath() call.

The table to be relocated must be a MyISAM table.

If those conditions are all true, you can move the table's .MYD data and .MYI index files to their new locations and then create symlinks to them in the database directory under the original data and index filenames. (Leave the .frm file in the database directory.)

You should not try to relocate a table if any of the preceding conditions are not satisfied. If you do so anyway and then refer to the table with an ALTER TABLE, OPTIMIZE TABLE, or REPAIR TABLE statement, your changes may be undone. Each of those statements operates by creating in the database directory a temporary table that implements your alteration or optimization, and then deleting the original table and renaming the temporary table to the original name. The result is that your symlinks are removed and the new table ends up right back in the database directory where your original table was before you moved it. Furthermore, the old table files that you moved out of the database directory are still in the location where you moved them—and you might not even realize they are there, continuing to take up space. Also, the symlinks have been destroyed, so when you realize later what has happened, you may not have any good way of tracking down the files if you've forgotten where you moved them. Because it's difficult to guarantee that no one with access to the table will ever alter or optimize it (and thus undo any attempted relocation), it's best to leave tables in the database directory.

Relocating the InnoDB Tablespace
You configure the InnoDB tablespace initially by listing the locations of its component files in an option file, using the innodb_data_home_dir and innodb_data_file_path options. (For details on configuring the tablespace, see ) If you have already created the tablespace, it's possible to relocate regular files that are part of it, for example, to distribute them across different file systems. Because you list the file locations using startup options, the way to relocate some or all of the tablespace files is like this:

Shut down the server if it is running.

Move the tablespace file or files that you want to relocate.

Update the option file where the InnoDB configuration is defined to reflect the new locations of any files that you moved.

Restart the server.

Strictly speaking, it's possible to relocate a tablespace component by moving it and then creating a symlink to it at the original location. But there's no point in doing so; you have to list a location for component in the option file anyway, so you may as well list the real location rather than that of a symlink.

Relocating Status and Log Files
To relocate the PID file, bring down the server and then restart it with the appropriate option to specify the file's new location. For example, to create the PID file as /tmp/mysql.pid, use --pid-file=/tmp/mysql.pid on the command line or include the following lines in an option file:

[mysqld]
pid-file=/tmp/mysql.pid

If you specify the filename as an absolute pathname, the server creates the file using that pathname. Otherwise, the file is created under the data directory. For example, if you specify --pid-file=mysqld.pid, the PID file will be mysqld.pid in the data directory.

To relocate log files, use server startup options.