Remote Execute MySQL Query via Command Line

Posted on

This article consists explanation to connect to a certain database remotely without having to log in to the remote MySQL command console.

It can be done by adding a parameter which actually only passing the query to MySQL Database Server without having to connect to the MySQL console.

This is an example of query executed to select the number of record exists in table be_users which is one of the default table provided in Code Igniter Framework version 1.7.1.

The command pattern of query execution without having to log in to MySQL Command Console is shown below :

mysql -e "query" -u username -h server_ip_address -p db_name > /location_or_path_of_file/file_dump

Description : 
mysql : It is a command used to call mysql database server
-e : It is an additional parameter used to execute query. It can be considered that the 'e' itself stands for 'execute'. 
"query" : It is 
-u : It is an additional parameter used to pass user argument
username : It is the value of -u parameter, it can be any value but in this context, it is 'username' for an example
-h : It is an additional parameter used to pass host argument
server_ip_address : It is the value of -h parameter, it can be any value but in this context, it is 'server_ip_address'. Off course the format can be like 192.168.1.99, etc. 
-p : It is an additional parameter used to pass password argument 
db_name : It is an argument in a value of database name which is going to be accessed. In this context the name of the database is db_name
> : It is a redirect output sign which is redirecting any output generated from any command before the '>' sign to be passed to any media specified after te '>' sign
/location_or_path_of_file/file_dump : It is an argument used to specify file created to store the output generated before the '>' sign. 

Below is the execution command on the real situation for an example :

user@hostname:~$ mysql -e "select count(*) from be_users" -u dbuser -h xxx.xxx.xxx.xxx -p dbname > /location_or_path_of_file/file_dump
Enter password:
user@hostname:~$ 

After executing the above command, there will be a file named file_dump. Below is the content of the file which is viewed by executing the following command :

user@hostname:~$ vim /location_or_path_of_file/file_dump

The content of file_dump in the above execution command is done using ‘vim’ editor as shown below :

count(*)
594

So, executing query without having to connect to MySQL command console directly by sending parameter in form of query can be done as shown in the above steps. The file named ‘file_dump’ generated will store the output retrieved from the successful query execution in MySQL Database Server.

4 thoughts on “Remote Execute MySQL Query via Command Line

Leave a Reply