To do this a command needs to be run as an administrative user in MySQL workbench or the MySQL command line utility:
SHOW VARIABLES WHERE Variable_Name LIKE "%dir" OR Variable_Name LIKE "%directory%" OR Variable_Name LIKE "log_bin%";The response to this command provides a list of all of the directories on which the MySQL instance will have the various data files.
Output for MySQL deployed on Linux/Unix
Using the r_volume_and_file_system_architectural_layout.html#r_volume_and_file_system_architectural_layout__r_volume_and_file_system_architectural_layout-tbl1 the output should have the basedir and datadir populated but some others will either have no entry or contain "./" which indicates the files will be stored in the datadir.
mysql> SHOW VARIABLES WHERE Variable_Name LIKE "%dir" OR Variable_Name LIKE "%directory%" OR Variable_Name LIKE "log_bin%";
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| basedir | /usr/ |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
| datadir | /var/lib/mysql/ |
| innodb_data_home_dir | |
| innodb_doublewrite_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| lc_messages_dir | /usr/share/mysql-8.0/ |
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| plugin_dir | /usr/lib64/mysql/plugin/ |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+---------------------------------+--------------------------------+
18 rows in set (0.00 sec)
If using Separate data/binary and transaction log directories the output will have specific values for directories other than basedir and datadir.
mysql> SHOW VARIABLES WHERE Variable_Name LIKE "%dir" OR Variable_Name LIKE "%directory%" OR Variable_Name LIKE "log_bin%";
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| basedir | /mysql/base/ |
| character_sets_dir | /mysql/base/share/mysql-8.0/charsets/ |
| datadir | /mysql/base/data/ |
| innodb_data_home_dir | /mysql/base/data |
| innodb_doublewrite_dir | |
| innodb_log_group_home_dir | /mysql/log |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_tmpdir | |
| innodb_undo_directory | /mysql/undo |
| lc_messages_dir | /mysql/base/share/mysql-8.0/ |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| plugin_dir | /mysql/base/lib64/mysql/plugin/ |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+---------------------------------+---------------------------------------+
18 rows in set (0.00 sec)
Output for MySQL deployed on Microsoft Windows
This is an example of a Separate data/binary and transaction log directories deployment.
mysql> SHOW VARIABLES WHERE Variable_Name LIKE "%dir" OR Variable_Name LIKE "%directory%" OR Variable_Name LIKE "log_bin%";
+---------------------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------+
| basedir | C:\MySQL\Base\ |
| character_sets_dir | C:\MySQL\Base\share\charsets\ |
| datadir | C:\MySQL\Base\Data\ |
| innodb_data_home_dir | C:\MySQL\Base\Data |
| innodb_doublewrite_dir | |
| innodb_log_group_home_dir | C:\MySQL\Log\Data |
| innodb_temp_tablespaces_dir | .\#innodb_temp\ |
| innodb_tmpdir | |
| innodb_undo_directory | C:\MySQL\Undo\Data |
| lc_messages_dir | C:\MySQL\Base\share\ |
| log_bin | ON |
| log_bin_basename | C:\MySQL\Base\Data\MYSQL-WINDOWS-bin |
| log_bin_index | C:\MySQL\Base\Data\MYSQL-WINDOWS-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| plugin_dir | C:\MySQL\Base\lib\plugin\ |
| slave_load_tmpdir | C:\Windows\SERVIC~1\NETWOR~1\AppData\Local\Temp |
| tmpdir | C:\Windows\SERVIC~1\NETWOR~1\AppData\Local\Temp |
+---------------------------------+-------------------------------------------------+
18 rows in set (0.01 sec)