Export data to XLS/CSV/TEXT from MySQL

To dump all the records from a table called "users" into the file /home/sapna/users.xls as a XLS file you need to do is.

1) Go to mysql comand prompt using command

mysql -u root -p database_name

2)Then use the following SQL query:

SELECT *
INTO OUTFILE '/home/sapna/users.xls'
FIELDS TERMINATED BY ','
ENCLOSED BY ' " '
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM users;

Note that the directory must be writable by the MySQL database server. 

If it's not, you'll get an error message like this:

Can't create/write to file '/home/sapna/users.xls' (Errcode: 13)

To resolve above error, you need to do is

i)sudo gedit /etc/apparmor.d/usr.sbin.mysqld

ii)Add line at last  /home/sapna/* rw, - means read/write permission of folder where you want to write xls file

iii)And then make AppArmor reload the users. 

sudo /etc/init.d/apparmor restart/reload

Also note that it will not overwrite the file if it already exists, 

instead showing this error message:

File '/home/sapna/users.xls' already exists