Export MySQL Query Result to CSV

One of my developer team wants me to export some SQL query result and export it to CSV so they can import it to the local MySQL server. Since the database is quite big, around 2 million rows, dumping the whole table can really impact the database performance because he request this during peak hours.

After browse around in the Internet, I found the solution in StackOverflow forum. I share it here for the knowledge base. Variables as below:

Database server: MySQL 5.0
Database name: product_system
Table: tblproduct
Fields to be exported: ProductID and Name
Export to: tblproduct.csv
Username: root
Password:  dbserverpass123$

Following command should be run in SSH environment:

$ mysql -u root -p"dbserverpass123$"  product_system -B -e "select ProductID,Name from tblproduct;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > tblproduct.csv

After that, I copied over the csv into public_html folder so it can be downloaded by my developer team.

$ cp tblproduct.csv /home/user1/public_html

Done. Simple and easy!