I am trying to reset my root password using following command:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test');
and it gives me following error:
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 ‘PASSWORD(‘test’)’ at line 1
Please let me know what is it that I am doing wrong here.
Tiny
26.8k103 gold badges329 silver badges595 bronze badges
asked Apr 25, 2015 at 14:49
I know it is old but it can help.
I hade this error when i used PASSWORD() function:
SET PASSWORD FOR 'myuser@localhost' = PASSWORD('my_new_password');
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
‘PASSWORD(‘basket8′)’ at line 1
Because in MySQL 5.7 and later it is not necessary to use the PASSWORD() function:
SET PASSWORD FOR 'myuser'@'localhost' = 'my_new_password';
Query OK, 0 rows affected (0.01 sec)
answered May 25, 2019 at 14:36
Yacine RouiziYacine Rouizi
1,2102 gold badges14 silver badges31 bronze badges
You do not need to quote the user name and it should be as
mysql> set password for root@'localhost' = password('test');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges ;
Query OK, 0 rows affected (0.00 sec)
The other way of re-setting the password is
Login to mysql as root and then update the mysql database User table
abhik@-N4010:~$ mysql -u root -p
Enter 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 user set Password = password('test') where host='localhost' and User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges ;
Query OK, 0 rows affected (0.00 sec)
answered Apr 25, 2015 at 14:53
13
I was trying to install mysql_secure_installation in Ubuntu But I am facing the following error Failed! Error: SET PASSWORD has no significance for user ‘root’@’localhost’ as the authentication method used doesn’t store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.. We are going to Learn about All Possible Solutions So Lets Get Start with This Article.
Contents
- Solution 1: Follow these steps
- Update: 28-06-2022
- Solution 2: ALTER User with mysql_native_password
- Conclusion
Solution 1: Follow these steps
First of all, You need to change the authentication parameters to do so just run this command first.
sudo mysqlThen run This ALTER Query to change the authentication parameters.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';And now, you are able to run the mysql_secure_installation command.
sudo mysql_secure_installationNow, Your error must be solved. Thanks.
Update: 28-06-2022
If You Face Below Error After ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password by ‘mynewpassword’; This Command.
ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsThen there is Three Possible Solution For This Error.
- The first one is to Use a Strong Password Just Like Addy@789**$ This Password Will satisfy current policy requirements.
- The second one is just Uninstall the validate_password plugin with these two commands
- mysql -h localhost -u root -p
- and then
- uninstall the plugin validate_password;
- The third solution is You can set the validate_password.policy to do so Just run
- sudo mysql
- and Then
- SET GLOBAL validate_password.policy=LOW;
And Now Your ERROR 1819 (HY000): Your password does not satisfy the current policy requirements is Solved.
Solution 2: ALTER User with mysql_native_password
You just need to Update the User With the mysql_native_password method. Just Set the Password for your root user with the help of mysql_native_password. Just Run this command in your terminal.
# Open MySql From terminal
sudo mysqlThen Update the user With the mysql_native_password method. Just like Below.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';Now Your error will be solved Successfully. Thank You So Much For Visiting Our Website.
Conclusion
You need to change the authentication parameters for the root user and then your error will be solved. Hope Our Article helped you to solve this error. Comment below if you are still facing this issue. Also, Comment below on which solution worked for you.
Also, Read
- How to Switch PHP versions on Commandline ubuntu
During our work in support, we see this again and again: “I try to connect to MySQL and am getting a 1045 error”, and most times it comes accompanied with “…but I am sure my user and password are OK”. So we decided it was worth showing other reasons this error may occur.
MySQL 1045 error Access Denied triggers in the following cases:
1) Connecting to wrong host:
|
[engineer@percona]# mysql -u root -psekret mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES) |
If not specifying the host to connect (with -h flag), MySQL client will try to connect to the localhost instance while you may be trying to connect to another host/port instance.
Fix: Double check if you are trying to connect to localhost, or be sure to specify host and port if it’s not localhost:
|
[engineer@percona]# mysql -u root -psekret -h <IP> -P 3306 |
2) User does not exist:
|
[engineer@percona]# mysql -u nonexistant -psekret -h localhost mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user ‘nonexistant’@‘localhost’ (using password: YES) |
Fix: Double check if the user exists:
|
mysql> SELECT User FROM mysql.user WHERE User=‘nonexistant’; Empty set (0.00 sec) |
If the user does not exist, create a new user:
|
mysql> CREATE USER ‘nonexistant’@‘localhost’ IDENTIFIED BY ‘sekret’; Query OK, 0 rows affected (0.00 sec) |
3) User exists but client host does not have permission to connect:
|
[engineer@percona]# mysql -u nonexistant -psekret mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user ‘nonexistant’@‘localhost’ (using password: YES) |
Fix: You can check to see which host user/host MySQL allows connections with the following query:
|
mysql> SELECT Host, User FROM mysql.user WHERE User=‘nonexistant’; +————-+————-+ | Host | User | +————-+————-+ | 192.168.0.1 | nonexistant | +————-+————-+ 1 row in set (0.00 sec) |
If you need to check from which IP the client is connecting, you can use the following Linux commands for server IP:
|
[engineer@percona]# ip address | grep inet | grep -v inet6 inet 127.0.0.1/8 scope host lo inet 192.168.0.20/24 brd 192.168.0.255 scope global dynamic wlp58s0 |
or for public IP:
|
[engineer@percona]# dig +short myip.opendns.com @resolver1.opendns.com 177.128.214.181 |
You can then create a user with correct Host (client IP), or with ‘%’ (wildcard) to match any possible IP:
|
mysql> CREATE USER ‘nonexistant’@‘%’ IDENTIFIED BY ‘sekret’; Query OK, 0 rows affected (0.00 sec) |
4) Password is wrong, or the user forgot his password:
|
[engineer@percona]# mysql -u nonexistant -pforgotten mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user ‘nonexistant’@‘localhost’ (using password: YES) |
Fix: Check and/or reset password:
You cannot read user passwords in plain text from MySQL as the password hash is used for authentication, but you can compare hash strings with “PASSWORD” function:
|
mysql> SELECT Host, User, authentication_string, PASSWORD(‘forgotten’) FROM mysql.user WHERE User=‘nonexistant’; +————-+————-+——————————————-+——————————————-+ | Host | User | authentication_string | PASSWORD(‘forgotten’) | +————-+————-+——————————————-+——————————————-+ | 192.168.0.1 | nonexistant | *AF9E01EA8519CE58E3739F4034EFD3D6B4CA6324 | *70F9DD10B4688C7F12E8ED6C26C6ABBD9D9C7A41 | | % | nonexistant | *AF9E01EA8519CE58E3739F4034EFD3D6B4CA6324 | *70F9DD10B4688C7F12E8ED6C26C6ABBD9D9C7A41 | +————-+————-+——————————————-+——————————————-+ 2 rows in set, 1 warning (0.00 sec) |
We can see that PASSWORD(‘forgotten’) hash does not match the authentication_string column, which means password string=’forgotten’ is not the correct password to log in. Also, in case the user has multiple hosts (with different password), he may be trying to connect using the password for the wrong host.
In case you need to override the password you can execute the following query:
|
mysql> set password for ‘nonexistant’@‘%’ = ‘hello$!world’; Empty set (0.00 sec) |
5) Special characters in the password being converted by Bash:
|
[engineer@percona]# mysql -u nonexistant -phello$!world mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user ‘nonexistant’@‘localhost’ (using password: YES) |
Fix: Prevent bash from interpreting special characters by wrapping password in single quotes:
|
[engineer@percona]# mysql -u nonexistant -p’hello$!world’ mysql: [Warning] Using a password on the command line interface can be insecure ... mysql> |
6) SSL is required but the client is not using it:
|
mysql> create user ‘ssluser’@‘%’ identified by ‘sekret’; Query OK, 0 rows affected (0.00 sec) mysql> alter user ‘ssluser’@‘%’ require ssl; Query OK, 0 rows affected (0.00 sec) ... [engineer@percona]# mysql -u ssluser -psekret mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user ‘ssluser’@‘localhost’ (using password: YES) |
Fix: Adding –ssl-mode flag (–ssl flag is deprecated but can be used too)
|
[engineer@percona]# mysql -u ssluser -psekret —ssl-mode=REQUIRED ... mysql> |
You can read more in-depth on how to configure SSL in MySQL in the blog post about “Setting up MySQL SSL and Secure Connections” and “SSL in 5.6 and 5.7“.
7) PAM backend not working:
|
mysql> CREATE USER ‘ap_user’@‘%’ IDENTIFIED WITH auth_pam; Query OK, 0 rows affected (0.00 sec) ... [engineer@percona]# mysql -u ap_user -pap_user_pass mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user ‘ap_user’@‘localhost’ (using password: YES) |
Fix: Double check user/password is correct for the user to authenticate with the PAM currently being used.
In my example, I am using Linux shadow files for authentication. In order to check if the user exists:
|
[engineer@percona]# cat /etc/passwd | grep ap_user ap_user:x:1000:1000::/home/ap_user:/bin/bash |
To reset password:
|
[engineer@percona]# sudo passwd ap_user Changing password for user ap_user. New password: |
Finally, if you are genuinely locked out and need to circumvent the authentication mechanisms in order to regain access to the database, here are a few simple steps to do so:
- Stop the instance
- Edit my.cnf and add skip-grant-tables under [mysqld] (this will allow access to MySQL without prompting for a password). On MySQL 8.0, skip-networking is automatically enabled (only allows access to MySQL from localhost), but for previous MySQL versions it’s suggested to also add –skip-networking under [mysqld]
- Start the instance
- Access with root user (mysql -uroot -hlocalhost);
-
Issue the necessary GRANT/CREATE USER/SET PASSWORD to correct the issue (likely setting a known root password will be the right thing: SET PASSWORD FOR ‘root’@’localhost’ = ‘S0vrySekr3t’). Using grant-skip-tables won’t read grants into memory and GRANT/CREATE/SET PASSWORD statements won’t work straight away. First, you need to execute “FLUSH PRIVILEGES;” before executing any GRANT/CREATE/SET PASSWORD statement, or you can modify mysql.users table with a query which modifies the password for User and Host like “UPDATE mysql.user SET authentication_string=PASSWORD(‘newpwd’) WHERE User=’root’ and Host=’localhost’;”
- Stop the instance
- Edit my.cnf and remove skip-grant-tables and skip-networking
- Start MySQL again
- You should be able to login with root from the localhost and do any other necessary corrective operations with root user.
Learn more about Percona Server for MySQL
I am going to take a very wild guess on this one
My guess is that you upgraded to MySQL 5.7 but did not upgrade the the mysql schema.
I wrote about the mysql.user table having a different number of columns per version:
Oct 10, 2014: MySQL service stops after trying to grant privileges to a userAug 07, 2013: MySQL — ERROR 1045 (28000): Access denied for user : permission issueMay 24, 2013: How often should MySQL (stock, Percona, etc.) be upgraded?May 01, 2013: How often should MySQL (stock, Percona, etc.) be upgraded?Apr 12, 2012: Cannot GRANT privileges as root (I actually show the differences in the column layouts in MySQL 5.6, 5.5, 5.1, 5.0, 4.x)
Now for the shocker
Did you know the following about mysql.user in MySQL 5.7 ?
- There are 45 columns
- There is no longer any column named
password - The column
authentication_stringstores the password now - I mentioned this
Oct 28, 2015: Install MySQL for Windows from .zip and reset root password
At this point, you are probably asking : Why in the world did create user 'testing'@'localhost' identified by 'password'; work ?
Look in my Apr 12, 2012 post. Please note the the column layout for mysql.user. The column authentication_string column appears in MySQL 5.6 and MySQL 5.5.
Please run this query
select Host, User, password_expired,Password,authentication_string from mysql.user;
You should see the encrypted password in the authentication_string column
What makes me believe this to be the case was the clue you provided
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
The command SHOW GRANTS; no longer shows the encrypted password in MySQL 5.7. I ran in to this issue even with pt-show-grants.
What should you do ?
Perhaps you should run mysqld --upgrade to fix the column layout. It looks like this in 5.7:
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
mysql>