3

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)

4

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
  • Actually @f35 I didn't manage to fix it after all - would you mind reading my EDIT? – Max Williams Dec 7 '16 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 '16 at 19:07
  • I actually just dropped and created the root user, following this post: askubuntu.com/questions/766334/… – Max Williams Dec 8 '16 at 9:18
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
  • 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 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.