Dump a mysql database to a plaintext CSV backup from the command line

Question

I d like to avoid mysqldump since that outputs in a form that is only convenient for mysql to read   CSV seems more universal  one file per table is fine    But if there are advantages to mysqldump  I m all ears   Also  I d like something I can run from the command line  linux    If that s a mysql script  pointers to how to make such a thing would be helpful

User · Answer

Here is the simplest command for it  mysql -h lt hostname gt  -u lt username gt  -p lt password gt  -e  select   from databaseName tableNaame    sed   s  t   g   gt  output csv   If there is a comma in the column value then we can generate  tsv instead of  csv with the following command  mysql -h lt hostname gt  -u lt username gt  -p lt password gt  -e  select   from databaseName tableNaame   gt  output csv

User · Answer

Here is the simplest command for it  mysql -h lt hostname gt  -u lt username gt  -p lt password gt  -e  select   from databaseName tableNaame    sed   s  t   g   gt  output csv   If there is a comma in the column value then we can generate  tsv instead of  csv with the following command  mysql -h lt hostname gt  -u lt username gt  -p lt password gt  -e  select   from databaseName tableNaame   gt  output csv

User · Answer

If you can cope with table-at-a-time  and your data is not binary  use the -B option to the mysql command   With this option it ll generate TSV  tab separated  files which can import into Excel  etc  quite easily     echo  SELECT   FROM table    mysql -B -uxxx -pyyy database   Alternatively  if you ve got direct access to the server s file system  use SELECT INTO OUTFILE which can generate real CSV files   SELECT   INTO OUTFILE  table csv      FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY         LINES TERMINATED BY   n  FROM table

User · Answer

The select into outfile option wouldn t work for me but the below roundabout way of piping tab-delimited file through SED did    mysql -uusername -ppassword -e  SELECT   from tablename  dbname   sed  s  t     g s      s        gt   path to file filename csv

User · Answer

In MySQL itself  you can specify CSV output like   SELECT order id product name qty FROM orders INTO OUTFILE   tmp orders csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n    From http   www tech-recipes com rx 1475 save-mysql-query-results-into-a-text-or-csv-file

User · Answer

You can use below script to get the output to csv files  One file per table with headers   for tn in  mysql --batch --skip-page --skip-column-name --raw -uuser -ppassword -e show tables from mydb   do  mysql -uuser -ppassword mydb -B -e  select   from    tn       sed  s  t     g s      s      s  n  g   gt   tn csv done   user is your user name  password is the password if you don t want to keep typing the password for each table and mydb is the database name   Explanation of the script  The first expression in sed  will replace the tabs with     so you have fields enclosed in double quotes and separated by commas  The second one insert double quote in the beginning and the third one insert double quote at the end  And the final one takes care of the  n

User · Answer

If you can cope with table-at-a-time  and your data is not binary  use the -B option to the mysql command   With this option it ll generate TSV  tab separated  files which can import into Excel  etc  quite easily     echo  SELECT   FROM table    mysql -B -uxxx -pyyy database   Alternatively  if you ve got direct access to the server s file system  use SELECT INTO OUTFILE which can generate real CSV files   SELECT   INTO OUTFILE  table csv      FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY         LINES TERMINATED BY   n  FROM table

User · Answer

In MySQL itself  you can specify CSV output like   SELECT order id product name qty FROM orders INTO OUTFILE   tmp orders csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n    From http   www tech-recipes com rx 1475 save-mysql-query-results-into-a-text-or-csv-file

User · Answer

If you really need a  Backup  then you also need database schema  like table definitions  view definitions  store procedures and so on   A backup of a database isn t just the data   The value of the mysqldump format for backup is specifically that it is very EASY to use it to restore mysql databases   A backup that isn t easily restored is far less useful   If you are looking for a method to reliably backup mysql data to so you can restore to a mysql server then I think you should stick with the mysqldump tool     Mysql is free and runs on many different platforms   Setting up a new mysql server that I can restore to is simple   I am not at all worried about not being able to setup mysql so I can do a restore   I would be far more worried about a custom backup restore based on a fragile format like csv tsv failing   Are you sure that all your quotes  commas  or tabs that are in your data would get escaped correctly and then parsed correctly by your restore tool   If you are looking for a method to extract the data then see several in the other answers

User · Answer

Check out mk-parallel-dump which is part of the ever-useful maatkit suite of tools  This can dump comma-separated files with the --csv option   This can do your whole db without specifying individual tables  and you can specify groups of tables in a backupset table   Note that it also dumps table definitions  views and triggers into separate files  In addition providing a complete backup in a more universally accessible form  it also immediately restorable with mk-parallel-restore

