I've recently upgraded a system from Ubuntu 14.04 to 16.04 (mysql Ver 15.1 Distrib 10.0.36-MariaDB) and noticed root can get in without a password eg:mysql -u root is empty.

Tried setting a new pass via mysql with UPDATE mysql.user SET authentication_string = PASSWORD('supersekrit') WHERE User = 'root'; however this didn't disable the empty password. Also tried running mysql_secure_installation to no avail.

Found a post about setting the plugin field in the user row to 'NULL'. I did that, but now root cannot login at all.

I followed the link to percona in the post, which states the correct way to do this is to run the following: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';. This results in and error message -> ERROR 1064 (42000): You have an error in your SQL syntax...

Not quite sure what the correct way is to fix this. any ideas?

UPDATE: after setting the password for the root mysql account, Ubuntu 16.04 cron daemon is now throwing errors during logrotate. Looks like this issue is a rabbit hole. I've emptied the root password for now until I can figure out what's borked with logrotate. Yeesh.

error: 'Access denied for user 'root'@'localhost' (using password: NO)'

The reason that you can access as the root user is likely due to the fact that UNIX domain socket authentication is enabled. Given that the ALTER USER command failed, you are likely running a pre-10.2 MariaDB.

You can create a replacement user with the same grants as root by copying the grants in SHOW GRANTS FOR 'root'@'localhost':

MariaDB [test]> SHOW GRANTS FOR 'root'@'localhost';
| Grants for root@localhost                                           |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION       |
2 rows in set (0.00 sec)

For example, if I want to have my user named as backup-root with the password set to password, I would execute the following:

CREATE USER 'backup-root'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'backup-root'@'localhost WITH GRANT OPTION;
  • the unix_socket plugin was the reason root had an empty password. That added some confusion to the fact that two of the 16.04 VMs I was provisioning had different SQL stacks installed -- one Mysql, the other MariaDB. These two bundles are now different enogh that password handling is completely different between the two. for MariaDB: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); for Mysql: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'supersekrits';. I like your idea about creating a backup-root user however. Would also be a good fix. – Server Fault Nov 13 '18 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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