Do not restart to modify the MySQL root password

Do not restart to modify the MySQL root password

--------------------2014/09/28

First, generally forget the password solution, need to restart MySQL
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, typically with skip-networking, MYSQLD does not listen for 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.

Copy CodeThe code is as follows: UPDATE MySQL. USER SET Password=password (' newpwd ') 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.

Copy CodeThe code is as follows: UPDATE mysql.user SET password=password (' newpwd ') WHERE user= ' root ';
FLUSH privileges;

2) Close the MYSQLD service process.
3) Use Mysqld_safe to start mysqld;

Copy CodeThe code is as follows: Mysqld_safe--init-file=/home/me/mysql-init &

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.

Ii. methods of not restarting Mysqld

1 . First, you have to 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.

Copy CodeThe code is as follows:
[email protected] mysql]# CP mysql/user.* test/
[Email protected] mysql]# chown mysql.mysql test/user.*

2 . Use another low-privileged account link database to set the user stored password data in the test database.

Copy CodeThe code is as follows:
[Email protected] mysql]# mysql-utest-p12345
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 17
Server Version:5.5.25a-log Source Distribution

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

mysql> Use test
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
mysql> Update user Set Password=password (' Yayun ') where user= ' root ';
Query OK, 0 rows Affected (0.00 sec)
Rows Matched:5 changed:0 warnings:0

Mysql>

3, the user after the change. MyD and user.myi Copy to the MySQL directory, remember to back up the previous files.

Copy CodeThe code is as follows:
MV Mysql/user. MYD Mysql/user. Myd.bak
MV Mysql/user. MYI Mysql/user. Myi.bak
CP Test/user. my* mysql/
Chown Mysql.mysql mysql/user.*

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

Copy CodeThe code is as follows:
[[email protected] mysql]# pgrep-n MySQL
2184
[Email protected] mysql]#
[Email protected] mysql]# kill-sighup 2184

5. Landing Test

Copy CodeThe code is as follows:
[Email protected] mysql]# Mysql-uroot-pyayun
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 20
Server Version:5.5.25a-log Source Distribution

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

Mysql>

Do not restart to modify the MySQL root password