MySQL Change root password

Three ways to change the root password

Method 1: Use the Set password command

First log in to MySQL.
Format:mysql> set password for user name @localhost = password (' new password ');
Example:mysql> set password for [email protected] = 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 the user table directly with update
First log in to MySQL.
mysql> use MySQL;
mysql> Update user set Password=password (' 123 ') where user= ' root ' and host= ' localhost ';
mysql> flush Privileges;

two forgot root password solution

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 Mysql\bin directory.

3. Enter Mysqld--skip-grant-tables carriage return. --skip-grant-tables means to start the MySQL service skip the authorization table authentication, note that Skip is preceded by two "-", the DOS window cannot be entered

4. Open a DOS window and go to the Mysql\bin directory.
5. Enter MySQL return and, if successful, the 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. Quit quit.

10. Log out of the system and then enter, using the username root and the new password you just set 123 to log in.

Three mistakenly delete the root user's solution

1. Close the running MySQL service -->net stop MySQL
2. Open the DOS window and go to the Mysql\bin directory.

3. Enter Mysqld--skip-grant-tables carriage return. --skip-grant-tables means to start the MySQL service skip the authorization table authentication, note that Skip is preceded by two "-", the DOS window cannot be entered

4. Open a DOS window and go to the Mysql\bin directory.
5. Enter MySQL return and, if successful, the MySQL prompt > will appear.
6. Connection rights database: use MySQL;.

7. Insert the root user into the users table:

mysql> INSERT INTO user set user= ' root ', ssl_cipher= ' x509_issuer= ', x509_subject= ';

8. Authorize the new root user:

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 the service:

net start MySQL

11. Log in to the root user

MySQL Change root password