Friday, June 30, 2017

how to find physical location of mysql database under linux?

If you are using MySQL, and want to know the actual physical path of MySQL, where MySQL stores the db, and its information files and the amount of data for MySQL data and MySQL index files i.e. table_name.MYD and table_name.MYI respectively, below is the easy solution.

Default MySQL Physical Directory Path is
/var/lib/mysql/ or /var/db/mysql

You can open it in Terminal  (using sudo or root access). Once you get into the folder, you can easily find the folders which are actually your database names, and in the database name folders, you can find the table names, having MYD and MYI extensions, which are actually your data and index files.

You can use the following command to locate MySQL datadir:

Method 1:>>grep datadir /etc/mysql/my.cnf

Sample output
datadir                        = /var/lib/mysql

Method 2:>>ps -eo cmd,args | grep mysql

Sample output:

/bin/sh /usr/bin/mysqld_saf /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/ --user=mysql
/usr/libexec/mysqld --based /usr/libexec/mysqld --basedir=/usr
--datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/ --skip-external-locking --socket=/var/lib/mysql/mysql.sock

Method 3:

mysql -u root -p

mysql> select @@datadir;
| @@datadir       |
| /var/lib/mysql/ |
1 row in set (0.00 sec)

This output confirms that MySQL is configured to use the default data directory, /var/lib/mysql/.

No comments:

Post a Comment