15

I have a .my.cnf file in my home directory on my desktop that includes:

[dbid]
user = myusername
password = mypassword
database = dbname
host = server.location.com

If I recall correctly, before upgrading from Ubuntu 10.04 to 11.04, I was able to use the command 

mysql dbid

To connect directly to the database

But today I get this error:

ERROR 1049 (42000): Unknown database 'dbname'

Have I done something wrong?

David LeBauer
  • 3,162
  • 8
  • 32
  • 34

3 Answers3

27

I'm not sure how your previous .my.cnf used to work, and I actually have never used these files before (mainly because I didn't know about them). So after a bit of research, I found this link and came up with the following ~/.my.cnf that worked for me:

[clientdbid]
password = mypass
database = dbname
host = server.location.com

and the command that reads it:

mysql --defaults-group-suffix=dbid

A couple things to point out (highlights from the article linked):

  • group has to be preceded by 'client' to be read by mysql
  • has to go after any [client] groups, otherwise it will be overridden

I tested this on mysql 5.5 on a Mac, worked great. And now that I know about them, I will use them!

UPDATE After I set this up, I realized that the command line mysql --defaults-group-suffix=dbid was a little hefty. So as added bonus, assuming you're running Linux/Mac/Etc do this:

echo 'alias mysql_dbid="mysql --defaults-group-suffix=dbid"' >> ~/.profile

Where dbid is the name of your suffix group.

Derek Downey
  • 23,568
  • 11
  • 79
  • 104
1

As an update to Derek Downey's update, this is how I use a common ~/.my.cnf file across different Linux computers, choosing the suitable section according to hostname automatically:

echo 'alias mysql="mysql --defaults-group-suffix=$HOST"' >> ~/.bash_aliases

After that, all I have to type on the command line is mysql.

Christian Pietsch
  • 111
  • 1
0

My database admin helped me uncover the problem, and so here is the solution to the original issue that prompted this question:

The issue (as I understand it) was that the database only allows access to computers based on specific IP addresses. My IP address had changed when I got a new computer, and I had different passwords for the accounts that had been set up with the different IP addresses, in the words of my database administrator

I had a few different usernames in there for you based on user@olddesktopip.edu, user@newdesktopip.edu, and your IP address. The passwords weren't the same for them all which caused confusion, it was my fault for the trouble.

David LeBauer
  • 3,162
  • 8
  • 32
  • 34