MySQL from bash

It turns out I've been accessing my favourite database from bash the hard way.

I just needed to make a MySQL configuration file in my home directory and set the permissions:

echo "
[client]
user=purple_drupal6
password=metoknow
host=ord-mysql-001-sn.bananas.net.au
[mysql]
database=nz_drupal" > .my.cnf

chmod 0600 ~/.my.cnf

 So that's easy.

Now I can just call mysql as a single word.... or better still do stuff like back up a table:

t=agc_geo_set

mysqldump au_drupal $t | gzip > $t.sql.gz

then to reload

cat $t.sql.gz  | gunzip | mysql

Running sql into the database from the shell is also a joy.

Having a quick look at a query is simple, and I can page through the results with less.

echo "
SELECT *
FROM agc_geo_set
" | mysql | less

Because I'm doing this from bash I can do variable substitution and reformat the output as I like it:

start=5
finish=10
echo "
SELECT *
FROM $t
WHERE id BETWEEN $start AND $finish
" | mysql | tocsv > $t.csv

Or copy permissions from one user to another:

u1=billblogs

u2=janeayre
echo "show grants for $u1;" | mysql | sed "s/$u1/$u2/" | grep "GRANT SELECT" | mysql