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

by

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

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 an SQL file using the command line in MySQL?
  4. What is the opposite of GROUP_CONCAT in MySQL?
  5. MySQL load NULL values from CSV data
  6. Restoring MySQL database from physical files
  7. How to import a CSV file into a MySQL table
  8. How to export and import a .sql file from command line with options? [duplicate]
  9. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  10. MySQL: Split comma separated list into multiple rows
  11. Importing a csv into mysql via command line
  12. Mysql string split [duplicate]
  13. How to take backup of a single table in a MySQL database?
  14. Dump all tables in CSV format using ‘mysqldump’
  15. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  16. Downloading MySQL dump from command line
  17. Import CSV to MySQL
  18. How to run SQL script in MySQL?
  19. List of Stored Procedures/Functions Mysql Command Line
  20. How to remove all MySQL tables from the command-line without DROP database permissions? [duplicate]
  21. How can I correct MySQL Load Error
  22. How to connect to MySQL from the command line
  23. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  24. How to join two tables using a comma-separated-list in the join field
  25. Import CSV to Update only one column in table
  26. What is the best way to do incremental backups in MySQL?
  27. How to import a SQL Server .bak file into MySQL?
  28. Import CSV to Update rows in table
  29. How to import a csv file into MySQL workbench?
  30. Speeding up mysql dumps and imports [closed]

Leave a Comment