How Can I Export All MySQL Tables to CSV Using `mysqldump` and Other Commands?

CSV Export for All MySQL Tables using 'mysqldump'

Exporting all MySQL tables into CSV format using 'mysqldump' can be achieved through a combination of commands. While traditional methods involving 'mysqldump --tab' or '-T' require local access to the MySQL server, an alternative approach utilizes 'mysql' with the '-B' option.

To dump a single table as CSV with header fields, execute the following command:

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

This command generates a CSV file with double-quoted fields and a header row.

To dump all tables in the database, follow these additional steps:

1. Retrieve a list of table names:

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

2. Iterate over the table names and dump them individually as CSV:

for tb in $(mysql -u username -ppassword dbname -sN -e "SHOW TABLES;"); do
     echo .....;
done

Replace the ellipsis with the command from step 1, substituting $tb for the table name.

This process will result in CSV files for each table, allowing for easy data extraction and analysis.

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