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
There is a section in the MySQL manual on how to reset the root password which will solve your problem.
I got the same problem when accessing mysql with root. The problem I found is that some database files does not have permission by the mysql user, which is the user that started the mysql server daemon.
We can check this with
ls -l /var/lib/mysql
command, if the mysql user does not have permission of reading or writing on some files or directories, that might cause problem. We can change the owner or mode of those files or directories withchown/chmod
commands.After these changes, restart the mysqld daemon and login with root with command:
Then change passwords or create other users for logging into mysql.
HTH
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:
and after you've created your file, you can run:
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:See also:
This worked for me:
https://blog.dotkam.com/2007/04/10/mysql-reset-lost-root-password/
Step 1: Stop MySQL daemon if it is currently running
Step 2: Run MySQL safe daemon with skipping grant tables
Step 3: Login to MySQL as root with no password
Step 4: Run UPDATE query to reset the root password
In MySQL 5.7, the 'password' field was removed, now the field name is 'authentication_string':
Step 5: Stop MySQL safe daemon
Step 6: Start MySQL daemon