278

I just installed Ubuntu 16.04 LTS along with the packages php, mariadb and nginx. I ran mysql_secure_installation and changed the root password.

Now when I try to login to mysql using the root account while logged in Ubuntu as normal user account I get access denied.

When I login using sudo mysql, mysql doesn't even ask me password. If I run mysql_secure_installtion I see that old settings never got set permanently.

What am I doing wrong?

2
  • If you'd like to get back to the default password less root access via the unix_socket plugin: sql UPDATE mysql.user SET plugin='unix_socket' WHERE user='root'; flush privileges Sep 29, 2020 at 5:44
  • 1
    ^ No, you'd use alter user root@localhost identified by unix_socket. The days of manipulating mysq.user tables are long gone. Use the SQL provided.
    – danblack
    Jan 17, 2021 at 2:56

11 Answers 11

499
+50

I recently upgrade my Ubuntu 15.04 to 16.04 and this has worked for me:

  1. First, connect in sudo mysql

     sudo mysql -u root
    
  2. Check your accounts present in your db

     SELECT User,Host FROM mysql.user;
     +------------------+-----------+
     | User             | Host      |
     +------------------+-----------+
     | admin            | localhost |
     | debian-sys-maint | localhost |
     | magento_user     | localhost |
     | mysql.sys        | localhost |
     | root             | localhost |
    
  3. Delete current root@localhost account

     mysql> DROP USER 'root'@'localhost';
     Query OK, 0 rows affected (0,00 sec)
    
  4. Recreate your user

     mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY '';
     Query OK, 0 rows affected (0,00 sec)
    
  5. Give permissions to your user (don't forget to flush privileges)

     mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
     Query OK, 0 rows affected (0,00 sec)
    
     mysql> FLUSH PRIVILEGES;
     Query OK, 0 rows affected (0,01 sec)
    
  6. Exit MySQL and try to reconnect without sudo from your local machine.

Edit: a previous version of the answer post recommended creating a user 'root'@'%'. However, it is more secure to create 'root'@'localhost' so connections can only be made from localhost, and not remotely. Both solutions work equally as well for local development.

I hope this will help someone :)

21
  • 18
    This was the only thing that worked for me, does somebody knows exactly what happened with the root user when you install mysql-server on 16.04?
    – Ruggi
    Aug 18, 2016 at 17:45
  • 43
    Hold it, Doesn't % mean you can connect from anywhere...remotely?
    – tread
    Nov 4, 2016 at 8:01
  • 12
    You can change row to: CREATE USER 'root'@'localhost' IDENTIFIED BY '';
    – vladnev
    Dec 5, 2016 at 8:36
  • 16
    To the security-concerned: the unsecured root@localhost mysql connection pattern is a bread-and-butter staple of local development but should appear absolutely nowhere else. Jan 11, 2017 at 20:31
  • 5
    The grant statement for the new root user here does not give "with grant" to root so root can't grant without running this afterwards: dba.stackexchange.com/a/62046/115679 Please change the grant statement to grant all privileges on *.* to 'root'@'localhost' with grant option;
    – Loren
    Apr 4, 2017 at 14:17
181

If you install 5.7 and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.

Taken from Change User Password in MySQL 5.7 With "plugin: auth_socket"

So in order to to change the plugin back to mysql_native_password:

  1. Login with sudo:

    sudo mysql -u root
    
  2. Change the plugin and set a password with a single command:

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

Of course you can also use the command above to set an empty password.

Just for the record, (and MariaDB < 10.2 users) there is also another way to only change the plugin without providing a password (leaving it empty):

