MySQL database root password lost after the recovery

Preparation before the experiment:
1. Environment
Port 3306 The instance is a production environment, to retrieve the root password of 3306
Use when testing 3307 tests
2. Open 3306,3307 mysqld Service

[Email protected] ~]# mysqld--DEFAULTS-FILE=/DATA/MYSQL/MYSQL3306/MY.CNF &

Two methods:
I. Using the Skip_grant_tables parameter

3306|grep-9473650:xx pts/0 :xx: mysqld--defaults-file=/data/mysql/mysql3306/~]# Kill-9947 ~]# mysqld--defaults-file=/data/mysql/mysql3306/my.cnf--skip_grant_tables &~]#  Mysql-s/tmp/mysql3306.sock

mysql> alter User User () identified by'zstzst'; ERROR1290(HY000): the MySQL server isRunning with the--skip-grant-tables option so it cannot execute ThisStatementmysql> Update Mysql.userSetAuthentication_string=password ('zstzst3306')wherehost='localhost'and user='Root'; Query OK,1Row affected,1Warning (0.00sec) Rows matched:1Changed:1Warnings:1MySQL>flush Privileges; Query OK,0Rows Affected (0.00sec) MySQL>shutdown; Query OK,0Rows Affected (0.00Sec

[Email protected] ~]# mysqld--defaults-file=/data/mysql/mysql3306/my.cnf &#登陆成功

This method needs to restart the MySQL service, because the password-free login, so the database should be started again, it is best to add parameter --skip__networking

Two. Copy modification user.*

3306 old password is zstzst, pretend to forget, by copy user* file way change into zstzst3306
3307 Password ' zstzst3307 '

[Email protected] ~]# mysql-s/tmp/mysql3306.sock-pzstzst3306mysql'zstzst' #3306密码修改成 zstzst

[Email protected] ~]# Ps-ef|grep Mysqld|grep-v Grepmysql1032 365 0  +:xxpts/0 xx:xx:xxMysqld--defaults-file=/data/mysql/mysql3307/My.cnfmysql3960 365 0  +: topts/0 xx:xx:xxMysqld--defaults-file=/data/mysql/mysql3306/My.cnf[[email protected]~]# Kill-9 1032 #杀掉3307[[email protected]~]# \cp/data/mysql/mysql3306/data/mysql/user.frm/data/mysql/mysql3307/data/mysql/user.frm [[email protected]~]# \cp/data/mysql/mysql3306/data/mysql/user. myd/data/mysql/mysql3307/data/mysql/user. Myd[[email protected]~]# \cp/data/mysql/mysql3306/data/mysql/user. myi/data/mysql/mysql3307/data/mysql/user. Myi[[email protected]~]# mysqld--defaults-file=/data/mysql/mysql3307/my.cnf--skip_grant_tables & #这样不用重启生产库[[email protected]~]# mysql-s/tmp/mysql3307.sock

mysql> Update Mysql.userSetAuthentication_string=password ('zstzst3306')wherehost='localhost'and user='Root'; Query OK,0Rows affected,1Warning (0.00sec) Rows matched:1Changed:0Warnings:1MySQL>flush Privileges; Query OK,0Rows Affected (0.01sec) MySQL>shutdown; Query OK,0Rows Affected (0.01Sec

[Email protected] ~]# \cp/data/mysql/mysql3307/data/mysql/user.frm/data/mysql/mysql3306/data/mysql/~]# \ Cp/data/mysql/mysql3307/data/mysql/user. myd/data/mysql/mysql3306/data/mysql/~]# \cp/data/mysql/mysql3307/data/mysql/user. myi/data/mysql/mysql3306/data/mysql/3960#验证登陆成功

MySQL database root password lost after the recovery