-
Problem Description
Use in cloud server
The Mysql5.7 database installed by docker was used normally at the beginning, but suddenly one day later it could not be connected, and the error was reported as:
ERROR 1045 (28000): Access denied for user ‘root’@’1xxx'(using password: YES),
When an Error 1045 error occurs when logging in to the MySQL database, it indicates that the user name or password entered is wrong
access denied
-
Cause Analysis
-
mysql server stopped
-
The user’s port number or IP leads to
-
Mysql configuration file error: my.ini and other files
-
Wrong password for root user
Note: After the MySQL service is set up,
The default root user cannot perform remote access connections
-
Specific solutions
Eliminate the above errors through the following inspection steps
(1). Check whether the mysql service is started
When the mysql server stops, the above error will be reported. Here, docker is used, so use
The docker ps command checks whether mysql related services are normal
#View docker related services docker ps ... #Enter docker-mysql docker exec -it docker_mysql bash ... # enter mysql mysql -uroot -p # then enter the password ...
Through the above command, you can check whether mysql is started,
(2). Modify mysql password
After the MySQL service is set up,
By default, the root user cannot perform remote access connections, or if the password of the root user is wrong, the above error will also be reported, so modify the mysql password to exclude
1). View temporary password
The password of mysql installed for the first time has not been changed, use the following command to view the generated temporary password
grep "A temporary password is generated for root@localhost" /var/log/mysqld.log
2). Login
mysql -uroot -p #Then enter the temporary password and enter the mysql side
3). Change password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
localhost represents the address,
root stands for
Database password, root can be changed to its own password
Executing the above code may result in an error:
Solution:
View current password policy
show variables like '%validate_password_policy%'; show variables like '%validate_password_length%';
Modify password policy
Modify Length and Weakness
set global validate_password_policy=0; #Set to weak password set global validate_password_length=1; #The minimum password length is 1
Revise again
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Refresh
flush privileges;
Exit
quit;
Login again
mysql -uroot -p
show databases;
(3). Enable remote access
use mysql; #access mysql library update user set host = '%' where user = 'root'; #Make root accessible on any host FLUSH PRIVILEGES; #refresh
When executing the above update, an error may be reported:
Duplicate entry '%-root' for key 'PRIMARY'
This is because
There are multiple ROOT user records in the USER table,
It is best to delete the user whose user field is root except that the host is ‘%’ or ‘localhost’, and then use the command
FLUSH PRIVILEGES
Refresh will fix it
use
select host from user where user = ‘root’; Check if the host already exists
% this value
Then
use command
FLUSH PRIVILEGES
Refresh, this solves the above problem, connect to the database again to see if it is successful
(4). Modify mysql configuration
1). Method 1
in my.cnf[
add configuration in mysqlId]
skip-grant-tables
This configuration can realize remote login, but skip the permission and write directly in the configuration file.
Not recommended for use
2). Method 2
Use the following commands to operate
grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option; FLUSH PRIVILEGES;
Enter the database through the mysql -uroot -p command and enter the password
mysql -uroot -p
Then execute the following command
grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
refresh and exit
FLUSH PRIVILEGES; exit;
(5). Summary
It can be solved by the above exclusion method
ERROR 1045 (28000)_ Access denied for user ‘root’@’localhost’ reported an error
Notice:
If mysql is deployed by docker, you may need to run it again every time you restart the container
Step (4). Modify the mysql configuration
This command, this also has a solution:
#Configure the mysql password and the default authentication plug-in - solve the problem of not being able to log in every once in a while #Identity plugin-mysql_native_password local password default_authentication_plugin=mysql_native_password #Password expiration time - never expires default_password_lifetime=0
Other solutions:
docker exec -it to enter mysql, execute the following command to solve:
mainly
Pay attention to the parameter mysql_native_password
mysql -u root -p ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; flush privileges; exit;