MariaDB without password prompt for Root on Ubuntu Linux

|

|

The MariaDB database server now installs on Ubuntu without requiring a root user password for server access, a departure from its MySQL origins. This could cause issues with dependent applications like phpMyAdmin and MySQL Workbench. However, it’s possible to re-enable password authentication by updating the user plugin settings, restarting and securing the MariaDB server, and…

I recently tested the MariaDB database server on Ubuntu 17.10 / 18.04 and discovered that the system now installs on Ubuntu without prompting the root user for a password to access the server.

Is this new?

It’s always been the case where MySQL and MariaDB, a fork of MySQL, prompt passwords every time before access is granted to the server. Not anymore for MariaDB. Now, simply installing the database gives the root access without a password.

Even after running the command sudo mysql_secure_installation. The root account password is never required. However, other applications and services that depend on MariaDB will fail if the root password is needed for authentication.

phpMyAdmin and MySQL Workbench database may fail if MariaDB is set up this way.

This brief tutorial will show students and new users how to set a root password for MariaDB and allow password authentication.

After digging, I discovered that MariaDB uses the unix_socket plugin to authenticate. And not passwords. Even if you set a password, it is ignored. To re-enable password authentication, follow the steps below:

Login to the MariaDB server by running the commands below

sudo mysql -u root

Notice no password?

That should get you into the database server. After that, run the commands below to turn off plugin authentication for the root user.

use mysql;
update user set plugin='' where User='root';
flush privileges;
exit

Restart and run the commands below to set a new password.

sudo systemctl restart mariadb.service

After that, run the commands below to secure the MariaDB server and create a new root password.

sudo mysql_secure_installation

When prompted, answer the questions below by following the guide.

  • Enter current password for root (enter for none): Just press Enter
  • Set root password? [Y/n]: Y
  • New password: Enter password
  • Re-enter new password: Repeat password
  • Remove anonymous users? [Y/n]: Y
  • Disallow root login remotely? [Y/n]: Y
  • Remove test database and access to it? [Y/n]:  Y
  • Reload privilege tables now? [Y/n]:  Y

You should now be able to log on with password authentication. Other applications should now work with root password authentication.

The next time, type the commands below to log

sudo mysql -u root -p

Then, type the password to sign on

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.1.25-MariaDB-1 Ubuntu 17.10

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

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

MariaDB [(none)]>

Enjoy!

You may also like the post below:

Like this:



10 responses to “MariaDB without password prompt for Root on Ubuntu Linux”

  1. LKT Avatar
    LKT

    Thanks for the hint – saved me some time!

    In addition I would add that once returning to old password style auth, you need to also alter and fix /etc/mysq/debian.cnf in order for startup scripts to be able to work and shutdown the service as the least.
    By default now they put user root there with no password, I reverted back to the debian-sys-maint user with the following steps:
    1. From another server that has the old-style auth: mysqldump –complete-insert –extended-insert=0 -u root -p mysql | grep ‘debian-sys-maint’ copy the output and execute it as a query against the ‘mysql’ database on the new setup. (mysql -p mysql ,then paste the result from the dump)
    2. flush privileges;
    3. SET PASSWORD FOR ‘debian-sys-maint’@’localhost’ = PASSWORD(‘verystrongpass’);
    4. flush privileges; \q
    5. edit /etc/mysql/debian.cnf and set user to debian-sys-maint and password to the value you set above
    6. service mysql stop and ps ax | grep mysql to check it realy succeeded to stopp it – so it worked!

    Hope that helps someone.
    BR

  2. HUSSEIN SALUM Avatar
    HUSSEIN SALUM

    Thank you very much.

  3. 1 Avatar
    1

    Access denied for user root after this how-not-to-do

    1. Juan Avatar
      Juan

      you did it something wrong or your version isn’t the same. I’ve tried this and it works!.

  4. Bob Kline Avatar
    Bob Kline

    A Great help to get the LAMP shining again………..Thanks

  5. Michael Avatar
    Michael

    thanks, been having this problem for a long time. Other sites didnt help me. Thanks again!

  6. Jos Avatar
    Jos

    Greates website for newbie! Came back second time to find setting for my server!

  7. Ric Avatar
    Ric

    update user set plugin=” where User=’root’ throws me some errors.

    Column ‘plugin’ is not updatable

    1. Ben Avatar
      Ben

      Had the same problem. I guess you’re using MariaDB 10.4? The user table is now only a view over a new table called global_priv, which is why you can’t change it. They’ve changed the root authentication method for 10.4 and explained it here: https://mariadb.org/authentication-in-mariadb-10-4/
      If the link doesn’t get through the comment system, just search “authentication in mariadb 10.4” in your favourite search engine.

      Hope it helps.

  8. Ben in Seattle Avatar
    Ben in Seattle

    Works great for Debian 10 as well. I dislike running anything as root (sudo), so it’s great to be able to set the “root” account of the database to have a password. That way, I can administrate the database without having excessive powers, like being able to destroy the whole machine. I cannot fathom why they changed this.

Leave a Reply to LKT Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.