SELECT id, name, email INTO OUTFILE '/tmp/result.csv'Or you can use sed:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1
mysql -u username -ppassword database -B -e "SELECT * FROM table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csvExplanation:
username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export
The -B option will delimit the data using tabs and each row will appear on a new line.
The -e option denotes the MySQL command to run, in our case the "SELECT" statement.
The "sed" command used here contains three sed scripts:
s/\t/","/g;s/^/"/ - this will search and replace all occurences of 'tabs' and replace them with a ",".
s/$/"/; - this will place a " at the start of the line.
s/\n//g - this will place a " at the end of the line.
You can find the exported CSV file in the current directory. The name of the file is filename.csv.
However if there are a lot of tables that you need to export, you'll need a script like this:
#!/bin/bashJust be sure to change the configuration section to meet your needs.
#### Begin Configuration ####
#### End Configuration ####
MYSQL_CMD="$MYSQL -u $MYSQL_USER -p$MYSQL_PASSWD -P $MYSQL_PORT -h $MYSQL_HOST"
TABLES=`$MYSQL_CMD --batch -N -D $DB -e "show tables"`
for TABLE in $TABLES
SQL="SELECT * FROM $TABLE;"
$MYSQL_CMD --database=$DB --execute="$SQL" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > $OUTFILE
Name the file something like: export_csv.sh and be sure to make it executable. In Linux, do something like:
chmod +x ./export_csv.shIf you want to have all of the exported files in a certain directory, you could either modify the script or just make the cirectory, "cd" into it, and then run the script. It assumes you want to create the files in the current working directory.
To change that behavior, you could easily modify the "OUTFILE" variable to something like: