How to Export MySQL Data to Plaintext CSV Backups Using Command Line Tools?

Exporting MySQL Data to Plaintext CSV Backups using Command Line Tools

Backing up database content in a convenient and universal format is essential for data protection. While mysqldump remains a popular choice, users often seek alternatives that provide more flexibility and compatibility. This article provides two command-line methods for exporting MySQL data to plain text CSV backups.

Method 1: Using -B Option

If your data does not contain binary values and you do not require a single CSV file for all tables, you can utilize the -B option of the mysql command. This option generates tab-separated (TSV) files, which are easily importable into tools like Excel:

% echo 'SELECT * FROM table' | mysql -B -uxxx -pyyy database

Method 2: Using SELECT INTO OUTFILE

For direct access to the server's file system, the SELECT INTO OUTFILE statement creates CSV files with customizable field and line delimiters. This method provides a more controlled CSV output:

SELECT * INTO OUTFILE 'table.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
FROM table

By selecting one method based on your specific requirements, you can effectively export MySQL data to plaintext CSV backups from the command line, ensuring data preservation and accessibility.

The above is the detailed content of How to Export MySQL Data to Plaintext CSV Backups Using Command Line Tools?. For more information, please follow other related articles on the PHP Chinese website!