Location of the Data Directory

A default data directory location is compiled into the server. Under UNIX, typical defaults are /usr/local/mysql/var if you install MySQL from a source distribution, /usr/local/mysql/data if you install from a binary distribution, and /var/lib/mysql if you install from an RPM file. Under Windows, the default data directory is C:\mysql\data.

The data directory location can be specified explicitly when you start up the server by using a --datadir=dir_name option. This is useful if you want to place the directory somewhere other than its default location. Another way to specify the location is to list it in an option file that the server reads at startup time. Then you don't need to include it on the command line each time you start the server. Data directory relocation is covered later in the chapter.

As a MySQL administrator, you should know where your server's data directory is located. If you run multiple servers, you should know where each one's data directory is. But if you don't know the location (perhaps you are taking over for a previous administrator who left poor notes), there are several ways to find out:

• Ask the server for the location. The server maintains a number of variables pertaining to its operation, and it can report any of their values. The data directory location is indicated by the datadir variable, which you can obtain using a mysqladmin variables commandor a SHOW VARIABLES statement. From the command line, use mysqladmin. On UNIX, the output might look like this:

   % mysqladmin variables  +---------------+-----------------------+ | Variable_name | Value                 | +---------------+-----------------------+ ... | datadir       | /usr/local/mysql/var/ | ... 

  On Windows, the output might look like the following instead:

   C:\> mysqladmin variables  +---------------+-----------------------+ | Variable_name | Value                 | +---------------+-----------------------+ ... | datadir       | c:\mysql\data\        | ... 

  From within mysql, check the variable's value like this:

   mysql> SHOW VARIABLES LIKE 'datadir';  +---------------+-----------------------+ | Variable_name | Value                 | +---------------+-----------------------+ | datadir       | /usr/local/mysql/var/ | +---------------+-----------------------+ 

  If you have multiple servers running, they will be listening on different TCP/IP port numbers, sockets, or named pipes. You can get data directory information from each of them in turn by supplying appropriate --port or --socket options to connect to the port or socket on which each server is listening. Specifying a host of 127.0.0.1 explicitly tells mysqladmin to connect to the server on the local host using a TCP/IP connection:

   % mysqladmin --host=127.0.0.1 --port=port_num variables  

  Under UNIX, specifying a value of localhost causes a UNIX socket connection to be used. You can also specify a --socket option if necessary to indicate the socket file pathname:

   % mysqladmin --host=localhost --socket=/path/to/socket variables  

  Under Windows NT-based systems, a named pipe connection can be specified by giving '.' as a hostname, perhaps with a --socket option to indicate the pipe name:

   C:\> mysqladmin --host=. --socket=pipe_name variables  

  For any platform, to connect via TCP/IP to a remote server running on another host, specify a --host option that indicates the name of the server host:

   % mysqladmin --host=host_name variables  

  Specify a --port option as well if you need to connect to a port number other than the default.

• Under UNIX, use the ps command to see the command line of any currently executing mysqld process or processes. By looking for a --datadir option, you may be able to determine the data directory location. If you have a BSD-style ps, try the following command:

   % ps axww | grep mysqld  

  For a System V-style ps, try this instead:

   % ps -ef | grep mysqld  

  The ps command can be especially useful if your system runs multiple servers because you can discover multiple data directory locations at once. The drawbacks are that ps must be run on the server host and that no useful information is produced unless the --datadir option was specified explicitly on the mysqld command line. (On the other hand, some of the startup scripts that invoke mysqld for you attempt to determine the data directory pathname and put it in the mysqld command line, which makes that information available to ps.)

• Look in an option file that the server reads when it starts up. For example, if you look in /etc/my.cnf under UNIX or C:\my.cnf under Windows, you may find a datadir line in the [mysqld] option group:

   [mysqld]  datadir=/path/to/data/directory 

  The pathname indicates the location of the data directory.

• The server's help message includes an indication of the default data directory location that is compiled in. This will tell you the directory that the server actually uses when it runs, if the location is not overridden at startup time. To see this output, issue the following command:

   % mysqld --help  ... datadir      /usr/local/mysql/var/ ... 

• If you installed MySQL from a source distribution, you can examine its configuration information to determine the data directory location. For example, the location is available in the top-level Makefile. But be careful: The location is the value of the localstatedir variable in the Makefile, not the value of the datadir variable, as you might expect. Also, if the distribution is located on an NFS-mounted file system that is used to build MySQL for several hosts, the configuration information will be accurate only for the host for which the distribution was most recently built. That may not show you the data directory for the server in which you're interested.

• Failing any of the previous methods, you can use find to search for database files. The following command searches for .frm (description) files:

   % find / -name "*.frm" -print  

  The .frm files store the definitions of the tables managed by the server, so they are part of any MySQL installation. These files normally will be found in directories that all have a common parent directory; that parent should be the data directory.

In the examples that follow throughout this chapter where I denote the location of the MySQL data directory as DATADIR, you should interpret that as the location of the data directory for the server on your own machine.