Error code:

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 'exit()' at line 1

　　or

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

　　or

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

　　or

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

　　or

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 '('*******') WHERE User='root'' at line 1

All the above mistakes are grammatical errors in general; The password entered is correct, but you just can't get in; Even if you skip the password, you can't change it when you change the password. (MySQL version 8.0 only)

 

resolvent:

It's simple. Change the password.

 

Reasons why changing the password is not enough:

　　1,MySQL8. Version 0 cannot directly modify mysql User table to change the password;

　　2,authentication_ The string field can only be the 43 bit string password encrypted by MySQL;

 

Solution:

1. Clear the original password;

Modify my CNF file

vim /etc/my.cnf

add

skip-grant-tables

Save the changes and restart the MySQL service

systemctl restart mysqld.service

After restarting, you can use the mysql command directly and enter mysql in seconds

Then clear the root password

UPDATE user SET authentication_string='' WHERE user='root';

Then refresh

flush privileges;

Exit exit and insert into my Remove the skip grant tables of CNF and restart MySQL.

2. The root password has been cleared and modified;

Enter MySQL (the password has been cleared in the previous step, so you can enter).

Password modification (pay attention to distinguish the scope Host):

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY "***";

or (if the scope is localhost, run the following code)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY "***";

Success is achieved if the following results are returned

Query OK, 0 rows affected (0.00 sec)

Refresh and exit MySQL;

Go in again and find success!

 

Summary:

　　MySQL 5.* The version change password command is

UPDATE user SET authentication_string="123456" WHERE user="root";

　　MySQL 8.* The version change password command is

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY "123456";

As can be seen, 5* The later version cancels the password function, so it is not applicable to 8* edition.

 

Appendix:

1. Modify Host to%:

update user set host='%' where host= 'localhost';

2. centos 7 installs MySQL 8.0 from scratch

First, uninstall the MariaDB installed by default (the second command deletes it according to your situation, because the file name may be different)

rpm -qa | grep -i mariadb

rpm -e --nodeps mariadb-libs

Then, download the MySQL repository and install it (operate in the same directory)

wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm

yum -y install mysql80-community-release-el7-3.noarch.rpm

Finally, install the MySQL database

yum -y install mysql-community-server

3. MySQL service operation command

Start MySQL service:

systemctl start mysqld.service

Close MySQL service:

systemctl stop mysqld.service

Restart MySQL service:

systemctl restart mysqld.service

4. View MySQL default login password command

cat /var/log/mysqld.log | grep password

5. Remote connection problem

If the remote connection prompts: plugin caching_sha2_password error

Reason: mysql8 0 default password policy and 5* Different versions

Solution: (modify the plugin to mysql_native_password)

update user set plugin = 'mysql_native_password' where user = 'root';

Enabled after refresh.