Thursday, June 12, 2008

Execute mysql from bash script


From mysql prompt:


$ /usr/bin/mysql -u root

...
...

mysql> select * from NMS.main;
+------+-------+
| id | data |
+------+-------+
| 1 | slow |
| 3 | pizza |
| 4 | bash |
| 6 | awk |
+------+-------+
4 rows in set (0.00 sec)

mysql> select id,data from NMS.main
-> where id in (3,4);
+------+-------+
| id | data |
+------+-------+
| 3 | pizza |
| 4 | bash |
+------+-------+
2 rows in set (0.00 sec)



Now if we have to automate the same stuffs inside a bash script, this is how we can achieve that.


$ cat execmysql.sh

#!/bin/sh

ids="3,4"
table="NMS.main"

qry="select id,data from $table where id in ($ids)"

echo "Executing the following query"
echo $qry

/usr/bin/mysql -u root << eof
$qry
eof


Executing:


$ ./execmysql.sh
Executing the following query
select id,data from NMS.main where id in (3,4)
id data
3 pizza
4 bash

8 comments:

kon said...

Small UTF-8 conversion script I came up with some time ago for mysql 5.x+ databases:


#!/bin/bash
P=password
D=database
U=username
mysql -u$U -p$P <<< 'alter database '$D' default character set utf8;'
mysql -u$U -p$P -D$D <<< 'show tables;' | while read x; do mysql -u$U -p$P\
-D$D <<< 'alter table '$x' convert to character set utf8;'; done


Of course one can use any other encoding too.

Mixa said...

Thanks for small and concise post ;)

PUT ANY said...

thank you from morrocco ;)

PUT ANY said...

thank you for your post ;)

Jamal

fabjoa said...

Nice! My 2 cents contribution to catch an eventual error, let's say a duplicate entry:

#!/bin/bash

mysqlupdate=`(mysql -u $user -p$password -e "INSERT INTO company.employees VALUES (NULL,'John')") 2>&1`

if [[ "$mysqlupdate" =~ 'ERROR 1062' ]]
then
echo "There is already a employee called John. There cannot be two Johns in the same company says a new corporate rule"

else
echo 'John has been added as a new employee'
fi

shakya said...

thanks for the quick tip.Quite useful:)

gazza said...

Very useful!
I was struggling with mysql not liking my -e $sqlcmd, and your answer means i don't have to resort to exporting to a file either.

gazza said...

Very useful,
mysql -e $sqlcmd
wasn't working and this resolved it without having to resort to creating a file.

© Jadu Saikia www.UNIXCL.com