Export table to CSV in MySQL

Using the OUTFILE option, i ran the following:

--This select is for the headings
SELECT 
'id', 'created_at', 'searches_id', 'user_id', 'msisdn', 
'frequency', 'status', 'is_queue', 'mo_id', 'deleted_at', 
'restarted_at', 'last_visited_at', 'last_checked_at' 
UNION
--This select is for the data
SELECT * INTO OUTFILE '/tmp/savedtable.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM savethistable;

The Union is to allow for headings. To get a list of columns in a table do:

show columns from savethistable;

If you don’t specify a path then default path is whatever is specified as the datadir in the my.cnf configuration file.

Eg:

[dbamohsin@myserver /tmp]$ less /etc/my.cnf

################# MASTER #######################

[mysqld]

datadir=/var/site_db/master

Another way of doing this:

show variables like 'data%';