Shulou(Shulou.com)06/01 Report--

Let's start with a question: how to reset the root password without restarting mysqld and without permission to modify user accounts and permissions? It doesn't matter if I don't know. I didn't know how to do it before. Let's take a look at the following ways to reset your root password.

1 、 skip-grant-tables

Our common method is to use the skip-grant-tables option, and the privilege system (privilege system) is not used after mysqld server is started. Users do not need any account and have unrestricted access to all the data in the database. For security reasons, it is usually added that 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 to add skip-grant-tables and skip-networking in the mysqld option.

2) restart mysqld server.

3) modify the password stored in the mysql.user table through the sql statement. Execute flush privileges and re-enable the mysql permissions system.

Update mysql.user set password=password ('newpassword') where user='root';flush privileges

4) remove or comment the parameter options for skip-grant-tables and skip-networking in the configuration file. If you use skip-networking, you need to restart mysqld again. Because skip-networking is not a system variable, it is only a parameter option for mysqld, and cannot be set dynamically through the system variable. If skip-networking is not applicable, you only need to execute flush privileges to make the permission system effective again.

2.-- init-file

Mysqld_safe can have the-init-file parameter option to execute sql statements that reset the password.

1) create an initialization file, such as / tmp/initfile, with the content of the sql statement that changes 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 to reset the password if you forget the root password, and you can find that both require a restart of the mysqld service. Many people use the first method to reset root passwords, but the more recommended approach is the second, that is, security is fast and easy.

3. The method of not restarting mysqld

1) first, you must have a mysql database account with modification permission, the current mysql instance account (for example, you can modify the test database with lower permissions) or the account of other instances of the same version. Copy the files related to the user table under the data/mysql directory to the data/test directory.

Mv mysql/user.* test/chown mysql:mysql test/user.*

2) use another account with lower privileges to link to the database and set the password data stored by user in the test database.

[root@xiaoya 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) test@localhost [(none)] > show databases +-+ | Database | +-+ | information_schema | | test | +-+ 2 rows in set (0.00 sec) (product) test@localhost [(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) test@localhost [(none)] > select user,host from test.user +-+-+ | user | host | +-+-+ | root | localhost | | test | localhost | +-+-+ 2 rows in set (0.00 sec) (product) test@localhost [(none)] > quitBye

3) copy the modified user.MYD and user.MYI to the mysql directory and remember to back up the previous files.

Mv test/user.*.. / mysql/chown mysql:mysql test/user.*

4. Look for the mysql process number and send a SIGHUP signal to reload the permission table.

[root@xiaoya mysql] # pgrep-n mysql19764 [root@xiaoya mysql] # kill-SIGHUP 19764

5. Landing test

[root@xiaoya 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) root@localhost [(none)] > quitBye

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.