Does MySQL forgot password really need to restart the service?

Ask a question: How to reset the root password without restarting mysqld and without permission to modify user account and permissions? I do not know, before I do not know how to operate, first look at the following several ways to reset the root password.

1, Skip-grant-tables

Our common approach is to use the Skip-grant-tables option, and MYSQLD server does not use the permission system (privilege systems) after startup. The user does not need any account, unrestricted access to all data in the database. For security reasons, usually plus skip-networking,mysqld does not listen on any TCP/IP connection requests. The operation process is as follows

1) Modify the MY.CNF configuration file and add Skip-grant-tables and skip-networking to the MYSQLD option.

2) Restart Mysqld server again.

3) store the password in the Mysql.user table by modifying the SQL statement. Execute the flush privileges and re-enable the MySQL permissions system.

Update Mysql.user set Password=password (' NewPassword ') where user= ' root '; flush privileges;

4) Delete or comment The parameter options for Skip-grant-tables and skip-networking in the configuration file. If you use skip-networking, you will need to restart mysqld again. Because skip-networking is not a system variable, it is only a mysqld parameter option, and cannot be set dynamically through system variables. If Skip-networking is not available, only the flush privileges is required to bring the permission system back into effect.

2.--init-file

Mysqld_safe can enable the –init-file parameter option to execute the SQL statement that re-sets the password.

1) Create a new initialization file, such as/tmp/initfile, with the file content as the SQL statement that modifies the password above.

Update Mysql.user set Password=password (' NewPassword ') where user= ' root '; flush privileges;

2) Close the MYSQLD service process.

3) Use Mysqld_safe to start mysqld;

Mysqld_safe--init-file=/tmp/initfile &

The above two methods are the way to reset the password in the case of forgetting the root password, you can find that all need to restart the MYSQLD service. Many people use the first to reset the root password, but the recommended approach is the second, that is, security is quick and easy.

3, do not restart the method of Mysqld

1) First, you must have a MySQL database account with the right to modify, the current MySQL instance account (lower-privileged accounts, such as the test database can be modified) or other instances of the same version of the account. Copy the files associated with the user table below the Data/mysql directory to the Data/test directory.

MV mysql/user.* Test/chown Mysql:mysql test/user.*

2) use another lower-privileged account link database to set the user-stored password data in the test database.

[[Email protected] test]# mysql -utest -ptestwarning: using a password  on the command line interface can be insecure. Welcome to the mysql monitor.  commands end with ; or \ g.your mysql connection id is 5server version: 5.6.31-log mysql  community server  (GPL) copyright  (c)  2000, 2016, oracle and/or its  affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement. (product) [Email protected] [(None)]> show databases;+--------------------+| database            |+--------------------+| information_schema | |  test               |+-------- ------------+2 rows in set  (0.00 SEC) (product) [email protected] [(none)] > update test.user set password=password (123456)  where user= ' root '; error 2006  (HY000): mysql server has gone awayno connection.  Trying to reconnect ... Connection id:    6current database: *** none ***query ok,  1 row affected  (0.00 sec) rows matched: 1  changed: 1   warnings: 0 (product) [email protected] [(None)]> select user,host from  test.user;+------+-----------+| user | host      |+------+-----------+| root | localhost | |  test | localhost |+------+-----------+2 rows in set  (0.00 sec) ( Product) [email protected] [(None)]> quitbye

3) put the modified user. MyD and user.myi Copy to the MySQL directory, remember to back up the previous files.

MV Test/user.*. /mysql/chown Mysql:mysql test/user.*

4, find the MySQL process number, and send sighup signal, reload the permissions table.

[[email protected] mysql]# pgrep-n mysql19764[[email protected] mysql]# kill-sighup 19764

5. Login Test

[[email protected] mysql]# mysql -uroot -p123456warning: using a  Password on the command line interface can be insecure. Welcome to the mysql monitor.  commands end with ; or \ g.your mysql connection id is 7server version: 5.6.31-log mysql  community server  (GPL) copyright  (c)  2000, 2016, oracle and/or its  affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement. (product) [Email protected] [(None)]> quitbye 

This article is from the "it--Brother" blog, please make sure to keep this source http://402753795.blog.51cto.com/10788998/1828325

Does MySQL forgot password really need to restart the service?