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?

|improve this question
  • 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 2 '11 at 14:44
  • Often it is enough to comment the accounts, that should not be used with a "#". – user11398 Sep 6 '12 at 13:49
  • I don't understand what you mean - how does this answer the question? – David LeBauer Sep 6 '12 at 14:40
21

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.

|improve this answer
  • Hey @DTest, Good Stuff in your answer !!! +1 (BTW Congrats on your mysql badge) – RolandoMySQLDBA Jul 16 '11 at 0:41
  • I was going to upvote this one, then I said Wow Dejavu. This was from several months back. – RolandoMySQLDBA Feb 15 '12 at 16:24
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.

|improve this answer
0

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.