7

EDIT: I believe this is actually a duplicate of https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04
I used the top answer from that page and it worked.


I've just installed a new Linux Mint 18 MATE, and am trying to install Mysql. I did this as follows:

sudo apt-get install mysql-server

which has installed

mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrappe

and this went through a few "gui" screens prompting me for passwords. I just hit enter for each, which I thought was like saying "No password".

But now, I thought i'd be able to get into it with mysql -u root, but it says

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I tried a reboot just in case. Is the problem that I'm not using the right password, or is it something more fundamental? If it's the password, how to I find out what it is/reset it? Thanks, Max

EDIT: Following f35's answer below, i did the following:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root

then in mysql, i try to change the password but it doesn't recognise the field name: I do a desc to see what is in there:

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("password") where User='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> desc 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)

Not sure what to do next - also, what is the syntax for setting no password? Is it just set password=''?

NOTE: I can also log in with the debian-sys-main user but am still not sure how to change the root user's password.

My mysql version is: Server version: 5.7.16-0ubuntu0.16.04.1 (Ubuntu)

5 Answers 5

8

1) you can try to reconfigure the mysql-server :

sudo dpkg-reconfigure mysql-server

2) check if you have the debian-sys-maint passwd

cat /etc/mysql/debian.cnf

and check for :

user     = debian-sys-maint
password = xxxxGx0fSQxxGa

debian-sys-maint has all privileges on the mysql server

3) if it do not solve the problem, you can reset the passwd :

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root

mysql> use mysql;
mysql> update user set password=PASSWORD("NewPasswd") where User='root';
mysql> flush privileges;
mysql> quit

sudo service mysql stop
sudo service mysql start
mysql -u root -p
3
  • Actually @f35 I didn't manage to fix it after all - would you mind reading my EDIT? Dec 7, 2016 at 12:52
  • 1
    Try with SET PASSWORD FOR 'root'@'localhost' = 'NewPasswd'. (it's ok for me with mysql 5.7.16)
    – f35
    Dec 7, 2016 at 19:07
  • I actually just dropped and created the root user, following this post: askubuntu.com/questions/766334/… Dec 8, 2016 at 9:18
5

After my fresh installation of MySQL 8.0.24 on Ubuntu 20.10, I was unable to login with

mysql -u root

which results in

Error 1698 (28000): Access denied for user 'root'@'localhost'

However, I was able to login with

sudo mysql -u root

I guess this is because super user in linux is the root user.

2

I had to do this on CentOS 8:

# In regular terminal
sudo systemctl stop mysqld
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking --user root
mysql -u root

# In MySQL terminal
use mysql;
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'My^Pass1';
exit

# In regular terminal
sudo pkill mysqld
sudo systemctl start mysqld
mysql -u root -p
1
  • 1
    This worked for me on AWS Linux 2 as well! Thank you. May 24, 2021 at 22:04
1

it worked for me i just modify the line below from

mysql> update user set password=PASSWORD("NewPasswd") where User='root';

into

mysql> update user set password=PASSWORD("NewPasswd"), plugin='mysql_native_password', host='%' where User='root';
1

I ran into a couple issues with the accepted answer.

First time I ran sudo service mysql stop it didn't stop the service and I didn't realize it. Do a ps -fe | grep mysql. So make sure the process is done before running any of the following commands. After some tries I ended up killing it with sudo kill -KILL <mysqld process id>.

Then after running the sql update, again service mysql stop didn't work. So check your process id with ps -fe | grep mysqld_safe and use sudo kill -TERM <process id> as instructed below. If you have more than one process use the one with the lowest id.

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root

mysql> use mysql;
mysql> update user set password=PASSWORD("NewPasswd"), plugin='mysql_native_password', host='%' where User='root';
mysql> flush privileges;
mysql> quit

sudo kill -TERM <mysqld_safe process id>
sudo service mysql start
mysql -u root -p
1
  • This helped me, thanks. Only in my case I used authentication_string instead of password, that is UPDATE user set authentication_string = PASSWORD('101010'), plugin = 'mysql_native_password', where User = 'root' and Host = 'localhost'; I have MySQL 5.7 installed. And I killed processes with sudo killall mysql and sudo killall mysqld - I have Ubuntu 18 inside WSL on Windows 10.
    – RussCoder
    Apr 25, 2019 at 11:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .