How to Export All MySQL Tables to CSV Using mysqldump?

Dumping All MySQL Tables in CSV Format with mysqldump

Question:

How can I simultaneously dump the contents of all tables within a MySQL database into CSV format using mysqldump?

Answer:

While mysqldump lacks a direct command to dump all tables in CSV format, you can achieve this by employing the following approach:

Step 1: Parse Table Names

Obtain a list of all table names using the command:

mysql -u username -ppassword dbname -sN -e "SHOW TABLES;"

Step 2: Iterate Over Tables and Dump

For each table name retrieved in Step 1, run this command in a loop (replace with the actual table name):

mysql -B -u username -p password database -h dbhost -e "SELECT * FROM <table_name>;" \
| sed "s/\"/\"\"/g;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"</table_name>

Step 3: Save Output to File

Append the following to the end of the command to save the output as a CSV file:

> <table_name>.csv</table_name>

This approach generates separate CSV files for each table, providing a comprehensive dump of all table contents.

The above is the detailed content of How to Export All MySQL Tables to CSV Using mysqldump?. For more information, please follow other related articles on the PHP Chinese website!