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,142
  • 8
  • 30
  • 34
  • I flagged this as 'not a real question' based on the answer that I have included in the update. The problem had to do with changes made to the ip address rather than having to do with the database. I am not really sure if it should be closed it or if I should provide the update as the answer. – David LeBauer Jun 02 '11 at 14:44
  • Often it is enough to comment the accounts, that should not be used with a "#". –  Sep 06 '12 at 13:49
  • I don't understand what you mean - how does this answer the question? – David LeBauer Sep 06 '12 at 14:40

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,440
  • 11
  • 78
  • 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,142
  • 8
  • 30
  • 34