Talking about the problem of mysql password forgetting and logon error, talking about the problem of mysql password forgetting

Talking about the problem of mysql password forgetting and logon error, talking about the problem of mysql password forgetting

The mysql logon password is forgotten. In fact, the solution is 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 it in the [mysqld] Area
........
Skip-grant-tables // skip the authorization table

Restart the mysql service to log on without a password.

[Root @ test-huanqiu ~] #/Etc/init. d/mysqld restart

Reset Password after Logon

[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 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>

Bytes -----------------------------------------------------------------------------------------------------------------------

A pitfall discovered:

A full backup was performed before mysql. After the restoration, it was found that the login failed with the previous password!
Using the above method, You can reset the password after logging in without a password, but you still cannot log in After resetting the password.

The final result is that the mysql. user table is cleared!
Mysql> select host, user, password from user;
Empty set (0.00 sec)

Solution:

Insert data and 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: 0mysql> 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: 0mysql> select host,user,password from user;+-----------+------+-------------------------------------------+| host | user | password |+-----------+------+-------------------------------------------+| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+------+-------------------------------------------+

Then, use the Reset Password to log on!

Bytes ------------------------------------------------------------------------------------------------------------------

Mysql logon Error 1:

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

[Root @ test-huanqiu ~] # Ps-ef | grep mysql
Root 28279 1 0 :55? 00:00:00/bin/sh/usr/local/mysql // bin/mysqld_safe -- datadir =/data/mysql/data -- pid-file =/data/mysql. pid
Mysql 29059 28279 0? 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. pid -- socket =/usr/local/mysql/var/mysql. sock -- Ports = 3306
Root 30726 11268 0 00:00:00 pts/2 grep mysql

We can see that the current mysql. sock file path is/usr/local/mysql/var/mysql. sock,

Solution: build soft links

[Root @ test-huanqiu ~] # Ll/usr/local/mysql/var/mysql. sock
Rwxrwxrwx. 1 mysql 0 Nov 29 :55/usr/local/mysql/var/mysql. sock
[Root @ test-huanqiu ~] # Rm-f/var/lib/mysql. sock
[Root @ test-huanqiu ~] # Ln-s/usr/local/mysql/var/mysql. sock/var/lib/mysql. sock

So there is no problem.

[Root @ test-huanqiu ~] # Mysql-p123456
Mysql>

Bytes ----------------------------------------------------------------------------------------------------

When mysql is started, the following error occurs:

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

Solution:

(1) permission issues

It may be that the mysql. pid file does not have the write permission. You can set the permissions of the mysql installation directory and data directory to the mysql startup user permission. For example, change all permissions to mysql.

(2) The mysql process may already exist in the process.

Ps-ef | grep mysql: If a mysql process exists, kill it and restart mysql.

(3) mysql may be installed on the machine multiple times, and residual data may affect service startup.

Go to the mysql data directory to see, if there is a mysql-bin.index, immediately delete it, it is the culprit!

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

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

(5) skip-federated Field Problems

Check whether the skip-federated field has been commented out in the my. cnf file. If so, comment out the field immediately.

(6) The error log directory does not exist.

Check whether there is a log configuration path in the my. cnf file. If yes, check whether the log directory exists. Ensure that the log directory permission is the mysql startup user permission.

(7) selinux. If it is a centos system, selinux is enabled by default.

Close it, open/etc/selinux/config, change SELINUX = enforcing to SELINUX = disabled, and then save the disk and restart the machine.
(8) reinitialize mysql Data.

Switch to the mysql installation directory.

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

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

Log on to mysql using the mysql server-authorized information. The following error is returned:

ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.1.14 '(192)

Possible causes include:

1) network connection problems may occur. Remote ping to 192.168.1.14 can be pinged to exclude this situation;

2) mysql Server 192.168.1.14 iptables imposes port 3306 whitelist restrictions;

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

4) mysql Server 192.168.1.14 my. skip_networking is configured in the cnf file. In this case, MySQL can only connect through the local Socket (socket connection is also the default method of local connection), giving up listening to TCP/IP;

5) troubleshoot DNS resolution and 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 mode is not supported.

6) troubleshoot the -- port problem. It is possible that the mysql port of MySQL Server 192.168.1.14 is not the default 3306 port, for example, port 3307. In this way, -- port = 3307 must be added during remote connection.

7) Check the user and password. In fact, there will be no 111 error in the user and password, so the user password problem is ruled out.
ERROR 1045 (28000): Access denied for user 'root' @ 'xxxx' (using password: YES)

The above discussion about the problem of mysql password forgetting and logon error is all the content shared by Alibaba Cloud. I hope you can give us a reference and support for the customer's house.