Saturday, February 12, 2011

Access denied for user 'root'@'client machine name' (using password: YES)

I encounter this error frequently both at work and on the occasional home project. While attempting to connect to a MySQL server using say, MySQL Workbench, I get the following:

Access denied for user 'root'@'client machine name' (using password: YES)

To resolve this, there are three specific things you need to do. Please keep in mind that I'm a developer so please refer to a sys admin for more sophisticated solutions.
  1. Make sure MySQL is listening for incoming connections and that port 3306 is open
  2. Grant (remote) user privileges to your MySQL server
I'm using Ubuntu 10.10 (64-bit). I installed MySQL using

sudo apt-get install mysql-server mysql-client

and tested the installation using

mysql -u root -p
show databases;

In MySQL Workbench (I have 5.2.31 for Mac OS X), create a "New Connection" to "Start Querying", and provide the connection credentials (IP address of the MySQL server, username "root", and root password. You will likely see the error message described by the title of this blog.

Make sure MySQL is listening for incoming connections and that port 3306 is open

Perform steps 1 through 4 as described in this blog. I found that step 5 isn't necessary if you're just playing around and want to administer your own instance using the tools in Workbench.  Also, steps 6 and 7 aren't necessary because the default Ubuntu installation allows connections on all ports by default (!!!)

A simple test from your client computer is:

telnet [server ip address] 3306

You should see something meaningful but cryptic.  If you see "connection refused", then you will need to do step 6 above because it means the port has been disallowed by default (or your sys admin).

Grant (remote) user privileges to your MySQL server

Execute the following SQL query:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;