mysql tips - export csv file from MYSQL database in remote server

mysql tips - export csv file from MYSQL database in remote server

1.  Export csv file in client server directly

This is the situation that I think most beginners will have for doing development or backup of the mysql database. You have a remote mysql server to hold the data, and a local client server to develope. As solution 2 provided by MYSQL official csv export solution will export the file only on the database server (here is on remote server), that is not what we want here.

mysql -h hostname -u user -p'password' -P port dbname -e "SELECT * FROM tablename WHERE Date<='2013-07-14';" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > tablename.csv

Using variables and in shell script (which easily cause problems):
mysql -h $hostname -u $user -p$pass -P $port $dbname -e "SELECT * FROM $tablename WHERE Date<='$oldDate';" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > tablename.csv

For the sed usage, below is a reference from here

Regex Explanation:

• s/// means substitute what's between the first // with what's between the second //
• the "g" at the end is a modifier that means "all instance, not just first"
• ^ (in this context) means beginning of line
• $ (in this context) means end of line

So, putting it all together:

s/'/\'/          replace ' with \'
s/\t/\",\"/g     replace all \t (tab) with ","
s/^/\"/          at the beginning of the line place a "
s/$/\"/          at the end of the line place a "
s/\n//g          replace all \n (newline) with nothing

Note: The usage of this method should be limited by the size of the csv file. If it is huge, hundreds of M or G level, then the connection between the client and remote server should be considered. 

2. Export csv file in the mysql database server (and cp/mv to client server if you need)

This is simple and you can use the mysql official statement "SELECT * INTO OUTFILE" to do it.

mysql -h hostname -u user -p'password' -P port dbname -e

"SELECT * FROM tablename WHERE Date<='$oldDate' INTO OUTFILE '$Filename' fields terminated by ',' optionally enclosed by '"' LINES TERMINATED BY '\n';"

Pls notice that, even if you run this cmd in the client server, it will still export the csv file on the database server side. That is why if you want to export the csv file in the local client server directly, solution 1 can be used. You can use cp/mv to have the copy of the exported csv file on the local client server if you have access to the remote server.