195

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.

Prabhu
  • 4,977
  • 3
  • 31
  • 40

8 Answers8

311

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
Ian Kemp
  • 24,155
  • 16
  • 97
  • 121
hek2mgl
  • 133,888
  • 21
  • 210
  • 235
  • 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? – Prabhu Jul 31 '13 at 14:21
  • 1
    @Prabhu You can do it just with the `mysql` command. No other tools are required. Check my update – hek2mgl Jul 31 '13 at 15:43
  • 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 – Prabhu Aug 01 '13 at 01:57
  • When I try any of these commands in Win7 (AMPPS), I always get a long helpfile displayed, nothing useful. ??? – Ralf Mar 13 '15 at 14:29
  • @hek2mgl, What if I indeed want the path of the executable. What's the equivalent of `which` command in Windows? – Pacerier Aug 20 '15 at 06:01
  • This answer is for Linux only, it doesn't work on Windows 7 64bit, for that OS, login to mysql by `mysql -uuser -p` then enter password and then run query `select @@datadir;` just as As_913 stated. – Vicky Dev Oct 04 '16 at 16:22
  • The command is intended to work on Windows too. What's the issue? – hek2mgl Oct 05 '16 at 08:18
  • 1
    To make this work under Windows, just invert the double/single quotes: `mysql -uUSER -p -e "SHOW VARIABLES WHERE Variable_Name LIKE '%dir'"` – Leonardo Herrera Jul 14 '17 at 01:20
  • 1
    Thanks for this! Running "SHOW VARIABLES WHERE Variable_Name = "datadir""; on my phpmyadmin database helped me find the location of the dabase directory. – Aurgho Bhattacharjee Mar 18 '18 at 18:20
165

You can try this- 

mysql> select @@datadir;

PS- It works on every platform.

Community
  • 1
  • 1
As_913
  • 1,651
  • 1
  • 9
  • 2
5

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}'
Amrit Shrestha
  • 1,645
  • 20
  • 23
2

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/
Eugene Yarmash
  • 119,667
  • 33
  • 277
  • 336
  • 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` – toraman Nov 29 '20 at 05:55
1

Use bellow command from CLI interface

[root@localhost~]# mysqladmin variables -p<password> | grep datadir
Rakib
  • 1,530
  • 1
  • 9
  • 15
0

Output on Windows:
you can just use this command,it is very easy!

1. no password of MySQL: 

mysql ?

2. have password of MySQL:

mysql -uroot -ppassword mysql ?

enter image description here enter image description here

xgqfrms
  • 5,516
  • 1
  • 37
  • 42
-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();
GigolNet Guigolachvili
  • 643
  • 7
  • 6
-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.

bluegrounds
  • 521
  • 10
  • 18