Mysql Can’t Connect From Localhost -strange Behavior
Hey all,
I’ve been having some trouble creating a mysql user that can connect to the database from localhost. It’s always been a straight forward thing to do in the past, so its time for a sanity check, if you guys don’t mind.
Ok, so here’s the actual command with actual simplified password that I’m using. It’s on localhost so I don’t think it’s a security threat unless someone gets access to the box itself.
mysql> grant all privileges on ftp.* to ‘proftpd’@’localhost’ identified by
‘testpattern’;
Query OK, 0 rows affected (0.35 sec)
Here’s what it looks like when you select it from the mysql database:
mysql> select User,Host,Password from user where User like ‘proftpd’;
+———+———–+——————————————-+
| User | Host | Password |
+———+———–+——————————————-+
| proftpd | localhost | *2EE931CA39652F1ED359A3A36961511B387E74A9 |
+———+———–+——————————————-+
1 row in set (0.00 sec)
And here’s my attempt to connect with the password shown. Which is something I don’t usually do, but am doing now to demonstrate what’s going on:
[root@ops:~] #mysql -uproftpd -ptestpattern -h localhost ERROR 1045 (28000): Access denied for user ‘proftpd’@’localhost’ (using password: YES)
OK, so as I’ve said this should work!
The database I’m trying to give the user access to does also exist:
mysql> show databases like ‘ftp’;
+—————-+
| Database (ftp) |
+—————-+
| ftp |
+—————-+
1 row in set (0.34 sec)
I checked the error log for mysql and didn’t find any clues there:
[root@ops:~] #grep log /etc/my.cnf log-error=/var/log/mysqld.log
[root@ops:~] #tail /var/log/mysqld.log InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer…
150329 13:30:34 InnoDB: Waiting for the background threads to start
150329 13:30:35 InnoDB: 5.5.42 started; log sequence number 6071094973
150329 13:30:35 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
150329 13:30:35 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
150329 13:30:35 [Note] Server socket created on IP: ‘0.0.0.0’.
150329 13:30:35 [Note] Event Scheduler: Loaded 0 events
150329 13:30:35 [Note] /usr/libexec/mysqld: ready for connections. Version: ‘5.5.42’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL
Community Server (GPL) by Remi
Does anybody out there have any idea why this isn’t working?
Thanks Tim
6 thoughts on - Mysql Can’t Connect From Localhost -strange Behavior
mysql> FLUSH PRIVILEGES;
Yup! That was it. Thanks for the reminder! :)
Tim
[root@ops:~] #mysql –user=proftpd –password=testpattern -h localhost
Hi Tim,
You should keep in mind the security-related changes coming in MySQL 5..7, if you ever choose to upgrade. https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html http://mysqlserverteam.com/whats-new-in-mysql-5-7-so-far/
Repos https://dev.mysql.com/downloads/repo/yum/
Best, FC
Sorry, I erased this link by mistake while composing my reply. Here it goes.
http://mysqlopt.blogspot.com/2015/02/mysql-575-m15-out-of-box-security.html
FC
mysql> FLUSH PRIVILEGES;
Yup! That was it. Thanks for the reminder! :)
Tim
From the mySQL man pages:
If you modify the grant tables indirectly using account-management statements such as GRANT<https://dev.mysql.com/doc/refman/5.5/en/grant.html>, REVOKE<https://dev.mysql.com/doc/refman/5.5/en/revoke.html>, SET PASSWORD<https://dev.mysql.com/doc/refman/5.5/en/set-password.html>, or RENAME USER<https://dev.mysql.com/doc/refman/5.5/en/rename-user.html>, the server notices these changes and loads the grant tables into memory again immediately.
So I’m confused as to why FLUSH PRIVILEGES is necessary, because this suggests the GRANT command is doing a flush anyway.
I ask because I get this behaviour occasionally with adding mySQL users/permissions too, and I hate blithely running a command ‘because it works’ if it’s not actually the correct thing to do (though admittedly it does work).
Because we’re creating a user as well as assigning permissions maybe? So a GRANT to an existing user wouldn’t require the FLUSH PRIVILEGES?
Appreciate this is a mySQL rather than CentOS question, but it bugs me on CentOS machines, so that’s something!
Paul