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



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

echo "Executing the following query"
echo $qry

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


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


Anonymous said...

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

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 ;)


fabjoa said...

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


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

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

echo 'John has been added as a new employee'

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