How to manage several MySQL accounts

Here is something that I wish I had known a while ago.

So, I had my .my.cnf filled with the information for the login that was mine, which I used for test. Eventually, I started to work with a production database, and was given one login for general CRUD (generally for the web interface, but useful for day-to-day work as well) and one for creating and modifying tables.

I needed to be able to be in multiple ones at a time, which meant I couldn't just copy a config file with all the information about one setup to .my.cnf, then copy another when I needed that.

My initial take on the solution was to make a script and then an alias with all the correct login information (including the password), then call that when I wanted to get into the admin account. This puts the keys to the database into the process table, which is stupid and wrong and dangerous, but I didn't know a better way.

This morning, I thought to ask the DB StackExchange. Of course, I should've searched more thoroughly first. Here is what a sample .my.cnf would look like:

[clienttest]
host        = server.university.edu
user        = test
database    = test
password    = abc123

[clientprod]
host        = server.university.edu
user        = production
database    = production
password    = abc123

[clientadmin]
host        = localhost
user        = admin
database    = production
password    = abc123

[mysql]

prompt='mysql [\u][\d][\h]>'

You then start mysql by typing mysql --defaults-group-suffix=whatever, where the config says [clientwhatever].

That can, of course, be a [client] setting, so just typing mysql gets you into a default space. Adding aliases for easier typing is also useful.

alias test_db="mysql --defaults-group-suffix=test "
alias prod_db="mysql --defaults-group-suffix=prod "
alias admin_db="mysql --defaults-group-suffix=admin "

I think that prompt is too verbose, so mine is cut down to mysql [\u]>, but knowing which account you're in keeps you from accidentally killing something important.

And, of course, in the tradition of other pessimization removers I've blogged, it is good to have an alias to get into your .my.cnf.

alias mycnf="vi ~/.my.cnf"