Dump all tables in CSV format using 'mysqldump'

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 -p password 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.

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

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

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

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'

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

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.

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)