Export Data From MySQL Database to CSV Files

First  of all, thanks to the developer of MySQL who created mysqldump tools. By using this tool, you may be able to export data from MySQL databases to CSV files and deliminated text files. Here is the syntax:

 mysqldump -u[username] -p[password] -t -T/path/to/directory [database] --fields-terminated-by=,  

A destination path in the command above should be writeable for user mysql. You can use "--fields-terminated-by=" to change deliminated flag like comma, tab, etc. Consider that you have a database with its table like this:

 mysql> use trial;  
 Reading table information for completion of table and column names  
 You can turn off this feature to get a quicker startup with -A  
 Database changed  
 mysql> select * from moneter;  
 +----+--------+---------+  
 | id | mphone | dshell |  
 +----+--------+---------+  
 | 1 | BBBOH | P808091 |  
 | 2 | BBBIJ | P909091 |  
 | 3 | AABCO | P606052 |  
 | 4 | ABBCO | P608752 |  
 | 5 | AGHCO | P788752 |  
 | 6 | GGOUG | P102220 |  
 | 7 | GGOJG | P102343 |  
 +----+--------+---------+  
 7 rows in set (0.00 sec)  

Then by using mysqldump tools, you may dump all data above into CSV file like this:

 ngoprek# mkdir trial  
 ngoprek# chown mysql /root/trial  
 ngoprek# mysqldump -uroot -proot123 -t -T /root/trial trial --fields-terminated-by=,  
 ngoprek# ls /root/trial/  
 moneter.sql   moneter.txt  
 ngoprek# cat /root/trial/moneter.txt  
 1,BBBOH,P808091  
 2,BBBIJ,P909091  
 3,AABCO,P606052  
 4,ABBCO,P608752  
 5,AGHCO,P788752  
 6,GGOUG,P102220  
 7,GGOJG,P102343  

Restore data from CSV file to MySQL

After dumped your data from MySQL database to CSV, you'll have two files where the name of files as same as name of tables on databases. 

 ngoprek# ls /root/trial/  
 moneter.sql   moneter.txt  

To restore your data into MySQL database, you can use command mysqlimport from console. Here is the syntax:

 mysqlimport -u[username] -p[password] [db-name] -r --fields-terminated-by=, /path/to/csvfile.txt  

An option -r will replace a duplicate key. Remember that you have to use the full path to your CSV files, otherwise MySQL will complain it. Let's see how you do this:

 ngoprek# mysqlimport -uroot -proot123 trial -r --fields-terminated-by=, /root/moneter.txt  
 trial.moneter: Records: 7 Deleted: 0 Skipped: 0 Warnings: 0  
 ngoprek#