mysql .my.cnf not reading credentials properly?

Question

I can connect to mysql using the following line:

mysql -u myusername -p
(enters password into terminal)
>>>Welcome to the MySQL monitor.  Commands end with ; or \g.
>>>Your MySQL connection id is 51
>>>Server version: 5.6.10-log Source distribution

Here is my .my.cnf in my home directory (not /etc/my.cnf):

[client]
user = myusername
password = mypassword
host = localhost

There also appears to be a client section in my /etc/my.cnf:

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port     = 3306
socket      = /tmp/mysql.sock

However when I just type "mysql" into the terminal, I get:

ERROR 1045 (28000): Access denied for user 'myusername'@'localhost' (using password: YES)

What is wrong with my my.cnf?

ALso, it has nothing to do with anon user as mentioned here: MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

Here is the result of a suggested query:

>>>mysql --print-defaults
>>>mysql would have been started with the following arguments:
>>>--port=3306 --socket=/tmp/mysql.sock --no-auto-rehash --user=myusername --password=mypassword --host=localhost

this is the .my.cnf file in my home directory, not the file in /etc/my.cnf. Should this stuff be in that one? — Tommy, Oct 10 '13 at 15:55
No, but if you get a message like `'myusername'@'mypassword'`, it seems like you have entered your password after `host=`, and probably your host after `password=`. if should be `'myusername'@'localhost'`... — Wrikken, Oct 10 '13 at 16:06
Aight, then it should work AFAIK... What happens if you run `mysql --defaults-file=/your/home/dir/.my.cnf`, still the same? And what happens if you include the `socket` config in your own file? — Wrikken, Oct 10 '13 at 16:11
same error when pointing to the file. Will try to remove all that stuff in /etc and see what happens. — Tommy, Oct 10 '13 at 16:15
Did you add the `socket = /tmp/mysql.sock` to your `.my.cnf`, and is that indeed the socket of your server? Also, the output of `mysql --print-defaults` might shed some light on it.. — Wrikken, Oct 10 '13 at 17:33
Well, `mysql --print-defaults` is my final hope then, let's see what it says.. — Wrikken, Oct 10 '13 at 17:34
Is the space between `--` & `host` a copy/paste error, or really there? — Wrikken, Oct 10 '13 at 18:18
OK, if that's not it... I blame invisible stray characters _somewhere_ (or a wrong password). Either examine it in a hex editor, or dump the file, type it in again, does that work? — Wrikken, Oct 10 '13 at 18:24
Hm, one final guess came to me: if there are come characters like `=` & others in your password... you need to _quote_ the password, does that help? — Wrikken, Oct 10 '13 at 20:13
That was it! My passwords are randomly generated and contain lots of non a-z0-9 characters, so quoting it in mysql solved the problem. Please post that as an actual answer so I can accept it and give you my 50 rep points. — Tommy, Oct 15 '13 at 15:06
Ah, nice ;) I'll add that as an answer (with the rest of possible troubleshoots as well). — Wrikken, Oct 15 '13 at 15:11

score 14 · Accepted Answer · answered Oct 21 '13 at 22:07

14

As @Wrikken said in the comments, it will work if you quote your password and the password contains characters such as = or ;.

answered Oct 21 '13 at 22:07

Andy

49,085
60
166
233

score 4 · Answer 2 · edited Aug 19 '14 at 02:41

4

Your problem is that you are missing the quotes around the password.

[client]
user = myusername
password = "mypassword"
host = localhost

http://dev.mysql.com/doc/refman/5.7/en/option-files.html

Search for "Here is a typical user option file:" and see the example they state in there.

edited Aug 19 '14 at 02:41

Tommy

12,588
14
59
110

answered Jul 25 '14 at 17:06

mimoralea

9,590
7
58
59

Yes this was Wrikken's answer in the comments back in Oct, and that worked. (Andy later posted the exact same thing) – Tommy Jul 27 '14 at 21:32

mysql .my.cnf not reading credentials properly?

2 Answers2

Linked