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:
The rest of this section discusses which parts of the data directory can be moved and how you go about making such changes. Relocation MethodsThere are two ways to relocate the data directory or elements within it:
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.)
Assessing the Effect of RelocationBefore 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.
Relocating the Entire Data DirectoryTo 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 DatabasesThe 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:
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:
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.
Relocating Individual TablesRelocation 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.) 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 TablespaceYou 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 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 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:
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 FilesTo 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. For a description of these options and how to use them, see Chapter 11. |