Mysql – Name of Mysql database folder

MySQL

I try to access to mysql data via filezilla and download to my pc,
What is name of folder where is my mysql data ?
I use ubuntu.

Thanks.

Best Answer

By default, most packages in most distributions in Linux, including official and distributions ones on Ubuntu set the default MySQL datadir on /var/lib/mysql. You can check the actual datadir by connecting to mysql and executing:

mysql> SHOW GLOBAL VARIABLES like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.02 sec)

Technically, there could be files outside of the datadir (per-table configuration, innodb files, etc.), but not with the default configuration.

Please note that, in general, that directory is owned by the mysql user, so you will not be able to read or write it as a regular user. Only the mysql system user or a user with administration privileges will be able to access there.

If you are using Filezilla, you probably will be using ftp/sftp/ssh. Please note that copying the files directly has the following restrictions:

  • You can only do it consistently if the server has been stopped. Otherwise you will end up with a corrupt set of files.
  • The files you will copy can only be used as a backup, or to clone the current setup, you cannot access the tables unless you set up a new mysql instance on your local pc
  • You may want to copy other files like logs (/var/log/mysql*) and the configuration files (/etc/my*)

If you want simply to access the mysql service/data remotely and operate with it, you do not need filezilla, only a mysql client, like the mysql monitor command line, MySQL Workbench or phpmyadmin, by setting them to connect to the remote host (assuming the service is available from the outside).

Update: if what you want is a text-based representation of your database, and you do not want to use a client like Workbench or phpmyadmin, please follow these steps:

  • In Ubuntu, execute on a command line prompt:

     mysqldump -u root --all-databases > ~/database.sql
    
  • Copy the database.sql file found on your home directory to your local pc using filezilla

If you want to export a single table and open it in an application like Excel, you can export it also in CSV format.