update mysql.user set plugin = 'mysql_native_password' where User='root';
// to change the password too (credits goes to Pothi Kalimuthu)
// UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root';
FLUSH PRIVILEGES;
15
  • 2
    Thanks, for some reason this got set automatically suddenly after an apt-get upgrade I think...
    – Tominator
    Oct 28, 2016 at 9:32
  • 5
    Also, I like this answer more - it's a lot less destructive ;-)
    – benzkji
    Jan 30, 2017 at 11:27
  • 5
    For MariaDB < 10.2, to change plugin along with the password, here's the query: UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root'; FLUSH PRIVILEGES; Source: stackoverflow.com/a/41537019/1004587 Jul 11, 2017 at 11:12
  • 10
    This should be the accepted answer
    – Geo C.
    Nov 17, 2017 at 12:48
  • 4
    I wish I could vote this up twice. Mar 22, 2018 at 16:10
31

In short, on MariaDB

UPDATE mysql.user SET plugin = 'mysql_native_password', 
      Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

where you replace NEWPASSWORD with the password you want, and everything else verbatim.

The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user. You can test this as follows by switching to system root, and then type:

mysql -uroot -p

Then enter either no password or the wrong password. You'll probably be let in. (You may even be able to log in from the unix root by simply # mysql as the password is irrelevant and the user is defined).

So what's happening? Well, if you log in as root and do the following:

select User,host,plugin from mysql.user; 
+----------------+-----------+-----------------------+
| User           | host      | plugin                |
+----------------+-----------+-----------------------+
| root           | localhost | auth_socket           |
+----------------+-----------+-----------------------+

you'll note auth_socket (which may read unix_socket on MariaDB). These sockets ignore passwords and allow the corresponding Unix user in without a password check. This is why you can log in with root but not with a different user.

So the solution is to update the Users to not use the auth_socket/unix_socket and properly set a password.

On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. NEWPASSWORD is your password. mysql_native_password you type verbatim.

UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

(It's possible that setting the plugin to empty would work. YMMV. I didn't try this. So this is an alternative.)

UPDATE mysql.user SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

Otherwise:

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

Then

FLUSH PRIVILEGES;

For the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant statement exactly right - easier to simply update the root you already have.

In my experience, the issue only happens with the root user, as other users will be added manually not part of an initial install/update.

5
  • 2
    Found this to work perfectly. Makes me wonder though why changing the root password with mysql_secure_installation didn't have the same effect. Where does it store the new password?
    – Mausy5043
    Jul 28, 2018 at 10:14
  • Can you edit your answer and say more about each of the commands as you go? I'm not clear on which are applicable. Is it the two UPDATEs that apply to MariaDB <10.2 and the ALTER to other versions? And is the second UPDATE an alternative to the first, or do we need to type both? Finally, is 'mysql_native_password' something to type verbatim, or should we replace it with our intended root password for MySQL? Dec 17, 2018 at 2:23
  • 1
    @MichaelScheper It's a while back so I don't want to edit too much, but here's what I remember. (1) Do the first UPDATE only (<10.2 may not be a requirement). (2) Yes, type 'mysql_native_password' verbatim. Essentially, what you are doing is telling mariadb: "Don't use Unix priveleges, use the database priveleges". The mysql_native_password is that option.
    – Gazzer
    Dec 17, 2018 at 4:26
  • Damn I spelt 'privilege' wrong!
    – Gazzer
    Dec 17, 2018 at 4:32
  • 1
    Looks like you only missed the edit time limit by 1½ minutes, too! I get frustrated by that sometimes, too. Anyhow, thanks! Dec 21, 2018 at 2:01
16

By default, root user is set to authenticate through an auth_socket rather than with a password.

In order to use the password authentication, do:

  1. Login to MySQL root shell:

    sudo mysql
    
  2. Check authentication methods enabled for different users (optional)

    SELECT * FROM mysql.user;
    
  3. Make root to authenticate with a password:

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
    
  4. Flush privileges for the changes to take effect;

    FLUSH PRIVILEGES;
    
  5. Exit and authenticate with your password

     exit
     mysql -u root -p 
    
