Many a times we need to run MySQL query directly from command line to perform certain tasks like backup of MySQL database or creation of MySQL database and users, without logging into MySQL prompt. You can either run a single query quickly from command line Or you can run queries from Bash script file. Before we start things you must check with your hosting provider that you must have shell access for your account.

Lets, start with running single MySQL query from command line :

Syntax :
-----
# mysql -u [username] -p [password] -e '[mysql commands]'
-----
here,
-u : prompt for MySQL database username
-p : prompt for Password
-e : prompt for Query you want to execute

Examples :
-----
# mysql -u hoststud -p ****** -e 'select * from table' database name
-----

To check all available databases:
-----
# mysql -u hoststud -p ****** -e 'show databases'
-----

Execute MySQL query on command line remotely using -h option :
-----
# mysql -h [server IP] -u [username] -p [password] -e '[mysql commands]'
-----

To secify a particular database using -d option :
-----
# mysql -d [datbase name] -u [username] -p [password] -e '[mysql commands]'
-----

To save output in text file :
-----
# mysql -u [username] -p [password] -e '[mysql commands]' > output.txt
-----

Now, we will learn to run MySQL query from BASH script :
you can use EOF to create shell script
-----
#!/bin/bash
mysql -u [username] -p [password] << EOF
[mysql commands]
[mysql commands]
[mysql commands]
EOF

-----
save this file and you can execute this file.
Author
bhawanisingh
Views
29,783
First release
Last update
Rating
0.00 star(s) 0 ratings
Top