Retrieving Result from Remote Executable MySQL Database Query to File

This article is used to generate output from query executable which is passed through a parameter using mysql tool. Without having to directly connect to MySQL command console remotely, as long as in our local host, server, workstation has mysql-client installed, it is possible to retrieve data by passing the intended query and acquire the results that can also be redirected into a file. Below is the sample of the pattern :

username@hostname:~$ mysql -e "select * from table_name where a=xxx and left(b,2)='xx'" -u username -h xxx.xxx.xxx.xxx -p dbname > /home/user/test.csv
Enter password:
username@hostname:~$
Description : 
mysql : It is a command used to execute mysql 
-e : It is an additional parameter used to execute query, it can be assumed that the 'e' letter stands for 'execute' and it is designated to execute query. 
"select * from table_name" : It is the query used to be execute
-u : It is an additional parameter used to pass user which is going to be used
username : It is the value of username parameter and the user which is going to be used to connect to MySQL Database Server
-h : It is an additional parameter used to pass IP Address of MySQL Database Server which is going to be connected
xxx.xxx.xxx.xxx : It is the IP Address of MySQL Database Server and the value of -h parameter. 
-p : It is an additional parameter used to set interactively request password for MySQL Database Server
dbname : It is the name of the database used to be connected
> : It is the redirect output sign which is used to specify any output generated before the sign will be directed to any media specified after the sign
/home/user/test.csv : It is the location of the media which is used to store the output generated. In this case the file name is in csv format named 'test' and located in /home/user.  

Below is the sample of the execution of the above command pattern :

user@hostname:~$ mysql -e "select * from ci_sessions" -u admin -h xxx.xxx.xxx.xxx -p dbname > /home/user/test.csv
Enter password:
user@hostname:~$ 

The example is retrieved from ci_sessions table which is one of default table exist in Code Igniter Framework-based web application. Below is the output generated which is stored into file test.csv if it is opened using ‘vim’ editor as follows :

user@hostname:~$ vim /home/user/test.csv

Below is the content of the file named test.csv :

session_id      ip_address      user_agent      user_data       last_activity
80f184cf3d70d7995421cbeaead72ff6        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36              1477628529
9787235ed2e750f8c26600bb293db0f5        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36              1477630116
b095fea5e21affd38ac0b8b6bbe1b31f        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36              1477630129
842d1912301800496ee725ca4dcaf0bf        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36              1477630140
0adfd34be8a8fea4a54e566e01d75250        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36              1477630478
b246f34f8bc8eac874418bc0d5368863        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36      NULL    1477630815
eea4f038bf1063a33fd43c8aa7ec4673        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36      NULL    1477630829
f37b0cb4fc9b2ed7169f16c4df0afa3a        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36      NULL    1477631623
db9f5ba4ac09d7662d902222b64fa37c        xxx.xxx.xxx.xx   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36      NULL    1477631888
6806f0cd95ab11d6c8b1e68591d79e7e        xxx.xxx.xxx.x    Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:47.0) G      NULL    1477631987
6be2a27a6bc77bff3e7f8dfcd8833746        xxx.xxx.xxx.x   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36      NULL    1477636773
6e647951b766b7ff9e4e14111f5c83a3        xxx.xxx.xxx.x   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36      NULL    1477636794
09d040395f91cb706fa247c8a497eec5        xxx.xxx.xxx.x   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36      NULL    1477636810
7eccbe252de545d35c3eae6bb080629b        xxx.xxx.xxx.x   Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36      NULL    1477636957
f81fd792788bb0fbcfb8441a29b2e85e        xx.xxx.xxx.x    Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:47.0) G      NULL    1477651341
fb167eec8606c2a6ae0ea5dad31cbdc6        xx.xxx.xxx.x    Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:47.0) G      NULL    1477652022

Where the above output is actually reflecting all records exist in table ci_sessions.