Dump all tables in CSV format using mysqldump

Question

I need to dump all tables in MySQL in CSV format   Is there a command using mysqldump to just output every row for every table in CSV format

User · Answer

mysqldump has options for CSV formatting   --fields-terminated-by name                   Fields in the output file are terminated by the given --lines-terminated-by name                   Lines in the output file are terminated by the given   The name should contain one of the following    --fields-terminated-by     t or        --fields-enclosed-by name     Fields in the output file are enclosed by the given   and  --lines-terminated-by    r  n  r n   Naturally you should mysqldump each table individually   I suggest you gather all table names in a text file  Then  iterate through all tables running mysqldump  Here is a script that will dump and gzip 10 tables at a time   MYSQL USER root MYSQL PASS rootpassword MYSQL CONN  -u  MYSQL USER  -p  MYSQL PASS   SQLSTMT  SELECT CONCAT table schema     table name   SQLSTMT    SQLSTMT  FROM information schema tables WHERE table schema NOT IN   SQLSTMT    SQLSTMT    information schema   performance schema   mysql    mysql   MYSQL CONN  -ANe   SQLSTMT    gt   tmp DBTB txt COMMIT COUNT 0 COMMIT LIMIT 10 TARGET FOLDER  path to csv files for DBTB in  cat  tmp DBTB txt  do     DB  echo    DBTB     sed  s      g    awk   print  1        TB  echo    DBTB     sed  s      g    awk   print  2        DUMPFILE   DB -  TB  csv gz     mysqldump   MYSQL CONN  -T   TARGET FOLDER  --fields-terminated-by     --fields-enclosed-by      --lines-terminated-by   r n    DB    TB    gzip  gt    DUMPFILE         COMMIT COUNT          if     COMMIT COUNT  -eq   COMMIT LIMIT        then         COMMIT COUNT 0         wait     fi done if     COMMIT COUNT  -gt 0   then     wait fi

User · Answer

This command will create two files in  path to directory table name sql and table name txt   The SQL file will contain the table creation schema and the txt file will contain the records of the mytable table with fields delimited by a comma   mysqldump -u username -p -t  -T path to directory dbname table name --fields-terminated-by

User · Answer

It looks like others had this problem also  and there is a simple Python script now  for converting output of mysqldump into CSV files   wget https   raw githubusercontent com jamesmishra mysqldump-to-csv master mysqldump to csv py mysqldump -u username -p --host rdshostname database table   python mysqldump to csv py  gt  table csv

User · Answer

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   mkobylecki squaredesign com     4   miedziak   xxxxxxxxxx   miedziak   miedziak mail com     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       WOBC-14 squaredesign atlassian net yoMama com    Add a  gt  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

User · Answer

This worked well for me  mysqldump  lt DBNAME gt  --fields-terminated-by       --fields-enclosed-by   quot   --fields-escaped-by       --no-create-info --tab  var lib mysql-files   Or if you want to only dump a specific table  mysqldump  lt DBNAME gt   lt TABLENAME gt  --fields-terminated-by       --fields-enclosed-by   quot   --fields-escaped-by       --no-create-info --tab  var lib mysql-files   I m dumping to  var lib mysql-files  to avoid this error   mysqldump  Got error  1290  The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing  SELECT INTO OUTFILE

User · Answer

If you are using MySQL or MariaDB  the easiest and performant way dump CSV for single table is -   SELECT customer id  firstname  surname INTO OUTFILE   exportdata customers txt    FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY       LINES TERMINATED BY   n    FROM customers    Now you can use other techniques to repeat this command for multiple tables  See more details here    https   mariadb com kb en the-mariadb-library select-into-outfile  https   dev mysql com doc refman 5 7 en select-into html

User · Answer

You also can do it using Data Export tool in dbForge Studio for MySQL   It will allow you to select some or all tables and export them into CSV format

[mysql] Dump all tables in CSV format using 'mysqldump'

Examples related to mysql

Examples related to csv