Solve the problem of incorrect mysql password under linux-Linux Operation and Maintenance-php.cn

Solve the problem of incorrect mysql password under linux

hzc

Release： 2020-06-06 16:01:56

Original

5357 people have browsed it

Solve the problem of incorrect mysql password under linux

Solution to the wrong mysql password under Linux:

Preface

After installing mysql on the server today, I found the password after logging in Error, but I did not set a password. Finally, Baidu learned that mysql will automatically create an initial password after version 5.7.

The error is reported as follows:

[root@mytestlnx02 ~]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user &#39;root&#39;@&#39;localhost&#39; (using password: YES)

Copy after login

Change password

1. Check whether the mysql service is started. If it is started, close the mysql service

//查看mysql服务状态
[root@mytestlnx02 ~]# ps -ef | grep -i mysql
root     22972     1  0 14:18 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql    23166 22972  0 14:18 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root     23237 21825  0 14:22 pts/0    00:00:00 grep -i mysql
//关闭服务
[root@mytestlnx02 ~]# service mysql stop
[root@mytestlnx02 ~]#

Copy after login

2. Modify the mysql configuration file my.cnf

my.cnf The location of the configuration file, generally In /etc/my.cnf, some versions are in /etc/mysql/my.cnf

In the configuration file, add 2 lines of code

[mysqld]
skip-grant-tables

Copy after login

The function is to skip password verification when logging in to mysql

Then start the mysql service and enter mysql

[root@mytestlnx02 ~]# service mysqld start
[root@mytestlnx02 ~]#
[root@mytestlnx02 ~]# mysql -u root 
Type &#39;help;&#39; or &#39;\h&#39; for help. Type &#39;\c&#39; to clear the current input statement.
mysql>

Copy after login

3. Change the password

Connect to the mysql database and modify it User password

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update mysql.user set authentication_string=password(&#39;root_password&#39;) where user=&#39;root&#39;;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> exit

Copy after login

4. Restart the mysql service

First comment or delete the 2 lines of code previously added to the configuration file, then restart the mysql service, and you can log in using the password you just set. .

[root@mytestlnx02 ~]# service mysql start
[root@mytestlnx02 ~]#
[root@mytestlnx02 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
p.s.

Copy after login

The operation method is different on CentOS.

Execution of the command to change the password keeps reporting errors

mysql> update user set authentication_string=password(&#39;xxxxxxxx&#39;) where User=&#39;root&#39;;       
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;(&#39;root_password&#39;) where User=&#39;root&#39;&#39; at line 1

Copy after login

It can’t be a syntax problem. I checked it many times and finally found that it should be done like this under CentOS:

View the initial password

[root@VM_0_8_centos ~]# grep &#39;temporary password&#39; /var/log/mysqld.log
2018-09-26T04:25:54.927944Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: DN34N/=?aIfZ

Copy after login

You can see that the initial password is DN34N/=?aIfZ

Use the initial password to log in

[root@VM_0_8_centos ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Copy after login

Change the password

mysql> ALTER USER &#39;root&#39; IDENTIFIED BY &#39;xxxxxxxxx&#39;;  
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER &#39;root&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;xxxxxxxx&#39;;
Query OK, 0 rows affected (0.11 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
重启服务

Copy after login

It will take effect

[root@VM_0_8_centos ~]# service mysqld stop 
Redirecting to /bin/systemctl stop  mysqld.service
[root@VM_0_8_centos ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service

Copy after login