Howto: Use mysql or run mysql queries from shell script
Q. I have job (executed from shell script) where I need to run mysql query from a shell script. I want to run mysql from within a shell script rather than use it interactively by typing mysql -u user -p dbnane command.
How do I use mysql in Shell Scripts?
A. I recommend using Perl or php as it is easier to get data from mysql. You can easily use APIs. BASH/KSH Shell does not provide access to mysql directly.
However you can pass sql command using mysql -e swich. Following query connect as root and print mysql server uptime:
$ mysql -u root -p -e STATUS | grep -i uptime
Output:
Uptime: 4 hours 59 min 56 sec
You can add code as follows from shell script:
#!/bin/bash mysql dbnane<<EOFMYSQL SELECT * from table; EOFMYSQL
Save and execute script as follows:
$ chmod +x script.sh
$ ./script.sh
I strongly recommend using perl or php as shell provides limited access.
Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
Related Other Helpful FAQs:
- PHP not connecting to a MySQL database server
- How do I access MySQL server from the shell prompt (command line)?
- Securing MySQL server
- MySQL startup script under BSD/Linux
- What is MySQL?
Discussion on This FAQ
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
March 15th, 2007 at 10:11 am
Executing mysql queries that result in a single variable or a single column of values is very useful and straightforward:
result=$(mysql -u $user $db -sN -e “select distinct ttuState from path where state = ‘active’”)
(Note the options that suppress column names and pseudo-graphic characters.)
March 16th, 2007 at 8:35 am
Thanks for sharing this script with us!
March 26th, 2008 at 4:56 pm
Yassen.. that line right there is perfectt, thanks for that…