A brief discussion on the problem of forgotten mysql password and login error

If you forget your MySQL login password, the solution is actually very simple. You only need to add a line of "skip authorization table" parameter selection in the MySQL main configuration file my.cnf!

Add the following line to my.cnf:

[root@test-huanqiu ~]# vim /etc/my.cnf //Add in the [mysqld] area
........
skip-grant-tables //Skip the authorization table

Then restart the mysql service and you can log in without a password

[root@test-huanqiu ~]# /etc/init.d/mysqld restart

Reset password after login

mysql> update mysql.user set password=password("123456") where host="localhost" and user="root";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

Comment out the line added in my.cnf again and restart mysql

[root@test-huanqiu ~]# vim /etc/my.cnf
........
#skip-grant-tables

[root@test-huanqiu ~]# /etc/init.d/mysqld restart

[root@test-huanqiu ~]# mysql -p123456
mysql>

-----------------------------------------------------------------------------------------------------------------------

A pit found:

I had previously performed a full backup of mysql. After restoring it, I found that I could no longer log in using the previous password!
Use the above method to log in without a password and then reset the password, but after resetting the password, you will find that you still cannot log in.

Finally found out that it was because the contents of the mysql.user table were cleared!
mysql> select host,user,password from user;
Empty set (0.00 sec)

solve:

Insert data and reset password

mysql> insert into user(host,user,password) values("localhost","root","123456");
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> select host,user,password from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | 123456 |
+-----------+------+----------+
1 row in set (0.00 sec)

mysql> update mysql.user set password=password("123456") where host="localhost" and user="root";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select host,user,password from user;
+-----------+------+------------------------------------------+
| host | user | password |
+-----------+------+------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into user(host,user,password) values("127.0.0.1","root","123456");
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> select host,user,password from user;
+-----------+------+------------------------------------------+
| host | user | password |
+-----------+------+------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | 123456 |
+-----------+------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> update mysql.user set password=password("123456") where user="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0

mysql> select host,user,password from user;
+-----------+------+------------------------------------------+
| host | user | password |
+-----------+------+------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+------------------------------------------+

Then you can log in normally using the reset password!

------------------------------------------------------------------------------------------------------------------

mysql login error 1:

[root@test-huanqiu ~]# mysql -p123456
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

[root@test-huanqiu ~]# ps -ef|grep mysql
root 28279 1 0 12:55 ? 00:00:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/mysql.pid
mysql 29059 28279 0 12:55 ? 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/data/mysql/data/mysql-error.log --pid-file=/data/mysql/data/mysql.pid --socket=/usr/local/mysql/var/mysql.sock --port=3306
root 30726 11268 0 12:58 pts/2 00:00:00 grep mysql

It can be seen that the current mysql.sock file path is /usr/local/mysql/var/mysql.sock.

Solution: Make a soft link

[root@test-huanqiu ~]# ll /usr/local/mysql/var/mysql.sock
rwxrwxrwx. 1 mysql mysql 0 Nov 29 12:55 /usr/local/mysql/var/mysql.sock
[root@test-huanqiu ~]# rm -f /var/lib/mysql/mysql.sock
[root@test-huanqiu ~]# ln -s /usr/local/mysql/var/mysql.sock /var/lib/mysql/mysql.sock

That's fine.

[root@test-huanqiu ~]# mysql -p123456
mysql>

----------------------------------------------------------------------------------------------------

When starting mysql, an error occurs:

Starting MySQL.... ERROR! The server quit without updating PID file (/data/mysql/data/mysql.pid).

Attempted solutions:

(1) Permission issues

It may be that the mysql.pid file does not have write permission. Set the permissions of the mysql installation directory and data directory to the permissions of the mysql startup user. For example, all permissions are changed to mysql:mysql

(2) There may already be a mysql process in the process

ps -ef|grep mysql If there is a mysql process, kill it and try to restart mysql

(3) MySQL may have been installed multiple times on the machine, and residual data may have affected the startup of the service.

Go to the mysql data directory and check if mysql-bin.index exists, delete it immediately, it is the culprit!

(4) When no configuration file is specified at startup, mysql uses the /etc/my.cnf configuration file. Open this file and check whether the data directory is specified under the [mysqld] section.

Add settings under [mysqld], such as datadir = /data/mysql/data

(5) Skip-federated field issue

Check the my.cnf file to see if there is any skip-federated field that is not commented out. If there is, comment it out immediately.

(6) The error log directory does not exist

Check if there is a log configuration path in the my.cnf file. If so, check whether the log directory exists. Make sure that the log directory permissions are the permissions of the mysql startup user.

(7) SELinux is to blame. If it is a CentOS system, SELinux will be enabled by default.

Close it, open /etc/selinux/config, change SELINUX=enforcing to SELINUX=disabled, save and exit, and restart the machine to try again.
(8) Try reinitializing MySQL data

Switch to the mysql installation directory

./scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Log in to MySQL using the MySQL server-side authorization information, and the error is as follows:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.14' (111)

Possible causes include:

1) There may be a network connection problem. If you ping 192.168.1.14 remotely, it can be pinged successfully, which eliminates this problem.

2) A whitelist restriction on port 3306 is set in iptables of mysql server 192.168.1.14;

3) The bind_address address binding is configured in the my.cnf file of the mysql server 192.168.1.14, and local connection is not allowed;

4) The skip_networking is configured in the my.cnf file of the MySQL server 192.168.1.14. This means that MySQL can only be connected through the local socket (socket connection is also the default mode of local connection), and TCP/IP monitoring is abandoned;

5) Troubleshoot DNS resolution issues and check whether skip_name_resolve is set in the my.cnf file of the MySQL server 192.168.1.14. After this parameter is added, the host name connection method is not supported.

6) Check the --port problem. It is possible that the MySQL port of the MySQL server 192.168.1.14 is not the default 3306, for example, it is port 3307. In this case, add --port=3307 when connecting remotely.

7) Check the user and password issues. In fact, if the user and password are wrong, the error code 111 will not appear, so the user and password issues should be eliminated.
ERROR 1045 (28000): Access denied for user 'root'@'XXXX' (using password: YES)

The above brief discussion on the problem of forgotten MySQL password and login error is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:

Initialize MySQL users (delete anonymous users)
How to delete anonymous users in MYSQL (to improve security)
Solution to MySQL server login error ERROR 1820 (HY000)
Solve the problem of changing the password when logging in for the first time after installing MySQL 8.0
Solution to the problem that the mysql8.0.11 client cannot log in
How to quickly log in to MySQL database without password under Shell
MySql login password forgotten and password forgotten solution
PHP+MySQL method to realize user registration and login
Solution to adding a new user to mysql and not being able to log in
Mysql anonymous login cannot create a database problem solution

<<: How to manage users and groups when running Docker

>>: Detailed explanation of Docker Volume permission management