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.
sql UPDATE mysql.user SET plugin='unix_socket' WHERE user='root'; flush privileges
alter user root@localhost identified by unix_socket
. The days of manipulating mysq.user tables are long gone. Use the SQL provided.