Location of the Data Directory

A default data directory location is compiled into the server. Typical defaults are /usr/local/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.

The data directory location may be specified explicitly when you start up the server by using a --datadir= /path/to/dir option. This is useful if you want to place the data directory somewhere other than its default location.

As a MySQL administrator, you should know where your data directory is. If you run multiple servers, you should know where all the data directories are. 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:

• Use mysqladmin variables to get the data directory pathname directly from your server. On UNIX, the output looks like this

  % mysqladmin variables
  +----------------------------+--------------------------------+
  | Variable_name              | Value                          | 
  +----------------------------+--------------------------------+
  | back_log                   | 5                              | 
  | connect_timeout            | 5                              | 
  | basedir                    | /var/local/                    | 
  | datadir                    | /usr/local/var/                | 
  …
  

  This output indicates a data directory location of /usr/local/var on the server host.

  On Windows, the output might look like this instead:

  								C:> mysqladmin variables
  +----------------------------+--------------------------------+
  | Variable_name              | Value                          | 
  +----------------------------+--------------------------------+
  | back_log                   | 5                              | 
  | connect_timeout            | 5                              | 
  | basedir                    | c:mysql                      | 
  | datadir                    | c:mysqldata                 | 
  …
  

  If you have multiple servers running, they will be listening on different TCP/IP port numbers or sockets. 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:

  % mysqladmin --port=port_numvariables
  % mysqladmin --socket=/path/to/socketvariables
  							

  The mysqladmin command can be run on any host from which you can connect to the server. Use a --host= host_name option if you need to connect to a server on a remote host:

  % mysqladmin --host=host_namevariables
  							

  On Windows, you can contact a Windows NT server that is listening on a named pipe by using --pipe to force a named pipe connection and --socket= pipe_name to specify the pipe name:

  C:> mysqladmin --pipe --socket=pipe_namevariables
  							

• You can use ps to see the command line of any currently executing mysqld process. Try one of the following commands (depending on which version of ps your system supports) and look for --datadir in the commands that are shown in the output:

  % ps axww | grep mysqld  BSD-style ps
  % ps -ef | grep mysqld   System V-style ps
  					

  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.

• If MySQL was installed 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 datadir, as you might expect. Also, if the distribution is located on an NFS-mounted file system and is used to build MySQL for several hosts, the configuration information reflects the host on which the distribution was most recently built. That may not show you the data directory for the host 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, which are part of any MySQL installation:

  % find / -name "*.frm -print

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