Handling method for mysql forgotten root password and deleted root account

If the windows server uses our company's management assistant, for the reset method, please refer to:http://www.west.cn/faq/list.asp?unid=876

If the linux server uses the WDCP system, use the script at http://www.west.cn/faq/list.asp?unid=722 to reset.

Note: Please select the corresponding steps according to your own host system and problem.

1. Find and modify the configuration file

Modify the my.ini (windows system) or my.cnf (linux system) file,

  windows system: my.ini is in the mysql installation directory. If it is our default environment, the path is: d:\SOFT_PHP_PACKAGE\mysql\my.ini 

Open it with Notepad to edit, and add the following line under the [mysqld] section

  [mysqld]

  skip-grant-tables

  linux system: Use ssh to log in to the server, then execute mysql --help |grep my.cnf and press Enter

  [root@west9853 etc]# mysql --help |grep my.cnf

                      order of preference, my.cnf, $MYSQL_TCP_PORT,

  /etc/my.cnf /etc/mysql/my.cnf /www/wdlinux/etc/my.cnf ~/.my.cnf

  You can find the location of my.cnf, then execute vi /www/wdlinux/etc/my.cnf and press Enter, then press i to enter edit mode

  Add the following line under [mysqld]

  [mysqld]

  skip-grant-tables

  Press esc, then press : enter wq and press Enter

2. Restart MySQL Service

windows system In the Run dialog, enter services.msc, open Services, find mysql in it, then right-click and select Restart.

linux system execute service mysqld restart

3. Reset Password

windows system: Open Start-Run, enter cmd and press Enter, then in the black window that pops up, enter:

 mysql     press Enter

 use mysql;press Enter

linux system: Enter:

 mysql     press Enter

 use mysql;press Enter

(1) If the root user was accidentally deleted, use the following statement to create the root user:

insert into user set Host='localhost',User='root',Password=Password('111111'),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',Event_priv='Y',Trigger_priv='Y';

    (Note: After copying the statement, remove the carriage return and line feed characters)

(2) If you forgot the root user's password, use the following statement to modify the root password (note the version):

mysql5.1, mysql5.6 versions

update user set Password=Password('111111') where User='root';    //press Enter, and replace 111111 with the new password you need to set

flush privileges;                //refresh privileges

mysql5.7 version

update mysql.user set authentication_string = password('testpassword') where user='root';  //replace testpassword with the new password to be set

flush privileges;                   //refresh privileges

4. Restore mysql configuration

windows system:

Find the my.ini or my.cnf file, remove skip-grant-tables under the [mysqld] section, or add a # character in front of it, then save and restart the mysql service.

linux system:

vi /www/wdlinux/etc/my.cnf, use the up and down arrow keys to move and find skip-grant-tables press d twice to delete it, then enter :wq to save and exit, enter service mysqld restart to restart