5
  • When I did this, I couldn't sudo mysql as root anymore. I had to su first, and then could log into mysql as root. So I changed it back to auth_socket.
    – untill
    Jan 22, 2018 at 14:17
  • Single command: mysql -u root -p -e "use mysql;update user set plugin='mysql_native_password' where user='root';flush privileges;" Jul 23, 2018 at 18:43
  • If you want to lose access to your MySQL instance, then this answer is the easy way. Aug 22, 2019 at 8:56
  • Yes. This worsened the problem.
    – Guissmo
    May 30, 2020 at 13:18
  • This is the only thing that worked on me for a pristine Ubuntu 20.04 LTS install.
    – chx
    Mar 24, 2021 at 16:33
3

Try to create new mysql account, for me it has worked (mysql 5.7.12):

  1. Login as sudo:

    sudo mysql -uroot
    
  2. Create new user and grant him privileges (no password):

    CREATE USER 'admin'@'localhost' IDENTIFIED BY '';
    GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
    
  3. Login as new user:

    mysql -uadmin
    
0
3

If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost. .

The only way to set password is to switch to native authentication like:

$ sudo mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

1
  • This is actually the only answer that explains what is really happening. Everyone else is shooting from the hip.
    – Houman
    Apr 13, 2021 at 11:30
2

Try this code first,

echo "CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';" > your_init_file.sql
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;" >> your_init_file.sql 
echo "FLUSH PRIVILEGES;" >> your_init_file.sql

and then,

killall mysqld
mysqld_safe --init-file=$PWD/your_init_file.sql

then press Ctrl+Z and type: bg to run the process from the foreground into the background, then verify your access by:

mysql -u root -proot
mysql> show grants;
2

I’ve been adapting some provisioning scripts I have created to use MariaDB and ran into this exact issue. Piecing together lots of info here an Gazzer’s answer really zeros in in the issue; it all boils down to the auth_socket/unix_socket setting.

So when using MariaDB 5.5 (under Ubuntu 14.04) and MariaDB 10 under (Ubuntu 16.04), logging into MySQL and running this command cleared things up right away:

UPDATE mysql.user SET plugin='' WHERE User='root';
FLUSH PRIVILEGES;

The other answers—including the highest voted answer as of this post by Loremhipsum—really encourage bad practices by recommending dropping a user and then recreating them. To me, that is a pretty radical solution. The best/simplest solution os to nullify the plugin value, flush privileges and get on with life.

2

I had to do two things (thanks to @Todor and @Loremhipsum):

update mysql.user set plugin = 'mysql_native_password' where User='root';
grant all privileges on *.* to 'root'@'localhost';

and then:

FLUSH PRIVILEGES;

I would not recommend to drop user root.

0

Maximum security

As unix authentication is the most secure authentication, the best thing to do is leave the default root account as is (i.e. continue to use unix socket authentication with no password) and if not using the root account, use it via sudo. If an attacker has root/sudo access; then they already have full control of your server...

When you need to access MySQL/MariaDB via an alternate method (i.e. not CLI), create a new MySQL/MariaDB user with a password and only the required permissions.

-2

I had the same issue and running the following fixed it:

mysql_upgrade --force
3
  • Sadly that didn't work. I am reinstalling ubuntu.
    – codescope
    May 3, 2016 at 9:58
  • 1
    I just did fresh install of ubuntu 16.04 and installed mariad-server. After installing I ran mysql_secure_installation and set password. After going through rest of the steps in mysql_secure_installation, I ran it again and it looks like it is not saving the changes. I still can't login from my normal user account. Could it be a bug?
    – codescope
    May 3, 2016 at 11:45
  • This will never help. The issue has nothing do with the actual install of the MariaDB binary but rather the user root being set with plugin settings that are non-standard. The only edge case where an upgrade of the DB might help in a case like this is if the mysql_upgrade process itself accounts for the plugin setting which I doubt. Oct 20, 2017 at 1:37

You must log in to answer this question.

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