User · Answer

If you can cope with table-at-a-time  and your data is not binary  use the -B option to the mysql command   With this option it ll generate TSV  tab separated  files which can import into Excel  etc  quite easily     echo  SELECT   FROM table    mysql -B -uxxx -pyyy database   Alternatively  if you ve got direct access to the server s file system  use SELECT INTO OUTFILE which can generate real CSV files   SELECT   INTO OUTFILE  table csv      FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY         LINES TERMINATED BY   n  FROM table

User · Answer

You can dump a whole database in one go with mysqldump s --tab option  You supply a directory path and it creates one  sql file with the CREATE TABLE DROP IF EXISTS syntax and a  txt file with the contents  tab separated  To create comma separated files you could use the following   mysqldump --password  --fields-optionally-enclosed-by     --fields-terminated-by     --tab  tmp path to dump  database name   That path needs to be writable by both the mysql user and the user running the command  so for simplicity I recommend chmod 777  tmp path to dump  first

User · Answer

You can dump a whole database in one go with mysqldump s --tab option  You supply a directory path and it creates one  sql file with the CREATE TABLE DROP IF EXISTS syntax and a  txt file with the contents  tab separated  To create comma separated files you could use the following   mysqldump --password  --fields-optionally-enclosed-by     --fields-terminated-by     --tab  tmp path to dump  database name   That path needs to be writable by both the mysql user and the user running the command  so for simplicity I recommend chmod 777  tmp path to dump  first

User · Answer

In MySQL itself  you can specify CSV output like   SELECT order id product name qty FROM orders INTO OUTFILE   tmp orders csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n    From http   www tech-recipes com rx 1475 save-mysql-query-results-into-a-text-or-csv-file

User · Answer

Two line PowerShell answer     Store in variable  Global csv    mysql -uroot -p -hlocalhost -Ddatabase name -B -e  SELECT   FROM some table       ConvertFrom-Csv -Delimiter   t     Out to csv  Global csv   Export-Csv  C  temp file csv  -NoTypeInformation   Boom-bata-boom  -D   the name of your database  -e   query  -B   tab-delimited

User · Answer

In MySQL itself  you can specify CSV output like   SELECT order id product name qty FROM orders INTO OUTFILE   tmp orders csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n    From http   www tech-recipes com rx 1475 save-mysql-query-results-into-a-text-or-csv-file

User · Answer

If you want to dump the entire db as csv     bin bash  host hostname uname username pass password  port portnr db db name s3 url s3   bxb2-anl-analyzed-pue2 bxb ump db dump     DATE  date   Y m d  rm -rf  DATE  echo  show tables    mysql -B -h  host  -u  uname  -p  pass  -P  port    db   gt  tables txt awk  NR gt 1  tables txt  gt  tables new txt  while IFS  read -r line do   mkdir -p  DATE  line   echo  select   from  line    mysql -B -h   host   -u   uname   -p   pass   -P   port      db    gt   DATE  line dump tsv done  lt  tables new txt  touch  DATE  DATE fin   rm -rf tables new txt tables txt

User · Answer

The select into outfile option wouldn t work for me but the below roundabout way of piping tab-delimited file through SED did    mysql -uusername -ppassword -e  SELECT   from tablename  dbname   sed  s  t     g s      s        gt   path to file filename csv

User · Answer

If you really need a  Backup  then you also need database schema  like table definitions  view definitions  store procedures and so on   A backup of a database isn t just the data   The value of the mysqldump format for backup is specifically that it is very EASY to use it to restore mysql databases   A backup that isn t easily restored is far less useful   If you are looking for a method to reliably backup mysql data to so you can restore to a mysql server then I think you should stick with the mysqldump tool     Mysql is free and runs on many different platforms   Setting up a new mysql server that I can restore to is simple   I am not at all worried about not being able to setup mysql so I can do a restore   I would be far more worried about a custom backup restore based on a fragile format like csv tsv failing   Are you sure that all your quotes  commas  or tabs that are in your data would get escaped correctly and then parsed correctly by your restore tool   If you are looking for a method to extract the data then see several in the other answers

User · Answer

If you want to dump the entire db as csv     bin bash  host hostname uname username pass password  port portnr db db name s3 url s3   bxb2-anl-analyzed-pue2 bxb ump db dump     DATE  date   Y m d  rm -rf  DATE  echo  show tables    mysql -B -h  host  -u  uname  -p  pass  -P  port    db   gt  tables txt awk  NR gt 1  tables txt  gt  tables new txt  while IFS  read -r line do   mkdir -p  DATE  line   echo  select   from  line    mysql -B -h   host   -u   uname   -p   pass   -P   port      db    gt   DATE  line dump tsv done  lt  tables new txt  touch  DATE  DATE fin   rm -rf tables new txt tables txt

