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

The main purpose of this article is to tell you briefly that the mysql5.6 password has forgotten how to recover. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that the mysql5.6 password has forgotten how to restore this article can bring you some practical help.

Experimental environment:

1 、 centos7.3

2 、 mysql5.6.35

Lab description:

Originally, only the root account of mysql can be logged in with a password protected, but now the password has been forgotten and cannot be recovered. Today's goal is to reset mysql's root password by cracking it.

During the experiment:

1. Before you start, make sure that mysql cannot log in without a password.

[root@c73 mysql] # mysqlERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@c73 mysql] # [root@c73 ~] # mysql-u root-p654321 / / you can't enter it with the password 654321. Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

2. Skip authorization to enter mysql

[root@c73 mysql] # systemctl stop mysql// stop mysql// because I am using a root account, and mysql requires an mysql account to run, so-- user=mysql,// is mainly used here to skip the authorization table [root@c73 mysql] # mysqld-- skip-grant-tables & [1] 6022 [root@c73 mysql] # 2017-04-11 09:36:41 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-- explicit_defaults_for_timestamp server option (see documentation for more details). 2017-04-11 09:36:41 0 [Note] mysqld (mysqld 5.6.35) starting as process 6022... 2017-04-11 09:36:41 6022 [Note] Plugin 'FEDERATED' is disabled.2017-04-11 09:36:41 6022 [Note] InnoDB: Using atomics to ref count buffer pool pages2017-04-11 09:36:41 6022 [Note] InnoDB: The InnoDB memory heap is disabled2017-04- 11 09:36:41 6022 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2017-04-11 09:36:41 6022 [Note] InnoDB: Memory barrier is not used2017-04-11 09:36:41 6022 [Note] InnoDB: Compressed tables use zlib 1.2.32017-04-11 09:36:41 6022 [Note] InnoDB: Using Linux native AIO2017-04-11 09:36:41 6022 [Note] InnoDB: Using CPU crc32 instructions2017-04-11 09:36:41 6022 [Note] InnoDB: Initializing buffer pool Size = 128.0M2017-04-11 09:36:41 6022 [Note] InnoDB: Completed initialization of buffer pool2017-04-11 09:36:41 6022 [Note] InnoDB: Highest supported file format is Barracuda.2017-04-11 09:36:41 6022 [Note] InnoDB: 128 rollback segment (s) are active.2017-04-11 09:36:41 6022 [Note] InnoDB: Waiting for purge to start2017-04-11 09:36:42 6022 [Note] InnoDB: 5.6.35 started Log sequence number 1626007 2017-04-11 09:36:42 6022 [Note] Server hostname (bind-address):'*'; port: 33062017-04-11 09:36:42 6022 [Note] IPv6 is available.2017-04-11 09:36:42 6022 [Note] -': 'resolves to'::' 2017-04-11 09:36:42 6022 [Note] Server socket created on IP:'::. 2017-04-11 09:36:42 6022 [Note] mysqld: ready for connections.Version: '5.6.35' socket:' / var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) [root@c73 mysql] # netstat-lnpt | grep 3306 / / confirm that the service is enabled successfully tcp6 0: 3306 : * LISTEN 6022/mysqld [root@c73 mysql] # mysql / / enter mysqlWelcome to the MySQL monitor without a password. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.6.35 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.mysql > / / successfully enter ^ _ ^

3. Modify the root password

Mysql > set password for 'root'@'localhost' = password (' 654321'); ERROR 1290 (HY000): The MySQL server is running with the-- skip-grant-tables option so it cannot execute this statement// in-- skip-grant-tables mode, the above method of changing password is not feasible. / / so we directly modify the user table to update the password. Mysql > update mysql.user set password=password ("654321") where user='root';Query OK, 4 rows affected (0.00 sec) # successfully updated 4 root users' passwords Rows matched: 4 Changed: 4 Warnings: 0 sec / finally don't forget to refresh mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > exit / / exit Bye

At this time, let's test it with the new password to see if we can get in.

[root@c73 ~] # ps aux | grep mysqlmysql 6022 0.011.6 1038816 452312 pts/0 Sl 09:36 0:00 mysqld-- user=mysql-- skip-grant-tablesroot 6113 0.0 112664 968 pts/1 R+ 0:00 grep-- color=auto mysql [root@c73 ~] # kill-9 6022 / / end the original mysql service [root@c73 ~] # systemctl start mysql starts the mysql service in normal mode [root@c73 ~] # ps aux | grep mysqlmysql 6132 0.0 113252 1588? Ss 10:00 0:00 / bin/sh / usr/bin/mysqld_safe-- basedir=/usrmysql 6310 1.82.8 698944 110984? Sl 10:00 0:00 / usr/sbin/mysqld-- basedir=/usr-- datadir=/var/lib/mysql-- plugin-dir=/usr/lib64/mysql/plugin-- log-error=/var/log/mysqld.log-- pid-file=/var/run/mysqld/mysqld.pid-- socket=/var/lib/mysql/mysql.sockroot 6337 0.0 112664 972 pts/1 R + 10:01 grep-- color=auto mysql [root@c73] # Mysql # Test cannot enter ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@c73 ~] # mysql-u root-p654321 # Test successfully entered ^ _ ^ password: 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 3Server version: 5.6.35 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.mysql >

Summary of the experiment:

Keep the password as much as possible, if it is online 24-hour business, this situation must restart the service to deal with, more or less will bring some unnecessary trouble.

Mysql5.6 password forgot how to recover first to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.