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?

Chirurgeon

You can do it using mysql.exe program, try SELECT * FROM table INTO OUTFILE 'file_name'. But you should specify each table manually. – Analemma

Answer 1

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.

Evadne

Since you need to specify columns with some joins and where clauses, This answer is better to accept. – Doud

This fails if the query results exceed memory on the machine you're dumping to. Any work arounds? – Monarski

@T.BrianJones Recommend you try a new question with details of your situation...which sounds very general to any program, btw. You probably weren't looking for the obvious "increase RAM" or "try another PC with more memory." Because yes, generally programs processing and outputting data need to fit that into memory first. In my loop, at least, each mysql exits and frees resources before starting the next table in the list. – Evadne

@T.BrianJones can you select into outfile, transfer the file, then work on it from there? It should not be hard to get this into line-at-a-time processing which should use only trivial amounts of memory. – City

This doesn't seem to take into account STRING column types that have double quotes in them -- they should be escaped, otherwise it's broken. Any idea how to do that? – Lycanthropy

@Blossoming_Flower : Here is the updated regex to escape double quotes : | sed "s/\"/\"\"/;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" (Properly escape a double quote in CSV) – Lemmueu

@Olivier If I want to use this inside a command that's already surrounded by ", how could I re-write this? I'm getting confused with all the escaping – Sibie

-1: Regex looks entirely ad-hoc and not well thought out: Inconsistencies in trailing g on s// are senseless. They should ALL specify g, otherwise what's the point--every occurrence should be escaped and not just the first. s/\n//g why? sed by default doesn't even include \n in the pattern buffer. – Bathrobe

@Olivier , @Blossoming_Flower: maybe | sed "s/\"/\"\"/g;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" , add g option to double every " in the input? Ruby CSV expects such a convention. – Damarisdamarra

Here is the sed without the quotes: sed "s/'//;s/\t/,/g;s/\n//g" – Diocletian

@BrianGlaz I would consider embedding it all in your language's HERE-DOC with variables where needed, rather than attempting to re-escape that confusing yarn ball. Ruby is pretty intuitive & friendly. – Evadne

i get this warning when i try to load this csv to my MySQL DB Warning: Incorrect integer value: '"1921196975"' for column 'id' at row 1 , it seems that it add " quotes to every values how to get rid of it ? – Taitaichung

is there a way of doing so with mysqldump ? since large tables dump requires large RAM in MySQL while mysqldump has quick mode to retrieve row by row – Taitaichung

This strikes me as a terrible idea because it's doing the escaping at the wrong end. It seems much safer (though more verbose) to SELECT CONCAT('"',REPLACE(field,'"','""'),'",') each field. And you'd probably need to wrap nullable fields in an IF(field IS NULL,...,...) , too. – Playtime

Answer 2

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=','

Guizot

Remember to use -T/path to something which is writable by the mysql process. – Felting

only works if you are running mysqldump from the same machine as the db server – Templeton

if you run into mysql's secure file priv issues, do SHOW VARIABLES LIKE "secure_file_priv"; and use the folder you are told there as the output folder in your mysqldump command, if yo u cannot restart the mysql server. – Unseemly

Answer 3

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}" > /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 > ${DUMPFILE}
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Screwdriver

That's not CSV, that's tab-delimited. CSV requires escaping of commas, quoting, and so on. – Archival

@KenWilliams Thanks. I added double quotes in --fields-enclosed-by. – Screwdriver

This produces error. mysqldump: You must use option --tab with --fields-... – Ripley

I can't find the --tab option you say you added. – Bunt

The Cloud SQL docs describes how to create a mysqldump. cloud.google.com/sql/docs/mysql/import-export Some of the options may also be relevant for creating a CSV dump for bigquery like mysqldump ---hex-blob --default-character-set=utf8 – Clint

Answer 4

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

Harrovian

This seems to be the most appropriate answer in that it’s flexible and performant at the same time. – Gainly

Answer 5

This worked well for me:

mysqldump <DBNAME> --fields-terminated-by ',' \
--fields-enclosed-by '"' --fields-escaped-by '\' \
--no-create-info --tab /var/lib/mysql-files/

Or if you want to only dump a specific table:

mysqldump <DBNAME> <TABLENAME> --fields-terminated-by ',' \
--fields-enclosed-by '"' --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'

Windswept

Answer 6

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 > table.csv

Lolita

Heads up, mysqldump-to-csv turned out to contain some bugs in its simple code. So be prepared to bugfix it, or stay with a slower but stable solution... – Lolita

Answer 7

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.

Analemma

Answer 8

Here is a Python solution:

import os
import subprocess

import pymysql.cursors


def get_table_names(cursor: pymysql.cursors.Cursor, database_name) -> list[str]:
    """Returns a list of all the table names in the database"""
    with cursor:
        cursor.execute(f"SHOW TABLES FROM {database_name};")
        tables = cursor.fetchall()
    tables = [table[0] for table in tables]
    return tables


def save_clean_data(traget_directory: str, mysql_user: str, my_sql_password: str, database_name: str, host: str, cursor: pymysql.cursors.Cursor) -> None:
    """Saves the each table in the database to a csv file"""
    os.makedirs(traget_directory, exist_ok=True)
    expresion = r"s/\"/\"\"/g;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"
    for table_name in get_table_names(cursor, database_name):
        file_path = os.path.join(traget_directory, f'{table_name}.csv')
        if not os.path.exists(file_path):
            dump_command = (
                f'mysql -B -u {mysql_user} -p{my_sql_password} {database_name} -h {host}'
                f' -e "SELECT * FROM {table_name};" | sed "{expresion}" > {file_path}'
            )
            subprocess.call(dump_command, shell=True)

Quoit