User · Answer

Two line PowerShell answer     Store in variable  Global csv    mysql -uroot -p -hlocalhost -Ddatabase name -B -e  SELECT   FROM some table       ConvertFrom-Csv -Delimiter   t     Out to csv  Global csv   Export-Csv  C  temp file csv  -NoTypeInformation   Boom-bata-boom  -D   the name of your database  -e   query  -B   tab-delimited

User · Answer

Check out mk-parallel-dump which is part of the ever-useful maatkit suite of tools  This can dump comma-separated files with the --csv option   This can do your whole db without specifying individual tables  and you can specify groups of tables in a backupset table   Note that it also dumps table definitions  views and triggers into separate files  In addition providing a complete backup in a more universally accessible form  it also immediately restorable with mk-parallel-restore

User · Answer

If you can cope with table-at-a-time  and your data is not binary  use the -B option to the mysql command   With this option it ll generate TSV  tab separated  files which can import into Excel  etc  quite easily     echo  SELECT   FROM table    mysql -B -uxxx -pyyy database   Alternatively  if you ve got direct access to the server s file system  use SELECT INTO OUTFILE which can generate real CSV files   SELECT   INTO OUTFILE  table csv      FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY         LINES TERMINATED BY   n  FROM table

User · Answer

If you really need a  Backup  then you also need database schema  like table definitions  view definitions  store procedures and so on   A backup of a database isn t just the data   The value of the mysqldump format for backup is specifically that it is very EASY to use it to restore mysql databases   A backup that isn t easily restored is far less useful   If you are looking for a method to reliably backup mysql data to so you can restore to a mysql server then I think you should stick with the mysqldump tool     Mysql is free and runs on many different platforms   Setting up a new mysql server that I can restore to is simple   I am not at all worried about not being able to setup mysql so I can do a restore   I would be far more worried about a custom backup restore based on a fragile format like csv tsv failing   Are you sure that all your quotes  commas  or tabs that are in your data would get escaped correctly and then parsed correctly by your restore tool   If you are looking for a method to extract the data then see several in the other answers

User · Answer

If you really need a  Backup  then you also need database schema  like table definitions  view definitions  store procedures and so on   A backup of a database isn t just the data   The value of the mysqldump format for backup is specifically that it is very EASY to use it to restore mysql databases   A backup that isn t easily restored is far less useful   If you are looking for a method to reliably backup mysql data to so you can restore to a mysql server then I think you should stick with the mysqldump tool     Mysql is free and runs on many different platforms   Setting up a new mysql server that I can restore to is simple   I am not at all worried about not being able to setup mysql so I can do a restore   I would be far more worried about a custom backup restore based on a fragile format like csv tsv failing   Are you sure that all your quotes  commas  or tabs that are in your data would get escaped correctly and then parsed correctly by your restore tool   If you are looking for a method to extract the data then see several in the other answers

User · Answer

Check out mk-parallel-dump which is part of the ever-useful maatkit suite of tools  This can dump comma-separated files with the --csv option   This can do your whole db without specifying individual tables  and you can specify groups of tables in a backupset table   Note that it also dumps table definitions  views and triggers into separate files  In addition providing a complete backup in a more universally accessible form  it also immediately restorable with mk-parallel-restore

User · Answer

You can use below script to get the output to csv files  One file per table with headers   for tn in  mysql --batch --skip-page --skip-column-name --raw -uuser -ppassword -e show tables from mydb   do  mysql -uuser -ppassword mydb -B -e  select   from    tn       sed  s  t     g s      s      s  n  g   gt   tn csv done   user is your user name  password is the password if you don t want to keep typing the password for each table and mydb is the database name   Explanation of the script  The first expression in sed  will replace the tabs with     so you have fields enclosed in double quotes and separated by commas  The second one insert double quote in the beginning and the third one insert double quote at the end  And the final one takes care of the  n

User · Answer

Check out mk-parallel-dump which is part of the ever-useful maatkit suite of tools  This can dump comma-separated files with the --csv option   This can do your whole db without specifying individual tables  and you can specify groups of tables in a backupset table   Note that it also dumps table definitions  views and triggers into separate files  In addition providing a complete backup in a more universally accessible form  it also immediately restorable with mk-parallel-restore

[mysql] Dump a mysql database to a plaintext (CSV) backup from the command line

Examples related to mysql

Examples related to command-line

Examples related to csv

Examples related to backup