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 rest of this section discusses which parts of the data directory can be moved and how you go about making such changes.
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).
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.
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
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:
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:
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:
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
[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.