1130 error resolution for local or remote connection to MySQL database under Windows

Re-install MySQL, because do not know the root password before reloading, using the password after the reload to connect MySQL data, the general report ERROR 1130:host ' localhost ' not allowed to connect to this M YsqlServer, unable to connect to database, guess user rights and password issues.

1. Log in to the MySQL database with the root user

(1) stop MySQL service , execute net stop MySQL;

(2) Locate the configuration file My.ini under the MySQL installation path,

Find [mysqld]
Input: Skip-grant-tables, save

(3) Restart MySQL service, net start MySQL;

(4) Execute mysql-uroot-p, enter, and then enter the MySQL database;

2, after the machine log in MySQL, change the "MySQL" Database in the "User" table "host", from "localhost" renamed '% '.

Mysql>use MySQL;

Mysql>select Host,user,password from user;

Mysql>update User Set host = '% ' where user = ' root ';

Mysql>flush privileges; #刷新用户权限表

Mysql>select Host,user,password from user where user= ' root ';

3. Insert a user who is logged on locally

mysql>INSERT INTO user values (' localhost ', ' root ', ' ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' Y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ', ', ', ', ', 0,0,0,0, ', ', ');

The local connection's user localhost password is empty at this time

4. Change the root password

(1) Use Set password to modify root password encountered error 1290 (HY000)

Mysql> Set password for [email protected]' localhost ' =password (' 12345 ');
ERROR 1290 (HY000): The MySQL server is running with the--skip-grant-tables option so it cannot exe
Cute this statement

(2) change the root password with update mode normal

mysql> Update user set Password=password ("123") where user= "root";

Mysql>flush privileges;

5. Quit MySQL, log out in config file:skip-grant-tables, restart MySQL service

6. Reconnect the MySQL database locally, enter the modified password, and the connection is successful.

1130 error resolution for local or remote connection to MySQL database under Windows