How to Create CSV Backups from MySQL via Command Line?

Creating CSV Backups from MySQL via Command Line

Dumping a MySQL database into a CSV backup can be achieved through various methods. Here are two approaches that can be executed from the command line:

1. Using the MySQL Client with -B Option

For tabular data, the -B option in the mysql command allows you to generate TSV (tab-separated) files. These files can be easily imported into Excel or other spreadsheet programs:

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

In this command, xxx and yyy represent the MySQL username and password, respectively.

2. Using SELECT INTO OUTFILE

If you have server-side access, you can employ the SELECT INTO OUTFILE statement to create CSV files directly:

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

This statement generates a CSV file named table.csv with the specified formatting options.

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