Let’s break up the travel blog with a good old-fashioned tech post. I’ve been putting in some work for The Coworking Society at the moment and part of that involved our MySQL database. I recently purchased a new MacBook and have mimicked our production environment pretty darn well. I created the required MySQL user but every time I tried to access the page I would get an unauthorized MySQL error.
I’m not sure if you’ve had this error before but it was damn annoying. Essentially it meant that any time I would try to log in I’d get rejected BUT I could log in with my other user account. What could be happening?
Well, it turns out that when you create a MySQL user within the MySQL console it doesn’t hash the password for you.
I was running a command that looked a little something like this:
SET PASSWORD FOR 'abe'@'host_name' = 'eagle';
And every time I tried to log in I would get access denied. Luckily MySQL has a bit of writing about this on their website. What you need to do when add a password is use the password method. Seems obvious, right?
I had read about creating users in MySQL and all the literature I found said that if you create a user and pass it the password when you create it then MySQL will automatically hash it for you. This is false and you need to run some code like this:
SET PASSWORD FOR 'abe'@'host_name' = PASSWORD('eagle');
The MySQL website has a section on this but it required a bit of bing-ing. You’ll also notice that on the MySQL site itself they say that you don’t need to do it when creating a user and passing in the password flag.
PASSWORD() function is unnecessary when you specify a password using the
CREATE USER or
GRANT statements or the mysqladmin password command. Each of those automatically uses
PASSWORD() to encrypt the password. See Section 6.3.5, “Assigning Account Passwords”, and Section 188.8.131.52, “CREATE USER Syntax”.”
Good luck next time you’re working with MySQL, you can read the page I was reading here: http://dev.mysql.com/doc/refman/5.1/en/access-denied.html. If you’re interested in checking out The Coworking Society and finding people in your city to share ideas and collaborate with you can sign up today.