Dump all tables in CSV format using ‘mysqldump’

by

First, I can give you the answer for one table:

The trouble with all these INTO OUTFILE or --tab=tmpfile (and -T/path/to/directory) answers is that it requires running mysqldump on the same server as the MySQL server, and having those access rights.

My solution was simply to use mysql (not mysqldump) with the -B parameter, inline the SELECT statement with -e, then massage the ASCII output with sed, and wind up with CSV including a header field row:

Example:

 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"

“id”,”login”,”password”,”folder”,”email”
“8”,”mariana”,”xxxxxxxxxx”,”mariana”,””
“3”,”squaredesign”,”xxxxxxxxxxxxxxxxx”,”squaredesign”,”[email protected]”
“4”,”miedziak”,”xxxxxxxxxx”,”miedziak”,”[email protected]”
“5”,”Sarko”,”xxxxxxxxx”,”Sarko”,””
“6”,”Logitrans
Poland”,”xxxxxxxxxxxxxx”,”LogitransPoland”,””
“7”,”Amos”,”xxxxxxxxxxxxxxxxxxxx”,”Amos”,””
“9”,”Annabelle”,”xxxxxxxxxxxxxxxx”,”Annabelle”,””
“11”,”Brandfathers and
Sons”,”xxxxxxxxxxxxxxxxx”,”BrandfathersAndSons”,””
“12”,”Imagine
Group”,”xxxxxxxxxxxxxxxx”,”ImagineGroup”,””
“13”,”EduSquare.pl”,”xxxxxxxxxxxxxxxxx”,”EduSquare.pl”,””
“101”,”tmp”,”xxxxxxxxxxxxxxxxxxxxx”,”_”,”[email protected]”

Add a > outfile.csv at the end of that one-liner, to get your CSV file for that table.

Next, get a list of all your tables with

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

From there, it’s only one more step to make a loop, for example, in the Bash shell to iterate over those tables:

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

Between the do and ; done insert the long command I wrote in Part 1 above, but substitute your tablename with $tb instead.

More Related Contents:

  1. SQL split values to multiple rows
  2. How can I output MySQL query results in CSV format?
  3. How do I import CSV file into a MySQL table?
  4. Import data in MySQL from a CSV file using LOAD DATA INFILE
  5. What is the opposite of GROUP_CONCAT in MySQL?
  6. MySQL load NULL values from CSV data
  7. How to import a CSV file into a MySQL table
  8. MySQL: Split comma separated list into multiple rows
  9. Importing a csv into mysql via command line
  10. Mysql string split [duplicate]
  11. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  12. Import CSV to MySQL
  13. LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version
  14. Importing a CSV to MySQL with different date format
  15. mysql pivot query results with GROUP BY
  16. How to export data from SQL Server 2005 to MySQL [closed]
  17. Dump a mysql database to a plaintext (CSV) backup from the command line
  18. How can I correct MySQL Load Error
  19. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  20. How to join two tables using a comma-separated-list in the join field
  21. Import CSV to Update only one column in table
  22. How I can Import data from CSV to MySQL?
  23. Import CSV to Update rows in table
  24. How to import a csv file into MySQL workbench?
  25. MySQL Server has gone away when importing large sql file
  26. Problems with contenttypes when loading a fixture in Django
  27. How to restrict a column value in SQLite / MySQL
  28. How to connect locally hosted MySQL database with the docker container
  29. how to partition a table by datetime column?
  30. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)

Leave a Comment