MySQL: can't access root account

问题:

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?

回答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

回答2:

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

  ps -ef | grep mysql      - checks if mysql/mysqld is one of the running processes.

  pkill mysqld             - kills the daemon, if it is running.

Step 2: Run MySQL safe daemon with skipping grant tables

  mysqld_safe --skip-grant-tables &

  mysql -u root mysql

Step 3: Login to MySQL as root with no password

  mysql -u root mysql

Step 4: Run UPDATE query to reset the root password

  UPDATE user SET password=PASSWORD("value=42") WHERE user="root";
  FLUSH PRIVILEGES;

In MySQL 5.7, the 'password' field was removed, now the field name is 'authentication_string':

  UPDATE user SET authentication_string=PASSWORD("42") WHERE 
  user="root";
  FLUSH PRIVILEGES;

Step 5: Stop MySQL safe daemon

Step 6: Start MySQL daemon

回答3:

There is a section in the MySQL manual on how to reset the root password which will solve your problem.

回答4:

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 with chown/chmod commands.

After these changes, restart the mysqld daemon and login with root with command:

mysql -u root

Then change passwords or create other users for logging into mysql.

HTH