How to Create CSV Database Backups from the Command Line?

CSV Database Backups via Command Line

While mysqldump provides a convenient backup option for MySQL databases, its output format may not be universally accessible. This article explores alternative methods for creating plaintext CSV backups from the command line, offering greater flexibility.

Using MySQL's '-B' Option

For table-by-table exports, MySQL's '-B' option can generate TSV (tab-separated) files. These files can be easily imported into various applications such as Excel. To use this method:

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

Replace 'table' with the desired table name, 'xxx' with the username, 'yyy' with the password, and 'database' with the database name.

Using 'SELECT INTO OUTFILE'

This method requires direct access to the server's file system and generates true CSV files. To use 'SELECT INTO OUTFILE':

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

Remember to adjust the file path in 'table.csv' and ensure that the necessary permissions are in place for the file creation.

The above is the detailed content of How to Create CSV Database Backups from the Command Line?. For more information, please follow other related articles on the PHP Chinese website!