Detailed explanation of the problem of forgotten mysql password and login error

The editor below will bring you a brief discussionmysqlThe problem of forgotten passwords and login errors. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

Forgot mysql login password? In fact, the solution is very simple. You only need to add a line "jump" to the main configuration file of mysqlmy.cnf. Just select the parameters of "Authorization Form"!

Add the following line in my.cnf:

[root@test-huanqiu ~]# vim /etc/my.cnf            
  //在[mysqld]区域里添加
........
skip-grant-tables 
                     
 //跳过授权表

Then restart the mysql service to log in without a password

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

Reset password after logging in

[root@test-huanqiu ~]# mysql 
mysql> select host,user,password from mysql.user;
+--------------------+------+-------------------------------------------+
| host | user | password |
+--------------------+------+-------------------------------------------+
| localhost | root | *481ACA1BD6D1E86221244904E9C0FABA33B40B84 |
| host-192-168-1-117 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| host-192-168-1-117 | | |
+--------------------+------+-------------------------------------------+
6 rows 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.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user,password from mysql.user;
+--------------------+------+-------------------------------------------+
| host | user | password |
+--------------------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| host-192-168-1-117 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| host-192-168-1-117 | | |
+--------------------+------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql>

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

[root@test-huanqiu ~]# vim /etc/my.cnf........#skip-grant-tables
[root@test-huanqiu ~]# /etc/init.d/mysqld restart
[root@test-huanqiu ~]# mysql -p123456mysql>

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

A pit discovered :

Mysql was fully backed up before. After restoring it, I found that I could not log in with the previous password!
Use the above method to log in without a password and then reset the password. However, after resetting the password, you still cannot log in.

Finally found out that the content of the mysql.user table was cleared!

mysql> select host,user,password from user;
Empty set (0.00 sec)

Solution:

Insert data, and then reset the 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 no problem

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

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

Error reported when starting mysql:

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

Attempted solutions:

##(1) Permission problem

It may be that the mysql.pid file does not have write permissions. Set the permissions of mysql's

installation directory and data directory to mysql startup user permissions. For example, change them to mysql: mysql permissions

(2) Mysql process may already exist in the process

ps -ef|grep mysql Find out if there is a mysql process, Just kill it and try to restart mysql

(3) It may be that mysql has been installed on the machine multiple times, and there is residual data that affects the startup of the service.

Go to the mysql data directory and take a look. If mysql-bin.index exists, delete it immediately. It is the culprit!

(4) When mysql does not specify a configuration file when starting, it will use the /etc/my.cnf configuration file. Please open this file to see if there is a data directory specified under the [mysqld] section.

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

(5) skip-federated field problem

Check whether there is an uncommented skip-federated field in the my.cnf file, and if so, comment it out immediately.

(6) The error log directory does not exist

Go to the my.cnf file to see if there is a log configuration path. If so, check whether the log directory exists. Log The directory permissions must be the permissions of the mysql startup user.

(7) Selinux is the cause of trouble. If it is a centos system, selinux will be enabled by default.

Turn it off, open /etc/selinux/config, and set SELINUX=enforcing Change it to SELINUX=disabled, save and exit, and try restarting the machine.

(8) Try reinitializing mysql data

Switch to the installation directory of mysql

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

──────────── -----------------------

##Use the mysql server authorized information to log in to mysql , the error is reported as follows:
##ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.14' (111)

Possible reasons are:

1) There may be a network connection problem. Remote ping 192.168.1.14 can ping successfully. This rule out the situation;

2) The iptables of mysql server 192.168.1.14 has made a whitelist restriction for port 3306 connection;

3) The my.cnf file of mysql server 192.168.1.14 has bind_address configured. Address binding, local connection is not allowed;

4) skip_networking is configured in the my.cnf file of mysql server 192.168.1.14, which can only be connected through the local Socket using MySQL (socket connection It is also the default method of local connection) and abandons monitoring of TCP/IP;

5) To troubleshoot DNS resolution problems, check the my.cnf file of mysql server 192.168.1.14 Whether skip_name_resolve is set in it. After this parameter is added, the host name connection method is not supported.

6) Troubleshoot the --port problem. It is possible that the MySQL port of 192.168.1.14 on the mysql server is not the default 3306, such as port 3307. In this way, --port= must be added when connecting remotely. 3307

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

The above is the detailed content of Detailed explanation of the problem of forgotten mysql password and login error. For more information, please follow other related articles on the PHP Chinese website!