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.
🐧 28 comments so far... add one ↓
| Category | List of Unix and Linux commands |
|---|---|
| Disk space analyzers | df • ncdu • pydf |
| File Management | cat • cp • mkdir • tree |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | NetHogs • dig • host • ip • nmap |
| OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
| Package Manager | apk • apt |
| Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
| Searching | grep • whereis • which |
| User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
| WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
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.)
Thanks for sharing this script with us!
Yassen.. that line right there is perfectt, thanks for that…
This vl more easier than all of above. Try this
mysql -uuser -hhost -ppassword -e “use mydb; show tables;” > /tmp/logs/querry-output.log
Great help. Thanks a lot Liju.
Thanks Liju
Tried out all options but only this worked:
mysql -uuser -hhost -p’password’ -e “use mydb; show tables;”
Please note the change for password: -p’pwd123′
This command would become part of a larger shell script I am working on.
Thanks for sharing script.. worked perfectly
Thanks for sharing this script this is working perfectly, but I think it execute only one query at a time.
Great job!!
Simple & Functional.
Igs
thnx Liju mathew…
good one…
Hi, I am working on automating a process of delivering some monthly reports by e-mail to a group of users. I implemented successfully multiple queries in one mysql call to the database, however I want to make it more actively such as to accept a date and a file name parameter. After I get these I programmatically do different things depending on the file name. Do I need to call mysql multiple times for each condition, or I can embed shell conditionals within mysql part? Also how would I exit a mysql process within the shell ? As of now it simply terminate the session to the server instead of getting back to the command line after execution.
Any tip would be really appreciated.
Thanks, — Alice
I am wondering the same thing I guess it automatically exits. Did you ever figure it out?
How can i flush tables ?
Jake
truncate table is the fastest way to do it in mysql.
hi All,
i need to send one report on daily basis to client by executing below command
zgrep “\*xxx\*xxx\#” XXX.log.2009-05-15 |cut -d “,” -f5|sort|uniq >111_15.txt
so i am planning to make this automated process
please help for this, requirement is as fallows
if date is 15 I need to take 14th data and need to send result to client automatically , how to right the shell script for this
Hi Sir,
You will need to use a crontab/cron if you are planning to automate your script.
hi buddies i have a pro…..
my db name is atul
and table name is explem.i wanna access my table value from the shell script
plz help me
i want to access a routing table and accsess a data given by bandwidth monitoring tool using shell scripting
Nice mysql queries in shell tutorial, thank you.
Hi everyone,
I’m trying to insert in one MySQL table the date of the server. I get the date of the server with this shell command:
date +%Y-%m-%d–%H:%M:%S
An this is my script:
fecha= date +%Y-%m-%d–%H:%M:%S
mysql rsync<Slave’);
EOFMYSQL
when i check the database, i don’t get the real date of the insert. instead of that i get “fecha”.
How i can fix this?
Thanks
Hi joan,
when you define your Variable in a script using the date command you need to add back ticks. (`)
so it should look like
fecha=`date +%Y-%m-%d–%H:%M:%S`
that should fix the problem.
I need to create an automated shell script which can execute the sql query and get the data from sql database.
mh? how do I upload an sql file
relly helpful commands
nice post its really helpful
if you want to get connect another host db table values you can use it :
query=$(mysql -h hostname -u username dbName -sN -e “select bldurl from productdetails where productname=’MAIL'”)
Thanks
FLUSH TABLES WITH READ LOCK;
lvcreate -L4G -s -n dbbackup /dev/mynew_vg/data01
mkdir -p /mnt/dbbackup
mount /dev/mynew_vg/data01 /mnt/dbbackup/
tar -cf /home/db.tar /mnt/dbbackup/mysql
Unlock the tables
UNLOCK TABLES;
any one send the procedure of rum above command using script
When i try to put statement as “user dbname; select * from table where id=’123′;”
I am getting warning as :
Warning: Using a password on the command line interface can be insecure.
Unable to get output as well.
Whereas if I use the condition without where clause i am able to get data. please suggest