Use mysqldump to Export All Tables from an MySQL Database to CSV Files

Preface

This solution will download all tables in a single database to flat files so you can keep a backup copy or if you want to work with the data outside of mysql.

Prerequites

Make sure the directory you are exporting to when using the mysqldump command has permissions to write to, otherwise you will receive an error like one of the below:

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

mysqldump: Got error: 1: Can't create/write to file '/path/to/filename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

To prevent this from happening you will have to perform one of the following:

1. Execute the command using the directory specified by secure-file-priv.
   1. Note that if you are using “select .. into outfile” you must specify the full path and the full path must match the results of SHOW VARIABLES LIKE "secure_file_priv";
2. Disable secure-file-priv. This must be removed from startup and cannot be modified dynamically. You can modify this in my.conf. The default locations of my.conf can be found in the following directories:

/etc/my.cnf
/etc/mysql/my.cnf

Modify the [mysqld] config group with the following:

[mysqld]
secure-file-priv = ""

Exporting the Tables

You can use either one of the commands below to export the tables. The first one is to only export comma-separated values and the second command is if you want to enclose each value in the columns with quotes.

Legend:

• -u username
• -p password
• -t without this flag, mysqldump will also create .sql files when exporting. Add this flag if you only want the csv files
• -T By default the -T flag will dump the data into tab-delimited files. You can however override this by adding the --fields-terminated-by= flag.

mysqldump -u [username] -p -t -T/path/to/directory [database] --fields-terminated-by=,

mysqldump -u [username] -p -t -T/path/to/directory [database] --fields-enclosed-by=" --fields-terminated-by=,

Credits to:

This solution is a combination of the two following resources.

https://electrictoolbox.com/using-mysqldump-to-save-data-to-csv-files/

https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql