0

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.

1

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.

4
  • I check and found but this is not clean database.sql this is some .frm .ibd files, here some way for found clear database.sql file which i can easy download on my pc ? – nikolr8 Feb 7 '15 at 11:51
  • @nikolr8 Yes, those .frm and .ibd files are your database. If you want a logical export of the contents ".sql", please use the tools commented on the last paragraph. In particular, have a look at mysqldump: dev.mysql.com/doc/refman/5.6/en/mysqldump.html – jynus Feb 7 '15 at 12:02
  • i open this .frm and .ibd with notpead ++ but this is hard to read there some other solution for read this clear ? – nikolr8 Feb 7 '15 at 12:16
  • Using notepad on a binary file will just produce gibberish - try mysqlfrm, Percona's data recovery tool and TwinDB's tools. – Vérace Feb 7 '15 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.