MySQL: can't access root account

Question

I'm running MySQL 5.x on my local Windows box and, using MySQL administrator, I can't connect to the databases I created using the root account. The error I get is:

MySQL Error number 1045 Access denied for user 'root'@'localhost' (using password: YES)

I can't recall changing root account credentials, but I know I tried to give other computers on the LAN access to the db by adding their IPs. One thing I did for one of the IPs was to specify access to the account 'root' instead of root, i.e. I surrounded root with single quotation chars. All using MySQL administrator. Could this be the reason why i can't login using root?

Also, is there a way to create a new or reset the root account? As previously mentioned, I have full access to my box.

See these questions

• How to change MySQL root password to default?
• How do I retrieve my MySQL username and password?
• How do I change the password of the root user in MySQL?

Answer 1

You can use the init files. Check the MySQL official documentation on How to Reset the Root Password (including comments for alternative solutions).

So basically using init files, you can add any SQL queries that you need for fixing your access (such as GRAND, CREATE, FLUSH PRIVILEGES, etc.) into init file (any file).

Here is my example of recovering root account:

echo "CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';" > your_init_file.sql
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;" >> your_init_file.sql 
echo "FLUSH PRIVILEGES;" >> your_init_file.sql

and after you've created your file, you can run:

killall mysqld
mysqld_safe --init-file=$PWD/your_init_file.sql

then to check if this worked, press Ctrl+Z and type: bg to run the process from the foreground into the background, then verify your access by:

mysql -u root -proot
mysql> show grants;
+-------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' |

See also:

• No Password – No Problem at Everything MySQL
• Bug #28331: Unclear error message when CREATE USER fails due to duplicate key