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

Answer 1

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 

Answer 2

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