How to find the mysql data directory from command line in windows
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 ie ..MYSQLmysql server 5data It might be installed on any drive however. I want to get it returned from the command line.
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"'
This will work on Windows as well.
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
You can try this-
mysql> select @@datadir;
PS- It works on every platform.
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}'