Inchoate Curmudgeon

If you have groups defined in your .my.cnf file so that your scripts don’t need to specify database connections directly, such as from R or perl scripts, you may wish to use these group definitions for mysql scripting from bash.

Unfortunately, there doesn’t seem to be any way to tell mysql to load login information from a group specified in the ~/.my.cnf file, although you can specify a “defaults file” to use with this syntax: mysql --defaults-file=<my special file> where "<my special file>" is a full path to a file that contains a [client] section that the mysql command will use.

However, if you use this mechanism, you would need to proliferate copies of user names, passwords and connections in multiple files for use by various tools, in my case both R and mysql. If there is a way to do this directly from a mysql command line, I have been unable to locate it.

Here then is a trick that will coerce the uncooperative mysql command to load the defaults from the specified [group] in the ~/.my.conf file. It works by filtering the ~/.my.conf file for the specified group, replacing the arbitrary group name with a [client] section into an anonymous pipe that mysql will read as the --defaults-file parameter

In my case, I have a group named [rss2twitter] that specifies login information I’m using for an R script to connect via the RMySQL package. I want to use this same information to connect from arbitrary bash scripts.

mysql --defaults-file=<(cat ~/.my.cnf | echo-group.pl "rss2twitter")

This is the echo-group.pl perl script:

#!/usr/bin/env perl
# mode=0 is initial
# mode=1 is reading the section of interest
$mode=0;
# loop through the lines in the config file
while(<STDIN>){
        # this looks out of order, but what gets executed is based on the mode
        # if we are reading the section of interest and hit another section, exit
        if(/^\[/ && $mode == 1) {exit;} 

        # if we are reading the section of interest, echo it
        if($mode ==1) {print $_;}

        # if we hit the section of interest, matched on the first command line argument, change to mode=1
        if(/^\[$ARGV[0]\]/) {print "[client]\n"; $mode=1;}
}

And here is the example ~/.my.cnf file that is used by the perl script. The “garbage_entries” are for testing and represent other sections you may have in your ~/.my.cnf file.

[garbage_entry1]
user = w00t
database = junk
password = junk
host = george

[rss2twitter]
user = root
database = rss2twitter
password = secret
host = naiad

[garbage_entry2]
user = w00t
database = junk
password = junk
host = trixie