Security

What If You Forgot Root Password ?

What do you do if you have forgotten the root password for MySQL (and there is no other MySQL user with sufficient administrative privileges and a known password to restore the forgotten password)?

Fear not, for I bring you glad tidings. MySQL has thought about this possibility. The way to proceed is this: Terminate MySQL (that is, the MySQL server mysqld) and then restart it with the option --skip-grant-tables. The result is that the table with access privileges is not loaded. You can now delete the encrypted password for root, terminate MySQL, and then restart without the given option. Now you can give the root user a new password.

The following example is based on MySQL under Linux, although the same procedure is possible under other configurations (though with slight variations). In each case, we assume that you have system administrator privileges on the operating system under which MySQL is running.

The first step is to terminate MySQL:

root# /etc/rc.d/mysql stop

Under Windows you end MySQL in the Service Manager (CONTROL PANEL | ADMINISTRATIVE TOOLS |SERVICES).

In the second step you relaunch mysqld via safe_mysqld (a launch script for mysqld) with the option --skip-grant-tables. The option --user specifies the account under which mysqld should be executed.

The option --datadir tells where the MySQL databases can be found. Here the same setting as with a normal MySQL launch should be used. (The precise instruction depends on the system configuration.)

root# startproc /usr/bin/_mysqld_safe --user=mysql \ --datadir=/var/lib/mysql --skip-grant-tables

Under Windows, execute the following commands for a manual start:

C:\> cd [mysql_dir]\bin C:\[mysql_dir]\bin> mysqld --skip-grant-tables

Now you can use mysql to reset the root password both for the host name localhost and the actual computer name. (Under Windows you must execute mysql in a second command window, since the first window is blocked by mysqld.)

root# mysql -u root Welcome to MySQL monitor. mysql> USE mysql; Database changed. mysql> UPDATE user SET password=PASSWORD('new password') > WHERE user='root' AND host='localhost'; Query OK, 1 row affected (0.00 sec) mysql> UPDATE user SET password=PASSWORD('new password') > WHERE user='root' AND host='computername'; Query OK, 1 row affected (0.00 sec)

Then, you must restart MySQL so that the access database mysql can again be used. (As long as MySQL is running with --skip-grant-tables, anyone can establish a connection to the server with unlimited privileges!) Under Linux you execute the following commands:

root# /etc/init.d/mysql[d] start root# /etc/init.d/mysql[d] start