Problem
With the command which mysql in Linux, I was able to locate the mysql installation location. However, I was unable to locate any in the windows. I attempted echo percent path percent and got a lot of routes, including the path of the mysql bin.
I needed to locate the mysql data location in Windows using the command line for usage in a batch program. I’d also like to locate the mysql data directory using the linux command line. Is that even possible? or how are we going to accomplish it?
The mysql data directory, in my instance, is located in the installation folder, namely..MYSQLmysql server 5data. It can, however, be installed on any drive. I’d like to obtain it from the command line.
Asked by Prabhu
Solution #1
You can use the command line to run the following query:
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 |
+---------------------------+----------------------------+
On macOS Sierra, the following is the output:
+---------------------------+-----------------------------------------------------------+
| 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/ |
+---------------------------+-----------------------------------------------------------+
Alternatively, if you only want the data dir, use:
mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"'
These instructions also work on Windows, however the single and double quotes must be inverted.
By the way, if you run which mysql in Linux like you suggested, you won’t obtain the installation directory. You’ll just receive the binary path, which on Linux is /usr/bin, but you’ll notice that the mysql installation has numerous folders where files are stored.
If you only need the value of datadir as output, without any column headers or other information, and you don’t have access to a GNU environment (awk|grep|sed…), 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 disables the tabular output and column headers and selects the value only from mysql’s internal information schema database.
Output on Linux:
/var/lib/mysql
Answered by hek2mgl
Solution #2
This is something you could try:
mysql> select @@datadir;
PS: It’s compatible with all platforms.
Answered by As_913
Solution #3
If you want to find datadir in linux or windows, use the commands below.
‘mysql -uUSER -p -e”mysql -uUSER -p -e”mysq VARIABLES WHERE Variable Name = “datadir”‘ SHOW VARIABLES
If you want to find datadir, you can use the grep and awk commands.
mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"' | grep 'datadir' | awk '{print $2}'
Answered by Amrit Shrestha
Solution #4
By using the command, you can get a complete list of MySQL server parameters.
mysqld --verbose --help
On Linux, for example, you can run: to get the path to the data directory.
mysqld --verbose --help | grep ^datadir
Example output:
datadir /var/lib/mysql/
Answered by Eugene Yarmash
Solution #5
Use the CLI interface to run the commands listed below.
[root@localhost~]# mysqladmin variables -p<password> | grep datadir
Answered by Rakib
Post is based on https://stackoverflow.com/questions/17968287/how-to-find-the-mysql-data-directory-from-command-line-in-windows