How to find the mysql data directory from command line in windows

Question

In linux I could find the mysql installation directory with the command which mysql. But I could not find any in windows. I tried echo %path% and it resulted many paths along with path to mysql bin.

I wanted to find the mysql data directory from command line in windows for use in batch program. I would also like to find mysql data directory from linux command line. Is it possible? or how can we do that?

In my case, the mysql data directory is on the installation folder i.e. ..MYSQL\mysql server 5\data It might be installed on any drive however. I want to get it returned from the command line.

Ian Kemp - SO dead by AI greed · Accepted Answer · 2018-07-11 13:57:04Z

346

You can issue the following query from the command line:

mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"'

Output (on Linux):

+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| basedir                   | /usr                       |
| character_sets_dir        | /usr/share/mysql/charsets/ |
| datadir                   | /var/lib/mysql/            |
| innodb_data_home_dir      |                            |
| innodb_log_group_home_dir | ./                         |
| lc_messages_dir           | /usr/share/mysql/          |
| plugin_dir                | /usr/lib/mysql/plugin/     |
| slave_load_tmpdir         | /tmp                       |
| tmpdir                    | /tmp                       |
+---------------------------+----------------------------+

Output (on macOS Sierra):

+---------------------------+-----------------------------------------------------------+
| Variable_name             | Value                                                     |
+---------------------------+-----------------------------------------------------------+
| basedir                   | /usr/local/mysql-5.7.17-macos10.12-x86_64/                |
| character_sets_dir        | /usr/local/mysql-5.7.17-macos10.12-x86_64/share/charsets/ |
| datadir                   | /usr/local/mysql/data/                                    |
| innodb_data_home_dir      |                                                           |
| innodb_log_group_home_dir | ./                                                        |
| innodb_tmpdir             |                                                           |
| lc_messages_dir           | /usr/local/mysql-5.7.17-macos10.12-x86_64/share/          |
| plugin_dir                | /usr/local/mysql/lib/plugin/                              |
| slave_load_tmpdir         | /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T/         |
| tmpdir                    | /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T/         |
+---------------------------+-----------------------------------------------------------+

Or if you want only the data dir use:

mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"'

These commands work on Windows too, but you need to invert the single and double quotes.

Btw, when executing which mysql in Linux as you told, you'll not get the installation directory on Linux. You'll only get the binary path, which is /usr/bin on Linux, but you see the mysql installation is using multiple folders to store files.

If you need the value of datadir as output, and only that, without column headers etc, but you don't have a GNU environment (awk|grep|sed ...) then use the following command line:

mysql -s -N -uUSER -p information_schema -e 'SELECT Variable_Value FROM GLOBAL_VARIABLES WHERE Variable_Name = "datadir"'

The command will select the value only from mysql's internal information_schema database and disables the tabular output and column headers.

Output on Linux:

/var/lib/mysql

edited Jul 11, 2018 at 13:57

Ian Kemp - SO dead by AI greed

1

answered Jul 31, 2013 at 10:54

hek2mgl

159k31 gold badges263 silver badges280 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Prabhu Over a year ago

I used mysql -uroot -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir" | grep "datadir" | awk "{print $2}"' for finding actual datadir path in linux. How can find only that path in windows?

hek2mgl Over a year ago

@Prabhu You can do it just with the mysql command. No other tools are required. Check my update

Prabhu Over a year ago

The command executes ok in linux. It does not execute well in windows xp. the first command gives error - you have error in your sql syntax near where Variable_Name = 'dirname'. The second command gives error - unknown database information_schema

Leonardo Herrera Over a year ago

To make this work under Windows, just invert the double/single quotes: mysql -uUSER -p -e "SHOW VARIABLES WHERE Variable_Name LIKE '%dir'"

Aurgho Bhattacharjee Over a year ago

Thanks for this! Running "SHOW VARIABLES WHERE Variable_Name = "datadir""; on my phpmyadmin database helped me find the location of the dabase directory.

|

Community · Accepted Answer · 2014-10-31 15:26:26Z

186

You can try this-

mysql> select @@datadir;

PS- It works on every platform.

edited Oct 31, 2014 at 15:26

CommunityBot

11 silver badge

answered Mar 31, 2014 at 5:38

As_913

1,8611 gold badge11 silver badges2 bronze badges

6 Comments

jmunsch Over a year ago

Works fine on Ubuntu.

SimonS Over a year ago

Works on Server 2012 r2

Alessandro Blasetti Over a year ago

Works on RHEL 6

breekoy Over a year ago

Works on CentOS 7

geeves Over a year ago

Works on Windows

|

Eugene Yarmash · Accepted Answer · 2018-02-04 18:05:29Z

8

You can see the complete list of MySQL server options by running

mysqld --verbose --help

For example, to find out the path to the data directory on Linux, you can run:

mysqld --verbose --help | grep ^datadir

Example output:

datadir                                     /var/lib/mysql/

answered Feb 4, 2018 at 18:05

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

1 Comment

toraman Over a year ago

interestingly this command gives me mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied) right before the actual datadir I have absolutely no idea why it tries to change working directory. I inspected a bit and it tries that when I use mysqld --verbose --help. Probably tries to do some work and needs to go to the data directory Anyways quick workaround is mysqld --verbose --help 2>/dev/null | grep ^datadir

Amrit Shrestha · Accepted Answer · 2017-08-10 05:20:48Z

4

if you want to find datadir in linux or windows you can do following command

mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"'

if you are interested to find datadir you can use grep & awk command

mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"' | grep 'datadir' | awk '{print $2}'

edited Aug 10, 2017 at 5:20

answered Jul 31, 2013 at 14:18

Amrit Shrestha

1,66021 silver badges25 bronze badges

Comments

Rakib · Accepted Answer · 2018-03-04 06:20:17Z

1

Use bellow command from CLI interface

[root@localhost~]# mysqladmin variables -p<password> | grep datadir

answered Mar 4, 2018 at 6:20

Rakib

2,1261 gold badge14 silver badges15 bronze badges

Comments

Gigoland · Accepted Answer · 2015-01-06 00:27:39Z

-1

public function variables($variable="")
{
  return empty($variable) ? mysql_query("SHOW VARIABLES") : mysql_query("SELECT @@$variable");
}

/*get datadir*/
$res = variables("datadir");

/*or get all variables*/
$res = variables();

edited Jan 6, 2015 at 0:27

answered Jan 6, 2015 at 0:14

Gigoland

1,59817 silver badges15 bronze badges

Comments

Eyad Arafat · Accepted Answer · 2018-02-12 14:53:24Z

-1

Check if the Data directory is in "C:\ProgramData\MySQL\MySQL Server 5.7\Data". This is where it is on my computer. Someone might find this helpful.

answered Feb 12, 2018 at 14:53

Eyad Arafat

6118 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to find the mysql data directory from command line in windows

7 Answers 7

9 Comments

6 Comments

1 Comment

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

9 Comments

6 Comments

1 Comment

Comments

Comments

Comments

Comments

Linked

Related