Mysql Forgot root Password and Solutions to Modify root Password of Summary

1 Three ways to modify root password

Method 1: Use the SET PASSWORD command

Log in to MySQL first.

Format: mysql > set password for username@localhost = password ('new password');

Example: mysql > set password for root@localhost = password('123');

Method 2: Use mysqladmin

Format: mysqladmin -u Username-p Old Password password New Password

Example: mysqladmin -uroot -p123456 password 123

Method 3: Edit user table directly with UPDATE

Log in to MySQL first.

mysql> use mysql; 
mysql> update user set password=password('123') where user='root' and host='localhost'; 
mysql> flush privileges;

2Forget root password resolution

Here are the steps for the Windows operating system:

1. Close the running MySQL service-- > net stop mysql

2. Open the DOS window and go to the mysqlbin directory.

3. Input mysqld --skip-grant-tables Enter 。--skip-grant-tables This means skipping the permission table authentication when starting the MySQL service, noting that skip is preceded by two'-'when the DOS window cannot enter

4. Open another DOS window and go to the mysqlbin directory.

5. Enter mysql to return, if successful, an MySQL prompt will appear > .

6. Connection rights database: use mysql;.

7. Change password:

update user set password=password("123") where user="root"; (Don't forget the last semicolon) 

8. Refresh permissions (required steps): flush privileges;.

9.Exit quit.

10. Log off the system and enter again. Log in with the username root and the new password 123 you just set.

3 Solutions for mistakenly deleting root users

1. Close the running MySQL service-- > net stop mysql

2. Open the DOS window and go to the mysqlbin directory.

3. Input mysqld --skip-grant-tables Enter 。--skip-grant-tables This means skipping the permission table authentication when starting the MySQL service, noting that skip is preceded by two'-'when the DOS window cannot enter

4. Open another DOS window and go to the mysqlbin directory.

5. Enter mysql to return, if successful, an MySQL prompt will appear > .

6. Connection rights database: use mysql;.

7. Insert the root user into the user table:

 mysql> insert into user set user='root',ssl_cipher=''x509_issuer='',x509_subject='';

8. Authorize new root users:

mysql>update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y',

Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';

9. Exit: mysql > exit;Or mysql > quit

10. Restart service: net start mysql

11. Log on to root user