Relocating Data Directory Contents

Earlier parts of this chapter discuss the data directory structure in its default configuration, which is that all databases, status files, 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:

• The filesystem that contains the data directory has become full and you need to move it to a filesystem with more capacity.

• If your data directory is on a busy disk drive, 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 on one disk and logs on another disk 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.

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:

• On any platform, 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 directory 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 option 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. 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. Chapter 11 discusses which option groups apply to different server startup methods, and also provides instructions for running multiple servers.

• On Unix, you can move the file or directory 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 my.ini in the Windows directory or C:\my.cnf on Windows).

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
Server PID file	Startup option
Log files	Startup option

On Unix, 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 the data directory itself. 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.

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. For example, on Unix, you can use the du, df, and ls -l commands for obtaining disk space information. However, you must correctly understand the layout of your filesystem for any of these to be useful.

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

% 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 filesystem? To find out, use du -s to see how much space that directory uses:

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

Assuming that df reports values in 1KB block sizes, that's about 130MB. Moving the data directory from /usr to var should free quite a lot of space on /usr. But would it really? To find out, try df in the data directory. Suppose that you get output like this:

% 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 filesystem containing the data directory (that is, /usr), why does df report the space on the /var filesystem? The following ls -l command provides the answer:

% ls -l /usr/local/mysql/data
...
lrwxrwxr-x  1 mysql  mysql  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 filesystem 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 stop the MySQL server before performing any relocation operation, and then restart it afterward. For certain types of relocations, such as moving a database directory, it is sometimes possible to keep the server running, but not recommended. If you do that, you must make sure that 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 that 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, stop the MySQL server, and then move the data directory to its new location. After the move, you should restart the server with a --datadir option that explicitly indicates the new location. On Unix, an alternative to using the --datadir option is to create a symbolic link in the original data directory location that points to the new location. The symlink method actually is preferable to using --datadir if the data directory contains a my.cnf file that you want the server to use. (The symlink has the effect of making the data directory appear to be located at the original compiled-in location, which is one of the locations where the server looks for option files.)

Relocating Individual Databases

The server always looks for database directories in the data directory, so the only way to relocate a database is by the symlink method. The procedure for this is different for Unix and Windows.

Under Unix, relocate a database as follows:

The following example shows how you might use this procedure to move a database bigdb from the /usr/local/mysql/data directory to /var/db:

% mysqladmin -p -u root shutdown
Enter password: ******
% cd /usr/local/mysql/data
% 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:

E:\mysql-book\sampdb\

[mysqld]
symbolic-links

If you're moving a database to another filesystem as an attempt to redistribute database storage, remember that if you are using InnoDB tables that are stored in the InnoDB shared tablespace, the contents of those tables are not located in the database directory. For a database composed primarily of such InnoDB tables, relocating the database directory will relocate only their .frm files, not their contents. This will have little effect on storage distribution.

Relocating Individual Tables

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

• You must be using MySQL 4.0 or later on Unix and the table to be relocated must be a MyISAM table.

• Your operating system must have a working realpath() call. If you do, the result of the following query will be YES:

  mysql> SHOW VARIABLES LIKE 'have_symlink';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | have_symlink  | YES   |
  +---------------+-------+
  

  
  

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.) Before doing this, either stop the server while you move the files, or lock the table to prevent the server from using it, as described in "Performing Database Maintenance with the Server Running," in Chapter 13.

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 themand 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 Shared Tablespace

You configure the InnoDB shared 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 shared tablespace, see Chapter 11.) 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 filesystems. Because you list the file locations using startup options, the way to relocate some or all of the tablespace files is as follows:

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 the 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 or a log file, stop 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 these 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. If you use a relative name, the server creates the file under the data directory. For example, if you specify --pid-file=mysqld.pid, the PID file will be mysqld.pid in the data directory.

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 put the MySQL logs there, too. 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. What you can do instead is create subdirectories /var/run/mysql and /var/log/mysql and set them to be owned by the account you use for running the server. For example, if that account has user and group names of mysql, you can execute the following commands as root:

# mkdir /var/run/mysql
# chown mysql /var/run/mysql
# chgrp mysql /var/run/mysql
# chmod u=rwx,go-rwx /var/run/mysql
# mkdir /var/log/mysql
# chown mysql /var/log/mysql
# chgrp mysql /var/log/mysql
# chmod u=rwx,go-rwx /var/log/mysql

Then the server won't have any problems writing files in those directories. You can start the server with options that specify files there. For example:

[mysqld]
pid-file = /var/run/mysql/mysql.pid
log-error = /var/log/mysql/log.err
log = /var/log/mysql/querylog
log-bin = /var/log/mysql/binlog

For more information about log file options and how to use them, see Chapter 11.