Share solutions to mysql password forgetting and login error problems

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 and take a look.

Forgot mysql login password? In fact, the solution is very simple. You only need to add a line of "skip authorization table" parameter to the main configuration file my.cnf of mysql. Just choose!

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

After logging in Reset password

[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 I made a full backup before. After restoring it, I found that I couldn’t 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 -p123456ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[root@test-huanqiu ~]# ps -ef|grep mysqlroot 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.pidmysql 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=3306root 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’ll be 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 the 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 to reinitialize mysql data

Switch to the mysql installation directory

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

－－－－－－－－－－－－－－－－－－ -----------------------

Using mysql server authorization Log in to mysql, and the error is as follows:

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

Possible reasons are:

1) Possible network connection problem, remote ping 192.168.1.14, can ping successfully, rule out this situation;

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

3) The my.cnf file of the mysql server 192.168.1.14 is configured with bind_address address binding, which is not allowed. Local connection;

4) Skip_networking is configured in the my.cnf file of mysql server 192.168.1.14. MySQL can only be connected through the local Socket (socket connection is also the default method of

local connection), so give up. TCP/IP monitoring;

5) To troubleshoot DNS resolution problems, check whether skip_name_resolve is set in the my.cnf file of mysql server 192.168.1.14. 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 Share solutions to mysql password forgetting and login error problems. For more information, please follow other related articles on the PHP Chinese website!