Do not restart mysqld change root password

Ever found yourself working on a MySQL server where Root's password is unavailable? It has happened to me a few times, always because the person who set up the DB left the place long ago, and this Informati On is not documented anywhere.

If you had root access to the OS, MySQL lets restart the server bypassing access checks, using the Skip-grant-tables option, which requires a service restart.

However, if you need-regain root access and want to minimize service impact, you can take advantage of the the-the-the-serv ER responds to SIGHUP signals and the fact that access credentials is stored on a MyISAM table.

MySQL uses a few tables to store credentials and privileges for the users (you can find more on this here), but for the this PR Ocedure, we only need to work with the Mysql.user table.

Specifically, we'll work with the columns ' user ', ' host ' and ' password ' from this table.

Here's an example of about this can look on a server:

 mysql> Select User,host,password from mysql.user;+-----------+-----------+-------------------------------- -----------+| user | Host | Password |+-----------+-----------+-------------------------------------------+| Root | localhost | *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | Root | MySQL | *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | Root | 127.0.0.1 | *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | Root | :: 1 |           *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | |                                           localhost |           || |                                           MySQL | || DBA | %         | *4fc8d8270bec4364c78799065996f5306139b412 | | ReadWrite | localhost | *202273e75bd11d06fbe2f057bfa1b1bb2b26549c | | readonly | localhost | *fc69e042ce30d92e2952335f690cf2345c812e36 |+-----------+-----------+-------------------------------------------+ 9 Rows in Set (0.00 sec) 

To start, we'll need a copy of this table to a database where we can change it. On this example server, this means the ' test ' schema, as the ' readwrite ' user have write privileges on it. Even if Root ' s password is lost, you can typically get a less privileged MySQL account by checking the applications that Connects to this database. If for some reason the case, you can achieve the same results by copying this table to another server, and cop Ying it back after the necessary changes has been made.

The following command happen on the DataDir:

[email protected] mysql]# CP mysql/user.* test/; Chown Mysql.mysql test/user.*

Please don ' t overwrite a existing table when doing this! Rename the copied files as needed instead ...

Now you should is able to access (and write) to the This table:

[[email protected] mysql]# mysql-ureadwrite-p testenter password:reading table information for completion of table  and column namesyou can turn off this feature to get a quicker startup With-awelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 34Server version:5.6.16 mysql Community Server (GPL) Copyright (c) +, Oracle an d/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> select User,host,password from user;+-----------+-----------+--- ----------------------------------------+| user | Host | Password |+-----------+-----------+-------------------------------------------+| Root | localhost | *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | Root | MySQL | *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | Root | 127.0.0.1 | *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | Root | :: 1 |           *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | |                                           localhost |           || |                                           MySQL | || DBA | %         | *4fc8d8270bec4364c78799065996f5306139b412 | | readonly | %         | *fc69e042ce30d92e2952335f690cf2345c812e36 | | ReadWrite | %         | *202273e75bd11d06fbe2f057bfa1b1bb2b26549c |+-----------+-----------+-------------------------------------------+ 9 Rows in Set (0.00 sec)

By now you've probably figured out what I'll do:update test.user, changing the password column for user ' root ' and host ' localhost ' to the result of running the PASSWORD () function with some string of my choice, then copying this table back, a nd then sending SIGHUP to the server.

A couple of caveats:

• either make a copy of the original table file, (and?) or write-down the original HA SH for Root (the one would replace)
• even if nobody on the customer's current Team knows how to get you MySQL's root password, that does not mean they don't have some old app someone had forgotten Abo Ut that uses the root account to connect. If This was the case, access would break the for this app. You can follow the same steps outlined this, but instead of permanently changing root ' s password, use your regained access To create a new super user account, and then replace Root ' s hash with the one saved (and flush privileges!)

for completion, here's the rest of the process:

 mysql> Update test.user set Password=password (' Newpass but it's insecure so dont use ') where user = ' root ' and host = ' localhost '; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0mysql> select User,host,password from test.u Ser where user= ' root '; +------+-----------+-------------------------------------------+| user | Host | Password |+------+-----------+-------------------------------------------+| Root | localhost | *0a131bf1166fb756a61317a40f272d6ffdd281e9 | | Root | MySQL | *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | Root | 127.0.0.1 | *1bd9c328233cf457571a4bb5db8d32892ab8edbf | | Root | :: 1 | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |+------+-----------+-------------------------------------------+4 Rows in Set (0.00 sec) Mysql> 

Time to copy the table back and reload the grant tables:

[[Email protected] mysql]# ' CP ' Test/user. my* Mysql/[[email protected] mysql]# kill-sighup $ (pidof mysqld)

And now you should is able to get back in:

[[email protected] mysql]# mysql-p ' newpass it is insecure so dont use ' warning:using a password on the command Line interface can insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 35Server version:5.6.16 mysql Community Server (GPL) Copyright (c) +, Oracle an d/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show grants;+------------------------------------------------- ---------------------------------------------------------------------------------------+|                                                                                                              Grants for [email protected] |+--------------------------------------------------------------------------------------------------- -------------------------------------+|  GRANT all privileges on * * to ' root ' @ ' localhost ' identified by PASSWORD ' *0a131bf1166fb756a61317a40f272d6ffdd281e9 ' with GRANT OPTION | |                                                                           Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION |+----------------------------------------------------------------------------------------------------------- -----------------------------+2 rows in Set (0.00 sec)

There you go. We ' ve regained root access to MySQL without restarting the service!

I hope you find this useful, and I'll leave opinions on MySQL ' s security as a exercise to the reader ...

Do not restart mysqld change root password