Zero logo

MySQL - Restore password

There are two methods for restoring the MySQL root password: by a command window or UniController. The better method is to use UniController which is fully automated. The other method requires running two command windows and a certain amount of typing and editing.

Restore MySQL root user password using UniController

Restoring the MySQL root password is fully automated just click the following sub-menu:

MySQL > Restore default password (root)

You can run restore password with or without the MySQL server running. In either situation the MySQL server is started or re-started with no grant tables and the password restored, after restoration the MySQL server is stopped.

Restore MySQL root user password using command window

To restore the MySQL root user password, the MySQL server requires restarting with "skip grant tables" option set. This bypasses any security, allowing access to the server. A new password is set and the grant tables updated. The following method uses two command console windows and task manger.

Kill MySQL process

If the MySQL server is running, the process must first be stopped. Open windows task manager by pressing ctrl, alt and del keys together. In the window that opens, click on the Process tab (default); scroll up or down and locate the file mysqld_z.exe. Right click on this and select End Process; click Yes to confirm kill process.

Restart MySQL server skip grant

The MySQL server requires restarting from a command console window with "skip grant tables" option set as follows:

Start MySQL Server:
  1. Start a command window: Click Server Console button
  2. Type the following into the command window:
    mysqld_z --skip-grant-tables --user=root
  3. A flashing cursor indicates the server is running.
    Minimise this window (do not close it).
 

The dialogue in the command window looks similar to this:

C:\UniServerZ>mysqld_z --skip-grant-tables --user=root
_

Set Password and Update grant tables

To set a new password (root) and update grant tables proceed as follows:

  • Start a second command window: Click Server Console button
  • Start a mysql prompt by typing: mysql --user=root
  • At the mysql prompt type following lines:
    • USE mysql;
    • UPDATE user SET password=password("root") WHERE user="root";
    • UPDATE user SET password=password("root") WHERE user="pma";
    • flush privileges;
    • exit;
  • Finished.

The dialogue in the command window looks similar to that on the right.

Note: Both user root accounts have their passwords changed. 

C:\UniServerZ> mysql --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE mysql;
Database changed
mysql> UPDATE user SET password=password("root") WHERE user="root";
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE user SET password=password("root") WHERE user="pma";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

mysql> exit;
Bye

C:\UniServerZ>

Kill process and restart

  1. Kill the process mysqld_z ( see the section above)
  2. Close both command windows.
  3. Close UniController.
  4. Edit file C:\UniServerZ\htpasswd\passwd.txt Replace existing password with root default password
  5. Start UniController.
  6. Start the servers using UniController
  7. Click MySQL Console. If the MySQL Prompt is displayed, it confirms the update was successful.
  8. At the MySQL prompt type: Exit;

Related topics

MySQL Prompt.