Solution of Forgetting root Password of Self built MySQL under Alibaba Cloud Linux CentOS 7.2

Validation environment:

[root@~~/]# rpm -qa | grep mysql
mysql-5.6.28-1.el6.x86_64
[root@~~/]# lsb_release -a
LSB Version:  :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description:  CentOS Linux release 7.2.1511 (Core)
Release:    7.2.1511
Codename:    Core
[root@~~/]# uname -r
3.10.0-327.22.2.el7.x86_64

First, make sure that the server is in a secure state, that is, no one can connect to the MySQL database at will.

Because during the reset of the root password of mysql, the MySQL database is completely in a state without password protection, and other users can log in and modify the information of MySQL at will. The quasi-security state of the server can be realized by closing the external port of MySQL and stopping Apache and all user processes. The safest state is to operate on Console of the server and unplug the network cable.

Modify the login settings of MySQL:

Add a sentence to the paragraph [mysqld]: skip-grant-tables

[root@~~/]# vi /etc/my.cnf

For example:

[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
skip-grant-tables 

Save and exit vi.

Restart mysqld

[root@~~/]# /etc/init.d/mysqld restart 
Stopping MySQL: [ OK ] 
Starting MySQL: [ OK ]

Log in and change the root password for MySQL

[root@~~/]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.28-log Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
mysql> update user set password=password("test") where user='root';
mysql> flush privileges;
mysql> exit; 
Bye

Modify the login settings of MySQL back

Delete skip-grant-tables just added in the paragraph of [mysqld], save and exit vi;

[root@~~/]# vi /etc/my.cnf

Save and exit vi.

Restart mysqld again

[root@~~/]# /etc/init.d/mysqld restart 
Stopping MySQL: [ OK ] 
Starting MySQL: [ OK ]

Log in with a new password and log in normally

If the external network cannot access the database, you can do the following:

[root@~~/]# firewall-cmd --permanent --zone=public --add-port=3306/tcp 
success
[root@~~/]# firewall-cmd --reload
success