The MySQL data directory is where the databases are stored along with its retain database. The retain database stores the header data and pointers to databases and data on the disk. While working in MySQL, often the data administrator wants to find the location of the data directory containing the system’s retain database to perform various administrative tasks.

This post will discuss finding the MySQL data directory containing the retain directory in Windows and Linux.

Pre-requisite: Login to MySQL Server

Make sure to log in to the MySQL server, for that open the command prompt and use this syntax to login to your local MySQL server:

Provide the username of MySQL server in the syntax to form the command and run it to log in to your MySQL server successfully:

Find MySQL Data Directory Containing Retain Database Using “SELECT” Command

The “SELECT” command can be used to locate the data directory containing the retained database on Windows and Linux operating systems. Let’s run the given below command on Windows:

The output will display the folder location that contains the retain database:

On Linux, the command is the same to find the data directory containing retain databases, run this command after logging in MySQL server:

The output displays the location of the data directory successfully.

Find MySQL Data Directory Containing Retain Database Using “SHOW VARIABLE” Command

The “SHOW VARIABLE” command displays the names and values of system variables. The command to find the data directory containing retain databases is the same for Windows and Linux. The command is given below:

Run the command in Windows, and the output will display the location of the data directory, and for this post, it is located in the “C” drive:

Run the same command in Linux to obtain the data directory location containing retain databases:

You have successfully located the data directory using the “SHOW VARIABLE” command in Linux and Windows.

Find MySQL Data Directory Containing Retain Database Using “SHOW VARIABLE” Command with “WHERE” Clause

The “WHERE” clause can be used in the “SHOW VARIABLE” command to specify a pattern in the “Variable_Name”.

Let’s see an example of using the “SHOW VARIABLE” command with a specific pattern that name should start with the string “data” and can contain zero, single or multiple words after the string. The command is given below on Windows and Linux both:

The Windows will display this output:

When you will run the same command on Linux, the output will provide the data directory of Linux containing the retained databases:

Another example could be when the user can specify the pattern that ends with “dir”. The given below command can run on Windows and Linux. It will show all the system variables whose names end with “dir”:

On Windows, the output displayed “13” rows of variable names, locate for “datadir” to find the location of data directory:

On Linux, the output will display the list of variable names, locate the “datadir”:

You have successfully located the MySQL data directory containing the retained database in Windows and Linux using the “SHOW VARIABLE” command with the “WHERE” clause.

Alternatively, if the user does not want to log in and then run the commands provided above. This syntax can be used on the terminal of Windows and Linux to find the location of the data directory containing retain database:

In Windows Command Prompt, make sure to provide the username in the syntax and run the command:

The folder containing the retain database is visible in the output above.

In the Linux terminal run the command by providing the username of MySQL and run the command:

You have successfully located the data directory containing retain a database.

Conclusion

In Linux and Windows, to find MySQL data directory containing the retain database. Login to MySQL server and run the “select @@datadir;” or “SHOW VARIABLES LIKE ‘datadir’;” command. Alternatively, run the “mysql -u <username> -p -e “SHOW VARIABLES LIKE ‘datadir’;” command. This post discussed how to find the MySQL data directory containing the retain database.