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
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
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)
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;
1. Clear the original password;
Modify my CNF file
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';
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!
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.
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)
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.