I'm working on Xubuntu 15.04. I already installed the MariaDB-Server on various systems and was always asked for a root password during installation. This time, however, I don't remember being asked for the password. When I try to login without a password (or a blank password), I get the Access denied for user 'root'@'localhost' error. I tried uninstalling the package completely by

sudo apt-get remove mariadb-server
sudo apt-get purge mariadb-server

When I reinstalled, I still didn't get asked for the root password.

I tried the mysqld --skip-grant-tables approach from mysql how to fix Access denied for user 'root'@'localhost' . I can modify the password for the root user in the mysql database - at least the hash value changes - but I still cannot login with the new password after a restart of the mysql-server. I still get the same error.

The user debian-sys-maint does not exist. So, I cannot use it to fix anything.

Any ideas what else I could try?

You need to reset the password. so for that

sudo mysql -u root
use mysql;
update user set plugin='' where User='root';
flush privileges;
exit;
  • 6
    The non-obvious "magic" to this approach is that the root operating system user is able to log into the database without having to specify a password because plugin: auth_socket is enabled, by default. In other words, there is no need to use --skip-grant-tables as would have been necessary in MySQL versions past. (Also, technically, this measure is not "resetting the password"; it is disabling a plugin.) – Ben Johnson Jul 7 '16 at 13:05
  • 1
    +1, Can you explain about reason why? – A1Gard Nov 19 '16 at 10:40
  • 4
    This approach will cause problems later. If you disable the plug-in, the daily cron job will break as it's assuming it can log in with that plug-in. See my answer for details. – colan Dec 19 '16 at 14:27

The idea with the new set-up is that you shouldn't be using passwords at all. See UNIX_SOCKET Authentication Plugin for details.

What's especially relevant is the contents of /usr/share/doc/mariadb-server-10.0/README.Debian.gz on Ubuntu 16.04:

On new installs no root password is set and no debian-sys-maint user is created anymore. Instead the MariaDB root account is set to be authenticated using the unix socket, e.g. any mysqld invocation by root or via sudo will let the user see the mysqld prompt.

You may never ever delete the mysql user "root". Although it has no password is set, the unix_auth plugin ensure that it can only be run locally as the root user.

The credentials in /etc/mysql/debian.cnf specify the user which is used by the init scripts to stop the server and perform logrotation. This used to be the debian-sys-maint user which is no longer used as root can run directly.

So if you disable that plug-in for root and set a password, the daily cron job will break as it's assuming it will log in as root without a password, but with the plug-in.

Later it says:

Scripts should run as a user have have the required grants and be identified via unix_socket.

So it looks like passwords should no longer be used by applications.

  • 1
    I just encountered the very problem that you describe: "So if you disable that plug-in for root and set a password, the daily cron job will break as it's assuming it will log in as root without a password..." The non-obvious implication of the UNIX_SOCKET Authentication Plugin being enabled (which is now the default) is that one's ability to authenticate as the root database user with a password is disabled. Nowhere on mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin is this considerable limitation mentioned. Accordingly, it is impossible to login as root e.g. over SSH. – Ben Johnson Aug 30 '16 at 18:04
up vote 2 down vote accepted

I solved the problem following the answer from this post:

Can't reset MySQL (MariaDB) root password

One has to change the plugin field of mysql.user for all roots to a blank string.

  • This issue is described here: percona.com/blog/2016/03/16/… – antonu17 Jun 12 '16 at 5:36
  • 1
    This approach will cause problems later. If you disable the plug-in, the daily cron job will break as it's assuming it can log in with that plug-in. See my answer for details. – colan Dec 19 '16 at 14:27

I did it by running this command, right after installation:

$ sudo mysql_secure_installation

At first step, password is blank, so just press Enter.

I had the same issue on a raapberry pi with stretch. My solution was to create a new user with all privileges by doing the following:

sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root

MariaDB [(none)]> use mysql
MariaDB [mysql]> FLUSH PRIVILEGES;
GRANT ALL privileges ON mysql.* TO 'admin'@'localhost' with grant option;

Now I can login and us the user "admin" as superuser.

I hope this is going to help someone.

Just use sudo mysql -u root - that's it


Details: Newer versions authenticate to mysql using system authentication. So if you can sudo to the OS, it assumes you're db root too. You can confirm this by issuing sudo mysql -u root -e "USE mysql; SELECT User, Host, plugin FROM mysql.user;". You should see something like this (maybe with auth_socket in other distros)

+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+

Just use sudo mysql -u root and you'll be in.

Details: Newer versions authenticate to mysql using system authentication. So if you can sudo to the OS, it assumes you're db root too. You can confirm this by issuing sudo mysql -u root -e "USE mysql; SELECT User, Host, plugin FROM mysql.user;". You should see something like this (maybe with auth_socket in other distros)

+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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