SQL query results into CSV file

You must have fired those select statements tons of times. So you hit a query, get your results and you are done. But what if the next day you felt like going through the results you found the other day. Simple..hit the query again. But what if the query was complex and you need the results fast. What you shud have done in the first place was to generate a CSV file out of your query result.

The way I do it and others must be doing is pretty simple.

So say your query was something like this:

mysql> select * from users where dob > ‘1981-10-07′;

To get the output of this query into a CSV file is pretty simple. Do dis….

mysql> select * from users where dob > ‘1981-10-07′
INTO OUTFILE ‘user_details.csv’ FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘n’;

Bingo..u have ur file waiting for u. In case u don’t know where that file got created…check in dis location:

$/var/lib/mysql/<ur_database>/user_details.csv

So its dat very simple. So next time just keep this in mind.

And though this is off topic, here is a way to add time to a datetime attribute in your table.

mysql> select batchid, date_add(created_at, interval ‘9:30′ HOUR_MINUTE)
from batches where created_at >= ‘2000-01-01 00:00:00′ and created_at <= ‘2000-01-02 01:00:00′ ;

Simple enough.

MySQL – Dump those tables

So how do you take backup of your MySQL database? Simple, launch SQLYog or HeidiSQL and export your database. Well its an easy solution on Windows. How would you go about it on Linux or simply put how wud you do it on command prompt. The answer is mysqldump.

Its there on your system once MySQL gets installed. Once you know what database you need to take backup of, its pretty simple then:

Lets say you have a database named workshop and you want to back it up in a file named workshop_backup.sql. Simple execute the following statement:

$ mysqldump -u root -p workshop > workshop_backup.sql

-u stands for user and -p is for password. If u have set some password for root, it will ask you to enter it once you hit enter.

In case of large database, you can gzip your dump like this:

$ mysqldump -u root -p workshop | gzip > workshop_backup.sql.gz

What if you want only specific tables from your database and not the entire database as such. Do this:

$ mysqldump -u root -p workshop payments orders > only_payment_orders.sql

This will copy only payments and orders tables from workshop database.

Whatz the use of a database dump unless you use it. So to restore the files generated in the examples above, simply run this command:

$ mysql -u root -p new_workshop < workskop_backup.sql

This will dump the entire workshop database into new_workshop database.

In case you had created gzipped file:

$ gunzip < workshop_backup.sql.gz | mysql -u root -p new_workshop

Therez one more utility called mysqlhotcopy. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It can make backup of the database or single table pretty fast, but it can be run only on the same machine where the database directories are located.

One more thing. if you are on windows and you happen to have a big dump file, restoring it might give you an error, something like MySQL is down or something similar. In that case you need to add the following line in my.ini file in your MySQL directory (generally under C:\Program Files\MySQL\MySQL Server 5.1)

max_allowed_packet=32m

Save the changes. Don’t forget to restart the mysql service or you will keep getting the error.

Now that you know how to take backup, do not forget to take one today. Happy dumping!!