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

CSV Export of All MySQL Tables via 'mysqldump'

Developers often need to export MySQL data in CSV format, necessitating a method to dump all tables in this format simultaneously. This article explores a solution using the 'mysqldump' utility.

One-Table Export:

To export a single table, consider 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 uses the 'mysql' utility with the '-B' option to obtain raw data and inline the SELECT statement with the '-e' option. Subsequently, 'sed' is used to convert the output into CSV format.

Exporting All Tables:

To export all tables, follow these steps:

1. Obtain a list of all tables using the command:

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

2. Create a loop in a shell script, such as Bash, to iterate over these tables:

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

3. Replace the '.....' in the loop with the extended command from Step 1, substituting '$tb' for the tablename.

By executing this shell script, you can export all MySQL tables into CSV format in a single operation.

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