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 often is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 4.1\data.

If you compile MySQL from source yourself, the default data directory location to compile in can be specified by using the --localstatedir=dir_name option when you run configure.

The data directory location can be changed when you start 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.

If the data directory already has been created at one location and you want to move it somewhere else, 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 running on the same machine, 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 system 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 command or 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 this instead:
```
C:\> mysqladmin variables
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
...
| datadir       | c:\mysql\data\        |
...
```
To determine the value using SHOW VARIABLES, issue this statement:
```
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 network interfaces (TCP/IP ports, Unix socket files, or Windows named pipes or shared memory). You can get data directory information from each of them in turn by supplying appropriate connection parameter options to connect to a network interface that the server is listening to. For example, specifying a host of 127.0.0.1 explicitly tells mysqladmin to use a TCP/IP connection to connect to a server running on the local host:
```
% mysqladmin --host=127.0.0.1 --port=port_num variables
```
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
```
Use a --port option as well if you need to specify a port number other than the default.
Under Unix, use the ps command to see the command line of any currently executing local 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 this 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. No useful information about the locations is available this way unless the --datadir option was specified explicitly on the mysqld command line. However, some of the startup scripts that invoke mysqld for you do attempt to determine the data directory pathname and put it in the mysqld command line, which makes that information accessible to ps.
Look in an option file that the server reads when it starts. 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. This tells you the directory that the server actually will use when it runs, unless you override it with a --datadir option on the command line. To see this output, issue the following command (leaving out the --verbose option before MySQL 4.1.1):
```
% mysqld --verbose --help
...
datadir      /usr/local/mysql/var/
...
```
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 are found in directories that all have a common parent directory; that parent should be the data directory.