Export Mysql Database into a CSV File

If you want to export your mysql databases into a csv file here is simple tip for you.

You need to run the following command from your terminal

mysql -u exampleuser -p letmein exampledb -B -e "select * from \`person\`;" | sed ‘s/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Here is some sample output of the above

"id","username","group","password"
"1","male","admin","5f4dcc3b5aa765d61d8327deb882cf99″
"2","newton","admin","5f4dcc3b5aa765d61d8327deb882cf99″
"3","ruchi","admin","5f4dcc3b5aa765d61d8327deb882cf99″
"4","ruchi1″,"staff","5f4dcc3b5aa765d61d8327deb882cf99″
"5","tej","staff","5f4dcc3b5aa765d61d8327deb882cf99″
"6","tej1″,"admin","5f4dcc3b5aa765d61d8327deb882cf99″

And now for the explanation:

Starting with the MySQL command.

The -u option is you need to enter the username

The -p option is you need to enter the password

"exampledb" -- Database name

The -B option will delimit the data using tabs and each row will appear on a new line.

The -e option denotes the command to run once you have logged into the database.

In this case we are using a simple SELECT statement.

Onto sed. The command used here contains three seperate 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.

filename.csv ---> Name of the file you want to export.

After running the result set through sed we redirect the output to a file with a .csv extension.