MariaDB installed without password prompt

Question

I've installed mariadb from Ubuntu 15.04 repositories using the Ubuntu software center or at the command prompt (apt-get install maraidb-server), but no password is asked for root user. Now I'm able to connect to mysql on command line without password, but connecting using Mysql-Workbench or python mysqldb library failed with the "Access denied for user 'root'@'localhost'" message

This is not a question of the installation. Those servers _always_ create the root account without password. You have to internally change it as documented. About the workbench or similar failing: whyever that is, but the problem will be solved the moment you have set a password for the account which you have to do anyway. — arkascha, Jun 13 '15 at 07:03
@arkascha: According to this manual, mariadb will ask for root password at installation time: http://tecadmin.net/install-mariadb-10-on-ubuntu/ — mtoloo, Jun 13 '15 at 08:06
I don't know much about Ubuntu, I prefer other distributions, so I cannot say anything specific. Certainly such thing is possible, I have never seen it though it probably makes sense. Anyway: that is not your issue, is it? Your issue is that your root account apparently has no password set. Did you check that inside the internal `mysql` table? If so, then just set a password and all is fine. — arkascha, Jun 13 '15 at 08:08
I've set the password with three different ways: using `set password` or `update user` commands then `flush privileges` inside mysql. Using `mysqladmin` command. Using `mysql_secure_installation`. But None of them fixed the problem. I am still able to connec to mysql from command line without password, but mysql-workbench can not connect. — mtoloo, Jun 13 '15 at 10:34

score 58 · Accepted Answer · edited Aug 26 '20 at 21:34

58

Starting with MariaDB 10.4 root@localhost account is created with the ability to use two authentication plugins:

First, it is configured to try to use the unix_socket authentication plugin. This allows the root@localhost user to login without a password via the local Unix socket file defined by the socket system variable, as long as the login is attempted from a process owned by the operating system root user account.

Second, if authentication fails with the unix_socket authentication plugin, then it is configured to try to use the mysql_native_password authentication plugin. However, an invalid password is initially set, so in order to authenticate this way, a password must be set with SET PASSWORD.

That is why you don't need a password to login on a fresh install.

But then another quote:

When the plugin column is empty, MariaDB defaults to authenticating accounts with either the mysql_native_password or the mysql_old_password plugins. It decides which based on the hash used in the value for the Password column. When there's no password set or when the 4.1 password hash is used, (which is 41 characters long), MariaDB uses the mysql_native_password plugin. The mysql_old_password plugin is used with pre-4.1 password hashes, (which are 16 characters long).

So setting plugin = '' will force it to use password based authentication. Make sure you set a password before that.

sudo mysql -u root

[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q

edited Aug 26 '20 at 21:34

HoldOffHunger

18,769
10
104
133

answered Jan 08 '16 at 10:03

Aashish

767
8
4

18

Please add some explanation to your answer! – ρss Jan 10 '16 at 15:30
2

Mysql tries to authenticate root using plugin, not password. You need just disable plugin usage for root. – marshall May 23 '16 at 17:07
1

This advice saved me - thanks a lot. I can't believe this isn't in the official documenation, or in any other guides that describe how to reset the root password. – user1283068 Aug 31 '16 at 12:12
3

**On Ubuntu 16.04**, I had an "SQL Syntax Error" using this code given by Aashish: >update user set plugin='' where User='root'; In place, I used that to overwrite authentication method : > update user set plugin="mysql_native_password"; – nrvx Mar 03 '17 at 13:39
1

also on debian 9 stretch, you have to use "update user set plugin='mysql_native_password' ..." – deviato May 25 '17 at 10:26
3

explanation can be found [here](https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/) – Pedru Feb 03 '18 at 12:22

score 13 · Answer 2 · answered Nov 04 '17 at 06:10

13

sudo mysql -u root

[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q

This needs to be followed by following command

# mysql_secure_installation

answered Nov 04 '17 at 06:10

ShaileshKumarMPatel

129
1
6

If you're just dabbling around in a development environment, do you need to configure mysql_secure_installation? – DeltaFlyer Jan 02 '20 at 18:21

score 6 · Answer 3 · answered Dec 12 '17 at 21:15

it is common for root to have password-less access if accessing from localhost, I recommend this setting to be left alone.

I also recommend that you create a user with less permissions and allow that user to login remotely.

create user my_admin identified by '12345';
create database my_database;
grant all on my_database.* to my_admin;

This way you have a little more security.

If you do need to connect as root from a tool like workbench, you can configure those tools to create an ssh tunnel and connect to the database as localhost.

score 3 · Answer 4 · answered Jun 27 '18 at 08:54

As @Pedru noticed, the "Access denied for user 'root'@'localhost'" message is due to the fact that Debian and Ubuntu enable the UNIX_SOCKET Authentication Plugin plugin by default, allowing passwordless login (See also Authentication Plugin - Unix Socket). This is not an installation problem.

It means that if you type mysql -u root -p in the Linux Shell, root is actually the Linux root (or linked to it, I don't know how this actually works). So that if you logged on Linux with another account, you will get an error: ERROR 1698 (28000): Access denied for user 'root'@'localhost'. Better type sudo mysql -u root -p or sudo mysql -u root if the password is not yet defined.

score 3 · Answer 5 · answered Aug 31 '20 at 08:34

3

If you want to switch to the mysql_native_password authentication plugin, then you could use

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('new_password');

For further information see https://mariadb.com/kb/en/authentication-plugin-unix-socket/

answered Aug 31 '20 at 08:34

umairnaqvi

57
6

The [ALTER USER](https://mariadb.com/kb/en/alter-user/) statement was introduced in MariaDB 10.2.0. – jfgiraud Nov 25 '20 at 17:32

score 1 · Answer 6 · answered Nov 25 '20 at 17:47

For a MariaDB version greater than or equals to 10.2.0, the response of user3054067 is correct.

For a MariaDB version less than 10.2.0, you can do this :

me$ sudo su -

root$ mysql -u root

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> UPDATE mysql.user SET plugin='mysql_native_password' WHERE User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

In another terminal,

me$ mysql -u root -pnew_password
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 83
Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

MariaDB installed without password prompt

6 Answers6