This is another article which is used to describe on generating file through remote query execution. In this context, an additional tool called sed is used to manipulate the content of query result so that it can be viewed as a suitable csv file.
This article has the same purpose with the other article titled ‘Retrieving Result from Remote Executable MySQL Database Query to File‘ and another one titled ‘Remote Execute MySQL Query via Command Line‘. Below is the sample pattern of command which can be executed to achieve the purpose :
user@hostname:~$ mysql -u username -h xxx.xxx.xxx.xxx -p -B -e "select * from ci_sessions" -p dbname | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > /home/username/tester-file.csv Enter password: user@hostname:~$ vim tester-file.csv Description : mysql : It is a command executed to call mysql utility -u : It is an additional parameter to specify username to be used to connect to MySQL Database Server username : It is the value of -u parameter, in this context the value is username. It is used as an additional MySQL Database Server connection parameter. -h : It is an additional parameter to specify host to be used to connect to MySQL Database Server xxx.xxx.xxx.xxx : It is the value of -h parameter, in this context the value is xxx.xxx.xxx.xxx. It is used as an additional MySQL Database Server connection parameter. -p : It is used as an additional parameter for activating interactive password request to log in to MySQL Database Server -B : It is used as an additional parameter for printing the result of the query into whatever output specified in a column separator using tab as the separator with each line as one row of record. With this option mysql doesn't use the history file. It can also specified as --batch -e : It is used as an additional parameter to pass query which is going to be executed. It can also assume that the 'e' itself stands for execute in order to be easily remembered.
For an example :
In the above pattern, it is also act as a real example. It is a command used to get all records from ci_sessions table which is a default table exists in Code Igniter Framework application. The query itself is specified after -e parameter, ‘select * from ci_sessions’.
The other patterns are clearly explained including, -u additional parameter which is accompanied with the suitable user as the value to connect to MySQL Databse Server. Another one is -h additional parameter which is followed with the correct IP Address acts as the identifier of MySQL Database Server location and it is specified if it exists remotely.
Another additional parameter which is also important is -p where the primary usage is to make MySQL Database Server interactively provide password when it is trying to be accessed.
There is a pipe sign, after mysql command, specified with ‘|’ sign. It is used to redirect output of the command specified before the ‘|’ sign to be directed to any command or media defined after the ‘|’ sign.
The command after the ‘|’ sign is sed command. The ‘sed’ itself stands for stream editor. This is detail of sed command :
s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g
This is the general pattern of substitute command :
s/old_pattern/new_pattern/
The first part : s/’/\’/;
s stands for substitute, the above part means replace the first part with the second part. Replace ‘ with \’.
The second part : s/\t\”,\”/g;
s stands for substitute, the above part means replace the first part with the second part. Replace all \t which is character symbolizes tab with \”,\” or “,”. In this context, there is another additional parameter which is ‘/g and it stands for global. The /g or global context means replace every character in the whole part of output.
The third part : s/^/\”/;
s stands for substitute where the above part means place \”” or ” in the begining of the line, character ^ is a regular expression character identifying the beginning of the line.
The fourth parth : s/$/\”/;
s stands for substitute where the above part means place \”” or ” in the end of the line. the character $ is a regular expression character identifying the end of the line.
The fifth part : s/\n//g
s stands for substitute where the above part means replace \n or new line character with nothing for every sign met in the whole part of output.
The sample case for example is taken by retrieving the content of table ci_sessions available in Code Igniter Framework web-based application.
Below is the output of the file if it is normally executed without sed command to remodel it into a CSV display format. This is the command executed :
mysql -uroot database_name -p -B -e "select * from ci_sessions into outfile '/var/lib/mysql-files/test-ci-sessions.csv' fields terminated by ',' enclosed by '\"' lines terminated by '
Below is the content of test-ci-sessions.csv file located in /var/lib/mysql-files :
session_id ip_address user_agent user_data last_activity 68425dd410a8a22d92543aaba4c130db xxx.xxx.xxx.xxx Mozilla/5.0 (Windows NT 6.1; WOW64; rv:29.0) Gecko 1402044289 cc3a2c3cb30124070e6c7d178c1a5e3e xx.xxx.xx.xx Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww 1402516491 211260689f0fa8eea1c847b5c8a47a1f xx.xxx.xx.xx Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS 1403638946 74e61fd3bd80bb12fff1ec64f9b44d0e xx.xxx.xx.xx Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS 1399906204 6337d725db0d31b1f0c8cdae2817ac98 xx.xxx.xx.xxx Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww 1398496769 75c09573148a85611d22290a4ecbea1b xx.xxx.xx.xx Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww 1400689934 9e1d9c148abab728ef79388540172b3b xx.xxx.x.xx Mozilla/5.0 (Windows NT 5.1; rv:29.0) Gecko/201001 1401098090 bbebe6d0e4bbfd1fc617e043956bc217 xx.xxx.xx.xx Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww 1399007426 b8c5afa8d5375aaa7d6467191771bf59 127.0.0.1 Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G 1405167152 12abe207cdc22751b39ccc27b06ba9cf 127.0.0.1 Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G 1405167164 bb4f0c298644597bda9e6caa5cdef364 127.0.0.1 Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G a:13:
On other hands, below is the output of the display if it is modified with sed to be able to construct it into a CSV display format. This is the command executed :
mysql -uroot -p -B -e "select * from ci_sessions" database_name | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > /home/user/testing-sample.scv
Below is the content of file ‘testing-sample.csv’ located in /home/user :
"session_id","ip_address","user_agent","user_data","last_activity" "68425dd410a8a22d92543aaba4c130db","xxx.xxx.xxx.xxx","Mozilla/5.0 (Windows NT 6.1; WOW64; rv:29.0) Gecko","","1402044289" "cc3a2c3cb30124070e6c7d178c1a5e3e","xx.xxx.xx.xx","Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww","","1402516491" "211260689f0fa8eea1c847b5c8a47a1f","xx.xxx.xx.xx","Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS","","1403638946" "74e61fd3bd80bb12fff1ec64f9b44d0e","xx.xxx.xx.xx","Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS","","1399906204" "6337d725db0d31b1f0c8cdae2817ac98","xx.xxx.xx.xxx","Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww","","1398496769" "75c09573148a85611d22290a4ecbea1b","xx.xxx.xx.xx","Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww","","1400689934" "9e1d9c148abab728ef79388540172b3b","xx.xxx.x.xx","Mozilla/5.0 (Windows NT 5.1; rv:29.0) Gecko/201001","","1401098090" "bbebe6d0e4bbfd1fc617e043956bc217","xx.xxx.xx.xx","Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww","","1399007426" "b8c5afa8d5375aaa7d6467191771bf59","127.0.0.1","Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G","","1405167152" "12abe207cdc22751b39ccc27b06ba9cf","127.0.0.1","Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G","","1405167164" "bb4f0c298644597bda9e6caa5cdef364","127.0.0.1","Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G","a:13:
As shown in the above output, it is obviously based on the sed command pattern, each field is enclosed with “” and each field is separated with ‘,’ (comma sign).
One thought on “Export MySQL Query Result to CSV via Command Line”