Ubuntu server 20.04 can't set MySQL password

Question

What is the right way to set the root password in the database mysql?

I installed phpmyadmin and set a password there and log in as phpmyadmin and the password I did but it has no privileges.

Guess this is a new database and can't set it up the same way as the old one not sure.

Looked up on Google how to set the password in mysql and just get errors.

Guess they have to update Webmin for this when trying to set the password in there get this error.

Failed to change administration password : SQL set password for 'root'@'localhost' = password('XXXXXXX') failed : 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('XXXXXXX')' at line 1

In phpmyadmin I can change the password but in Webmin it's still says no root password. So I don't think it worked.

Guess they have to update Webmin and phpmyadmin for this new Database MySQL version 8.0.19-0ubuntu5

Here is the command line I can log in with the password I set in phpmyadmin but can't change it.

root@fujitsu-laptop:~# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 289
Server version: 8.0.19-0ubuntu5 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password for 'root'@'localhost' = PASSWORD('XXXXXX');
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('XXXXXX')' at line 1
mysql> exit
Bye
root@fujitsu-laptop:~#

Does this answer your question? How to change root password for mysql and phpmyadmin — Marc Vanhoomissen, Apr 30 '20 at 18:36
"Rather than using SET PASSWORD to assign passwords, ALTER USER is the preferred statement for account alterations, including assigning passwords. " https://dev.mysql.com/doc/refman/8.0/en/set-password.html so try with "alter" :) — Rinzwind, May 01 '20 at 10:16

Akib Azmain Turja · Answer 1 · 2020-05-18T10:10:11.210

Yes, you are right. That command for changing password is outdated. But Webmin and phpMyAdmin still use that command. In MySQL 8.0, to change your password, you have to do the following:

Login to MySQL

As Webmin says there is no password set for root you can this which won't prompt you for password:

sudo mysql

If it says ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) then webmin is wrong and the password is already set (if you have not screwed up everything and all is fine). Then you can try this if you want to change your password (this will prompt for password):

sudo mysql -p

Set or change your password

Change your password with the following command: (You should use ALTER USER as it has a lot of options then SET PASSWORD)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

Or you can use this command:

SET PASSWORD = 'your_password';

You can now exit with exit in MySQL client.

If you wonder why I instructed to try sudo mysql first, then you should know that if a MySQL user don't have password set, then whatever set he or she type in the prompt by sudo mysql -p , the login will be successful. So, by trying sudo mysql first, you will know if your password is already set.

Be sure to change your_password with your own password.

Now I can't get WordPress to use the DataBase. I did get it to use it but not with user wordpress like I set up. I had to have it use phpmyadmin the only one besides root but can't do a lot with root ether they don't let you. It looks like I set all permissions for wordpress user but I still get by WordPress password is right but can't connect to the database till I switch the user to phpmyadmin for wordpress database. — Raymond Day, May 19 '20 at 10:36
The sudo mysql -p does ask for a password and I can log in. But Webin swill shows No password! I guess have to wait for Webmin to update to use this new mysql right yet. — Raymond Day, May 19 '20 at 10:41
phpmyadmin says no password for root when I go to set it on the command line it says "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements" mysql> quit — Raymond Day, May 19 '20 at 10:48

Ubuntu server 20.04 can't set MySQL password

1 Answers1

Login to MySQL

Set or change your password